Google Sheets API v4 receives HTTP 401 responses for public feeds

Question

I'm having no luck getting a response from v4 of the Google Sheets API when running against a public (i.e. "Published To The Web" AND shared with "Anyone On The Web") spreadsheet.

The relevant documentation states:

"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."

And to provide the API key, the documentation states:

"After you have an API key, your application can append the query parameter key=yourAPIKey to all request URLs."

So, I should be able to get a response listing the sheets in a public spreadsheet at the following URL:

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}?key={myAPIkey}

(with, obviously, the id and key supplied in the path and query string respectively)

However, when I do this, I get an HTTP 401 response:

{
  error: {
    code: 401,
    message: "The request does not have valid authentication credentials.",
    status: "UNAUTHENTICATED"
  }
}

Can anyone else get this to work against a public workbook? If not, can anyone monitoring this thread from the Google side either comment or provide a working sample?

Answer 1

I managed to get this working. Even I was frustrated at first. And, this is not a bug. Here's how I did it:

1. First, enable these in your GDC to get rid of authentication errors.

-Google Apps Script Execution API

-Google Sheets API

Note: Make sure the Google account you used in GDC must be the same account you're using in Spreadsheet project else you might get a "The API Key and the authentication credential are from different projects" error message.

2. Go to https://developers.google.com/oauthplayground where you will acquire authorization tokens.
3. On Step 1, choose Google Sheets API v4 and choose https://www.googleapis.com/auth/spreadsheets scope so you have bot read and write permissions.
4. Click the Authorize APIs button. Allow the authentication and you'll proceed to Step 2.
5. On Step 2, click Exchange authorization code for tokens button. After that, proceed to Step 3.
6. On Step 3, time to paste your URL request. Since default server method is GET proceed and click Send the request button.

Note: Make sure your URL requests are the ones indicated in the Spreadsheetv4 docs.

Here's my sample URL request:

https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?includeGridData=false

I got a HTTP/1.1 200 OK and it displayed my requested data. This goes for all Spreadsheetv4 server-side processes.

Hope this helps.