BLOG

Using the Google Sheets API in a Cloud Function on Google Cloud Platform

July 2, 2020

Cloud functions on Google Cloud Platform (GCP) are a great way to ship production code quickly and efficiently. I often use them to take advantage of the services that GCP offers, such as Google Sheets API.

This post details the process of using Google Cloud Platform (GCP) to deploy a cloud function that uses the Sheets API. I use the code included in this article as a template for all my GCP projects. This speeds up the process of creating new projects.

The post covers the following topics:

Create a new GCP project
Set up a local development environment
Leverage the Google Sheets API
Deploy the code as a Cloud Function

Some notes about my setup

I'm using the Visual Studio Code editor on a Mac, so please make the necessary adjustments if your setup is different.

To push the local code to GCP, you need to download and install the gcloud CLI SDK. The page lists installation instructions. You'll then need to initialize the environment:

gcloud init

New GCP project

Create a new folder on your machine and CD into it.

Clone my gcp-boilerplate GitHub repo. You can use the code as a boilerplate for all your GCP projects.

Once you've downloaded the code, you can install the dependencies using the install script in the package.json file:

npm run install

Next, create a new GCP project. You can always do that via the GCP Console, but the command line is faster:

gcloud projects create <project_id> <options>

<project_id> can be whatever you want as long as it's unique. <options> can include --organization=<org_id> or --folder=<folder_id>

Depending on the GCP services you want to consume, you may need to enable billing for the project. I typically do that via the Console, by associating my billing account with the project.

Now you can authenticate for the new project. Entering the code below will open a web browser window where you can enter your Google credentials.

gcloud auth login

You can now associate the folder with the project you've just created:

gcloud config set project <project_id>

If you ever need to check what is the default project, use:

gcloud config list --format 'value(core.project)' 2>/dev/null

Local development

Although you can develop your cloud function directly in the GCP Console, it's better to do so locally. This makes it easy to quickly iterate through development and testing.

To get this done, we need to create a GCP service account, generate an authorization key for it, download the key, and use it in our project.

First, create a sub-folder inside your project folder. I name this subfolder as config.
Next, create a GCP service account for the project:

gcloud iam service-accounts create <service_account_name>

You need to grant permissions to the service account. The command below assigns an owner permission. It's recommended to set a more restrictive permission, depending on your project needs:

gcloud projects add-iam-policy-binding [PROJECT_ID] --member 
"serviceAccount:[NAME]@[PROJECT_ID].iam.gserviceaccount.com" --role "roles/owner"

Next, generate and download the authorization key. I typically name it as key.json and store it in the "config" folder. The folder is alreay called out in the .gitignore file. You should not check your key into your source code.

gcloud iam service-accounts keys create key.json
--iam-account [NAME]@[PROJECT_ID].iam.gserviceaccount.com

Using the Google Sheets API

In this section, we'll create a YAML file to hold our spreadsheet ID. We'll install the Google API dependency in our project. We'll add the code to read the Google Sheet. And finally, we will run our project locally.

Before we get going, we need a Google Sheet to read from. Create a Google Sheet and put some dummy data in columns A to C. Copy the sheet ID.

Inside, the "config" subfolder, create a .env.yaml file. This file will hold our environment variables, since we don't want to store the sheet ID directly in our code. The file name is important because the GCP cloud function will look for this file.

Check out my blog post about using an environment variables file for local and cloud.

Inside .env.yaml, enter the single line below along with your spreadsheet ID:

SPREADSHEETID: paste-your-spreadsheet-id-here

To use the Google API, install the dependency:

npm i googleapis@39

Inside index.js, paste the code below to access and read the Google Sheet:

const { google } = require('googleapis');
const key = require('./config/key.json');

exports.readGoogleSheet = (event, context) => {
  const client = new google.auth.JWT(key.client_email, null, key.private_key, [
    'https://www.googleapis.com/auth/spreadsheets.readonly'
  ]);

  const readSheet = async auth => {
    const gsapi = google.sheets({
      version: 'v4',
      auth
    });

    const options = {
      spreadsheetId: process.env.SPREADSHEETID,
      range: 'sheet1!A:C'
    };
    const { data } = await gsapi.spreadsheets.values.get(options);
    console.log(data.values);
  };

  client.authorize((err, tokens) => {
    if (err) {
      console.error(err);
      return;
    }
    readSheet(client);
  });
};

exports.readGoogleSheet();

To run the code locally, simply issue the following command:

npm run dev

You can read here about how the utility in this project works to set up the environment variable and execute index.js.

Deploy to the cloud

To run the code as a cloud function, comment out the last line in the code (exports.readGoogleSheet). Then, deploy as follows:

gcloud functions deploy readGoogleSheet --runtime nodejs8 \
--trigger-topic some_topic_name \
--env-vars-file config/.env.yaml

You can trigger the function using GCP Pub/Sub.
– Ben