How to assign a value to a ValueRange variable in java?

Question

I am trying to implement the Google sheets API writing feature into my app using following code from this web page but unable to figure out what and how to assign a value to a ValueRange variable.

public class SheetsExample {
public static void main(String args[]) throws IOException,
GeneralSecurityException {
// The ID of the spreadsheet to update.
String spreadsheetId = "my-spreadsheet-id"; // TODO: Update placeholder value.

//The A1 notation of the values to update.
String range = "my-range"; // TODO: Update placeholder value.

// TODO: Assign values to desired fields of `requestBody`. All existing
// fields will be replaced:
ValueRange requestBody = new ValueRange();

Sheets sheetsService = createSheetsService();
Sheets.Spreadsheets.Values.Update request =
    sheetsService.spreadsheets().values().update(spreadsheetId, range, requestBody);

UpdateValuesResponse response = request.execute();

// TODO: Change code below to process the `response` object:
System.out.println(response);
  }



 public static Sheets createSheetsService() throws IOException, GeneralSecurityException {
    HttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
    JsonFactory jsonFactory = JacksonFactory.getDefaultInstance();

// TODO: Change placeholder below to generate authentication credentials. See
// https://developers.google.com/sheets/quickstart/java#step_3_set_up_the_sample
//
// Authorize using one of the following scopes:
//   "https://www.googleapis.com/auth/drive"
//   "https://www.googleapis.com/auth/drive.file"
//   "https://www.googleapis.com/auth/spreadsheets"
GoogleCredential credential = null;

return new Sheets.Builder(httpTransport, jsonFactory, credential)
    .setApplicationName("Google-SheetsSample/0.1")
    .build();
}  
}

Answer 1

Refer the API Guide Google Sheets API v4

First, You need to create a ValueRange instance. You can do this by ValueRange.setValues() method. As @Ben has shown in his answer, you can create the ValueRange instance in the following way. https://stackoverflow.com/a/46171564/2999358

ValueRange requestBody = new ValueRange(); 
requestBody.setValues(
    Arrays.asList(
      Arrays.asList("Row 1 Cell 1", "Row 1 Cell 2", "Row 1 Cell 3"),
      Arrays.asList("Row 2 Cell 1", "Row 2 Cell 2", "Row 2 Cell 3")));

As you can see in the API Guide, setValues() requires an array of arrays

public ValueRange setValues(java.util.List<java.util.List<java.lang.Object>> 
values)

The outer List represents all the data (basically the whole spreadsheet), while the inner Lists represents ROWS. Each item in these Lists represents a CELL.

After you set the values you want to write to your spreadsheet, you can use the ValueRange object (in your case requestBody variable) to update the spreadsheet.

If you have followed https://developers.google.com/sheets/api/quickstart/android quick start guide, do the following changes to that code. Since that code only shows you how to fetch data from a spreadsheet.

Change the following line,

private static final String[] SCOPES = { SheetsScopes.SPREADSHEETS_READONLY }; 

to this,

private static final String[] SCOPES = { SheetsScopes.SPREADSHEETS };

This will enable you to view and manage your spreadsheets in Google Drive.

And inside the getDataFromAPI() method in this guide, contains following code,

ValueRange response = this.mService.spreadsheets().values()
                .get(spreadsheetId, range)
                .execute();

which is used to fetch data from the spreadsheet. Change this to following,

ValueRange response = this.mService.spreadsheets().values()
                    .update(spreadsheetId, range, valueRange)
                    .setValueInputOption("RAW")
                    .execute();

Setting ValueInputOption to "RAW" will make the values you enter to be stored as-is. If you use "USER_ENTERED", the values you enter will be parsed as you are entering a value to a cell from the Google Spreadsheet UI. Which will do conversions like strings to Numbers, Dates etc. Basically all the rules that will apply when you use the Google spreadsheet UI. This is up-to you how you want to handle.

Answer 2

I think what you are actually trying to do here is update an existing range with a set of values?

In that case you can do that like this :

   ValueRange requestBody = new ValueRange(); 
   requestBody.setValues(
        Arrays.asList(
          Arrays.asList("Row 1 Cell 1", "Row 1 Cell 2", "Row 1 Cell 3"),
          Arrays.asList("Row 2 Cell 1", "Row 2 Cell 2", "Row 2 Cell 3")));

Hope that helps, in practice you would build up your lists programatically.

Answer 3

I leave my contribution with a concrete example that does not throw compilation errors in the answer:

https://stackoverflow.com/a/48832281/1621848

Thanks.