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()); } 

}}

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++)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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