Google Sheets API returns "The caller does not have permission" when using server key

google sheets api example
google sheets api key
google sheets rest api
google sheets api json
google sheets api tutorial
google sheets api python
google sheets api pricing
google sheet api php

I've generated a server key in the API Manager and attempted to execute the following on my Mac:

curl 'https://sheets.googleapis.com/v4/spreadsheets/MySheetID?ranges=A1:B5&key=TheServerKeyIGeneratedInAPIManager'

But this is what it returns:

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

What am I doing wrong here?

To solve this issue, try to:

  1. create a service account: https://console.developers.google.com/iam-admin/serviceaccounts/
  2. in options, create a key: this key is your usual client_secret.json - use it the same way
  3. add owner permission to this service account (Member name = service account ID = service account email ex: thomasapp@appname-201813.iam.gserviceaccount.com
  4. copy the email address of your service account = service account ID
  5. simply go in your browser to the Google sheet you want to interact with
  6. go to SHARE on the top right of your screen
  7. go to advanced settings and share it with email address of your service account ex: thomasapp@appname-201813.iam.gserviceaccount.com

It worked for me :)

Sheets, There are only two possible projection settings in the Sheets API v3. full projection returns all the available information, whereas basic returns a  This request returns an AddSheetResponse, consisting of an object with the created sheet's properties (such as its sheetId). The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.

I know it is a little late to answer but for other people struggling with the same issue. Just change the permission of the sheet to public on your drive so it can be accessed without authentication via API calls.

To change access:

  1. Open sheet in google drive
  2. On top right corner, click share
  3. On bottom of prompt window, click advanced
  4. Change permission to public or people with link (no signin required)

Send API request to fetch data from sheets without authentication.

Note: if the sheet contains sensitive data then it is not safe to make it public and rather do it with Authenticated access.

Google Sheets API, JSON representation. { // Union field kind can be only one of the following: "​addNamedRange": { object ( AddNamedRangeResponse ) }  Acquiring and using an API key Requests to the Google Sheets API for public data must be accompanied by an identifier, which can be an API key or an access token. To acquire an API key: Open the Credentials page in the API Console. This API supports two types of credentials. Create whichever credentials are appropriate for your project:

Make sure to pay attention to @KishanPatel's comment:

Also, you can share this sheet with specific email Ex. your service account (project) email. "client_email": "XXXXX@northern-gasket-XXXX.iam.gserviceaccount.com", This will allow to access sheet by your script.

Migrate to the Google Sheets API, Returns a range of values from a spreadsheet. The caller must specify the spreadsheet ID and a range. HTTP request. GET https://sheets. Open sheet in google drive; On top right corner, click share; On bottom of prompt window, click advanced; Change permission to public or people with link (no signin required) Send API request to fetch data from sheets without authentication.

The easiest way is to fix using gcloud cli. More docs here https://cloud.google.com/pubsub/docs/quickstart-cli#before-you-begin

install gcloud

sudo apt-get install google-cloud-sdk

then call

gcloud init

then check your active project and credentials

gcloud config configurations list

If it is not ok, make sure you are authenticated with the correct account:

gcloud auth list
* account 1
  account 2

Change to the project's account if not:

gcloud config set account `ACCOUNT`

Depending on the account, the project list will be different:

gcloud projects list

- project 1
- project 2...

Switch to intended project:

gcloud config set project `PROJECT NAME`

Then Create Application Default Credentials with gcloud auth application-default login, and then google-cloud will automatically detect such credentials.

Responses | Sheets API, Limiting the range will return only the portions of the spreadsheet that intersect the requested ranges. Ranges are specified using A1 notation. REST Resource: v4.spreadsheets.sheets; REST Resource: v4.spreadsheets.values; Service: sheets.googleapis.com. We recommend that you call this service using Google-provided client libraries. If your application needs to call this service using your own libraries, you should use the following information when making the API requests. Discovery

Method: spreadsheets.values.get | Sheets API, The Google Sheets API lets you read and modify any aspect of a spreadsheet. Spreadsheets Duration: 7:29 Posted: Dec 13, 2019 The Sheets API allows you to read values from cells, ranges, sets of ranges and entire sheets. The examples on this page illustrate how some common read operations can be achieved with the spreadsheets.values collection of this API.

Method: spreadsheets.get | Sheets API, create. Creates a spreadsheet, returning the newly created spreadsheet. get. Returns the spreadsheet at the given ID. Formatting cells with the Google Sheets API One of the features you get with the latest API is the ability to format content in Google Sheets. The sample app for this video demonstrates how to set frozen rows, bold cells, format floating-point values as currency, and implement cell data validation.

Introduction to the Google Sheets API, Turn Google Sheets into API with Sheetsu in just 5 minutes. Access Google Spreadsheets via REST API and use it as a data store or CMS. No‑deploy  Google Sheets makes your data pop with colorful charts and graphs. Built-in formulas, pivot tables and conditional formatting options save time and simplify common spreadsheet tasks.

Comments
  • Key is for accessing public data, what you are doing requires authenticated access.
  • That makes perfect sense. Thank you!
  • In most of the cases there is some problem of scopes. Please check and verify which scopes are required by script.
  • this worked for me. by the way: in google cloud admin go to... project > IAM & Admin > Service Accounts.... If you've setup service accounts, you'll see a special email for each respective service account. Make sure you've also enabled the Google Sheets API. Literally, you just share the service account email address from the google sheet "share" button.
  • yes ...key is to just share the document with the service account email....
  • The key json is very different from the credentials json which I obtained from the Java quickstart guide (for the Sheets API). How do I implement it?
  • How do you use the client secret? Can you make an API key with a service account instead?
  • Also, you can share this sheet with specific email Ex. your service account (project) email. "client_email": "XXXXX@northern-gasket-XXXX.iam.gserviceaccount.com", This will allow to access sheet by your script.
  • Thanks bro. There is no written things in documentation to like you have mentioned.
  • Agreed @MaulikDodia. Google API docs say it here, but it's not clear for people that just want to use the API to display data on a public website. I'm in the process of writing a how-to on all this. I'll send you a DM of it when I'm done.
  • Thanks a lot friend@user3411192