How can my Google Apps Script be run by others the Sheet is shared with?

Question

I've got a Google Apps Script that works fine for me. Others who the sheet is shared with that try to use it get the message:

You do not have access to perform that action. Please ask the owner of this item to grant access to you.

The script is used to update an existing sheet with new rows of data. It's triggered using a menu item added to the UI and does the following:

• Finds a .xlsx file in a GDrive folder (this file is exported by a web service and manually placed by us into the GDrive Folder, we have no control over the contents of the file or its format)
• Converts the file from .xlsx to .gsheet using UrlFetchApp (uploadType=media&convert=true)
• Changes the filename and folder location of the resulting .gsheet using UrlFetchApp. The folder location now matches that of the .xlsx file (when a file is converted using UrlFetchApp the resulting file seems to be placed in the root of GDrive)
• Gets the .gsheet using DriveApp and then opens it for access by the script
• Stores all the data from the .gsheet into a 2d array using column headers as the keys for each element in a row
• Stores all the ID values (unique) of the existing data from the destination sheet into a 1d array
• Compares the two arrays and removes any rows from the 2d array that contain an ID that matches one in the 1d array, leaving only new data in the 2d array
• Loops through each row of the 2d array and then through each column of the destination sheet adding data from the 2d array row to a new row at the bottom of the data in the destination sheet, using the value of the column header as the key for each element

I know there's no problem with the code because it works fine for me so it must be a problem with permissions, but I can't figure out how. The sheet is shared with them and the GDrive folder that the files are stored in is shared with them. When I first ran the script myself, I had to grant permissions for the script to access the GDrive folder, which has obviously now been done.

I'm the developer of our group and not the user of the data but atm I'm having to run the script to update the data for the users every time it needs doing instead of them just doing it themselves, which is... annoying.

Any help in trying to figure out where the problem is here greatly appreciated.

EDIT: Reading through this again its occurred to me that when the file is converted, at first its saved to the GDrive root which is why I then have to change the folder. Being the root, it's not shared with the users of this file. Could this be the reason? If so, how can I get around this? Can I specify at convert time which folder the resulting file should be saved to?

Solution 1

To fix this, you need to deploy the script as a web app (the idea comes from @MarioR answer, but the settings are different):

• With the owner account, open the sheet, then script editor
• Click on Publish and then Deploy as web app
• For Execute the app as, choose User accessing the web app and for Who has access to the app, choose Anyone

The first time the users try to use the script, they will have to allow the script (if they get a This app isn't verified, they should click on Advanced at the bottom and then Go to <script name> (unsafe)). After this, they may need to refresh the sheet to be able to run the script.

If the owner shares the sheet to a new user, after the script has been deploy as a web app, the new user may need to wait 15 minutes (or so) before being allowed to authorize the script (meanwhile this user will continue to get the red warning).

Only the owner can deploy as a web app, the other users who try do to it won't get a message error, just a popup Fetching Data that remains stuck forever!) To change the owner: open the sheet with the owner account, click on Share > Advanced > Click on the arrow that triggers the dropdown next to the "owner-to-be" > Set as owner

Solution 2

In my experience, I created a Spreadsheet that retrieves G Suite License information for users every time the Spreadsheet is opened. What I did was that I created the script with a Super Admin but when the Spreadsheet was shared the other users had the same problem until I went to Publish > Deploy as Webb App from the Apps Script menu, I leave the options like this image

That worked for me, I hope it works for you. After this update, all users with Edit rights on the spreadsheet were able to retrieve the information on behalf of the Super Admin every time the spreadsheet opens. Just make sure to execute the App as you and not as the user who will use the script or spreadsheet.

Usually other users are not able to run scripts that required admin rights but this resolves my issue. Please let me know if this worked for you!

Greetings.

Comments

[browly]

If the owner shares the sheet to a new user, after the script has been deploy as a web app, the new user may need to wait 15 minutes (or so) was the part of your answer that helped me. I didn't need to deploy as a web app, I just needed to wait for ~15 minutes after sharing the sheet with a new user before the new user could run the script.

[0xh8h]

@browly: How did you know about the 15 minutes trick? It works for me actually

[leggett]

WebApps require either a doGet or a doPost function to run: https://developers.google.com/apps-script/guides/web You can add this to the top of your script to meet that requirement: function doGet(e) { var params = JSON.stringify(e); return HtmlService.createHtmlOutput(params); }