Unauthorized for only clear requests Google Sheets API

Question

I am using the Google Sheets API v4 in a node.js application. I am authenticating using a JWT client with the scope set to 'http://www.googleapis.com/auth/spreadsheets'. I find that I can successfully update a sheet or perform an append operation, but get a 401 Unauthorized error when trying to clear a sheet.

In my development environment I am using a service user that is outside of the organization that I belong to. Could this have an effect on which calls are authorized? Looking at the documentation for setting sharing permission at the organizational level, it doesn't seem like the controls are that fine-grained.

    auth(scopes) {
    const key = process.env.GOOGLE_SERVICE_ACCOUNT_PRIVATE_KEY;
    const email = process.env.GOOGLE_SERVICE_ACCOUNT_CLIENT_EMAIL;
    const jwtClient = new google.auth.JWT(email, null, key, scopes);
    jwtClient.authorize((err) => {
      if (err) {
        console.log(err);
      }
    });
    return jwtClient;
  }

...

async updateSheet(jwtClient, spreadsheet_id, sheet_name, data) {
     const write = util.promisify(sheets.spreadsheets.values.update);
     const body = {
         majorDimension: 'COLUMNS',
         values: data
     };
     const request = {
         spreadsheetId: spreadsheet_id,
         range: sheet_name,
         valueInputOption: 'user_entered',
         resource: body,
         auth: jwtClient,
     };
     const response = await write(request);
     return response;
 },
 async clearSheet(jwtClient, spreadsheet_id, sheet_name) {
     const clear = util.promisify(sheets.spreadsheets.values.clear);
     const request = {
         spreadsheetId: spreadsheet_id,
         range: sheet_name
     };
     const response = await clear(request);
     return response;
 }

Using the same jwt client, spreadsheet id, and sheet name, the first call is successful, but the second call fails.

await google.updateSheet(google_jwtClient, spreadsheet_id, sheet_name, data);
await google.clearSheet(google_jwtClient, spreadsheet_id, sheet_name);

{ message: 'Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.', domain: 'global', reason: 'unauthorized' }

DaImTo DaImTo · Accepted Answer · 2018-03-26T13:13:41

'Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential.

Means that your not sending the credentials with your request.

Your not applying the jwtClient to the request.

 async clearSheet(jwtClient, spreadsheet_id, sheet_name) {
     const clear = util.promisify(sheets.spreadsheets.values.clear);
     const request = {
         spreadsheetId: spreadsheet_id,
         auth: jwtClient,
         range: sheet_name
     };
     const response = await clear(request);
     return response;
 }

Unauthorized for only clear requests Google Sheets API

1 Answers