Push data to Google Sheets from your Java app
Tags: nps, googlesheets, vaadin, java
Published: 2023-06-29 14:46:01

---
title: Push data to Google Sheets from your Java app
published: true
description: Vaadin and Google Sheets - Using spreadsheet as the DB for a Java application. 
tags: nps, googlesheets, vaadin, java
series: Creating an NPS Feedback App
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
# published_at: 2023-06-29 10:44 +0000
---

The [previous article](https://dev.to/samiekblad/creating-custom-component-for-nps-feedback-4p4e) explained how to make a reusable [NPS feedback component](https://vaadin.com/directory/component/nps). 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p29oppx2l701jgz7f3v2.png)

## 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](https://console.cloud.google.com/iam-admin/serviceaccounts) 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](https://console.cloud.google.com/apis/library/sheets.googleapis.com) for the project.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yzocqswha1fcqj9nhw0r.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yixl6s820wr19c5l3yvj.png)

## 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](https://github.com/samie/nps/blob/v24/demo/src/main/java/com/example/application/data/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:
```
<dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-sheets</artifactId>
    <version>v4-rev612-1.25.0</version>
</dependency>    
<dependency>
    <groupId>com.google.auth</groupId>
    <artifactId>google-auth-library-oauth2-http</artifactId>
    <version>1.19.0</version>
</dependency>
<dependency>
    <groupId>com.google.http-client</groupId>
    <artifactId>google-http-client-gson</artifactId>
    <version>1.43.3</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](https://www.baeldung.com/google-sheets-java-client), 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] (https://developers.google.com/sheets/api/quickstart/java#configure_the_oauth_consent_screen). 

If you want to try locally, you'll find the [sample code](https://github.com/samie/nps/tree/v24/demo) in GitHub and [the sample feedback spreadsheet](https://docs.google.com/spreadsheets/d/1aTfU2_XuZU-HgUhSBu4_oB_gB4hhro-RzNsdN9_8YX8/edit?usp=sharing) in Google Sheets.