Service account impersonation for Google APIS with Nodejs client

Service account impersonation - sheets example posted on 8th Aug 2019


If you want to write to sheets from a server side Node app, you can use a service account, but it's a little tricky as you have to authorize that service account to be able to access the spreadsheets of a given user. One way is for the spreadsheet owner to share the spreadsheet with the email address of the service account, but if you need to be able to create sheets, and not bother with that you can allow the service account to impersonate a given user.

There are a couple of gotchas to look out for, and the documentation for the API and authorization favors python, and/or use the normal 3 legged oauth2 approach which is not practical for many automated operations - so here's the full Nodejs story.

Preparation

Most of this you'll know how to do, or you'll find elsewhere on this site, so I won't burden you with lots of screenshots, but here's what you have to do
  • create a service account, and download the file
  • allow that service account to do this - if you won't or can't, then stop here because it's required for this method.
  • get the client id of the service account and in admin.google.com security/advanced settings/manage api client access, you'll find an ancient looking console. Enter the numeric service account clientId (you'll be tempted to add .googleusercontent.com like the other stuff there, but don't - it won't work) and then give the scopes you want to allow it to have. In my case, its just about spreadsheets.  Don't forget, you are now allowing this service account to impersonate anyone on the domain - so think about it carefully.
  • enable the api you plan to use in the project belonging to the service account (this example is for the sheets API, but will work with most other APIS too)

Node set up

Install the googleapis module with yarn or npm and include it in your app
const {google} = require('googleapis');

Set up your secrets however you usually manage them in node. I generally have a secrets file where all parameters are stored so I'll be referencing them here. You'll have to create these methods to pick up the secrets from however you store them. Here's the secrets I'll be needing during this.
  • getSheetCreds is the contents of my service account json file
  • getSheetSubject is the email address of the person I want to impersonate
  • getSheetScopes is the scopes I want to be available (this should match or be a subset of those you set up back in the admin console
const {
  getSheetCreds,
  getSheetSubject,
  getSheetScopes
} = require('../private/visecrets');

Create an init function to get the authentication going. You'll see refernce to 'mode' here. That's just something I use to be able to use different credentials for different modes from my secrets file, so you can ignore it.
const init = async ({mode}) => {
  const {credentials} = getSheetCreds({mode});
  // jwt includes account to impersonate
  const auth = new google.auth.JWT(
    credentials.client_email,
    null,
    credentials.private_key,
    getSheetScopes(),
    getSheetSubject({mode})
  );
  // validate and authorize the jwt
  const {result, error} = await till(auth.authorize());
  if (!error) {
    console.log(
      `service account ready to access sheets on behalf of ${getSheetSubject({
        mode,
      })}`
    );
    return google.sheets({
      version: 'v4',
      auth,
    });
  } else {
    console.error(err);
  }
};

if you copy that exactly you'll need this little function which does async error handling
const till = waitingFor =>
  waitingFor.then(result => ({result})).catch(error => ({error}));

The init function will return a sheetsClient with all the authentication details built in, so after this point you don't need to worry about any tokens or anything else.

Accessing the sheets API

This article is about the authentication/impersonation bit - and that's all done now, but let's create a spreadsheet, a sheet, and put some data in it. We'll get to the functions that do the work shortly.
(async () => {
  // initialize auth
  const sheetClient = await init({mode});

  // make up some data
  const values = [
    ['Name', 'Profession', 'Address'],
    ['Sean Connery', 'Actor', 'Bahamas'],
    ['Richard Branson', 'Businessman', 'Necker Island'],
    ['Celine Dion', 'Singer', 'Henderson, Nevada'],
  ];

  // create a spreadsheet, add a sheet, insert some values
  createSpreadsheet({title: 'my spreadsheet', sheetClient})
    .then(spreadsheet =>
      createSheet({
        title: 'first sheet',
        spreadsheet: spreadsheet.data,
        sheetClient,
      }).then(sheet =>
        addValues({
          spreadsheet: spreadsheet.data,
          sheet: sheet.data.replies[0].addSheet.properties,
          sheetClient,
          values,
        })
      )
    )
    .catch(console.error);
})();

The created spreadsheet

Unsurprisingly, here's the result.


The Sheets API

The data structures are a bit fiddly, so that's why I like to separate them out. The approach is the same for them all, namely build the request, then fire it off. The important part if that the sheetClient knows whose account to build it in and automatically generates all the necessary token info to make it happen.

Create a spreadsheet

const createSpreadsheet = async ({title, sheetClient}) => {

  const request = {
    fields: 'spreadsheetId',
    resource: {
      properties: {
        title,
      },
    },
  };
  console.log('...creating new spreadsheet ', title);
  return sheetClient.spreadsheets.create(request);
};

Create a sheet
const createSheet = ({title, spreadsheet, sheetClient}) => {
  
  const request = {
    spreadsheetId: spreadsheet.spreadsheetId,
    resource: {
      requests: [
        {
          addSheet: {
            properties: {
              title,
            },
          },
        },
      ],
    },
  };
  console.log('...creating new sheet ', title);
  return sheetClient.spreadsheets.batchUpdate(request);
};
Add some values
const addValues = ({
  spreadsheet,
  sheet,
  sheetClient,
  values,
  rowOffset,
  columnOffset,
}) => {

  const {title, sheetId} = sheet;
  const range = `'${title}'!${rangeMaker({
    rows: values.length,
    columns: values[0].length,
    rowOffset,
    columnOffset,
  })}`;
  const request = {
    spreadsheetId: spreadsheet.spreadsheetId,
    resource: {
      valueInputOption: 'USER_ENTERED',
      data: [
        {
          range,
          values,
        },
      ],
    },
  };
  console.log('...adding values to ', range);
  return sheetClient.spreadsheets.values.batchUpdate(request);
};


Making a range

One of the annoying things about this API is that you need to generate an A1 notation range to accept the data, so you'll notice I'm using a function to do that. Here it is
/**
 * create a column label for sheet address, starting at 1 = A, 27 = AA etc..
 * @param {number} columnNumber the column number
 * @return {string} the address label
 */
const columnLabelMaker = (columnNumber, s) => {
  s =
    String.fromCharCode(((columnNumber - 1) % 26) + 'A'.charCodeAt(0)) +
    (s || '');
  return columnNumber > 26
    ? columnLabelMaker(Math.floor((columnNumber - 1) / 26), s)
    : s;
};
const rangeMaker = ({rows, columns, rowOffset, columnOffset}) =>
  `${columnLabelMaker((columnOffset || 0) + 1)}${(rowOffset || 0) +
    1}:${columnLabelMaker((columnOffset || 0) + (columns || 1))}${(rowOffset ||
    0) + (rows || 1)}`;

Next

This example is actual part of a much larger workflow which makes use of a range of APIS. If you found this useful, you may also like some of the pages here.

More

Since G+ is closed, you can now star and follow post announcements and discussions on github, here 

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'ReillyAmazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.

Comments