Workaround access permission for Google Sheets

Question

Being relatively new to Google Apps Script, I created / put together a script for a Spreadsheet that would be shared with other users (can edit). The script is working fine from my account, but when run by other users, the following message comes up:

"No item with the given ID could be found, or you do not have permission to access it."

I'm assuming the reason for this is that the script sends emails, creates new files, and copies data from the shared Spreadsheet to other Spreadsheets in my Drive folders that are not shared with the other users (and I do not want to share them).

Question 1: Is there a way in the permission settings around it?

Since the script clears the content of the shared Spreadsheet after data is entered by other users, I thought of creating a script that does the following:

1. Create new hidden sheet in the shared Spreadsheet
2. Copy data to new hidden sheet
3. Clear content from original sheet
4. Time trigger to run script for sending emails, creating new files and copying data from hidden sheet, then delete it

Question 2: If the answer to question 1 is no, could anyone think of better way to do it other than the steps above?

Thanks.

Answer 1

You can create a WebApp

• Deploy it as "You" (Execute the app as: Me) with the sharing option Who has access to the app: either Anyone, Anyone within [your domain] or Anyone, even anonymous.

• The WebApp will have all the access permissions you have, no matter which user runs the app.

You can implement a service account with domain-wide delegation in your script

• If you assign to the service account all the scopes and access permissions your script needs - any user of your domain will be able to run a script through the service account
• Alternatively, you can also set-up the service account to impersonate you, that is to act on your behalf.

See here for a sample.