How can my Google Apps Script be launched by others with a shared sheet associated with it?
I have a Google Apps script that works great for me. Others who have been provided with this sheet try to use it:
You do not have access to perform this action. Please ask the owner of this item to grant you access.
The script is used to update an existing sheet with new data rows. It is launched from a menu item added to the user interface and does the following:
- Finds the .xlsx file in the GDrive folder (this file is exported by the web service and placed manually by us in the GDrive folder, we have no control over the file content or format)
- Converts file from .xlsx to .gsheet using UrlFetchApp (uploadType = media & convert = true)
- Modifies the file name and folder location of the resulting .gsheet using UrlFetchApp. The folder location is now the same as the location of the .xlsx file (when the file is converted using UrlFetchApp, the resulting file appears to be placed in the GDrive root)
- Gets a .gsheet using DriveApp and then opens it for scripted access
- Saves all data from a .gsheet to a 2d array using the column headers as keys for each item in the row
- Saves all id values (unique) existing data from the target sheet to a 1d array
- Compares two arrays and removes all rows from the 2d array containing an identifier that matches the 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 target worksheet, adding the data from the 2d array row to a new row at the bottom of the target worksheet data, using the column header value as the key for each element
I know there is no problem with the code because it works fine for me, so it must be a permissions issue, but I can't figure out how. The sheet is shared with them, and the GDrive folder where the files are stored is shared with them. When I ran the script for the first time, I had to grant the script permissions to access the GDrive folder, which has obviously already been done.
I'm a developer on our group, not a data user, but right now I need to run a script to update the data for users every time they need it, instead of just doing it myself, which is ... annoying.
Any help in trying to figure out where the problem is here is much appreciated.
EDIT: Reading through this again it occurred to me that when the file is converted, it is first saved in the root of GDrive, so I have to change the folder. As a root, it is not shared with the users of this file. Could this be the reason? If so how can I get around this? During conversion, can I specify in which folder the resulting file should be saved?
source to share
Based on my experience, I've created a spreadsheet that retrieves the G Suite license information for users every time the spreadsheet is opened. What I did was I created a super admin script, but when the spreadsheet was published, other users had the same problem until I went to Publish> Deploy as Webb Application from the menu Application script ", I left options like this image
This works for me, I hope it works for you. After this update, all users with Edit rights on the spreadsheet could receive information on behalf of the Super-Admin each time the spreadsheet was opened. Just make sure the application is running as you
and not as the user
which script or spreadsheet will use.
Usually other users cannot run scripts that require admin rights, but this solves my problem. Please let me know if this worked for you!
Hey.
source to share
Use boundary screening . Regardless of who the developer is, as long as they provide access to your spreadsheet, your script will work as it attaches the sheet itself.
This is stated in
Collaboration with other developers
To collaborate on a project, you and your staff must all have editor access to the application's script file (and its container, if it's a script boundary). This allows everyone on your team to see and make changes to the application script code. Editors can also create new versions of code, publish add-ons, and deploy scripts as web applications or as executables for the Runtime API.
source to share
To fix this, you need to deploy the script as a web application (idea taken from @ MarioR's answer, but the settings are different):
- With owner account, open sheet then script editor
- Click
Publish
and thenDeploy as web app
- For "
Execute the app as
select"User accessing the web app
and for "YWho has access to the app
select"Anyone
The first time users try to use the script, they will need to allow the script (if they get This app isn't verified
, they should click " Advanced
bottom and then Go to <script name> (unsafe)
). After that, they may need to refresh the sheet so that the script can run.
If the owner has shared a sheet with a new user after the script has been deployed as a web application, the new user may need to wait 15 minutes (or so) before being allowed to authorize the script (while that user continues to receive red warning).
Only the owner can deploy as a web application, other users who try to do so will not receive an error, just a " Fetching Data
that will stay forever!" Popup . To change ownership: open the sheet with the owner account, click Share
>> Advanced
Click on the arrow that brings up a dropdown next to "future owner">Set as owner
source to share