Integrating Google Sheets as a backend

This guide is part 3 and a continuation of my series: So you want to start using Google Cloud — though you’re welcome to use this as a reference piece for just this section, I’ll be using the code base that I’ve been building up to this point. Make sure to npm install to get all the packages necessary.

To get started, you’ll need a few things.

1. A google account
2. A google sheet (I’m starting with this example)

You’ve probably used Google Sheets before. Maybe for personal finances or just a way to share information between multiple people for planning. It’s great for that.

You may have even used some of the functions that come with it. You can perform some pretty powerful functions just through the scripts and macros.

But I’ve started using it recently as something more powerful — as a lightweight cloud database.

I recently build a website to handle RSVPs for my wedding. This was the first project that I built from nothing to something that had to say a lot about me to people I’ve never met. I learned a lot and if I did it again, I’d do it very differently. But one thing I’ll definitely do again is use Google Sheets for read/write data storage.

Leveraging Google Sheets allowed my fiancée to easily access and modify the back end without needing to know any programming. It ensured I didn’t have to build or use a custom interface to manage it either.

In this tutorial, I’ll show you how to get started really quickly reading and writing to your new cloud database.

To get started, get into your console and head over to the APIs and Services -> Library. Once there, go ahead and search for the Google Sheets API. Enable it.

You’ll likely see the above warning. There’s multiple ways to go about this. You could proceed from here and set up OAUTH 2.0 access to your Sheets API.

We’re going to take the high road here since I don’t really care about the data I’m working with. If you want your data to be private, you might need to go through with locking down your sheets and implementing OAUTH.

https://docs.google.com/spreadsheets/d/1fOKdATKiouA3AIhuiuxerYA7meDVqfVq1uFvSEB3AGs/

I’ve started with a very simple Google Sheet. I’ve titled it Up and running as is the name of this series. I have populated the first row of the first column with the phrase Up and running with Google Sheets. We’re going to get and display the data in that cell in just a moment.

If you’re making your own, take key note of the 44 character identifier in the URL. You’ll need it soon.

Head into file and click on the option “Publish to the web…”

I’ve opted to just list this as public. If you’d rather restrict it, you’ll have the email address you can place here in a moment. For now, I’m going to set it to public.

I’m going to publish the entire document as a web page. You can check out the other options available.

If you opt to publish only the sheet instead of the entire document, please modify the code accordingly to avoid errors. You’ll access the sheet directly instead of selecting the first sheet in the array.

At this point, I’ve configured my google sheet to be public accessible. If you did the same and wanted to see that data, you can use the url example below with your key:

https://spreadsheets.google.com/feeds/cells/your44characterkeyhere/1/public/values?alt=json

In order to access the sheet as your server is about to access it.

Back over in the console, head into APIs & Services -> Service accounts in order to setup a service account which will act as a liason user between your application and your google sheet.

I have a default service account already created which I’ll latch onto. You could go ahead and create one specifically for this purpose if you need to. Click on the actions icon, ensure JSON is selected, and click create.

This should start downloading the credentials which you’ll reference in your application. Place the downloaded JSON output at the root directory of your folder. I simply called mine key.json and reference it with

const credentials = require(`./key.json`)

In the app, we require a few things here. As mentioned earlier, if you’re just downloading the code base, make sure you npm install to download these if you’re running locally.

As mentioned, we import our credentials we just created as a variable. We setup the primary import and declare it as a variable, GoogleSpreadsheet. And we’re going to use promisify to asynchronously make calls to our spreadsheet.

In my code, I’m directly referencing that 44 character identifier at

const doc = new GoogleSpreadsheet(`1fOKdATKiouA3AIhuiuxerYA7meDVqfVq1uFvSEB3AGs`)

You’ll need to switch this out with the key that references your google sheet from the URL and not from your recently created JSON file.

If you’ve followed along correctly to this point and you’ve successfully swapped out the references from my sheet to your sheet, and your sheet looks like this:

You should be able to launch your server, go to the /sheets endpoint in your browser

And see something like this:

If you did, congratulations! You’ve just implemented Google sheets as an API to reference data from. From here you could populate a sheet with all kinds of data to serve into your application.

But, if you’re like me, that’s not enough. I need to be able to write to it as well. We’ll cover that next.

If you encountered any errors, please reference the errors section towards the bottom before continuing.

To go a step further, I’ve commented out a block of code, go ahead and uncomment it or just type out what I’ve written below.

This references the bulkUpdateCells method to update all of the cells we’ve edited or passed new values to. This code just goes to the next cell in the defined area and sets its value to Now I’m writing.

Try accessing the endpoint again.

It probably didn’t do anything if you’ve been following along. One more step.

In the top right of your sheet, or through the menu, navigate to the Share window. Here you need to provide the service account you exported credentials for edit access.

I turned off the Notify people option, because it’s just a service account.

You’ll likely see this confirmation as soon as you hit OK. Just hit OK again — our service account doesn’t need to be notified.

Try accessing the endpoint again after those changes have taken effect.

If you did it correctly, you should now see that message appended to the end of the string.

If we go back and look at the actual sheet, we should see it appended to the next row.

So if you’ve followed along — you’re now reading and writing to a google sheet. You can push and pull data to this just as you would another tool.

How awesome is that? A lightweight, highly accessible database you can use with very little setup.

Errors

If you’ve experienced any errors, please make sure

1. that you’re referencing your spreadsheet id and not mine
2. you’re properly importing and referencing the credentials that you exported earlier
3. that you’ve properly published your sheet and either made it public or granted it access

If you’re having errors with any of the provided JavaScript, you may need to convert some of the syntax, use a transpiler like babel, or migrate to an updated browser.

If you liked what you’ve read, please let me know down in the comments or feel free to check out my other articles. What are you planning to implement with Google Sheets as your back end?