-1

In my web application, I have been perfectly able to access a public Google Spreadsheet until today using the following HTML/JavaScript code:

HTML

<script src="https://apis.google.com/js/client.js?onload=loadSheetsApi"></script>

JavaScript

function loadSheetsApi() {
  gapi.client.setApiKey('<<MY_API_KEY>>');
  var discoveryUrl =
    'https://sheets.googleapis.com/$discovery/rest?version=v4';
  gapi.client.load(discoveryUrl).then(<<MY_FUNCTION>>);
}

Even though I have changed neither HTML nor JavaScript, the request returns the following

Error Message

{
  "error": {
    "code": 403,
    "message": "The caller does not have permission",
    "status": "PERMISSION_DENIED"
  }
}

OAuth is not an option because I'm surfacing the spreadsheet data on a web site.

Any help is greatly appreciated!

bernland
  • 688
  • 8
  • 16
  • Did you not find the exact recent duplicates when you searched for previous questions? https://stackoverflow.com/questions/51391573/cannot-access-public-sheet-with-sheets-api https://stackoverflow.com/questions/51393986/getting-403-from-google-sheets-api-using-apikey https://stackoverflow.com/questions/51330409/public-sheet-get-access-with-api-key-works-for-days-then-permission-denied – tehhowch Jul 18 '18 at 13:46

1 Answers1

0

I had the same issue from yesterday, I only solution that I found was creating a service account and share the provided email with the document and change the code to get data. In my case y need synchronize the sheet data with some folder in a webserver. I leave you the python code if you need it.

import argparse
import sys
import json
import os

from google.oauth2 import service_account
import googleapiclient.discovery

reload(sys)
sys.setdefaultencoding('UTF8')

SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets.readonly',
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive.readonly',
    'https://www.googleapis.com/auth/drive.file',
    'https://www.googleapis.com/auth/drive'
]

SERVICE_ACCOUNT_FILE = './service.json'

credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = googleapiclient.discovery.build('sheets', 'v4', credentials=credentials)

def save_file(filename, path, data, header):
    absolute_filename = (path + '/' + filename)
    if not header:
        absolute_filename = path + '/sheets/' + filename
        print "Trying " + absolute_filename
        directory = os.path.dirname(absolute_filename)
        try:
            os.stat(directory)
        except:
            os.makedirs(directory)

        with open(absolute_filename, 'w+') as outfile:
           json.dump(data, outfile)

def get_data(spreadsheet_id, row_range):
    if row_range is None:
        request = service.spreadsheets().get(spreadsheetId=spreadsheet_id)        
    else:
        request = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=row_range)
    return request.execute()

def download_spreadsheet(args):
    spreadsheet_id = args.spreadsheetId
    output_path = args.outputPath
    head = get_data(spreadsheet_id, None)
    save_file(spreadsheet_id, output_path, head, True)
    detail = head['sheets']
    for sheet in detail:
        if 'GRID' == sheet['properties']['sheetType']:
            sheet_name = sheet['properties']['title']
            row_range = sheet_name + "!A1:AZ10000"
            data = get_data(spreadsheet_id, row_range)
            save_file(sheet_name, output_path, data, True)


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='Download google sheet from api as JSON')
    parser.add_argument('--spreadsheet-id', metavar='Your SPREADSHEET ID', dest='spreadsheetId', required=True,
                    help='Your Google\'s SpreadSheet Id')
    parser.add_argument('--output-path', metavar='Output directory', dest='outputPath', required=True,
                    help='Output path')
    download_spreadsheet(parser.parse_args())
Esteban Avendaño
  • 1