Push data to Google Sheets from your Java app

The previous article explained how to make a reusable NPS feedback component. After writing the article, I made a few tweaks to the component:

• A square style for the buttons
• Customizable texts

These are good additions to make the component usable in any application but to get further, we need something outside the UI: Store the NPS feedback data somewhere in the cloud.

Use the spreadsheet for NPS feedback

Isn't that obvious when you say that aloud? After all, people who collect this type of user feedback are also familiar with spreadsheets. Let's keep it simple for them.

For the scope of this NPS application, I used Google Sheets, which is more configuration than programming. The spreadsheet layout is simple enough: timestamp, anonymous user id, and the NPS score columns. I also added a "Feedback" column for future improvements.

Creating a Google Service Account

This is the configuration part you must do in Google Cloud. Service Accounts are the application-specific counterpart of user accounts. To create one to access Google Sheets:

• Select or create a new project, and add a new Service Account without other roles or permissions.
• Go to "Manage Keys", click "Add key", and create and download a new Service Account authentication JSON key. Store it somewhere safe outside the code repository.
• Enable the Google Sheets API for the project.

Authorize the Service Account to access your sheet

Now that you have your Service Account set up, we can give it access to the document you created:
Copy the email of your Service Account. It is the one like 'app-name@project-name-123456.iam.gserviceaccount.com'
Grant editor permission to this account in your NPS Spreadsheet.

Libraries and code

We are back in IDE. One service account and the spreadsheet has been created. It's time to access them from your application code.

When building new functionality, I like to start by "writing the code I like". This helps me design the API I need for the use case. In this case, I started with the following in my ValueChangeListener:

String userId = "" + UI.getCurrent().hashCode(); // for testing only
int npsScore =  e.getValue(); // User fee
FeedbackSheet sheet = new FeedbackSheet();
sheet.append(userId, npsScore);

So, most of the implementation would go to the FeedbackSheet.java. Logically it does the following things:

• Read the ServiceAccountCredentials from a JSON file
• Creates a Sheets instance to run authorized actions for a specified sheet. An append method adds a new row to the first sheet.

This is the code part. To make this work, you need to add a couple of dependencies to pom.xml. They are the Google Java libraries for Google Sheets, OAauth2 and JSON parsing:

Update 2024-10-24: latest versions of the libraries.

<dependency>
    <groupId>com.google.api-client</groupId>
    <artifactId>google-api-client</artifactId>
    <version>2.7.0</version>
</dependency>
<dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-sheets</artifactId>
    <version>v4-rev20241008-2.0.0</version>
</dependency>
<dependency>
    <groupId>com.google.oauth-client</groupId>
    <artifactId>google-oauth-client</artifactId>
    <version>1.36.0</version>
</dependency>
<dependency>
    <groupId>com.google.http-client</groupId>
    <artifactId>google-http-client-gson</artifactId>
    <version>1.45.0</version>
</dependency>

Findings and Conclusion

Using Google Sheets to store data is a manageable amount of Java code - once you find it. There are a few good articles like this one from Baeldung, but getting the code to match the configuration takes some work. Also, Google Cloud is a living thing; many other articles I found outdated in 2023.

The way I use it here has the benefit of the spreadsheet owner granting and revoking access to the documents to an app, but it does not offer any fancy impersonation possibilities which would need a OAUth consent dialog.

If you want to try locally, you'll find the sample code in GitHub and the sample feedback spreadsheet in Google Sheets.