Cloud Run for Google Sheets — Part 1

11 min readFeb 7, 2023

Sometimes the best part is building the DALLE artwork

Spreadsheets define my life, which sounds sad, but it’s actually a good thing. I use it to track my finances, groceries, travel plans, and much more. If something in my life can be tracked by a spreadsheet, it’s likely it will happen.

I use Google Sheets as my spreadsheet platform of choice, and here are some reasons why:

Free(ish)
Great documentation
Google Apps Script (if you’re not using this, you really should)
Access from any device
Also, did I mention Free(ish)

If you don’t use Google Sheets then this post won’t be for you, as today we’re taking a look at

Using Cloud Run to control Google Sheets

Google Apps Script is great and all (and it’s a great starting point for writing code to control spreadsheets) but it doesn’t do all of the things I want it to. Namely, it doesn’t run Python, only Javascript (actually App Script but it’s a small distinction).

We are going to write some App Script in this series, but it won’t be the powerhouse of our logic. Instead, we will use it to capture user input and proxy requests to our own API.

This approach uses a Python application as the API, but you are free to write in whichever language you like the most.

I like simplicity, ease, and clean-looking code. Python nails those objectives so hard that it’s almost difficult for me to stop writing in it.

I also like deploying containers, but I don’t like a) the complexity of GKE (even autopilot) for a “hobby” project and b) the overhead cost of a GKE control plane. For these reasons, we are going to deploy our API container to Cloud Run. However, you can actually use any backend infrastructure you want as long as it can take HTTPS requests.

But also Cloud Run is awesome and I don’t know why you wouldn’t use it.

This post will walk through the following:

Setting up a Cloud Run service in GCP via Terraform
Scaffolding a Python API application using fastapi
Building a basic CI/CD pipeline using GitHub actions (to automatically build and deploy our application when we push to our repository)

Part 2 will cover:

Authenticating the Cloud Run service to a Google Sheet
Creating a custom UI for a Google Sheet
Getting input from, and pushing output to, the Google Sheet based on this UI

I will post the finished repository link once part 2 is complete.

That’s a lot of things… but let’s get to it.

First — Diagrams

I’ve written a wall of text so far, so let’s visualize what this architecture will look like.

I’m trying a new thing (for me) called Mermaid for this diagram. It could be prettier but 🤷

In this diagram we see two flows:

The User interacts with the Google Sheets, which in turn makes requests to the Cloud Run service. and
Our code, when pushed to GitHub, triggers a GitHub Actions job which
Builds our container and pushes to Artifact Registry
Deploys the new container to our Cloud Run service

There will also be a GitHub Actions job to deploy our Terraform, but that is an optional part of this post.

[Optional] Terraform for Cloud Run

I live in Infrastructure as Code all day so it would be painful for me to walk through creating a Cloud Run service manually through the console. However, if you want the “easy” way out you can find instructions below.

You can skip to Build a Python API if you take these steps manually.

Quickstart: Deploy to Cloud Run | Cloud Run Documentation | Google Cloud

Now that we’ve lost those folks (totally joking 😜 ) let’s do this properly.

Repository Structure

We’re going to hold all of our code in a single repository, so we need to plan accordingly. Here is the folder structure I initialized in a new git workspace.

- .github/
	|- workflows/
- terraform/
- app/
- .gitignore
- README.md

The terraform directory will hold all of our Terraform code for the initial deployment, and (you guessed it) the app directory will hold our Python code.

Terraform Code

We can keep things fairly simple in our Terraform, as we’ll only be creating the following resources:

Enabling APIs
Service Account
Artifact Registry Repository
Cloud Run Service

I’ll be deploying this into an existing project with a bucket for the backend state, but if you have a brand new GCP project use the below guide to set up your state bucket (or use whichever state provider you want).

Backend Type: gcs | Terraform | HashiCorp Developer

Below are the code files for main.tf , variables.tf, and terraform.tfvars.

These files will live in the root terraform directory of our repository.

main.tf

# Enable APIs
resource "google_project_service" "google_api" {
  for_each = toset(var.apis)

  project = var.project_id
  service = each.key
}

# Create a Service Account
resource "google_service_account" "cloud_run_service_account" {
  project      = var.project_id
  account_id   = "cloud-run-service-account"
  display_name = "Cloud Run Service Account"
}

# Create the Artifact Registry Repository
resource "google_artifact_registry_repository" "repository" {
  location      = var.location
  repository_id = "${var.cloud_run_service_name}-repository"
  format        = "DOCKER"
}

# Create a Cloud Run Service
resource "google_cloud_run_service" "cloud_run_service" {
  project  = var.project_id
  name     = var.cloud_run_service_name
  location = var.location

  template {
    spec {
      containers {
        image = "us-docker.pkg.dev/cloudrun/container/hello"
      }

      service_account_name = google_service_account.cloud_run_service_account.email
    }
  }

  traffic {
    percent         = 100
    latest_revision = true
  }

  lifecycle {
    ignore_changes = [
      template
    ]
  }
}

