CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-apigoogle-cloud-iam
razonasistemas
razonasistemas

Reputation: 371

Permissions error when running a script in a Google spreadsheet

We are developing a program that uses Google spreadsheets as the input. The values of the spreadsheet is read, processed and a result is showed in a webpage.

When an user sign up we clone a spreadsheet from a template using a service account. The template has some scripts we want to use to help the user to introduce the values on the spreadsheet.

But the scripts seems to have as the owner the service account, and according to Google scripts belonging to service accounts can not be executed.

The error message is :

Google Apps Script: The script cannot be run because it is owned by a service account. Please copy or transfer the project to a valid account before running.

We transfer the ownership of the spreadsheet from the service account to a gmail user, and if that user creates an script it runs perfectly, but the scripts inherited from the template seems to still has as owner the service account and therefore can not been executed.

My question is ... how exactly can I transfer the ownership of a project (a script) from the service account to another user?

I've googled it and searched on the developer Google spreadsheet site but couldn't find an answer

Upvotes: 7

Views: 2879

Answers (2)

Dinesh Kumar S
Dinesh Kumar S

Reputation: 1

In the off-chance that you haven't yet solved the permissions problem, I just found this solution by chance. I'm using the gspread python module.

Create a gspread client using the service_account credentials

gc = gspread.service_account() - if the credentials file named service_account.json is in /home/user/.config/gspread or gc = gspread.service_account(filename="/path/to/creds.json")

Then, open the newly created/copied sheet either by its name or id or url

sheet = gc.open("sheet name") or sheet=gc.open_by_key(sheet ID) or sheet=gc.open_by_url(sheet URL)

Then, insert permission for the user to whom you want to transfer ownership

gc.insert_permission(sheet.id,user_email, perm_type='user', role='writer')

You can list all permissions of the sheet by

sheet.list_permissions()` - returns a list of dicts corresponding to all users with permissions

Note the permission id of the user to whom you want to transfer ownership.

sheet.transfer_ownership(permission_id) - transfers ownership to the target user

The user has to accept ownership when he/she opens the sheet. AppScripts should then work without a problem.

Upvotes: 0

ClementWalter
ClementWalter

Reputation: 5272

Short answer as of today:

  • see the doc here
  • make a POST request as follows:
curl --request POST \
  'https://www.googleapis.com/drive/v3/files/1_AVYCjzIATBg2OmK6CzgXXy8hB-GsLxoIKCHEqyndhs/permissions?emailMessage=[NEW_OWNER_EMAIL]&sendNotificationEmail=true&transferOwnership=true&alt=json&key=[YOUR_API_KEY]' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"role":"owner","type":"user","emailAddress":[NEW_OWNER_EMAIL]}' \
  --compressed

where:

  • NEW_OWNER_EMAIL: the email of the new owner
  • YOUR_ACCESS_TOKEN: an access token for the current owner (the service account)
    • using python (my project is in python) I did:
import json
from oauth2client.service_account import ServiceAccountCredentials

json_secret = json.load(open("client-secret.json"))
scope = ["https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_dict(json_secret, scope)
token = credentials.get_access_token()[0]
print(token)
  • YOUR_API_KEY: the API key that you can generate from the GCP Api and Services section, Ids

Upvotes: 0

Related Questions