As a part of the development of a data analytics project, we had to implement a dashboard app with metrics and KPIs to track and analyze streaming service data. In this article, we are going to describe how we've made use of the Google Sheets API for Java to implement read and write operations directly into sheets that are used by key stakeholders to track data. This allowed us to improve productivity by automating work that otherwise had to be done manually.
The data ingestion task we had to fulfill implied to efficiently collect, update and synchronize metrics from different streaming platforms into a unique database. To address this, as aforementioned, we have developed a Java application that uses Google Sheets API to collect some of these metrics.
To keep all the information synchronized it was necessary to read different spreadsheets and write information collected from our database to these spreadsheets too. The app in charge of this syncing runs scheduled jobs with a flexible data update frequency.
The first step to programmatically interact with a sheet is to initiate the API. This is done via a client service that was created to handle read, write, and update requests. Using a Google Service account, along with the necessary credentials, we could manage the connections automatically. This kind of Google account belongs to the project, not to an individual user, which simplifies credential management
To authenticate with Google Sheets API, the first was to create the service account in the Google Cloud Console, download the credentials and securely store it in the application.
Once the connection was established, three methods were defined for principal interactions: read, write and update cell values. It's important to give the Service Account the necessary permissions in the spreadsheet to do these interactions.
To work with a spreadsheet in general, it is necessary to have the spreadsheet id and range values of interest.
The range values need to be written in A1 notation, a syntax used to define a cell or range of cells with a string that contains the sheet name plus the starting and ending cell coordinates using column letters and row numbers (e.g. Sheet1!A:A). Below we detail some common tasks that we had to do via the API.
Case 1. Reading data from a single range
Case 2. Reading from multiple ranges
In this case, ValueRange contains a property named values with a list for rows with all the values in it, let's use the ranges defined above as an example to see the result.
These are our cells of interest in the “Sheet1” for both examples
For spreadsheets with large datasets, a flag system was implemented to track the last read row. The app checks the database to know exactly which row was the last read, avoiding unnecessary repeated readings.
Writing data into a spreadsheet involves creating a body and configuring some input options.
For instance, let's assume we want to write a list of supported programming languages in the Google Code Block tool and then persist them in a database (we will focus the example on the Google Sheets API process involved, not in the data ingestion). First we define the range and set the values.
We omitted the step where the B column is fulfilled with new values, but the process it's exactly the same.
To update a cell value, we need to give to the update method the cell value to update.
Let's assume we have a process that reads the spreadsheet, gets the values in the specific row and compares them with the ones that are in the database. Then iterates through every row in the spreadsheet and updates the value if the condition is acceptable.
That will trigger the write operation in the sheet and update the specific cell values we decide on.
In this article, we've examined how to implement spreadsheet reading and writing using Java Google Sheets API. This integration was applied successfully in a project in which we needed to collect, process and update data in sheets that are currently being used by stakeholders in the product team.
