Problems with google sheets auto hide rows script running over and over

Question

Very new to GAS but really enjoying getting to know how to customize Google Sheets for our sales team so they can generate project pricing out in the field using a tablet. Any help would be greatly appreciated.

I am currently using the following script along with helper columns to hide/unhide rows in a sheet that we use as an intake form to gather measurements and specs for roof replacement project (method found here - https://productforums.google.com/forum/#!topic/docs/PnDxvRfDZ7A;context-place=topicsearchin/docs/authorid$3AAPn2wQcI1gyAZ6TGNLNixhJidWO7WxTAnkDDZmrnn18VK7N4DRU2VZKQwGG_XClrBImjg06chA6H%7Csort:date%7Cspell:false)

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Shingle Roof Intake Form");
var row = s.getRange('A:A').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()); } 
}}

I am using a variation of this same code along with helper columns to hide rows on another sheet we use to calculate our cost on said roofing projects (the auto hide rule only hides rows where total material and labor cost equal zero)

function onOpen(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Newman Costs");
var row = s.getRange('A:A').getValues();                

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

}}

Both scripts are working and hiding rows based on other selections or edits, but the problem I'm having is that the script seems to run over and over each time a change or edit is made which makes the hidden rows reappear then hide again. Waiting on the script to run is holding things up so we'd like to avoid that if possible.

I've tried modifying the scripts based on information I was able to find while researching my issue, but no luck so far. Open to suggestions on editing the current script or using something different. Thank you in advance!

Hi, do you need the script to be executed every time the spreadsheet is edited? — k4k4sh1
I think so but I'm not 100% certain. The purpose of the script is to expand and collapse sections of the intake form depending on whether or not that part of the form needs to be used for the project we want to price out. A bunch of yes/no questions, if yes is selected, that section of the intake form expands. If no is selected (which it is by default for all of these particular questions since they only need to be answered about 20% of the time), it should stay hidden. Multiple yes/no questions could be answered while filling out the intake form. Hope that makes sense! — Tim S.
I see, so the right trigger is onEdit(). I don’t know how your Spreadsheet is set up, but you could try to terminate the script execution if the current range value isn’t a yes/no question. This would prevent the script from running to the point you show and then hide rows when someone is filling other ranges. — k4k4sh1
The solution above will prevent the script from triggering unnecessarily. If you want to work on the possibility that more users edit a “valid” range, see LockService — k4k4sh1
@k4k4sh1 -- Thanks again for all or your help! I went ahead and created a sample of the intake form we're having issues with so you can see for yourself how it behaves with yes/no options are toggled as we work our way down the form. It may give you better insight. docs.google.com/spreadsheets/d/… I'm not exactly sure how to do what you've suggested but I'm going to dig in and figure it out! — Tim S.

k4k4sh1 k4k4sh1 · Accepted Answer · 2018-01-31T20:07:57

OK, thank you for the challenge! Try this and let me know if it works for you:

Edit : .indexOf() returning -1 if not found was messing with the rows number to be shown / hidden. It also hid / showed rows starting from the current row: changed sh.showRows(row, numRowsToToggle) to sh.showRows(row+1, numRowsToToggle). Replaced e.source with SpreadsheetApp.getActive(), e.range with SpreadsheetApp.getActiveRange() and e.range.getSheet() with SpreadsheetApp.getActiveSheet(). Now it should work properly.

function onEdit(e) { 
  var ss = SpreadsheetApp.getActive(); // Current Spreadsheet
  var range = SpreadsheetApp.getActiveRange(); // Current range
  var sh = SpreadsheetApp.getActiveSheet(); // Current sheet 
  var row = range.getRow(); // Current row
  var col = range.getColumn(); // Current column
  var value = range.getCell(1,1).getValue().trim(); // Current value
  var data = ss.getDataRange(); // Data range
  // Gets the yes/no questions column starting from the row after the current one
  // .flatten() converts 2D Array in a flat Array
  var yesno = sh.getRange(row+1, col, sh.getLastRow()-row+1).getValues().flatten();
  // Gets the next occurrence of 'Yes' or 'No'
  var nextNo = yesno.indexOf('No');
  var nextYes = yesno.indexOf('Yes');
  var nextYesNoRow;
  if (nextNo > -1 && nextYes > -1) { 
    nextYesNoRow = Math.min(nextYes+row, nextNo+row);
  } else if (nextNo == -1 ) {
    nextYesNoRow = nextYes+row;
  } else if (nextYes == -1) {
    nextYesNoRow = nextNo+row;
  } else {
    return;
  }
  // Gets the number of rows to hide/show
  var numRowsToToggle = nextYesNoRow - row;
  if (numRowsToToggle == 0) { return; }
  /* PREVENT UNNECESSARY SCRIPT EXECUTIONS */
  // If the edited cell is in another sheet terminate script
  if ('Shingle Roof Intake Form' != sh.getName()) { return; }
  // If the edited cell is outside the table terminate script
  if (col > data.getLastColumn() || row > data.getLastRow()) { return; }
  // If the edited cell column is not 4 ('Yes/No' answers column) terminate the script
  if (col != 4) { return; }
  // If value is 'Yes' shows, if 'No' hides
  switch (value) {
    case 'Yes' :
      sh.showRows(row+1, numRowsToToggle);
      break;
    case 'No' :
      sh.hideRows(row+1, numRowsToToggle);
  }
};

Array.prototype.flatten = function() {
  return this.join('@').split('@');
};

Problems with google sheets auto hide rows script running over and over

1 Answers