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

[First result](https://developers.google.com/google-apps/spreadsheets/) I found seems to have info on how to write out cells. — CodeChimp, Jul 20 '16 at 16:50
Just explore the API/Java DOCS. You might be able to figure out — JavaHopper, Jul 20 '16 at 18:25
CodeChimp, you are linking to legacy code. I'd be interested in a working example with the current, up to date version. Thank you — Zsolt Balla, Jul 20 '16 at 21:22

score 12 · Answer 1 · answered Jul 22 '16 at 10:42

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

score 12 · Answer 2 · answered Jul 25 '16 at 00:39

Here is a modified version of the quick start tutorial code, to perform a write.

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.HttpTransport;
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.ValueRange;
import java.io.*;
import java.util.*;

public class SheetsIntegration {

    private static HttpTransport transport;
    private static JacksonFactory jsonFactory;
    private static FileDataStoreFactory dataStoreFactory;

I get a permissions warning at this line, but it's not fatal

    private static final java.io.File DATA_STORE_DIR = new java.io.File(System.getProperty("user.home"), ".credentials/sheets.googleapis.com.json");

Quick start tutorial uses readonly scope instead

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

    public SheetsIntegration() {
        try {
            transport = GoogleNetHttpTransport.newTrustedTransport();
            dataStoreFactory = new FileDataStoreFactory(DATA_STORE_DIR);
            jsonFactory = JacksonFactory.getDefaultInstance();

            service = getSheetsService();
        } catch (Exception e) {
            // handle exception
        }
    }

Per the quick start tutorial, you'll need to download the certification file from Google.

public static Credential authorize() throws IOException {
    // Load client secrets.
    File cfile = new File("certs/cert.json");
    cfile.createNewFile();
    GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(jsonFactory, new InputStreamReader(new FileInputStream(cfile)));

    // Build flow and trigger user authorization request.
    GoogleAuthorizationCodeFlow flow =
            new GoogleAuthorizationCodeFlow.Builder(
                    transport, jsonFactory, clientSecrets, scopes)
                    .setDataStoreFactory(dataStoreFactory)
                    .setAccessType("offline")
                    .build();
    Credential credential = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user");
    return credential;
}

public static Sheets getSheetsService() throws IOException {
    Credential credential = authorize();
    return new Sheets.Builder(transport, jsonFactory, credential)
            .setApplicationName("INSERT_YOUR_APPLICATION_NAME")
            .build();
}

public void writeSomething(List<Data> myData) {

    try {
        String id = "INSERT_SHEET_ID";
        String writeRange = "INSERT_SHEET_NAME!A3:E";

        List<List<Object>> writeData = new ArrayList<>();
        for (Data someData: myData) {
            List<Object> dataRow = new ArrayList<>();
            dataRow.add(someData.data1);
            dataRow.add(someData.data2);
            dataRow.add(someData.data3);
            dataRow.add(someData.data4);
            dataRow.add(someData.data5);
            writeData.add(dataRow);
        }

        ValueRange vr = new ValueRange().setValues(writeData).setMajorDimension("ROWS");
        service.spreadsheets().values()
                .update(id, writeRange, vr)
                .setValueInputOption("RAW")
                .execute();
    } catch (Exception e) {
        // handle exception
    }
}

One other note - I had to add servlet-api.jar to my project.

score 2 · Answer 3 · answered Jul 21 '16 at 07:29

Make use of XmlHttpRequest request.

I'll give you a quick demo where you will actually write on a google-spreadsheet. You will then apply the concepts here using your preferred language, Java.

open a google-spreadsheet to write on. Get it's spreadsheet ID
Go to oauth playground and navigate to Google Sheets API v4
Choose https://www.googleapis.com/auth/drive permission. Click Authorize APIs. Allow the permission.
On Step 2, press Exchange authorization code for tokens button.
On Step 3, Paste this on Request URI:

https://sheets.googleapis.com/v4/spreadsheets/{SpreadsheetID}/values/Sheet1!A1?valueInputOption=USER_ENTERED

HTTP Method is PUT.

Place this inside your Enter Request Body

{
  "range":"Sheet1!A1",
  "majorDimension": "ROWS",
  "values": [
    ["Hello World"]
  ],
}

Click Send the Request. If you get 200 OK response, expect "Hello World" to be written in A1 cell of your spreadsheet. You can read more about writing data in Sheets v4 here.

How to do this in Java? Learn about AJAX or XHR implementation in Java. Check this tutorial to get you started.

Write data into Google Spreadsheet w/ Java

3 Answers3

Linked