Permissions error when running a script in a Google spreadsheet

Question

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

Mercado

Is the script in the spreadsheet? It seems like the script is not attached to the spreadsheet itself and the problem is with the script file itself belonging to the service account. If you have a Google Apps Script file then transfer the ownership of that file to a Google account. Or better yet, if it is started from a web app, make sure you set it to execute as user accessing the web app (usually it defaults to me) – Biquadratic

@Biquadratic I created the script on the template by opening Tools | Script Editor | Create a new project, so I always thing that the script was attached to the spreadsheet. I think the problem is that cloning from the template creates a new spreadsheet where the script belongs to the service account. As I don't know how to transfer the ownership of a script project to a google account, I'm stuck. – Mercado

How does the user start the script? Is it by running it from a web app or do they open the spreadsheet and run it from there? – Biquadratic

@Biquadratic The user opens the spreadsheet and then the script should run – Mercado

Wouldn't you have problems with the triggers limiting auth anyway? onOpen wouldn't know who is running the session. Another thing you can try to transfer the ownership of the script is go Resources → Developers Console Project → Click link at the top and the go to IAM&Admin and change the owner there. That's about all I can think of. – Biquadratic

Are you still looking for a answer to this? If so, please add minimal reproducible example to describe how is your script creating the spreadsheet (I think that you are using UrlFetchApp instead of the Apps Script Spreadsheet/Drive services, is this correct?) – Thorton

Answer 1

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

Twoup

Answer 2

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.

Extol