javascript - Using default credentials for accessing Google Sheets - Stack Overflow

admin管理员组

文章数量:1401379

I have some data on Google sheets. I am using Sheets API for Node.js to access this data. For authentication, I am using Service Account JSON to generate a JWTClient.

I can access the data from sheets api and everything is working fine. The code is hosted on Google App Engine Flexible environment. It works fine for both localhost and on GAE.

The only problem is that I needed to provide the service account key json for authentication. This is fine in local environment, but on App Engine it provides a default credential for service account(I am using the same service account). So I think I should not provide the credentials manually and let GAE take care of the credentials on server.

But it's not authenticating automatically and still an auth parameter.

Here's my current implementation of the sheets api -

const { promisify } = require('util');
const google = require('googleapis');
const auth = require('./auth');
const credentials = require('../configs/credentials');

const sheets = google.sheets('v4');
const getSheetValuesAsync = promisify(sheets.spreadsheets.values.get);
//region private
function readSheet(params) {
  return getSheetValuesAsync(params);
}
//endregion private

//region public
async function get(sheetConfigName) {
  const sheetConfig = credentials.sheets[sheetConfigName];
  //This is theJWTClient which authenticates the api request currently.
  //I want to remove this
  const authClient = await auth.authorizeWithServiceAccount('serviceAccountName', [
    '.readonly'
  ]);
  console.log('Client received');
  let params = {
    auth: authClient, //I tried removing this parameter, but it didn't work
    spreadsheetId: sheetConfig.id,
    range: 'A:M'
  };
  let data = await readSheet(params);
  return data;
}
//endregion public
module.exports = {
  get: get
};

The error I am getting is as following:-

{
  code:403,
  message: 'The request is missing a valid API key.
}

I have setup GOOGLE_APPLICATION_CREDENTIALS environment variable for local environment already which worked for google-cloud APIs, but not working for sheets.

So is Application Default Credentials just for google-cloud APIs or can it be used with Sheets API or any other Drive APIs? If it can work with drive APIs too, how can it be done?

I have some data on Google sheets. I am using Sheets API for Node.js to access this data. For authentication, I am using Service Account JSON to generate a JWTClient.

I can access the data from sheets api and everything is working fine. The code is hosted on Google App Engine Flexible environment. It works fine for both localhost and on GAE.

The only problem is that I needed to provide the service account key json for authentication. This is fine in local environment, but on App Engine it provides a default credential for service account(I am using the same service account). So I think I should not provide the credentials manually and let GAE take care of the credentials on server.

But it's not authenticating automatically and still an auth parameter.

Here's my current implementation of the sheets api -

const { promisify } = require('util');
const google = require('googleapis');
const auth = require('./auth');
const credentials = require('../configs/credentials');

const sheets = google.sheets('v4');
const getSheetValuesAsync = promisify(sheets.spreadsheets.values.get);
//region private
function readSheet(params) {
  return getSheetValuesAsync(params);
}
//endregion private

//region public
async function get(sheetConfigName) {
  const sheetConfig = credentials.sheets[sheetConfigName];
  //This is theJWTClient which authenticates the api request currently.
  //I want to remove this
  const authClient = await auth.authorizeWithServiceAccount('serviceAccountName', [
    'https://www.googleapis./auth/spreadsheets.readonly'
  ]);
  console.log('Client received');
  let params = {
    auth: authClient, //I tried removing this parameter, but it didn't work
    spreadsheetId: sheetConfig.id,
    range: 'A:M'
  };
  let data = await readSheet(params);
  return data;
}
//endregion public
module.exports = {
  get: get
};

The error I am getting is as following:-

{
  code:403,
  message: 'The request is missing a valid API key.
}

I have setup GOOGLE_APPLICATION_CREDENTIALS environment variable for local environment already which worked for google-cloud APIs, but not working for sheets.

So is Application Default Credentials just for google-cloud APIs or can it be used with Sheets API or any other Drive APIs? If it can work with drive APIs too, how can it be done?

