TNS
VOXPOP
You’re most productive when…
A recent TNS post discussed the factors that make developers productive. You code best when:
The work is interesting to me.
0%
I get lots of uninterrupted work time.
0%
I am well-supported by a good toolset.
0%
I understand the entire code base.
0%
All of the above.
0%
I am equally productive all the time.
0%
Data / Frontend Development

How to Use Google Sheets as a Database with React and Serverless

How to use Google Sheets as a database. Choose this method over a more traditional Database solution for one reason: Data retrieval.
Oct 15th, 2022 7:00am by
Featued image for: How to Use Google Sheets as a Database with React and Serverless
Image via Shutterstock

In this tutorial I’ll be explaining how to use Google Sheets as a database, to store the results of a user poll. I’ve used this Google Sheets approach for a number of marketing campaigns. I chose this method over a more traditional Database solution for one reason: Data retrieval.

It does of course entirely depend on your requirements, but having the ability to simply share a Google Sheet with a technical or non-technical member of my team — so they can easily see captured data — has, on more than one occasion, proved really valuable.

Google Sheets as a Database

Screenshots of Gatsby and Next.js example User Poll apps with bar chart results.

Data is sent from the browser to a Serverless Function that securely posts to a Google Sheet, which stores the data. To use Serverless Functions in React you can use either Next.js or Gatsby.

On the following links, you will find a Live Preview and GitHub Repository for the completed User Polls, using both frameworks.

  • Gatsby
  • Next.js

I won’t be covering how to get started with either framework, so please consult the docs if you’re not familiar with these technologies.

What Are Serverless Functions?

Serverless functions enable frontend developers to add powerful “backend” logic to our apps just by writing JavaScript — no DevOps, no servers, just results. Jason Lengstorf

Using Serverless Functions allows you to “post” data from your frontend to your “backend” in the same project. The Serverless Function can then securely “post” to a Database to store data.

Since all the business logic is on the “backend”, the API keys or secrets required to make the database connection are never exposed to the frontend/user/(the browser).

The way to achieve this differs slightly between the frameworks, but the general idea is to have a “Page” that handles sending the request to the “API”.

Example Gatsby Serverless Function

The “Page” is saved in src/pages/some-page.js and sends a request to the “API” saved in src/api/some-endpoint.js. 

You can see the src for the Gatsby example using the following links.

some-page.js

some-endpoint.js

A successful “post” would display the following, returned in Jsx using an HTML <pre /> element.

Example Next.js Serverless Function

The “Page” is saved in pages/some-page.js and sends a request to the “API” saved in pages/api/some-endpoint.js

You can see the src for the Next.js example using the following links.

The code for the Page and API are the same for both frameworks; the difference between the two is the directory structure.

In these examples, the Serverless Function simply returns a string using the name value it receives as a query parameter. In the User Poll example apps, the Serverless Function is used to send data on to a Google Sheet to be saved and securely stored. More about that in a moment.

How to Setup Google Sheets

Before you get going with the Google Sheet, you’ll first need to set up what Google refers to as a Service Account. You can read more about Service Accounts in the Google docs: Understanding Google Service Account

Create a Google Cloud Project

Step one is to create a project. You’ll configure this project so that it contains access to the Google Sheets API, via a Service Account user/email address, and use it to generate the required API keys needed to “post” data to the Google Sheet.

The steps to create a Google Cloud project are outlined in the following guide: Grant an IAM role by using the Google Cloud console.

  1. From the above link start by clicking the Go to Project Selector button.
    Go to project selector
  2. Now click CREATE PROJECT.
    Projects Dashboard
  3. Give your project a name and click CREATE.
    Project name
  4. You should now be redirected to the Project dashboard. Click on the API’s & Services navigation item in the sidebar.
    APIs & Services
  5. Now you can enable access to the Google Sheets API. Click the + ENABLE APIS AND SERVICES button.
    Enable APIs and Services
  6. Search for “sheets” and select the Google Sheets API.
    Search Google Sheets API
  7. To enable access to the Google Sheets API, click the “ENABLE” button.
    Enable Google Sheets API
  8. To create the necessary credentials for the Google Sheets API, click the CREATE CREDENTIALS button.
    Create Credentials for Google
  9. Select the Google Sheets API and check the Application Data radio button. For the purposes of this tutorial you can answer No to the last question.
    Credentials Setup Options
  10. You can skip the Your Credentials step and click the DONE button when you’re ready.
    Credentials Setup Done
  11. Click on the Service Accounts navigation item in the sidebar. Create a service account for your project and give it a Service Account ID.
    Service Account Options
  12. Set the Role to Owner. You can skip the last step. Click DONE when you’re ready.
    Service Account Done
  13. Click on the IAM navigation item in the sidebar. Check that the permissions for service account details are correct.
    IAM Permissions
  14. Click on the Service Accounts navigation item in the sidebar. Then click the more dots and select Manage Keys.
    Service Account API Keys
  15. On the KEYS tab, click the ADD KEY button then click Create new key.
    Service Account API Keys
  16. Select JSON as the key type, then click the CREATE button to download a .json file containing your keys.
    Service Account API Keys
  17. The .json file you’ve just downloaded will look a little like the below. The two keys you’ll need to save as environment variables are. priviate_key and client_email.
    API Keys.json

