Hide/Unhide rows based on values in particular cells

Question

I am trying to Hide/Unhide rows based on values in particular cells, have the following code which works in isolation but not together. Below only makes B121 be active while B55 stops working. If i remove B121 part, B55 works. Any tips? Thanks!

function onEdit(e) { 
  var activeSheet = e.source.getActiveSheet(); 
  var range = e.range; 
  if (activeSheet.getName() !== 'Inputs' || range.getA1Notation() !== 'B55') return; 
  if (e.value === 'No') { 
    activeSheet.hideRows(56, 64);
  }  else if (e.value === 'Yes') { 
      activeSheet.showRows(56, 64); 
      } 
}
function onEdit(e) { 
  var activeSheet = e.source.getActiveSheet(); 
  var range = e.range; 
  if (activeSheet.getName() !== 'Inputs' || range.getA1Notation() !== 'B121') return; 
  if (e.value === 'No') { 
    activeSheet.hideRows(122, 67);
  }  else if (e.value === 'Yes') { 
      activeSheet.showRows(122, 67); 
      } 
}

Answer 1

When you have two onEdit(e) functions in the same script project, only one of them will run.

The simplest pattern to combine two onEdit(e) functions is to rename the existing functions to doFirstThing_(e) and doSecondThing_(e), and then call them like this:

function onEdit(e) {
  doFirstThing_(e);
  doSecondThing_(e);
}

You should however consider spreadsheet performance when doing this. The onEdit(e) function is a simple trigger that runs every time any cell in the spreadsheet is hand edited, and it has a max runtime of 30 seconds, so it makes sense to spend a little time to optimize its runtime performance.

onEdit(e) best practices

The best practices include:

• Use the event object instead of API calls.

• Use simple checks to find if the function needs to do its thing, and if that is not the case, return as early as possible.

• Use nested conditional statements or short-circuit evaluation to avoid executing code unnecessarily.

Modern JavaScript is lightning fast, and V8 especially so, while SpreadsheetApp and Sheets API calls are very slow. A typical Apps Script function will spend almost all of its runtime in those calls rather than plain vanilla JavaScript evaluation.

The e event object is available in functions that run on a trigger. You can use it to discover details about the event without using API calls, so it makes sense to utilize e.value instead of SpreadsheetApp.getActive().getActiveCell().getDisplayValue() or similar calls.

Here is an example of how you can combine the functionality of the two onEdit(e) functions in one onEdit(e) function that uses the minimum number of SpreadsheetApp API calls:

/**
* Hides and shows rows when certain cells are edited.
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  // version 1.1, written by --Hyde, 4 January 2023
  //  - see https://webapps.stackexchange.com/a/155429/269219
  if (!e) throw new Error('Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet.');
  const trigger = {
    sheetRegex: /^(Inputs|SheetX|SheetY|SheetZ)$/i,
    columns: [2,],
    rows: {
      55: { rowStart: 56, numRows: 64, },
      121: { rowStart: 122, numRows: 67, },
    },
    actions: {
      'yes': 'showRows',
      'no': 'hideRows',
    },
  };
  let row, action, sheet;
  if (!trigger.columns.includes(e.range.columnStart)
    || !(row = trigger.rows[e.range.rowStart])
    || !(action = trigger.actions[String(e.value).toLowerCase()])
    || !(sheet = e.range.getSheet()).getName().match(trigger.sheetRegex)) {
    return;
  }
  sheet[action](row.rowStart, row.numRows);
}

This may look more complex than the two onEdit(e) functions in the question, but it will run an order of magnitude faster. The performance boost is thanks to the fact that there are just three SpreadsheetApp calls here.

The three calls are to Range.getSheet(), Sheet.getName() and Sheet[action]. They only get executed if the edit happened in a triggerColumn and on one of triggerRows and involving a value of 'Yes' or 'No', so in most cases no API calls take place. The calls are in an if() statement, and the JavaScript || logical "or" operator is short-circuit evaluated, which means that the tests do not actually get evaluated when they are not needed.

The two onEdit(e) functions in the question make a minimum of four API calls, while the function would most often make no API calls. When all conditions are met, and rows get shown or hidden, the functions in the question make a total of eight calls, while the function above makes three. Since the grand majority of execution time is spend in API calls, it is easy to understand why it pays to spend a little time structuring the code.

For more information, see Apps Script at Stack Overflow and Clean Code JavaScript.