How to allow users to run a macro which edits a range or sheet they don't have 'edit' permissions to?

0
votes

I have made a macro that takes a set of input data, transfers it to a master data table, graphs trend lines of the data, and then clears the data entry sheet. All sheets are protected to avoid employees from changing or deleting data that has already been entered and only the cells which require data entry are available for editing by the employees who I've shared the sheet with. The macro works well if I try to run it but if another employee attempt to run the macro they get an error stating they don't have permission to edit certain ranges.

When I wrote the Excel macro for the same task I was able to unlock the sheets up front, transfer the required data, and then re-lock the sheets but can't figure out how to replicate this type of behavior in Google Apps Script.

The Protection class on the Google Developers page did not prove to be helpful, since I kept getting an error saying the function protect could not be located in the Object spreadsheet. I also tried the addEditor function without much luck. This is my first Google Apps Script and I've been trying to learn as I go but this project is proving difficult.

1
google-apps-scriptgoogle-sheetsgoogle-sheets-macros
Read about installed triggers and authorization- under whose authority a script will be executed in each circumstance. – TheMaster
Adding the link for the mentioned topic by @TheMaster, Installable Triggers and Authorization for Google Services. – MαπμQμαπkγVπ.0
Thank you both for the suggestions. After several failed attempts and the correction of a fatal error, I was able to set up an onEdit function that calls the macro when a certain cell is edited. (This actually allowed the macro to run on mobile, too which was more than I expected! Especially considering I'm running the sheet from the Sheets app on an iPhone.) I'm still attempting to get a coworker to test it and see if they can utilize the macro by using the cell specified by the onEdit function and will report back if we encounter any issues. – Rickey
I was wrong, coworker still received an error when trying to run the onEdit cell. I think I'll need to find a different approach. Thank you for the help. – Rickey
@Rickey Did you change the name of your 'onEdit' script when you created the Installable trigger? – Tedinoz

1 Answers

0
votes

Thank you to @Tedinoz for helping me realize you can rename the onEdit function so long as you designate 'e' as a function input. I was able to use an Installed Trigger on the sheet set to run RenamedonEdit(e) whenever the sheet is edited.