Reading and Writing a Private Google Sheet Programmatically

Google Sheets is amazing — a free, publicly accessible spreadsheet available to anyone with an Internet connection. With its expressive (if a bit obtuse) formula programming language and its ease of distribution, it’s not surprising that Google Sheets is a favorite component of many cloud automations.

Even here at Atomic, where we have easy access to loads of talented software developers, we often reach for a Google Sheet when rapidly prototyping an idea or automating a business process that doesn’t quite justify a custom solution.

Automating the Internet 🌎

When building automation that’s powered by a Google Sheet, you have a few options:

• You can use Google Scripts to publish a cloud app that interacts with your spreadsheet.
• If text-based programming languages aren’t your jam, you can use a more intuitive tool like IFTTT or Zapier.
• On the other hand, if text-based programming languages are very much your jam, you can reach for the Google Sheets API and build spreadsheet support directly into a full custom website, app, or script.

I recently had reason to take the third approach while building a little internal app. While rooting around in the excellent Google Sheets API documentation, I discovered something a little odd. If you’re writing an app that uses Google Sheets, you need some way of proving to the API that you should have access to a particular spreadsheet. You need to authenticate yourself.

The Secret Handshake 🤝

The first authentication approach listed in the docs is perfect if you’re building an app that lets people read and modify their own private spreadsheets. Your app should have the user log in with their Google account, and then you can act on their behalf to update their spreadsheets.

The second authentication approach listed in the docs works well if you’re building a website that lets the general public read and contribute to fully public spreadsheets. In that case, you don’t really need to authenticate the end user; you just need to prove, with an API key, that your app should be allowed to access all public Google Sheets. But if you find yourself somewhere in the middle, the docs are a little lacking.

My app was a little web server that would listen for requests and occasionally update a company-private Google Sheet that we maintain. Right away, it seems like I should be following the first authentication approach. But hang on, this server will be running around the clock, tirelessly doing work on behalf of everyone at the company. If someone has to occasionally punch in their Google account credentials just to keep it running, that kind of defeats the purpose of the automation. What to do?

I could’ve opted to make the spreadsheet public. That would make the authentication story really easy. The trouble is, this is a private spreadsheet. We believe strongly in open books management, but that takes it a bit too far even for us. To make my server work, I needed a non-human to prove to Google that it was allowed to read and modify a private Google Sheet.

A Robot Walks Among Us 🤖

I couldn’t find any one cohesive document that walked through all of the steps necessary to programmatically grant a non-human access to a private sheet, so I figured I’d collect them here for you (and future me). To get started, you’ll need access to a few things:

1. A way to run your program
2. Your own GSuite account with access to Google Sheets
3. A private Google Sheet that you want to modify
4. A project in Google Cloud Platform
5. A Google Service account

For my project, I’m using:

1. A little JavaScript that I run with Node.JS locally. It’ll run on Heroku’s cloud when I’m ready to publish
2. My work GSuite account
3. An exact copy of the Google Sheet that my server will be modifying
4. …But I’m not using Google Cloud 🤔
5. …A what now?

Those last two were hard to understand at first.

A Brief Digression on Google Accounts 📇

Number five made a decent amount of intuitive sense to me as a developer. In order for my robot server to get access to my Google Sheet, it needs to be given an identity in Google’s system. For GSuite, this means setting up a Google Service account. You can think of the service account like a driver’s license for the server. Like a license, the bearer is allowed to do things that an unlicensed entity can’t do (e.g., access some spreadsheets). Also like a license, it’s revocable, so if my server ever drinks one too many microbrews and lets someone copy its license (or if it just gets hacked like a less hipster server), I can revoke all of its privileges.

Number four only makes sense if you’re deeply embedded in Google’s cloud architecture. Short version: where do you go to make a Google Service account? The Google Cloud Console. And what is the organizing principle of the console? Projects. So in order to make yourself a new Google Service account, you’ll need a project. That’s not terribly complicated once you realize it, but it’s not exactly intuitive from the outside.

Fortunately, there are great docs for setting up Google Cloud projects and Google Service accounts. When I finally realized that I needed both, I had the whole thing set up in about ten minutes — a shiny new drivers license service account for my server and a project in Google Cloud. I was ready to use it to authenticate with Google Sheets! 😄

But then the harsh light of reality shone again. The Google Sheets API docs give really, really clear instructions if you’re authenticating as an individual or a public-facing app, but they’re somewhat more vague about how you’re supposed to authenticate using a service account.

Proving that You’re a Genuine Cylon 🕵️

I’ll show you the code first, then walk through it. For my project, I’m using the Google API client for JavaScript.

