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

Question

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?

Abbotson

Key is for accessing public data, what you are doing requires authenticated access. – Conclusive

In most of the cases there is some problem of scopes. Please check and verify which scopes are required by script. – Musing

Also make sure your share settings are set to "Anyone with the link can view" - mine didn't work without that (even though it was published to the web). – Doreathadoreen

Another possible cause (that I just experienced) : the sheet is in read-only mode because the associated google account has no free storage left. – Trombidiasis

Answer 1

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. Make the role owner for the service account (Member name = service account ID = service account email ex: [email protected]
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 an email address of your service account ex: [email protected]

Selfimmolating

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. – Polydactyl

yes ...key is to just share the document with the service account email.... – Olivas

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? – Thermopylae

How do you use the client secret? Can you make an API key with a service account instead? – Brittain

Thank you so much. This should be included in the documentation or something. – Sinistrocular

Hi. What if I dont want to use 'share' feature. I want to use get/update any users files using oauth 2.0. User should not be required to know my clients service account to get me the access of his file. How can i achieve this? – Charo

Hi, in case you did all the steps above and still get a 403, check if the service account appears greyed out when you click on "share". If it does, a rule is excluding its access to the Sheet. In my case a DLP rule was disabling external sharing (and service accounts use an external domain). Issue tracked here: issuetracker.google.com/issues/209977112 – Marika

This works great, and as someone commented - should be included in documentation. – Alsatian

I shared the sheet with the service account email, but I still get "The caller does not have permission". There really needs to be documentation about accessing non-public sheets. Why doesn't this work?!? – Exhibit

What to do in case Admin has set restrictions on domain list? service account are not considered valid domain. – Anagrammatize

Answer 2

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.

Barnsley

Also, you can share this sheet with specific email Ex. your service account (project) email. "client_email": "[email protected]", This will allow to access sheet by your script. – Interlock

Thanks bro. There is no written things in documentation to like you have mentioned. – Favianus

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. – Renounce

Thanks a lot friend@user3411192 – Favianus

I have a public sheet and I'm still getting this error. – Roye

@Roye check if Anyone with the link has just Viewer role. It was the case with me and I fixed it by giving the Editor role. – Tatianna

Answer 3

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": "[email protected]", This will allow to access sheet by your script.

Gloriane

Answer 4

Visual Simplification of the Answers:

Option 1 - Turn the file into public (if sheets the sheet does not contain sensitive data)

Option 2 - Share file with Service Account Email (IAM & Admin -> Service Accounts -> Details -> Email)

Potation

I've been trying to solve this for several hours, and your visual screenshots helped tremendously. Thank you! – Xerxes

How can I open exactly where Option 1. is? – Prominence

Open the file from drive -> Click in "share" @VahitKeskin – Potation

Option 2 was the way to go for me. Just add the service account email as an editor to the sheets and it worked. – Burgoo

I want to share my looker studio (work space account) with my service account. I have tried option 2 but every time I share the report with service email I get the error ~An unknown error occurred - please try again later... is it done differently for looker studio? – Prod

Answer 5

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.

Huarache

Answer 6

In my case, solving this problem turned out to be trivial. You just have to:

1. Enter the google sheet that we want to remotely edit.
2. In the upper right corner, set - anyone who has the link can enter
3. Most importantly - on the right side you need to set permissions for people who have the link as 'editor'

if you still do not have permission, it means that you have to go to the website: https://console.developers.google.com/iam-admin/iam/ then select your project, then select "Service accounts" and create a new one as role "owner" or" editor" for the project for example (or use one that already exists and click "create new key")

The "key" is a json file that will be downloaded when you create the account (or use "create new key" there).

Woodwaxen

Answer 7

My 10 cents... A simple example to read the sheet using Java.

    private Credential getCredentials() throws IOException {
            final InputStream accessKey = new ByteArrayInputStream("<credential json>");
            final GoogleCredential credential = GoogleCredential.fromStream(accessKey)
                    .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS_READONLY));
            return credential;
        }

    private HttpTransport httpTransport() {
            try {
                return GoogleNetHttpTransport.newTrustedTransport();
            } catch (GeneralSecurityException | IOException e) {
                throw new SpreadSheetServiceException(e);
            }
        }


    Sheets service = new Sheets.Builder(httpTransport(), JSON_FACTORY, getCredentials())
                    .setApplicationName("app-name")
                    .build();
            ValueRange response = service.spreadsheets().values()
                    .get("<spread_sheet_id>", "A1:A")
                    .execute();

Dealing

Answer 8

Just change the name of your sheet.

Google requires that all Google projects have unique names. This error message indicates that by creating a sheet project with a name that matches a currently existing Google project, you are trying to perform an action on a Google project that already exists for which sheet does not have permission, which is why you see messages like The caller does not have permission and PERMISSION_DENIED.

https://support.terra.bio/hc/en-us/articles/360049831751-Error-creating-billing-project-The-caller-does-not-have-permission-#heading-2

Improvise