0

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?

1
  • The question is a bit unclear. Consider sharing a publicly editable sample spreadsheet where the behavior you describe can be reproduced. Aug 25, 2021 at 10:59

2 Answers 2

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

Resources

0

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.

1
  • You are right regarding that running a macro doesn't show the warning of a protected range, but scripts can edit protected ranges set to warning-only. Aug 25, 2021 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.