# Disable auth for Cloud Run Service
data "google_iam_policy" "noauth" {
  binding {
    role = "roles/run.invoker"
    members = [
      "allUsers",
    ]
  }
}

resource "google_cloud_run_service_iam_policy" "noauth" {
  location = google_cloud_run_service.cloud_run_service.location
  project  = google_cloud_run_service.cloud_run_service.project
  service  = google_cloud_run_service.cloud_run_service.name

  policy_data = data.google_iam_policy.noauth.policy_data
}

variables.tf

variable "project_id" {
  type = string
}

variable "apis" {
  type = list(string)
}

variable "cloud_run_service_name" {
  type = string
}

variable "location" {
  type = string
}

terraform.tfvars

project_id = "<YOUR_PROJECT_ID>" # Get your own
apis = [
  "run.googleapis.com",
  "artifactregistry.googleapis.com",
  "sheets.googleapis.com",
  "cloudresourcemanager.googleapis.com",
  "iam.googleapis.com"
]
cloud_run_service_name = "cloud-run-for-google-sheets"
location               = "us-central1"

The astute may notice the lifecycle.ignore_changes policy we have on the cloud_run_service. This allows us to control the container spec outside of Terraform but still allows us to manage other configuration details.

We are also going to use the Cloud Run example container to initial start our service.

I also have a backend.tf file in my repository, but I’ll omit it as yours will likely be different.

Great! Now we have some untested Terraform code. I see you itching to deploy it from your local machine, but we can do better. Let’s build a

Continuous Deployment pipeline for Terraform

Authenticating from GitHub Actions to Google Cloud Platform is a well-documented act, and I don’t want to bog down this post with it. I’ve covered it before below.

The “Best” Terraform CD pipeline with GitHub Actions

Make sure to have an auth mechanism configured before the next step. I was lazy and used a Service Account JSON key in my GitHub Actions Secrets, which you’ll see referenced below.

Let’s check out a quick diagram for this GitHub Actions job:

Here is the flow:

We want a GitHub Actions job to trigger when we push changes to our terraform/ directory. This job will:
Auth to GCP
Initialize Terraform
Apply our Terraform changes

NOTE: This pipeline does not do any sort of plan/approval cycle, it just tries to apply any changes straight to your project. This is dangerous (but typically fine for a hobby project). If you want a more robust pipeline, view the article I shared above instead.

Here is the code for the GitHub action. Saved as .github/workflow/terraform.yml

terraform.yml

name: Terraform Deployment

