google sheets api key missing when using oAuth2

1
votes

I'm trying to use the google sheets API with oAuth2 in an express application. I've followed the basic setup to read data from a sheet but when I make a request, I get back an error that I'm missing an API key in the response.

As far as I can tell I am checking to see if a token is needed using the authorize() function and if it already exists, passing it in the request. Since the token should authenticate the request, why would I be getting this error?

*I have also already allowed access to the api through my account

checking authorization token

/**
 * 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.OAuth2(
      client_id, client_secret, redirect_uris[0]);

  // 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));
    callback(oAuth2Client);
  });
}

making a call to grab the sheet data

async function grabSheetData () {
  const authClient = await authorize(creds, ()=>{console.log('success')}); 

  const request = {
    spreadsheetId: 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 
    range: 'A1:C2',
    valueRenderOption: 'ValueRenderOption.FORMATTED_VALUE',  
    dateTimeRenderOption: '[DateTimeRenderOption.SERIAL_NUMBER]',  
    auth: authClient, //this should be my token
  }

  try {
    const response = (await sheets.spreadsheets.values.get(request)).data;
    // TODO: Change code below to process the `response` object:
    console.log(JSON.stringify(response, null, 2));
  } catch (err) {
    console.error(err);
  }
};

grabSheetData();
1
node.jsexpressgoogle-sheetsgoogle-sheets-api

1 Answers

1
votes
  • You want to use Sheets API using the access token retrieved with OAuth2.
  • You want to achieve this using googleapis with Node.js.
  • You have already been able to retrieve the access token for using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • In your script, I think that authClient returns undefined. Because authorize has no returned values.
    • I think that this is the reason of your issue in your question.
  • Also, I think that when the correct authClient is returned, an error occurs at sheets.spreadsheets.values.get(request). Because the values of valueRenderOption: 'ValueRenderOption.FORMATTED_VALUE' and dateTimeRenderOption: '[DateTimeRenderOption.SERIAL_NUMBER]' are not correct.

When above points are reflected to your script, it becomes as follows.

Modified script:

authorize:

In this modification, I modified your script without modifying the function of getNewToken().

function authorize(credentials) {
  return new Promise(resolve => {  // Added
    const { client_secret, client_id, redirect_uris } = credentials.installed;
    const oAuth2Client = new google.auth.OAuth2(client_id, client_secret, redirect_uris[0]);

    // Check if we have previously stored a token.
    fs.readFile(TOKEN_PATH, (err, token) => {
      if (err) return getNewToken(oAuth2Client, e => resolve(e));  // Modified
      oAuth2Client.setCredentials(JSON.parse(token));
      resolve(oAuth2Client);  // Modified
    });
  });
}

grabSheetData:

async function grabSheetData() {
  const authClient = await authorize(creds);  // Modified

  const request = {
    spreadsheetId: 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 
    range: "A1:C2",
    valueRenderOption: "FORMATTED_VALUE",  // Modified
    dateTimeRenderOption: "SERIAL_NUMBER",  // Modified
    auth: authClient //this should be my token
  };

  try {
    const response = (await sheets.spreadsheets.values.get(request)).data;
    // TODO: Change code below to process the `response` object:
    console.log(JSON.stringify(response, null, 2));
  } catch (err) {
    console.error(err);
  }
}

grabSheetData();

Note:

  • In this modification, it supposes that creds and sheets have already been declared elsewhere. So please be careful this.

References:

If I misunderstood your question and this was not the direction you want, I apologize.