In 2022, Is It Still Possible to Use Google Sheets API with a Service Account?-CodePudding

The Google documentation on their Nodejs Quickstart is not great and it only shows an example of authenticating with an OAuth credential. I have a working, functional Discord bot that writes to my personal Google Sheet. However, because it's unverified by Google, my refresh token expires every 7 days and my bot will just stop working without warning until I manually go back in and give the user consent again. This is a pain, obviously.

From what I understand, a Service Account wouldn't have this issue, however, I've been unable to find a working example of the authorize function when using a Service Account JSON key. For the OAuth key, I have this:

/**
 * Create an OAuth2 client with the given credentials, and then execute the
 * given callback function.
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback to call with the authorized client.
 */
function authorize(credentials, callback) {
  const {client_secret, client_id, redirect_uris} = credentials.installed;
  const oAuth2Client = new google.auth.GoogleAuth(client_id, client_secret);

  // Check if we have previously stored a token.
  fs.readFile(TOKEN_PATH, (err, token) => {
    if (err) return getNewToken(oAuth2Client, callback);
    oAuth2Client.setCredentials(JSON.parse(token));
    return callback(oAuth2Client, arguments[2], arguments[3]);
  });
}

The extra "arguments" parameters are just values I'm passing to the callback. Does anyone have any working examples of using the current API with a Service Account in Node? Thanks!

CodePudding user response：

At least with the version 3.1.15 of google-spreadsheet that I used to work with Google Spreadsheet API I did this in order to connect using a service account (the example will be in typescript):

import { GoogleSpreadsheet } from 'google-spreadsheet';

async function getDocument(sheetId: string): Promise<GoogleSpreadsheet> {
  const doc = new GoogleSpreadsheet(sheetId);

  await doc.useServiceAccountAuth({
    client_email: serviceEmail,
    private_key: servicePrivateKey.replace(/\\n/g, '\n'),
  })
  // now we can use `doc`
  return doc;
}

Some usages:

    const doc = await getSpreadsheet(documentId);
    await doc.loadInfo();
    const sheet = doc.sheetsByTitle[sheetName];
    const totalRowCount = sheet.rowCount;
    ...
    await sheet.loadCells({
        startRowIndex: 1,
        endRowIndex: 100,
        startColumnIndex: 0,
        endColumnIndex: 10
    });
    const cell = sheet.getCell(rowIndex, columnIndex);