on:
  push:
    branches:
      - main
    paths:
      - terraform/**

env:
  TF_SA: DEPLOY_GOOGLE_SA_EMAIL_HERE
  TF_VERSION: "1.2.9"
  TF_IN_AUTOMATION: "True"

jobs:
  terraform_deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Setup Terraform
        uses: hashicorp/setup-terraform@v2
        with:
          terraform_version: ${{ env.TF_VERSION }}
          terraform_wrapper: false
      
      - name: GCP Auth
        uses: google-github-actions/auth@v1
        with:
          service_account: ${{ env.TF_SA }}
          credentials_json: ${{ secrets.GOOGLE_CREDENTIALS }}
      
      - name: Terraform Init
        working-directory: terraform
        run: terraform init
      
      - name: Terraform Apply
        working-directory: terraform
        run: terraform apply --auto-approve

Note: I am using a different Service Account for these deployments than the one we are making for the Cloud Run service (not to mention Terraform will deploy that Service Account, so this warning may not be required). Make sure to update DEPLOY_GOOGLE_SA_EMAIL_HERE with your own information.

The paths key in our trigger ensures that this pipeline will only run when we change our Terraform code.

Pipeline Test!

Does it work?

The checkmarks don’t lie.

Note for those following along. Enabling the APIs can be a bit finicky, it may take 2 tries to get everything completely deployed.

If we check the Cloud Run page from the Cloud Console we see:

Looks like a functioning Cloud Run Service to me!

Now that we have the infrastructure out of the way, it’s time to

Build a Python API

When starting a new Python project it’s always good to create a new virtual environment. Just trust me.

Here are the commands I run in my repository to accomplish this:

$ virtualenv venv
$ source venv/bin/activate

If you don’t have virtualenv installed, you should. https://virtualenv.pypa.io/en/latest/

Before I write an API I always think through the endpoints I will need, and the logic they accomplish.

Design

Let’s keep it simple with three endpoints:

GET /health — We can use this to verify that our function is working properly
POST /example-sheets-read — Example endpoint to read data from Google Sheets
POST /example-sheets-write — Example endpoint to write data to Google Sheets

Get to the code already

Alright alright… First, we need to install the Python packages we are using in this tutorial. Here is the pip command.

$ pip install fastapi uvicorn google-api-python-client google-auth-httplib2 google-auth-oauthlib

I used the below article as a guide when I built the original prototype if you’re curious:

Python quickstart | Google Sheets | Google Developers

We’re going to bake all of our Python code into a single app.pyfile in the app directory. The code looks like this:

app.py

import time

from fastapi import FastAPI
from pydantic import BaseModel
import google.auth
from googleapiclient.discovery import build

# GLOBALS
SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets'
]

# App Instantiation
app = FastAPI()

# Models

class SheetsRequest(BaseModel):
    sheetId: str
    sheetName: str


@app.get("/health")
def health():
    return "Ok"


@app.post("/example-sheets-read")
def example_sheets_read_func(sheets_request: SheetsRequest):
    return example_sheets_read(sheets_request.sheetId, sheets_request.sheetName)


@app.post("/example-sheets-write")
def example_sheets_write_func(sheets_request: SheetsRequest):
    example_sheets_write(sheets_request.sheetId, sheets_request.sheetName)
    return "Ok"


def example_sheets_read(sheet_id, sheet_name):
    # Get GCP auth and create Sheets API Client
    creds, _ = google.auth.default(scopes=SCOPES)
    service = build("sheets", "v4", credentials=creds)
    sheets_client = service.spreadsheets()

    # Read data from the sheet_id and sheet_range
    sheet_range = f"{sheet_name}!A:Z"
    result = sheets_client.values().get(
        spreadsheetId=sheet_id,
        range=sheet_range
    ).execute()
    values = result.get("values", [])
    return values


def example_sheets_write(sheet_id, sheet_name):
    # Get GCP auth and create Sheets API Client
    creds, _ = google.auth.default(scopes=SCOPES)
    service = build("sheets", "v4", credentials=creds)
    sheets_client = service.spreadsheets()

    # Update the sheet_id/sheet_name with the example values below
    data = [
        "1", "2", "From Cloud Run!", f"{time.time()}"
    ]
    sheet_range = f"{sheet_name}!A1:D1"
    sheet_data = {
        "range": sheet_range,
        "values": [data]
    }
    result = sheets_client.values().update(
        spreadsheetId=sheet_id,
        range=sheet_range,
        valueInputOption="USER_ENTERED",
        body=sheet_data
    ).execute()
    return result

Let’s walk through a couple of specific parts of this file.

Models

I define a basic SheetsRequest model that all of our POST data will follow:

class SheetsRequest(BaseModel):
    sheetId: str
    sheetName: str

Reading from Google Sheets

The function example_sheets_read gets the default GCP credentials which will be a token for the Service Account we have attached to the Cloud Run Service.

I’m creating an obnoxious range SHEET_NAME!A:Z for the sheet_name we pass into the endpoint. This is a boilerplate to capture most of the data in a given spreadsheet but I recommend expanding on it.

Writing to Google Sheets

Again, we read the default GCP credentials.

I have a hardcoded data variable for example data to write to the spreadsheet. In a real project, you would have some logic to do something and then build the appropriate data to post.

The valueInputOption was an issue I had for a looong time when first prototyping this out, so don’t forget it.

Google Sheets API Documentation

This link was hard to find, so I’m happy to share it here.

https://googleapis.github.io/google-api-python-client/docs/dyn/sheets_v4.html

It contains the docs for the Sheets endpoints you can use.

Dockerfile!

With the Python code written, we now need a Dockerfile that defines how our application will run. This file should live in the root of the repository.

Dockerfile

FROM python:3.9
WORKDIR /app
COPY ./app/requirements.txt /app/requirements.txt
RUN pip install --no-cache-dir --upgrade -r /app/requirements.txt
COPY /app /app
CMD ["uvicorn", "app:app", "--host", "0.0.0.0", "--port", "8080"]

Also, be sure to run the following pip command to save our dependencies to a requirements.txt file.

$ pip freeze > app/requirements.txt

Also, be sure to add venv to your .gitignore so it looks like:

.gitignore

venv

💥 our container should be good to go. Now we need a

CI/CD Pipeline for Cloud Run

Let’s feed my love for diagrams and draw out what this process will look like:

Hopefully, this is simple and straightforward. For this example, I am once again going to use a Service Account JSON file to auth to GCP, but you can use a different pattern if you want.

I’ll create a file .github/workflow/cloud_run.yml for this pipeline:

cloud_run.yml

name: Cloud Run Deploy
on:
  push:
    branches:
    - main
    paths:
      - app/**
      - Dockerfile
jobs:
  cloud_run_deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - uses: google-github-actions/auth@v1
        name: "Authenticate to Google"
        with:
          service_account: GOOGLE_SA_EMAIL
          credentials_json: ${{ secrets.GOOGLE_CREDENTIALS }}

      - name: 'Set up Cloud SDK'
        uses: 'google-github-actions/setup-gcloud@v0.6.0'

      - name: Docker Auth
        run: gcloud auth configure-docker us-central1-docker.pkg.dev

      - name: "Build and Push"
        uses: docker/build-push-action@v4
        with:
          push: true
          tags: ARTIFACT_REGISTRY_URL/cloud-run:latest

      - name: "Deploy to Cloud Run"
        uses: google-github-actions/deploy-cloudrun@v1
        with:
          service: cloud-run-for-google-sheets
          image: ARTIFACT_REGISTRY_URL/cloud-run:latest