const emailAddress = '[email protected]';
const privateKey = `-----BEGIN PRIVATE KEY-----
Gobbledy Gook that you get when you set up a Service account.
-----END PRIVATE KEY-----`;
const spreadsheetID = "Gobbledy gook you get from the address bar of a web browser";
const accessScopes = ["https://www.googleapis.com/auth/spreadsheets"];

// ☝️ Stuff that you’ll need to get from Google

const { google } = require("googleapis");

async function doThings() {
    const client = await authenticateGoogleSheetsClient();
    await readFromCellInSpreadsheet(
        client,
        spreadsheetID,
        cellRange
    );
    await writeToCellInSpreadsheet(
        client,
        spreadsheetID,
        cellRange,
        value
    );
}

async function authenticateGoogleSheetsClient() {
  const client = new google.auth.JWT(
    emailAddress,
    null,
    privateKey,
    accessScopes,
    null,
  );
  await client.authorize();
  return google.sheets({
      version: 'v4',
      auth: client
  });
}

async function readFromCellInSpreadsheet(client, spreadsheetId, range) {
    return await client.spreadsheets.values.get({ spreadsheetId, range});
}

async function writeToCellInSpreadsheet(client, spreadsheetId, range, value) {
    return await client.spreadsheets.values.update({
        spreadsheetId: spreadsheetId,
        range: range,
        resource: { values: [[value]] }
    });
}

I removed all of my normal error handling code above to keep the code easy to read. The most important piece here is that, unlike the normal OAuth2 client that you use to authenticate a human’s Google account, you’ll use the JWT authentication client for a Service account. Let’s dive into the code to see how it works.

const emailAddress = '[email protected]';
const privateKey = `-----BEGIN PRIVATE KEY-----
Gobbledy Gook that you get when you set up a Service Account.
-----END PRIVATE KEY-----`;
const spreadsheetID = "Gobbledy gook you get from the address bar of a web browser";
const accessScopes = ["https://www.googleapis.com/auth/spreadsheets"];

// ☝️ Stuff that you’ll need to get from Google

Service accounts are identified using an email address that ends in “iam.gserviceaccount.com” and a private key. You can extract both of those from the credentials JSON document that Google offers you when you successfully create the account. Access Scopes describe what kind of access you’re asking for. In this case, we want to read and write Google Sheets, so we ask for that scope. You can find the Spreadsheet ID by loading your target Google Sheet in a browser and extracting part of the url.

const { google } = require("googleapis");

You’ll need to install the “googleapis” package from NPM to use Google’s JavaScript SDK for this example.

async function authenticateGoogleSheetsClient() {
  const client = new google.auth.JWT(
    emailAddress,
    null,
    privateKey,
    accessScopes,
    null,
  );
  await client.authorize();
  return google.sheets({
      version: 'v4',
      auth: client
  });
}

This is the part that took me a long time and a lot of googling to find. The authenticated client that the SDK uses to connect to the Google Sheets API may be constructed in a number of ways. The Sheets API only ever (that I could find, anyway) refers to building an OAuth2 client or an API Key client. Service accounts require a JWT client.

Fortunately, even though it isn’t explicitly mentioned in the docs, the JWT client works just fine with the Sheets API. We construct one using our Service account’s email address, private key, and whatever access scopes we might need (spreadsheets, in this case) and then just pop it into the Google Sheets client along with the API version that we want to use.

Give a Sheet 📊

Now that we have an authenticated Google Sheets client, we can use it to read from and write to any private spreadsheets that our Service account has access to. In order to grant access to a sheet, just share the sheet with your Service account’s email address. Once you’ve done so, you can use code that looks something like this to read and modify that sheet:

async function readFromCellInSpreadsheet(client, spreadsheetId, range) {
    return await client.spreadsheets.values.get({ spreadsheetId, range});
}

async function writeToCellInSpreadsheet(client, spreadsheetId, range, value) {
    return await client.spreadsheets.values.update({
        spreadsheetId: spreadsheetId,
        range: range,
        resource: { values: [[value]] }
    });
}

Tada! 🎉

And that’s it. The actual code for this project ended up being really simple. But, as is so often the case in software, finding out how to build the simple thing took a while. I had to learn about Google Service accounts, and because of them, about projects in Google Cloud. I then had to learn that the Google Sheets API allows more authentication methods than they let on. And then I had to learn how to identify the credentials in my service account that actually matter.

Now that I have all that in my brain cache, I can get back to automating the tedious process that started this journey. Hopefully, you found this post because you too are automating the Internet using Google Sheets. If so, good luck and happy automating!