Can Google Sheets macros respect protected ranges?

Question

I have developed a Google Sheets spreadsheet with loads of macros, but I want to have a protected version of it, a blank "master copy" that I then duplicate every time I want to use it.

I can set protection so that I get a warning if I accidentally try to edit the master, but if I run any of the macros then they ignore the protection.

I could put a line at the start of every macro to check for the existence of Protection objects, but there are quite a lot of macros!

Is there anything I can do to make macros respect the protection, outside of editing every macro?

Answer 1

In Google Sheets, warnings are not shown when a protected sheet / range set to show a warning only is edited by a macro by default, but you could set your macro to check if there is a protection applied the sheet / range being edited. In order to be able to do this you should open the Script editor and edit the macro accordingly.

---

Google Sheets Macros are managed through Google Apps Script. Macros, in the same way that scripts being called from a custom menu, simple triggers, etc. the macros are executed under the authorization of the active user. The spreadsheet owner always is an allowed editor of a protected sheet / range.

If you want to prevent accidental changes to an spreadsheet you could use two accounts, one to be used as the spreadsheet owner and reserved to be used only to change the protected sheets / ranges, the other account for routinely use.

Related

• Script to add protection and remove the user running it?

Resources

• https://developers.google.com/apps-script/reference/spreadsheet/protection

Answer 2

My understanding is that macros, or Google Apps Script in general, cannot circumvent sheet protections.

When you run a macro, it runs under your account. If you have editing rights to a range, the macro will have the same rights.