Google Sheets + Cloud Run

Published in

Google Cloud - Community

3 min readAug 5, 2020

In 5 minutes, we’re going to quickly and securely integrate the Google Sheets API to your Cloud Run app. Just copy & paste the documented scripts below.

TL;DR

Download service account credentials
Share Google Sheet with the service account email
Enable the Google Sheets API
Write some Node
Deploy to Cloud Run

Step 1: Download Credentials 🤖

Create a service account and download a key in a new file called creds.json.

Create a service account, download a service account key, and copy the service account’s email. Or use the UI.

This credential has the unique id: sa-name@$PROJECT.iam.gserviceaccount.com

2. Share Google Sheet with Service Account 🔗

Create a new Google Sheet if you don’t have one already: sheets.new

Share the Google Sheet with the service account email (above) as an editor:

Click that Share button in the top-right of your Sheet. (Name your Sheet if you haven’t done so).

The service account email should autocomplete for a valid service account.

If you needed to do this programmatically, you could use the Drive API and share with the service account via drive.v3.files.permissions.create.

3. Enable the Google Sheets API 📃

Enable the Sheets API for your project via the gcloud CLI (or UI):

gcloud services enable sheets.googleapis.com

4. Write some Node ⬢

Create a simple Node application with this package.json:

A basic package.json, in this case, using the Functions Framework.

And a Node program authorized with our service account in index.js:

A Node program that authorizes with our creds.json and appends a row to the Sheet.

In index.js, replace your SPREADSHEET_ID with the ID of the Sheet you created. Otherwise you’ll get the error: “Error: Requested entity was not found.”

Now, you can run this program locally with:

npm i && npm start

And go to localhost:8080 (or curl http://localhost:8080/) to test and create a new row programatically. Takes a short bit to warm up, but it’s fast from my testing (<1 second).

Making a localhost HTTP request now adds a row to our Google Sheet!

5. Deploy to Cloud Run 💨👟

To deploy a production server, you’ll need to add a Dockerfile:

Dockerfile

Then build and run your container on Google Cloud:

GCP_PROJECT=$(gcloud config list --format 'value(core.project)' 2>/dev/null)gcloud builds submit \
--tag gcr.io/$GCP_PROJECT/sheets-on-rungcloud run deploy sheets-on-run \
--image gcr.io/$GCP_PROJECT/sheets-on-run \
--platform managed \
--allow-unauthenticated

For this sample, whenever we access our Cloud Run service via the URL, we will add a new row to our Google Sheet.

Great! Now that your Sheets is “connected” to Run, your mind is your limit in terms of what you can do with this power. Create some cool apps. :)

Next Steps

Thanks for reading! I like to use Google Sheets as an easy UI for displaying data. It’s fast, powerful, and free too!

You might be interested in these related resources: