Cannot access public sheet with Sheets API

2020-03-03 08:03发布

For over a year I've used the Google Sheets APIv4 on a web page to present data from a publicly shared Google Spreadsheet.

Even though nothing has changed on our end, the sheets API is now returning a 403, Permission Denied error. The sheet I am trying to access is one that I own and the sharing settings do appear to be correct.

This issue was reported to me on 7/17/18.

I am using Javascript to access the public sheet and since no authentication should be needed, only an API key is specified.

If I try to open the public sheet using it's public URL within a "Guest" browser window, the sheet is visible and NO authentication is requested.

Why would the API suddenly be refusing to read a publicly shared sheet?

Here is the code I am using to retrieve the public sheet.

gapi.client.init({
            'apiKey': 'AIzaSyC5fTcQun-1r83DVWlPmGAZpoUGPNcuM34',
            'discoveryDocs': ['https://sheets.googleapis.com/$discovery/rest?version=v4']
        }).then(function() {
            return gapi.client.sheets.spreadsheets.values.get({
                'spreadsheetId': '1e3KBd0277mz79IgxNch1-lwWdlJGiJQSe2eMNcXWYes',
                 'range': 'Sheet1!A:E'
            });
 }, errorMessage).then(function(response) { // Code to process the response });

At the top of the html file that links to the script that reads the public spreadsheet, gapi is loaded by inserting a script tag whose src tag = "https://apis.google.com/js/api.js"

Then I use gapi.load('client', getData) to catch the client library load event, where getdata is the name of the function containing the code snippet quoted above.

Here is the direct URL to the public sheet:

NOTE: The API key in the code above is domain restricted to our domain only.

If I change the code slightly and make it so that authorization is required, by adding an OAuth clientId and Scope to the gapi.client.init() call, the sheets API does successfully retrieve the spreadsheet, but that defeats the object of making the sheet public.

Here is a snapshot of the sharing settings on the public sheet

0条回答
登录 后发表回答