Google-sheets – Show/Hide Rows based on text in a cell

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I'm trying to toggle which rows are shown/hidden on multiple sheets in my Google Sheets document. Simply put, a user will provide an input on Sheet1, which will populate a "show" or "hide" value in certain rows on column J of Sheet2 and Sheet3. This value of "show" or "hide" will inform whether or not the function should hide or unhide the row. I have achieved this (functionally) with the code below, but the rows start toggling on and off for about 20 seconds before arriving at the final set of row(s) that should remain shown. I think it's because this code loops through each and every row. Is there a way to have this run more efficiently to arrive at the final state once the user arrives at Sheet2 and Sheet3? 

 function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("01c - Tech Input (Tool Questions)");           // Enter sheet name
var row = s.getRange('J:J').getValues();                         // Enter column letter that has the text "hide" and  "unhide"
                       // Enter column letter that has the text "hide" and  "unhide"

  var tt = SpreadsheetApp.getActiveSpreadsheet();
var t = tt.getSheetByName("02 - Response Round-Up");           // Enter sheet name
var rowtwo = t.getRange('J:J').getValues();

s.showRows(1, s.getMaxRows());
for(var i=0; i< row.length; i++){ if(row[i] == 'hide') { s.hideRows(i+1, 1); } 
     else if(row[i] == 'unhide'){ s.unhideRow(ss.getDataRange()); } 

t.showRows(1, t.getMaxRows());
for(var j=0; j< rowtwo.length; j++){ if(rowtwo[j] == 'hide') { t.hideRows(j+1, 1); } 
     else if(rowtwo[j] == 'unhide'){ t.unhideRow(tt.getDataRange()); } 

}}

Best Answer

Analysing the code it came to my attention that first, the script forces ALL rows to show themselves and then iterate over ALL rows again to see if they need hiding or unhiding. This can be sped up by

  1. not changing a cell twice: drop the showRows statement

  2. not iterating over everything but just over what changed. The object e that is the parameter of onEdit(e) knows exactly what is updated and only that range needs those checks.

Pseudocode:

FOR(i=e.range().rowIndex();i<e.range().rowIndex()+e.range().numRows();i++)
Related Topic