How do you insert a single row into Google sheets using Sheets APIv4 Java

Question

If I want to insert a row into Google sheets using Java at a specified index, then write to that row, how would I go about that?

Answer 1

I was able to figure this one out after lots of googling, some struggling and adapting another answer.

Note: You can generate your own credentials.json. This goes in your "resources" folder in most IDEs.

Here's the code that I used, in full – the relevant method that you will need is the insertRow method below, which is invoked from writeTest Under normal circumstances I would just post the method in question, but the "quickstart" in the sheets API is definitely lacking so here's the full source:

Class & Imports

import com.google.api.client.auth.oauth2.Credential; import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp; import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver; import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow; import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets; import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.JsonFactory; import com.google.api.client.json.jackson2.JacksonFactory; import com.google.api.client.util.store.FileDataStoreFactory; import com.google.api.services.sheets.v4.Sheets; import com.google.api.services.sheets.v4.SheetsScopes; import com.google.api.services.sheets.v4.model.*;  import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.security.GeneralSecurityException; import java.util.Arrays; import java.util.Collections; import java.util.List;  public class SheetsTest {     private static final String APPLICATION_NAME = "Metrics project";     private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();     private static final String TOKENS_DIRECTORY_PATH = "tokens";     private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);     private static final String CREDENTIALS_FILE_PATH = "/credentials.json"; 

getCredentials:

private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException{     InputStream in = SheetsTest.class.getResourceAsStream(CREDENTIALS_FILE_PATH);     GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));     GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(             HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)             .setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))             .setAccessType("offline")             .build();     LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();     return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user"); } 

writeTest

//Creates a new row at row 2 then writes data to it... static void writeTest(Report r, String sheetID, String range)throws GeneralSecurityException, IOException{     final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();     ValueRange requestBody = requestBuilder(r, range);     Sheets sheetsService = new Sheets.Builder(HTTP_TRANSPORT,JSON_FACTORY, getCredentials(HTTP_TRANSPORT))             .setApplicationName(APPLICATION_NAME)             .build();     insertRow(sheetsService, sheetID);     Sheets.Spreadsheets.Values.Append request =             sheetsService.spreadsheets().values().append(sheetID, range, requestBody);     request.setValueInputOption("RAW");     //request.setInsertDataOption("INSERT_ROWS");     AppendValuesResponse resp = request.execute();      System.out.println(resp); } 

insertRow

//Inserts row at index, this is hardcoded to row 2 (0-indexed) private static void insertRow(Sheets ss, String sheetID)throws IOException{     InsertDimensionRequest insertRow = new InsertDimensionRequest();     insertRow.setRange(new DimensionRange().setDimension("ROWS").setStartIndex(1).setEndIndex(2).setSheetId(0));      BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest().setRequests(Arrays.asList(             new Request().setInsertDimension(insertRow)     ));     ss.spreadsheets().batchUpdate(sheetID, r).execute(); } 

requestBuilder

//Populate ValueRange static ValueRange requestBuilder( Report r, String range){     ValueRange v = new ValueRange()         .setValues(Arrays.asList(             Arrays.asList(r.value1),             Arrays.asList(r.value2),             Arrays.asList(r.value3),             Arrays.asList(r.value4),             Arrays.asList(r.value5)         ))         .setMajorDimension("ROWS")         .setRange(range)     ;     return v; }