Service account impersonation for Google APIS with Nodejs client

Preparation

• 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, it’s 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)

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

const {
  getSheetCreds,
  getSheetSubject,
  getSheetScopes
} = require('../private/visecrets');

Create an init function to get the authentication going. You’ll see reference 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

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

/**
 * 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)}`;