In the example projects, I created the following environment variables.

You can read more about configuring environment variables for both Gatsby and Next.js on the following links.

Creating a Google Sheet

Create a new Google Sheet and make a note of the id in the URL address bar.

Screen shot of Google Sheet with highlighted Sheet Id from URL address bar

Screen shot of Google Sheet with highlighted Sheet Id from URL address bar

Add Column Headings To Google Sheet

You can add as many headings as you like. It’s worth noting that spaces are replaced with underscores. I’ll explain why in a later step.

Screen shot of Google Sheet with highlighted heading rows

Screenshot of Google Sheet with highlighted heading rows.

Share The Google Sheet

With your new Sheet created, share it with your Service Account email address / the client_email from the .json file. Make sure the Service Account has Editor access.

Screen shot of Google Sheet "Share" modal

Screenshot of Google Sheet “Share” modal.

Creating a User Poll

There are two parts to creating the User Poll; The Page, and the API.

  • The page contains the UI elements to allow the user to vote and the visuals to display the store values from the Google Sheet.
  • The API receives requests from the page, posts to the Google Sheet and performs some minor calculations before returning the data.

Creating the Page

Create a new Page and add the following.

Page Config and useState Values

In this step, you’re creating a config that is used for the text on the voting buttons and an id that you’ll use later to target the correct cell in the Google Sheet. As mentioned earlier, the id’s shouldn’t contain spaces.

There are also a number of useState values that will hold the state of the application before, during and after a request has been made to the API.

Adding the ‘Click’ Handler Function

This function accepts an id and passes it on to the API request as a query parameter; also called id.

If the response is ok, you store the result in the useState value. The other states for isSubmitting and hasVoted are also updated.

If the response errors, the error state is updated.

Adding the Interface

The interface has two states. The first are the buttons that allow a user to vote, the second are the results. You can use the hasVoted state value to determine which state to render.

To create the buttons you can iterate over the config options. The name is the text that appears on the button and the id is used as an argument for the onClick function.

To create the results you can iterate over the results from the API, display each of the values and scale the “bar chart” using the percent. Additional styling could be added by using the isMax value, which will either be true or false (depending on the amount of votes).

Creating the API

Install the google-spreadsheet Dependency

Create the Serverless Function

  • Import (require) the google-spreadsheet dependency.
  • Create a new const called doc and using the GoogleSpreadsheet constructor provide the GOOGLE_SHEET_ID environment variable.
  • Destructure the id from the query parameter and add a try catch. You can throw an error if the id is omitted from the request.
  • await the doc authorisation using useServiceAccountAuth. Similar to the above, provide the required environment variables.
  • Add the success and error responses.

You can read more about the basic configuration options in the google-sheet docs.

Read and Update the Google Sheet

  • Create a new const called sheet. This will read data from the first tab defined in your Google Sheet.
  • Create a new const called rows. This will read the available rows from the sheet.
  • Create a new const called raw_data. These are the values from the first row in the sheet.
  • Create a new const called header_values. These are the values from the header row defined in the sheet.
  • Create a new const called row_value. This is the value for the specific cell. The id from query parameter is used to target the correct cell.

You can now update the value in the cell by incrementing its value by +1, and then save the data back to the sheet.

Calculating the votes

In this step, you will calculate some new values to return, based on the values from the sheet.

  • Create a new const called total. This sums up the total votes across all cells. This value is used to calculate the percentage value for each cell. 
  • Create a new const called max. This is the highest-value cell from the sheet. This is used to determine if the cell value is the highest value in the sheet.
  • Create a new const called results and iterate over the header_values. You can “look up” the raw_data using the index value to determine the count, and then return the calculated values.

You can now send the results and total back to the browser in the response.

Finished

The finished Page API can be found on the following links for both frameworks.

  • Gatsby
  • Next.js

All of the values returned from the Serverless Function help determine and populate the application state you defined earlier.

All errors should be handled to ensure the application doesn’t crash, should the Google API fail for any reason.

Thanks for reading, and if you have any questions please come and find me on Twitter: @PaulieScanlon or check out my site paulie.dev, where I have more React/Jamstack tutorials.

Further Reading

Group Created with Sketch.
TNS owner Insight Partners is an investor in: Enable, Pragma.
TNS DAILY NEWSLETTER Receive a free roundup of the most recent TNS articles in your inbox each day.