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

Answer 2

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.

Answer 3

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.

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

5. 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.