Write data into Google Spreadsheet w/ Java

Question

I'm lost on this one. I'm trying to connect Java with Google spreadsheet, and although the API's documentation is complete on retrieving data (and it is working fine), I am unable to figure out how to write into the spreadsheet.

Could anyone, please provide a full example (with the necessary imports and all) on how to do a very simple data entry into a Google Spreadsheet (say, enter "asdf" into the A1 cell of Sheet1)?

If a tutorial like this exists somewhere, I could not find it - any pointers would be much appreciated.

Thank you very much, Zsolt

Answer 1

OK, it took me a good few hours to figure it out finally, and the answer turns out to be easier than building an Ajax request from scratch. In the hope of saving hours and hours for others, here is the solution that worked for me.

Prereqs: I used the Quickstart tutorial of the Google Sheets API, to read from a table, that is pretty complex, but worked fine for me.

After the tutorial I needed to amend a few things, though

1, change the line

private static final List<String> SCOPES =
        Arrays.asList(SheetsScopes.SPREADSHEETS_READONLY);

to

private static final List<String> SCOPES =
        Arrays.asList(SheetsScopes.SPREADSHEETS);

for obvious reasons (as we want to write the table, not only read it.

2, Delete the stored credentials that are stored in your user directory in a folder called /.credentials/

One more note: there appears to be a method called

spreadsheets.values.update()

but I couldn't get that working, as it requires a valueInputOption parameter to be set, and hours of searching did not prove enough to find where can one set it.

So, finally, I ended up with a method called

spreadsheets.values.batchUpdate()

Here's the code full method that did the trick of writing "Hello World!" into a table cell for me (as for imports, I used the same as in the Quickstart tutorial above):

void WriteExample() throws IOException {
    Sheets service = getSheetsService();
    List<Request> requests = new ArrayList<>();

      List<CellData> values = new ArrayList<>();


      values.add(new CellData()
                .setUserEnteredValue(new ExtendedValue()
                        .setStringValue("Hello World!")));
        requests.add(new Request()
                .setUpdateCells(new UpdateCellsRequest()
                        .setStart(new GridCoordinate()
                                .setSheetId(0)
                                .setRowIndex(0)
                                .setColumnIndex(0))
                        .setRows(Arrays.asList(
                                new RowData().setValues(values)))
                        .setFields("userEnteredValue,userEnteredFormat.backgroundColor")));

        BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest()
                .setRequests(requests);
        service.spreadsheets().batchUpdate(spreadsheetId, batchUpdateRequest)
                .execute();
    }

The SheetId is the ID of the worksheet you are writing (it is always 0 for the first worksheet in a certain spreadheet, and you can get it from the URL for others: it's the part after #gid=

If you want to go into further complexities, like formatting or using formulas, you can - in this case, use the Java example provided here.

Hope it helps, Zsolt