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. The event queue only will only handle two events at a time, so if you edit values in quick succession and your onEdit(e) takes a while to finish, some events may get lost. The simple trigger has a max runtime of 30 seconds. Bottom line: it makes sense to spend a little time to optimize 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 to use e instead of SpreadsheetApp API calls:

/**
* Hides a row when "hide" is hand entered in Sheet1 or Sheet2 column D, excluding row 1.
* Simple trigger that runs every time the user manually edits the spreadsheet.
* 
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  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.');
  let sheet;
  if (e.value !== 'hide'
    || e.range.rowStart < 2
    || e.range.columnStart !== 4
    || !(sheet = e.range.getSheet()).getName().match(/^(Sheet1|Sheet2)$/i)) {
    return;
  }
  sheet.hideRows(e.range.rowStart);
  e.source.toast(`Row ${e.range.rowStart} was hidden.`);
}

The function will run every time you manually edit the spreadsheet, but it will return as soon as it finds that the value you entered is not hide. So most of the time the function will exit without making any API calls. If the value is hide, it will take a look at the row and column, and exit if they are not within the limits specified, again without API calls. The final test is whether the name of the edited sheet matches a pattern. This takes two API calls. The first call is optimized so that it saves the sheet object for later use.

The two onEdit(e) functions shown in the question can be combined into one onEdit(e) function that uses the minimum number of SpreadsheetApp API calls like this:

/**
* Hides and shows rows when certain cells are edited.
* Simple trigger that runs every time the user manually 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|Sheet1|Sheet2|Sheet3)$/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 above 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.