• javascript
• node.js
• google-app-engine
• google-cloud-platform
• google-sheets-api

Share Improve this question Follow asked Mar 29, 2018 at 19:20 0xC0DED00D0xC0DED00D 20.4k2323 gold badges125125 silver badges197197 bronze badges 4

• Did you share the sheet with the service account? – LundinCast Commented Mar 30, 2018 at 10:06
• @LundinCast The sheet has public access, so it isn't required. – 0xC0DED00D Commented Mar 30, 2018 at 10:15
• Then you could use an API key instead. Or do you need to use OAuth 2.0? – LundinCast Commented Mar 30, 2018 at 12:01
• Yeah, I could use the API key and it works with that too. My target is to minimize extra credentials in code. I don't want to have the API key or service account in the code. Since the service account is already available and easily manageable, I want to use that same account. Multiple credentials are pain to manage. – 0xC0DED00D Commented Mar 30, 2018 at 16:48

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 2

If you want to use Google Sheets API, you need to provide an OAuth service account as indicated in the documentation. The default service account is mostly designed to be used when pulling data from within the GCP (Google Cloud Platform). So your Compute Engine can access your Cloud Datastore or if your App Engine can access your Cloud Storage.

The default service account do not have a client secret JSON, which is needed by Sheets API.

Alternatively, you can use an API key for public docs if you want to use Google Sheets REST API.

Google Cloud service accounts are meant to access Cloud APIs and cannot be granted the appropriate scope for Sheets API.

Since App Engine Flexible applications run on Compute Engine VMs, you can get the token for the default service account by querying the metadata server like so:

> curl "http://metadata.google.internal/puteMetadata/v1/instance/service-accounts/default/token" -H "Metadata-Flavor: Google"

However, when calling the Sheets API providing that token, it gives the following error:

{
  "error": {
    "code": 403,
    "message": "Request had insufficient authentication scopes.",
    "status": "PERMISSION_DENIED"
  }
}

I believe you'll need to use the API key for your use case.

In the latest update to Google's Node.js client package (28.x), they are having a new way to get the authClient without requiring a second package. Here's how it's done -

const { google } = require('googleapis');
  const authClient = await google.auth.getClient({
  credentials: credentials,
  scopes: scopes
});

credentials is just the service account JSON object. The best part is, if you don't provide that property, it'll check the GOOGLE_APPLICATION_CREDENTIALS environment variable which will have the absolute path for the service account key JSON and automatically consider it for authentication.
You can use the authClient generated for setting the auth parameter for google sheets API using either of the following way -

First is when you want to provide different auth for different API calls.

const { google } = require('googleapis');
const sheets = google.sheets('v4');
sheets.spreadsheets.values.getAsync = promisify(sheets.spreadsheets.values.get);
sheets.spreadsheets.values.getAsync({
    auth: authClient,
    spreadsheetId: spreadsheetId,
    range: range
  });

or you can provide the auth to the sheets object directly. All the API calls on that object will use the same authClient and will not need the auth parameter.

const sheets = google.sheets({version:'v4',auth:authClient);

The best way is to make the auth default for all the Google APIs calls and then you won't have to pass authClient at all to any sheets object or API calls.

google.options({
  auth: authClient
});

You can still override the auth parameter in either sheets (or drive or any other object) API object or to any API call specifically, in case you plan to use different service accounts for different APIs.

For testing in local you can always set the environment path, so that you don't actually have to pass the service account to google.auth object. Here's how I did it

let resolve = require('path').resolve;
if (process.env.NODE_ENV === 'production') {
  //Set config based on production environment. It won't be set in local environment, unless you specially set it.
} else if (!process.env.GOOGLE_APPLICATION_CREDENTIALS) {
  process.env.GOOGLE_APPLICATION_CREDENTIALS = resolve(
      './relative/path/to/the/json/key.json'
  ); //resolve will just convert relative path to the absolute path.
}

本文标签： javascriptUsing default credentials for accessing Google SheetsStack Overflow