Write to Google Sheets from a local script via gcloud CLI authentication

Recently I needed to pull data from the GitHub API and publish to a Google Sheet so I could share some charts about code review workload. This post is about how I got authentication working.

I wrote a Node.js script because my use case was too complex for BASH and seemed too temporary for Go. Initially, the script generated ad hoc CSV data that I could manually copy into Google Sheets using the Paste-as-CSV feature. After a few rounds of manually copying, I wanted to use my time wisely: I estimated that I could get a Sheets integration working within a couple hours and if so that would
probably pay off within a few months.

Aside: Sheets isn't my database. It's my data reporting UI. Don't use Sheets as your database.

It took me closer to 4 hours to get this working, because authentication is hard. This post shows the more direct path to a working solution.

It's easy to be distracted with the complexity of creating an oAuth application, and while I especially like using a service account from my Cloud services, that's
less convenient when running locally.

The trick I learned is that the gcloud CLI's setup of application default credentials can operate as a sort of OAuth proxy for Google Workspace code, by expanding how it authenticates your account with Google to include some more permissions (OAuth Scopes).

? Local authentication with gcloud CLI

To make API requests to Google Sheets, enable the Sheets API in your Cloud project:

$> gcloud services enable sheets.googleapis.com

Operation "operations/acat.p2-480745230567-02564c8d-c6ba-4f60-90bd-13f33e41f0fe" finished successfully.

Set your application default credentials, also claiming some non-default OAuth scopes so the credential can be used with sheets:

$> gcloud auth application-default login --scopes \
   'https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/spreadsheets'

This will trigger an OAuth flow that involves visiting a webpage in your browser. Depending on the terminal configuration this may display a URL or even open the page.

When gcloud is granted this scope, code that can access your local credentials will have read/write access to all your Google Sheets data. You can re-run this command without the customized scopes to toggle that access on and off as needed.

?️ Initialize a Node client for Google Sheets

import {google} from 'googleapis';

function sheetsClient() {
    const authConfig = new google.auth.GoogleAuth({
        scopes: [
            // Only 'spreadsheets' scope is needed in the code.
            // gcloud CLI also needs 'cloud-platform' and 'drive'.
            'https://www.googleapis.com/auth/spreadsheets'
        ],
    });
    const auth = await authConfig.getClient();
    return google.sheets({version: 'v4', auth});
}

? Append data to the sheet

The sample from the docs on how to append data to the sheet
(click on Node.js tab) worked well. However, I couldn't understand how to get authentication working from there. The sample worked once I understood the trick above to add the missing OAuth scopes to my dev environment credentials.

I made a few changes to enable easier reuse of the client, parameterize the request differently, and emphasize how I wanted the data handled by Sheets.

My code to append data to the sheet, leveraging the client initialization code above:

let client;

async function appendDataToSheet(spreadsheetId, tab, values) {
    if (!client) {
        client = sheetsClient();
    }

    try {
        const result = await client.spreadsheets.values.append({
            spreadsheetId,
            range: `${tab}!A2:AG`,
            // Use my data as provided.
            valueInputOption: 'RAW',
            // Inserts rows as part of appending to reduce overwrites.
            insertDataOption: 'INSERT_ROWS',
            requestBody: { values },
        });
        console.log(`${result.data.updates.updatedCells} cells appended.`);
    } catch(e) {
        // Show the error, do not stop. Cross-reference the error with terminal output
        // and decide case-by-case to re-run the script or manually copy data.
        console.error(e);
    }
}

I'm using "append" because my script collects monthly metrics, and append allows me to add new rows without removing earlier rows.

Here's an example of how to call the appendDataToSheet() function:

const values = [
  // Each nested array is a spreadsheet row.
  [1, 2, 3, 4, 'luggage'],
  [4, 5, 6, 'N/A', 'sticks'],
];
appendDataToSheet(
  'HPDkfqdu6rfIq5-4uTGDqz2tvmPxDZMul27JFexample',
  'Exported Data Tab',
  values
);

There are some good tips about working with the Sheets API in the docs such as the suggestion not to send more than one API request per second per sheet. I found out the hard way: overwriting data from a first request with data from a second.

? Ship it!

If I move forward to productionize this, I might switch to using
Cloud Scheduler and Cloud Run Jobs. Let me know if you'd like to read about that.

Cover Photo by Glib Albovsky on Unsplash

The above is the detailed content of Write to Google Sheets from a local script via gcloud CLI authentication. For more information, please follow other related articles on the PHP Chinese website!