'How to fetch private data using Googlesheets API to my website using nodeJs

I need to access some data from a private Google Sheets document that only my google account has access to – it is not shared with anyone else.

From here: https://developers.google.com/sheets/guides/authorizing

When your application requests private data, the request must be authorized by an authenticated user who has access to that data.

Again, that user would be me – the application developer. The users of my application will not have these sheets shared with them.

From what I’m reading in the Google API docs, I’m not sure this is possible – but it seems to me like it should be. I am using nodeJs.

Can anyone help me out?

google-sheets-api


Solution 1:[1]

You'll want to use Google's OAuth flow (here) in order to get access to private sheets. If only you need to be able to access the sheet then you can keep the OAuth as only tied to yourself and make requests to the endpoint in your app.

Example Implementation                                                                                 View in Fusebit 
  // Create a sheet object for the client + auth
  const sheets = google.sheets({
    version: 'v4',
    auth
  })

  // Pull the data from our spreadsheet
  const data = (await sheets.spreadsheets.values.get({
      // See: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
      spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms', // ID of the spreadsheet
      range: 'Class Data!A2:E', // This gets all of the data in the sheet that we care about
  })).data;
  
  // Your rows/columns go into "data.values"
  const rows = data.values;
});

Solution 2:[2]

Service account with domain-wide delegation:

The only way to avoid user interaction when accessing user private data is to use a service account with domain-wide delegation to impersonate your regular account.

That requires being a domain admin, though, so I'm not sure this option is available to you.

Sharing the spreadsheet with a service account:

Another option would be to share the spreadsheet with the service account, so you can access it using that account. Service accounts are not linked to a specific user, and so follow a different OAuth flow which doesn't require user interaction. See reference below.

Otherwise:

If you are not a domain admin and you don't want to share the spreadsheet with a service account, you are left with the web server workflow, which requires user interaction: you just cannot bypass this interaction.

I'd suggest you to read the references linked below.

Reference:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Joe Iliff
Solution 2 Iamblichus

Related Questions