Search code examples
javascriptgoogle-sheets-apifetch-api

javascript read google sheet value.getBatch issue

I could use javascript fetch() to read a google sheet single value without an issue. However, when tried reading multiple ranges, get error 403.

This code works fine:

const apiKey = 'key-here'; 
const spreadsheetId = 'sheet-id-here';
const sheetName = 'Sheet1';

values = values = fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${sheetName}?key=${apiKey}`)
.then(response => response.json())
.then (data => console.dir (data))
.catch(error => console.error('Google Sheet Reading Error: ', error));

but when replace the fetch() with values.getBatch, Browser's Inspect panel displays error 403:

values = fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet?ranges=Sheet1!A1:A2&ranges=subtitle!B1:B2?key=${apiKey}`)

The error is:

code: 403
message: "Method doesn't allow unregistered callers (callers without established identity). Please use API Key or other form of API consumer identity to call this API."
status: "PERMISSION_DENIED"

The doc about values:getBatch is at https://developers.google.com/sheets/api/samples/reading

Solution

  • Modification points:

    • In your script, I think that ? of ?key=${apiKey} should be &. I think that this is the reason for your current issue of PERMISSION_DENIE.

    • Also, please do the URL encode as follows.

      • https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet?ranges=ranges=Sheet1!A1%3AA2&ranges=subtitle!B1%3AB2&key=${apiKey}

    When these points are reflected in your script, it becomes as follows.

    Modified script:

    values = fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet?ranges=Sheet1!A1%3AA2&ranges=subtitle!B1%3AB2&key=${apiKey}`)