I have 13 spreadsheets shared with different people and every day I copy (or editors copy) from the previous day's sheet. The spreadsheets have lots of formulae hence I try to lock them so no one messes them but it is really difficult. Old Google Spreadsheets was okay because if you locked same range at one time and copied it the copy was also locked.

I found a script (below, edited) but I have some problems with it:

  1. When I edit any cells every time, script works and locks same range again and again. If I edit sheet five times script protect same range five times not just one time.

  2. Script works and does Named Ranges but just the last range (test9) is protected not rest of them.

  3. Last question I don't want to use this script on the fist two sheets (Pages).

How can I solve it?

Example spreadsheet here

//Protect ranges, then remove all other users from the list of editors.

function  onLoad() { 
 var ss = SpreadsheetApp.getActive();

   var range = ss.getRange('A1:D3');  
   var protection = range.protect().setDescription('Test1');

   var range = ss.getRange('F1:H3');
   var protection = range.protect().setDescription('Test2');

   var range = ss.getRange('F43:H44');
   var protection = range.protect().setDescription('Test3');

   var range = ss.getRange('A68:D68');
   var protection = range.protect().setDescription('Test4');

   var range = ss.getRange('A93:A96');
   var protection = range.protect().setDescription('Test5');

   var range = ss.getRange('F96:H96');
   var protection = range.protect().setDescription('Test6');

   var range = ss.getRange('I:O');
   var protection = range.protect().setDescription('Test7');

   var range = ss.getRange('E1:E96');
   var protection = range.protect().setDescription('Test8');

   var range = ss.getRange('A1:A96');
   var protection = range.protect().setDescription('Test9');

 // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
 // permission comes from a group, the script will throw an exception upon removing the group.

 var eds = protection.getEditors();

 if (protection.canDomainEdit()) {

function onEdit(){onLoad();}
  • The last line function onEdit(){onLoad();} makes that the onLoad() function be called every time that the spreadsheet is edited. The onLoad() function overwrites several times the range and protection variables before removing the editors so that is applied to the last definition of the protection var. By the other hand, are you using a consumer account (@gmail.com) or a Google Apps account (an account with a custom domain)? – Rubén Jan 4 '16 at 16:15
  • What do you mean by "Last question i don't want to use this script fist two sheets (Pages)"? – Rubén Jan 4 '16 at 16:16
  • I am using an account with a custom domain. I want to use this script except first two pages (I don't want to use this script Page1 and Page2). this script should be work every new copy sheet for one at time. – user66322 Jan 4 '16 at 17:38
  • If you want to run the script only once then, instead of the onEdit() you should use a different way to trigger your function. You could use onChange(e). The e.changeType will be INSERT_GRID when a new sheet be inserted. Another alternative is to use a custom menu. – Rubén Jan 4 '16 at 18:03
  • Is there any different way with onEdit that i can add logic to check which rows are already protect. Because i want to protect sheets everyday automatically. – user66322 Jan 6 '16 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.