Google script: unprotect a sheet through function when user has no right to do so

Question

I have a spreadsheet with different sheets in Google sheet, 3 users can edit each one a sheet (protections are set, each user can edit only one sheet). They all can execute a google script function that writes what they edited in a summary sheet. I don't want anyone to be abble to edit the summary sheet, so I set myself as the only available editor. So my problem is to authorize the 3 users, only through the google script function, to write in the summary sheet. I tried to use the following function :

var unprotected = summarySheet.getRange('G3:G10'); protection.setUnprotectedRanges([unprotected]);

but since the users are not allowed to edit the summary sheet, and since the function is run with the active user, so they can't give themselves the right to unprotect a range in the summary sheet... Do you know how to workaround this problem?

Thanks a lot!

Answer 1

I see two script-based choices, one easy and one quite hard, and one sheet-based choice, that is easiest:

Easy:
You run the "summarize" script instead of them or, you set the summarize script run on a trigger out of your account. Then you actually leave protections alone. You could set the summarize script to run on open with error catching if the user doesn't have the necessary authority to unprotect the summary sheet and/or write to the summary sheet.

Hard:
When they run the "summarize" script it calls a published standalone script that has been given the authorization to make the necessary protection changes. I'll be honest, I wouldn't be able to code this but have seen/heard of similar implementations.

Easiest:

Finally, I want to make sure you've considered having the summary sheet itself contain the necessary formulas, parsing, etc. to summarize data from the other sheets without any need of scripts for this aspect of the sheet. The sheet could call custom functions as needed if the parsing or other summarization functionality is beyond built-in functions' capabilities. The sheet could stay fully protected and update itself in real time as users enter data (no need for users to trigger the summary creation, unless spreadsheet settings have auto-recalculate turned off).

Edited to add: put in A1 of Summary sheet something like:

=summarize()

And have that custom function return a 2-dimensional array of the summarized data.