Google Sheet API v4

Question

I try to use example "Read multiple ranges":

https://sheets.googleapis.com/v4/spreadsheets/{SpreadsheetID}/values:batchGet?ranges=Sheet1!B:B&ranges=Sheet1!D:D&valueRenderOption=UNFORMATTED_VALUES?majorDimension=COLUMNS

from: Google Sheets API v4 example I replaced "spreadsheetId" with my spreadsheet id. But when I send GET request (by Postman) I receive:

{
  "error": {
    "code": 403,
    "message": "The request is missing a valid API key.",
    "status": "PERMISSION_DENIED"
  }
}

Than I used https://developers.google.com/oauthplayground/ I made authorisation for Spreadsheet API v4 with my email and at the end I generated a long link with key and saw correct response with content of my data table.

My question is next: Why it's imposible to use the example from Google API (https://developers.google.com/sheets/api/samples/reading) only replace the spreadsheet id. P.s Link to my spreadsheet is public, My spreadsheet is public and available in JSON format and what is correct use of url for Google Spreadsheet API v4 in order to send batchGet with multiple requests?

P.s.s I use another approach and it works but why first approach (v4) doesn't work:

https://spreadsheets.google.com/feeds/list/{SpreadsheetID}/od6/public/values?alt=json

When I tried to combine 2 APIs in one request and received and error "Inconsistent repeating query parameter ranges":

https://spreadsheets.google.com/feeds/list/{SpreadsheetID}/od6/public/values:batchGet?ranges=Sheet1!B:B&ranges=Sheet1!D:D&valueRenderOption=UNFORMATTED_VALUES?majorDimension=COLUMNS

======================================
Edited:

SpreadSheetID: 1KBk1J7TJCwnayMnTbaysqvFk98kVDdxXWNw2JYc3bW0

Available in incognito mode: Spreadsheet

In JSON format: JSON

Reference: Google Sheets API v4: Read multiple ranges

The same example with SpreadSheetID: ERROR 403

Answer 1

I believe you are missing API in the url. This is from google spreadsheet api doc.

To acquire an API key:

Open the Credentials page in the API Console.

API keys: A request that does not provide an OAuth 2.0 token must send an API key. The key identifies your project and provides API access, quota, and reports.

The API supports several types of restrictions on API keys. If the API key that you need doesn't already exist, then create an API key in the Console by clicking Create credentials > API key. You can restrict the key before using it in production by clicking Restrict key and selecting one of the Restrictions. To keep your API keys secure, follow the best practices for securely using API keys.

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

The API key is safe for embedding in URLs; it doesn't need any encoding.

Google Spreadsheet Docs

Answer 2

You have to make sure to pass in the key=YOUR_API_KEY as the request parameter. For example:

https://sheets.googleapis.com/v4/spreadsheets/{sheet_id}/values/Sheet1!A1:D5?key={YOUR_API_KEY}

Answer 3

In addition to @skamble89's answer, this 403 error is usually caused by incorrect or missed some configuration in Authorizing Requests. If you're authorizing requests with OAuth 2.0, make sure you use the proper scope with it.

Make sure you have a permission to call this spreadsheet and you enable the Sheets API in your developer console.

Answer 4

• The Sheet must have a Public link for the API KEY to be used.
• You have to share the spreadsheet, select "Get Link", and "Anyone with the Link".
• That gives a link like this: https://docs.google.com/spreadsheets/d/1ShTP408LPzLOGjqXQTRcmXXXXXXXXXXXXXPUteQmY7o/edit?usp=sharing.
• So there, the Sheet ID is after: /d/, is: 1ShTP408LPzLOGjqXQTRcmXXXXXXXXXXXXXPUteQmY7o.

curl "https://sheets.googleapis.com/v4/spreadsheets/1ShTP408LPzLOGjqXQTRcmXXXXXXXXXXXXXPUteQmY7o/values/A1%3AD3?key={YourApy"