Google Sheets + Cloud Run
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
.
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:
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
:
And a Node program authorized with our service account in index.js
:
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).
5. Deploy to Cloud Run 💨👟
To deploy a production server, you’ll need to add a 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: