I like writing little scripts using Node. Most of the scripts I write are to help with small day-to-day tasks. I recently needed to write a small task to fetch and edit a spreadsheet from my Google Drive, and save it as a PDF.

If you’ve ever worked with Google APIs, you know that you can access them either using API Keys, OAuth 2.0, or Service Account. Since this was a script I was running locally on my machine, I decided to go with using a Service Account. API keys don’t give you access to your account data, and OAuth requires a login flow that requires logging in through a browser. Service accounts effectively give you a account that you can then give access to service (in this case, my spreadsheet), and allow them access to the specific sheet

Setting up a Service Account

In this short tutorial, I’ll show you how I managed to get my Node script to access my spreadsheet using a Service Account. Start by creating a project at the Google Developer console.

Next, you’ll want to enable the Sheets API from the Library Tab of your project’s dashboard.

Once the API has been enabled, on the far right, there should be a button labeled “Credentials.” Click on it, and fill out the options as follows

Be sure to select “Application Data” above. If you select User Data, you’ll have to setup a consent screen for OAuth 2.0. Next you’ll have to fill out some information on the service account.

Be sure to give the account the Editor role. When you hit create, you’ll be prompted to download a file with the extension “.json”. This file is pretty sensitive, so make sure you keep it safe. In it, it has your private key, and some other valuable information necessary for the Google API

{
  "type": "...",
  "project_id": "...",
  "private_key_id": "...",
  "private_key": "...",
  "client_email": "scratchpad@scratchpad.iam.gserviceaccount.com",
  "client_id": "...",
  "auth_uri": "...",
  "token_uri": "...",
  "auth_provider_x509_cert_url": "...",
  "client_x509_cert_url": "..."
}

Download the json file and put it in a folder where your script will reside. For the sake of this tutorial, you can name it client_secret.json Note the client_email. This email address should be added to the google sheet you intend to use.

I think it’s important to note that this email address is not tied to your account nor is this email account a full Google Account in any way. What I mean by this is, you can’t use this email address to send emails.

I say this because while playing around with the Google APIs, I thought I could use the same client_secret.json to send emails. However, I would get an error telling me that it was a bad request. In any case, understand that this email address is limited to the privileges you give it when creating the credentials.

Ok with that tangent out of the way, time to create a new Node project.

Creating a Node Project

When I create a new Node project, I generally tend to favour Typescript. Since creating and adding Typescript is boilerplate, here’s the package.json file I tend to use. Copy it into the same folder as the client_secret.json file you downloaded earlier

{
  "name": "node-script",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "build": "tsc index.ts",
    "run": "node index.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {},
  "devDependencies": {
    "@types/node": "^11.15.7",
    "typescript": "^3.4.1"
  }
}

Since I’m also trying to access Google API, I needed to add the node library through npm using the following command

npm -i googleapis --save

The latest version as of this writing was 47.0.0. Create a file named index.ts in the same directory as your package.json and client_secret.json, and add the following line to pull in the library.

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

Finally, in order to make any calls to the Google Sheets API, you must first “login”. You’ll first get the path to your client_secret.json file

const path = require('path');
const credentials =  path.join(__dirname, 'client_secret.json');

Then you’ll use this file to create and authenticate your client

const auth = new google.auth.GoogleAuth({
  keyFile: credentials,
  scopes: ['https://www.googleapis.com/auth/spreadsheets']
});

const client = await auth.getClient();

Note that I’m using the await/async style of handling promises in this case. With the client object in hand, you can now invoke any method from the Google API. In the case of accessing the Google API for Sheets, here’s an example

const sheets = google.sheets({
  version: 'v4',
  auth: client,
});

You can access the Sheets API from this link. However, if you need more examples of how to work with any Google API, then I strongly suggest you take a look at their samples directory from the GitHub project.

That’s All!

Hopefully you found this short tutorial useful. Oddly, this sort of information was not compiled in any one location from my searches, so having it all in one place may be helpful to you.