How can I speed this code used to protect and format specific ranges in worksheet?

datafarmer 0
Viewed 40

I am pretty green at this so I am really hoping there is way to speed this up. I have a script that runs on a sheet that has just over 1000 rows of data and protects the rows based on their contents. Problem is it takes an hour to run. Is there anyway to speed this up. if it helps every 5th row the RNG Value repeats. So if there is a way I can write script that formats and protects every 5th row the same in one line of code rather than looping that would probably be a good solution.

function protect_and_color(){
  var target_file  = SpreadsheetApp.openById("xxxxxxx"); 
  var sht = target_file.getSheetByName("FY22");

  
    for  (var x = 916;  x < (1064) ; x++)  // (must add one to the last row or will finish short one row)will need to updated this for each different template depending on number of rows of data.  X = is where to start x < is number of rows + start
    {     /// x is var (row number), column start, number of rows to impact, # of columns to color
          var rng  = sht.getRange(x,6,1,1);
          var rng2 = sht.getRange(x,6,1,14);   
          //var rng3 = sht.getRange(x,6,1,19); //need to update each month gray increment last argument
          //var rng4 = sht.getRange(x,7,1,13) //need to update each month green increment second argument and decrease the last argument by 1
          var rng5 = sht.getRange(x,1,1,18); //full row protections
          var rng6 = sht.getRange(x,19,1,1);// last column gray
          var rng7 = sht.getRange(x,1,1,6); //update protection, increment last argument - range that aes fill out
   
           if (rng.getValue() == "Bookings Last Year (FY21)")
           { //rng2.setBackground("#f9cb9c"); 
            //rng5.setBorder(true,true,null,true,null,null,"black",SpreadsheetApp.BorderStyle.SOLID_THICK);
            var protection = rng5.protect();
                protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                protection.setDomainEdit(false);}
           
           }
           else if (rng.getValue() == "Machine Learning Forecast")
           {//rng2.setBackground("#cfe2f3");
            //rng5.setBorder(null,true,null,true,null,null,"black",SpreadsheetApp.BorderStyle.SOLID_THICK);
            var protection = rng5.protect();
                protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                protection.setDomainEdit(false);}
           } 
           else if (rng.getValue() == "Bookings Actual (YTD)")
           {//rng2.setBackground("#d9d9d9");
            //rng5.setBorder(null,true,null,true,null,null,"black",SpreadsheetApp.BorderStyle.SOLID_THICK);
            var protection = rng5.protect(); 
                           protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                protection.setDomainEdit(false);}
           }   
           else if (rng.getValue() == "Bookings Forecast (rest of year)")
           //{rng3.setBackground("#d9d9d9");
            //rng4.setBackground("#d9d9d9");
           //rng5.setBorder(null,true,true,true,null,null,"black",SpreadsheetApp.BorderStyle.SOLID_THICK);
           var protection = rng7.protect();         
                protection.removeEditors(protection.getEditors());
                if (protection.canDomainEdit()) {
                protection.setDomainEdit(false);}
           //var protection = rng6.protect();  --This part removed to make the whole data sheet run faster. No concern if the totals are messed up by aes because it's not used in fcst data import (deleted).
            //          protection.removeEditors(protection.getEditors());
            //    if (protection.canDomainEdit()) {
            //    protection.setDomainEdit(false);}
         }
       
           //if (rng.getValue() == "UoM Forecast")//"Bookings Forecast (rest of year)")
           //{rng4.setBackground("#d9ead3");         
           //}
         //}
}
google-sheets google-apps-script
1 Answers

I'm not sure if this will speed things up but what I tried to do is reduce the number of calls to the server for data or information.

First rather than getValue() for each index of the loop I getValues() once and eliminate var rng = sht.getRange(x,6,1,1);.

Next, rather than getRange() for each index of the loop I only get the range I'm interested in in the if{} else if{} blocks. In this way if no protection needs to be set I have eliminated any calls to the server.

I'm curious to know if it improves performance.

function protect_and_color(){
  var target_file  = SpreadsheetApp.openById("xxxxxxx"); 
  var sht = target_file.getSheetByName("FY22");
  var range = sht.getDataRange();
  var values = range.getValues();
  
  for  (var x = 916;  x < (1064) ; x++) {
    var value  = values[x-1][5];
   
    if ( value == "Bookings Last Year (FY21)" ) {
      var rng5 = sht.getRange(x,1,1,18); //full row protections
      var protection = rng5.protect();
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }  
    }
    else if ( value == "Machine Learning Forecast" ) {
      var rng5 = sht.getRange(x,1,1,18); //full row protections
      var protection = rng5.protect();
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
    } 
    else if ( value == "Bookings Actual (YTD)" ) {
      var rng5 = sht.getRange(x,1,1,18); //full row protections
      var protection = rng5.protect(); 
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
    }   
    else if ( value  == "Bookings Forecast (rest of year)" ) {
      var rng7 = sht.getRange(x,1,1,6); //update protection, increment last argument - range that aes fill out
      var protection = rng7.protect();         
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
    }
  }     
}
TheWizEd0
Related