Google Sheets permissions

Question

I built two separate spreadsheets in Google Drive and spreadsheet "A" has a script that when run, will copy the input data into spreadsheet "B". When I test it, it works perfectly. But I have other users that only have access to spreadsheet "A". When they run the script it doesn't work because they don't have permission to spreadsheet "B", but I don't want to give them the historical data that's saved in spreadsheet "B".

The error message is:

Document ID is missing (perhaps it was deleted?)

How do I get through this?

_{I made a Demo copy that can be accessed: demo copy}

Answer 1

According to Authorization for Google Services it's not possible because for Standalone, add-on, or bound to Docs, Sheets, or Forms the script run as the user at keyboard. The alternatives is to convert your script to a web app or to a Installable trigger.

Answer 2

Sorry this is not possible yet in Google spreadsheet :(. But I am not sure if you have though of this solution yet;

You could make a duplicate of both the spreadsheet b and a so that " the other people" can't see your historical data.

Only issue with this is you would be remaking the whole thing:(

Hope this help!