1

Using this nodeJS example, I could get the data from a public sheet.
But how do I get the data from a non-public sheet owned by me (my google a/c) ?
Is there some way to send in the username and password as arguments ?
I don't want OAuth way as I want the data to be pulled from the sheet & displayed on a public webpage.

The other option I can think of is to have OAuth2 done once write a script to handle refresh tokens automatically as a cron every hour ?

Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
anjanesh
  • 3,771
  • 7
  • 44
  • 58
  • In your situation, I thought that there are 2 patterns. 1. Use the service account. When the service account is used, the access token can be directly retrieved. 2. Use Web Apps created by Google Apps Script. When as a wrapper API, this Web Apps is used, the authorization can be done when the Web Apps is deployed. By this, when you access to the Web Apps using Node.js, you can access it using a simple API key. If those are not useful, I apologize. – Tanaike Aug 19 '22 at 07:13
  • I am interested in using the "service account" method - but how do I authorize the method to use my google credentials to access the private sheet ? – anjanesh Aug 19 '22 at 07:56
  • Thank you for replying. For example, you can see the sample script for using the service account with googleapis of Node.js at [here](https://stackoverflow.com/q/72310707) and [here](https://stackoverflow.com/q/71744751). When you access to your Spreadsheet using the service account, please share your Spreadsheet with the email of the service account. By this, you can access your Spreadsheet using the service account. – Tanaike Aug 19 '22 at 07:59
  • "please share your Spreadsheet with the email of the service account" - this is the solution - thanks so much. – anjanesh Aug 19 '22 at 08:27

1 Answers1

1

Since this is a file that you the developer own i would recommend using a service account

If you share the file with the service account it will then have permissions to access it without you needing to go though the oauth2 steps of authorizing your application.

On google cloud console simply create Service account credentials

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

const auth = new google.auth.GoogleAuth({
  keyFile: '/path/to/your-secret-key.json',
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});

Then change your auth code slightly. Open the service account key file and look for the service account email address its the only one witha @ in it. Share the file with the service account like you would any other user in google drive web app.

Once it has access you shouldn't need to authorize the app again.

I have a video on Google drive API upload file with Nodejs + service account which might help you a bit you just need the authorization code. Everything else you have should work as is.

Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449