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?

are you using the GET command with the URL that looks something like: "GET sheets.googleapis.com/v4/spreadsheets/spreadsheetId{spreadsheetId}?key=‌{myAPIk‌ey} then posting it in the URL nav bar? — noogui, May 20 '16 at 08:16
With an "https" scheme, the "{spreadsheetId}" token replaced by the ID of a public spreadsheet, and the "{myAPIkey}" token replaced by a valid API key for my project, yes. — Jon Shrike, May 20 '16 at 09:02

noogui · Answer 1 · 2016-06-07T08:15:02.790

17

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

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.

Go to https://developers.google.com/oauthplayground where you will acquire authorization tokens.
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.
Click the Authorize APIs button. Allow the authentication and you'll proceed to Step 2.
On Step 2, click Exchange authorization code for tokens button. After that, proceed to Step 3.
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.

edited Jun 07 '16 at 08:15

answered May 20 '16 at 09:07

noogui

15,376
3
18
42

I just tried this approach, but I got the same results as described in the original post. Apparently, this may be a known issue as Sam has indicated. If you get it to work, though, please let me know. – Jon Shrike May 22 '16 at 03:47
It only works when tried from GDC, I tried to use the same token using Curl and it fails. key={API KEY} approach is also not working. I wonder if it is fixed! – wabbit May 02 '17 at 03:22
@wabbit are you getting errors like "insufficient permission" or "login required" ? "it doesn't work" doesn't describe the problem. – noogui May 02 '17 at 07:23
@noogui I tried using the same token generated by GDC in a command on shell and it still shows up as UNAUTHENTICATED (code: 401). Similar to what is mentioned in the initial question – wabbit May 02 '17 at 18:09
What exactly does this accomplish? It seems like it is just testing the request... – TulsaNewbie Jun 18 '19 at 20:59
and testing the request means, the service is available. However, reading between the lines, it seems you want me to write you code? (i have a code example BTW :D in one of these posts) – noogui Jun 24 '19 at 08:23

Sam Berlin · Answer 2 · 2016-06-14T00:19:04.477

6

We recently fixed this and it should now be working. Sorry for the troubles, please try again.

The document must be shared to "Anyone with the link" or "Public on the web". (Note: the publishing settings from "File -> Publish to the web" are irrelevant, unlike in the v3 API.)

edited Jun 14 '16 at 00:19

answered May 19 '16 at 13:50

Sam Berlin

2,993
8
18

Thanks, Sam. I'll be looking forward to the fix. – Jon Shrike May 19 '16 at 17:06
So what's the workaround for now? This still isn't working and I can't find any docs from v3 on how to achieve this. – JVG May 31 '16 at 04:48
@JonShrike -- sorry, the only workaround for now is to pass an authentication token... which isn't much of a workaround. – Sam Berlin Jun 07 '16 at 18:38
@JonShrike this should be working now, please see the edits to my answer. Sorry for the delay! – Sam Berlin Jun 14 '16 at 00:19
I'm passing an apitoken and it still says Request is missing required authentication token. The sheet is public. – The Muffin Man Apr 13 '17 at 23:33
2

@SamBerlin still seems broken to me – tayl0rs Sep 28 '17 at 01:01
@SamBerlin this is broken for me – Bennett Talpers Dec 13 '17 at 19:25
1

@sww314 -- The bug you filed refers to writing, this question refers to reading. Read requests work fine w/ only an apikey. – Sam Berlin Feb 28 '18 at 15:34

Jarek · Answer 3 · 2018-03-26T19:17:21.530

This is not a solution of the problem but I think this is a good way to achieve the goal. On site http://embedded-lab.com/blog/post-data-google-sheets-using-esp8266/ I found how to update spreadsheet using Google Apps Script. This is an example with GET method. I will try to show you POST method with JSON format.

How to POST: Create Google Spreadsheet, in the tab Tools > Script Editor paste following script. Modify the script by entering the appropriate spreadsheet ID and Sheet tab name (Line 27 and 28 in the script).

function doPost(e)
{
  var success = false;
  if (e != null)
  {
    var JSON_RawContent = e.postData.contents;
    var PersonalData = JSON.parse(JSON_RawContent);

    success = SaveData(
      PersonalData.Name, 
      PersonalData.Age, 
      PersonalData.Phone
    );
  }
  // Return plain text Output
    return ContentService.createTextOutput("Data saved: " + success);
}

function SaveData(Name, Age, Phone)
{
  try 
  {
    var dateTime = new Date();

    // Paste the URL of the Google Sheets starting from https thru /edit
    // For e.g.: https://docs.google.com/---YOUR SPREADSHEET ID---/edit 
    var MyPersonalMatrix = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/---YOUR SPREADSHEET ID---/edit");
    var MyBasicPersonalData = MyPersonalMatrix.getSheetByName("BasicPersonalData");


    // Get last edited row
    var row = MyBasicPersonalData.getLastRow() + 1;

    MyBasicPersonalData.getRange("A" + row).setValue(Name);
    MyBasicPersonalData.getRange("B" + row).setValue(Age); 
    MyBasicPersonalData.getRange("C" + row).setValue(Phone); 

    return true;
  }
  catch(error) 
  {
    return false;
  }
}

Now save the script and go to tab Publish > Deploy as Web App.

Execute the app as: Me xyz@gmail.com,

Who has access to the app: Anyone, even anonymous

Then to test you can use Postman app.

Or using UWP:

private async void Button_Click(object sender, RoutedEventArgs e)
{
    using (HttpClient httpClient = new HttpClient())
    {
        httpClient.BaseAddress = new Uri(@"https://script.google.com/");
        httpClient.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
        httpClient.DefaultRequestHeaders.AcceptEncoding.Add(new System.Net.Http.Headers.StringWithQualityHeaderValue("utf-8"));
        string endpoint = @"/macros/s/---YOUR SCRIPT ID---/exec";

        try
        {
            PersonalData personalData = new PersonalData();
            personalData.Name = "Jarek";
            personalData.Age = "34";
            personalData.Phone = "111 222 333";

            HttpContent httpContent = new StringContent(JsonConvert.SerializeObject(personalData), Encoding.UTF8, "application/json");
            HttpResponseMessage httpResponseMessage = await httpClient.PostAsync(endpoint, httpContent);
            if (httpResponseMessage.IsSuccessStatusCode)
            {
                string jsonResponse = await httpResponseMessage.Content.ReadAsStringAsync();
            //do something with json response here
            }
        }
        catch (Exception ex)
        {

        }
    }
}

public class PersonalData
{
    public string Name;
    public string Age;
    public string Phone;
}

To above code NuGet Newtonsoft.Json is required.

Result:

score 0 · Answer 4 · answered Feb 12 '19 at 17:54

0

If your feed is public and you are using api key, make sure you are throwing a http GET request.In case of POST request, you will receive this error. I faced same. Getting data using Method: spreadsheets.getByDataFilter has POST request

answered Feb 12 '19 at 17:54

namrata agarwal

1

Google Sheets API v4 receives HTTP 401 responses for public feeds

4 Answers4

Linked