CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Greg
Greg

Reputation: 1364

Hiding/unhiding named ranges: Improve script process time?

Below is a script I'm using to automate the hiding/unhiding of groups of rows in Google Sheets.

enter image description here

Col B contains drop-down menus that allow the user to toggle hiding or unhiding rows according to named ranges. However, the script is a bit laggy. Here is a sample spreadsheet where you can see for yourself.

I haven't found a more efficient approach than creating four different arrays in Google Apps Script...but any advice on how to speed up this script would be greatly appreciated!

(NB: The script operates with a "From spreadsheet" and "On edit" trigger.)

function hideRows() {
   var ss = SpreadsheetApp.getActive();
   var sheet = ss.getSheetByName("Sheet");

  //create array of spreadsheet's named ranges
   var namedRanges = ss.getNamedRanges();
   var named = [];
   for (var i = 0; i < namedRanges.length; i++) {
   named.push(namedRanges[i].getName()); 
   }
   var arrName = named.sort();

  //create array of named range values  
   var value = [];
   for (var i = 0; i < arrName.length; i++) {
   value.push(ss.getRangeByName(arrName[i]).getValue()); 
   }

  //create array of named range row indexes  
   var index = [];
   for (var i = 0; i < arrName.length; i++) {
   index.push(ss.getRangeByName(arrName[i]).getRowIndex()); 
   }

  //create array of named range row numbers  
   var rows = [];
   for (var i = 0; i < arrName.length; i++) {
   rows.push(ss.getRangeByName(arrName[i]).getNumRows()); 
   }

    for (var i = 0; i < arrName.length; i++) {
  //If a value in value is equal to "Collapsed" then hide the corresponding named range
      if (value[i] == "Collapsed") {
    sheet.hideRows(index[i+1],rows[i+1]);
      }
  //If a value in value is equal to "Expanded" then show the corresponding named range
  else if (value[i] == "Expanded") {
    sheet.showRows(index[i+1],rows[i+1]);
  }
    }
}

Upvotes: 0

Views: 1203

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

I didn't test this. I rewrote the code, and consolidated three of the loops into one loop. The code could be consolidated more, depending upon whether you need to further use the data for something else. For example, you are sorting the range names. If it's not really needed to have them in order, then the code could be consolidated further.

function HideRows() {
  var arrName,i,index,L,named,namedRanges,
      rngByName,rngNumRows,rngRowIndex,rngVal,rows,
      sheet,ss,thisRng,thisRngName,value;

  ss = SpreadsheetApp.getActive();
  sheet = ss.getSheetByName("Sheet");

  named = [];
  value = [];
  index = [];
  rows = [];

  //create array of spreadsheet's named ranges
  namedRanges = ss.getNamedRanges();

  L = namedRanges.length;

  for (i = 0; i < L; i++) {
    named.push(namedRanges[i].getName()); 
  }

  arrName = named.sort();
  Logger.log(arrName);

  //create array of named range values  

  L = named.length;

  for (i = 0; i < L; i++) {
    thisRngName = arrName[i];
    thisRng = ss.getRangeByName(thisRngName);

    rngVal = thisRng.getValue();
    rngRowIndex = thisRng.getRowIndex();
    rngNumRows = thisRng.getNumRows();

    value.push(rngVal);
    index.push(rngRowIndex);//create array of named range row indexes  
    rows.push(rngNumRows);//create array of named range row numbers

    //If a value in arrValue is equal to "Collapsed" then hide the corresponding named range
    if (rngVal == "Collapsed") {
      sheet.hideRows(index[i+1],rows [i+1]);
    }
    //If a value in arrValue is equal to "Expanded" then show the corresponding named range
    else if (rngVal == "Expanded") {
      sheet.showRows(index[i+1],rows [i+1]);
    }
  }
}

Upvotes: 1

Related Questions