Interact with Google Sheets from Java

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

1. Overview

Google Sheets provides a convenient way to store and manipulate spreadsheets and collaborate with others on a document.

Sometimes, it can be useful to access these documents from an application, say to perform an automated operation. For this purpose, Google provides the Google Sheets API that developers can interact with.

In this article, we’re going to take a look at how we can connect to the API and perform operations on Google Sheets.

3. Authorization

The Google Sheets API requires OAuth 2.0 authorization before we can access it through an application.

First, we need to obtain a set of OAuth credentials, then use this in our application to submit a request for authorization.

3.1. Obtaining OAuth 2.0 Credentials

To obtain the credentials, we’ll need to create a project in the Google Developers Console and then enable the Google Sheets API for the project. The first step in the Google Quickstart guide contains detailed information on how to do this.

Once we’ve downloaded the JSON file with the credential information, let’s copy the contents in a google-sheets-client-secret.json file in the src/main/resources directory of our application.

In our example, we’re setting the SPREADSHEETS scope since we want to access Google Sheets and using an in-memory DataStoreFactory to store the credentials received. Another option is using a FileDataStoreFactory to store the credentials in a file.

Here, we’re first creating a ValueRange object with multiple rows containing a list of expenses for two months.

Then, we’re using the update() method to build a request that writes the values to the spreadsheet with the given id, starting at the “A1” cell.

To send the request, we’re using the execute() method.

If we want our value sets to be considered as columns instead of rows, we can use the setMajorDimension(“COLUMNS”) method.

The “RAW” input option means the values are written exactly as they are, and not computed.

When executing this JUnit test, the application will open a browser window using the system’s default browser that asks the user to log in and give our application permission to interact with Google Sheets on the user’s behalf:

A requirement for the application to be able to view or edit the spreadsheet is that the signed-in user has a view or edit access to it. Otherwise, the request will result in a 403 error. The spreadsheet we use for our example is set to public edit access.

Now, if we check the spreadsheet, we’ll see the range “A1:B6” is updated with our value sets.

Let’s move on to writing to multiple disparate ranges in a single request.

5.2. Writing to Multiple Ranges

If we want to update multiple ranges on a sheet, we can use a BatchUpdateValuesRequest for better performance:

Here, we’re creating an UpdateSpreadSheetPropertiesRequest object which specifies the new title, a CopyPasteRequest object which contains the source and destination of the operation and then adding these objects to a List of Requests.

Then, we’re executing both requests as a batch update.

Many other types of requests are available to use in a similar manner. For example, we can create a new sheet in a spreadsheet with an AddSheetRequest or alter values with a FindReplaceRequest.

We can perform other operations such as changing borders, adding filters or merging cells. The full list of Request types is available here.

9. Conclusion

In this article, we’ve seen how we can connect to the Google Sheets API from a Java application and a few examples of manipulating documents stored in Google Sheets.

I would like to store a spreadsheets column’s type, but it seems I don’t get back this information. I mean how can I decide that a column stores int values or strings? I know nothing about that. I want to store its metadata like in case of a relational database, The excel is similar each sheet a table each column header a field. But in case of an database I know the types.

First, you have to get the grid data for the spreadsheet:Sheets.Spreadsheets.Get request = sheetsService.spreadsheets().get(SPREADSHEET_ID);
request.setIncludeGridData(true);
Spreadsheet spreadsheet = request.execute();
Then go through its elements:spreadsheet.getSheets().get(sheetNumber).getData().get(gridNumber).getRowData().get(rowNumber).getValues().get(cellNumber).getEffectiveFormat()
That can contain a numberFormat and textFormat if these are set in the spreadsheet.

Hope that helps.

You Must Be Logged In To Vote0You Must Be Logged In To Vote

1 year ago

Guest

Steve

Hi Loredana,

Here is what I wanted :numberFormat”:{“pattern”:”yyyy.MM.dd.”,”type”:”DATE”},
Multumesc frumos 🙂

You Must Be Logged In To Vote0You Must Be Logged In To Vote

1 year ago

Guest

Arnauv

Can I plz be helped for Maven/Gradle and told the steps accordingly.
Honestly, to implement spreadsheet-connectivity, I can work on any app/platform.
I need help with things like “where to enter dependency” and “where to add all of this code ” etc.
Any help appreciated