Access Google spreadsheet API without auth token

Question

I have created Google Spreadsheet, and given edit access to all (can edit even without login).

Here is the link. I would like to update this sheet with Google Spreadsheet API. But I am getting error. My requirement is update the sheet thru API even without access credential.

Answer 1

You need to be authorized to make such requests

Every request your application sends to the Google Sheets API needs to identify your application to Google. There are two ways to identify your application: using an OAuth 2.0 token (which also authorizes the request) and/or using the application's API key. Here's how to determine which of those options to use:

If the request requires authorization (such as a request for an individual's private data), then the application must provide an OAuth 2.0 token with the request. The application may also provide the API key, but it doesn't have to. If the request doesn't require authorization (such as a request for public data), then the application must provide either the API key or an OAuth 2.0 token, or both—whatever option is most convenient for you.

That's it. There's no bypassing authorization.

Answer 2

It is possible to write to spreadsheet without OAuth or API Keys. You need to use Service Account Keys.

Here is what I did for my Node.js environment.

1. Get a service account key from https://console.cloud.google.com/apis/credentials (You can here also restrict what this keys is allowed todo)
   1. When creating, make sure you click the Furnish a new private key
   2. Select JSON when it asks you how to download the key.
2. The service account key you have just generated includes a client_email.
   1. Go to you google spreadsheet and allow this client_email to have write access on this document

3. Use the following code to authenticate

   let jwtClient = new google.auth.JWT(client_email, null, private_key, [ "https://www.googleapis.com/auth/spreadsheets", ]); //authenticate request jwtClient.authorize(function(err, tokens) { // at this point the authentication is done you can now use `jwtClient` // to read or write to the spreadsheet });

client_email and private_key are part of the service account key

A more detailed description can be found here. http://isd-soft.com/tech_blog/accessing-google-apis-using-service-account-node-js/ Also, all credit goes to this page.

Answer 3

Finally digged deep enough and found the answer. Any kind of writing, even to publicly editable sheets requires an OAuth flow:

https://issuetracker.google.com/issues/36755576#comment3