Using Google Sheet As Web App Data Source

I wanted to use a Google Sheet in my personal account as the data source for a web application. I thought it would be simple. I was wrong.

To be fair, once I had spent more hours researching than I’d care to admit, it was reasonably straight-forward to connect to and read & write values on my Google Sheet. The main issue I had was that there was not a ready supply of instructions for my exact use case.

The remainder of this blog post will describe the steps I eventually took.

The Problem

“I have a spreadsheet” – Martin Luther King Jr.

I want to consume data from an existing spreadsheet with a web service, which will in turn be consumed by a desktop application. The data is entered by users of varying levels of computer literacy and a Google Sheet is ideal. As the spreadsheet is in my account, I held the naïve opinion that I might be able to include a Maven dependency in my Java Spring Boot application, supply a username and password and off I go.

Unfortunately, things are not so easy. Using the latest version of the Google API (v4 at the time of writing), it seems one does not simply access spreadsheets using one’s usual login, as you would via the Google Sheets site.

Create a Service Account & Enable the Google Sheets API

It turns out one accesses the Google API via a service account. This can be setup by logging in to Google’s developer console. In the ‘IAM and Admin’ section, I created a project and then selected the ‘Service Accounts’ menu item on the left, where I created a new account. I named the account appropriately for my application and gave it project editor permissions in the menu that appears. I assume this means that it can read and write to files that it accesses (the account does seem to be able to read and write spreadsheet data).

Next, we have to enable the Google Sheets API to allow its use. This can be achieved by accessing the API manager, via the menu in the top-left of the screen. This brings up the API dashboard where one can view graphs and stats of your APIs’ usage, error rates, etc. A blue ‘Enable API’ link to the right of the ‘Dashboard’ title will reveal a list of available APIs; ‘Sheets API’ is listed in the ‘Google Apps APIs’ section. Click this and then enable on the next screen.

Authorisation

“If your name’s not down, you’re not coming in” – Peter Stringfellow

To connect my application to Google’s API, I need to use two-legged authorisation. Three-legged authorisation is used for applications with a front-end, to allow the user’s to grant access to data from their own account. My web service is a RESTful API with no front-end and only needs to access my own data, so authorisation can be achieved though reuse of the same key. A more detailed description of two- and three- legged authorisation can be found here.

In the ‘Credentials’ section of the API manager, the ‘Create Credentials’ drop down has the ‘Service account key’ option. On the next screen, I selected my new service account and created a JSON key. A text file containing the key is downloaded. This cannot be re-downloaded, so if lost, a new key must be generated.

Share the Spreadsheet

At first, once I managed to connect to the Google API via code, I could not ‘see’ the spreadsheet I wanted to use. The contents of my account appeared to be empty, presumably as we’re accessing via the service account. To be able to access the spreadsheet, then, I simply accessed Google Sheets via its web front end, logged in as usual and shared the spreadsheet in question with the email address of my new service account, which can be readily viewed in the service accounts section of the Google developer console.

Now everything is setup on the Google side, time to write some code.

Project Dependencies

In my Java project, I need to include three dependencies for authorisation and the Sheets API. The relevant section of my Maven pom.xml configuration file is shown below:

<dependency>
  <groupId>com.google.apis</groupId>
  <artifactId>google-api-services-sheets</artifactId>
  <version>v4-rev33-1.22.0</version>
</dependency>

<dependency>
  <groupId>com.google.oauth-client</groupId>
  <artifactId>google-oauth-client-jetty</artifactId>
  <version>1.22.0</version>
</dependency>

<dependency>
  <groupId>com.google.api-client</groupId>
  <artifactId>google-api-client</artifactId>
  <version>1.22.0</version>
</dependency>

This, and most of the remaining code, is adapted from Google’s Java quick start guide.

Create a Credential & Sheets Application

I created a couple of methods to perform some of the setup required at runtime to access the spreadsheet. First, the key downloaded earlier is used to create a GoogleCredential authorisation object:

private GoogleCredential getCredential(String key) throws IOException {
    InputStream stream = new ByteArrayInputStream(key.getBytes(StandardCharsets.UTF_8));

    return GoogleCredential.fromStream(stream).createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
}

For simplicity, I’ve placed the authorisation key in a string within my code. This might not be advisable for your project.

The next method uses this credential to create a spreadsheet application object, of type Sheets, through which I can access my spreadsheet:

private Sheets getSheetsApplication(GoogleCredential credential) throws GeneralSecurityException, IOException {
    HttpTransport http = GoogleNetHttpTransport.newTrustedTransport();
    JsonFactory json = JacksonFactory.getDefaultInstance();

    return new Sheets.Builder(http, json, credential)
                     .setApplicationName("MyApplication")
                     .build();
}

I use these helper methods at application start up to setup the connection to my Google Sheets API. In the constructor of my data access object, I simply execute the following statement:

sheetsApplication = getSheetsApplication(getCredential(Key.key));

Read and Write Data

With a couple more helper methods, I now have a nice abstraction over a Google Sheet that allows me to easily push and pull data between my application code and an online spreadsheet.

private ValueRange getSheetRange(String range) throws IOException {
    return sheetsApplication.spreadsheets()
                            .values()
                            .get(sheetId, tabId + "!" + range)
                            .execute();
}

This method takes in an Excel-style range string to specify which section of the sheet to grab, e.g. “A1” will grab a single cell and “A2:B3” will grab a 2 x 2 group of cells. The Google Sheet the method tries to access is, in my project, held in class field sheetId. You can find the id of your spreadsheet from the URL when you view it in a browser (long alphanumeric string). The name of the tab within a spreadsheet to be accessed is held in field tabId, which is concatenated with an exclamation mark and the required range.

The ValueRange type contains a 2-dimensional list of objects representing the data in the corresponding spreadsheet cells. I found that if a full row of the data is blank in the spreadsheet, there was no corresponding row in the ValueRange object. Therefore, when iterating over the data, it’s probably best not to use the size of the requested range, but the size of the data returned as a guard on the iterator. The following method further abstracts to grab a single cell’s value:

private String getSingleCellValue(String cellReference) throws IOException {
    ValueRange valueRange = getSheetRange(cellReference);

    List<List<Object>> values = valueRange.getValues();

    if (values == null || values.size() == 0) {
        return null;
    }

    List<Object> row = values.get(0);

    if (row == null || row.size() == 0) {
        return null;
    }

    return (String) row.get(0);
}

To write data, I create a ValueRange object and add data to it in the form of a 2-dimensional list of objects, as in the following method with which I can write a value to a single cell:

private void setSingleCellValue(String cellReference, String content) throws IOException {
    List<List<Object>> writeData = new ArrayList<>();
    List<Object> writeDataRow = new ArrayList<>();
    writeDataRow.add(content);
    writeData.add(writeDataRow);

    ValueRange valueRange = new ValueRange().setValues(writeData)
                                            .setMajorDimension("ROWS");

    sheetsApplication.spreadsheets()
                     .values()
		     .update(sheetId, tabId + "!" + cellReference, valueRange)
		     .setValueInputOption("RAW")
		     .execute();
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s