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:
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:
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.
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.
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)
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.
In my case, solving this problem turned out to be trivial. You just have to:
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).
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();
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.
