google-sheetsgoogle-apps-script

How do I get multiple functions to work with onEdit trigger in a single sheet


I have multiple cells that I want to check for an onEdit trigger and then execute functions used on which cell was edited. I have used a mix of code from different sources unsucessfully

I want to look for an on edit to cell A5 where the first function will clear a drop-down menu in B5 and highlight that cell

I also want to look for on edit to E4 where the second function will clear drop-down menus in F4 & G4 and then highlight F4

I'm not getting any of the clearing of intended cells

function onEdit(e){
  const sheetName = "Wave(FREQ) MS Calc";
  const range = e.range;
  calc1(sheetName,range);
  calc2(sheetName,range);
}

function calc1(sheetName,range){
  const sheet = range.getSheet();
  const targetCell = range.getA1Notation();
  const clearCell = sheet.getCell(5,2);
  if (sheet.getName() === sheetName && targetCell === "A5")
  clearCell.clearContent();
  clearCell.activate();}

function calc2(sheetName,range){
  const sheet = range.getSheet();
  const targetCell = range.getA1Notation();
  const clearCell = sheet.getRange('F4:G4');
  if (sheet.getName() === sheetName && targetCell === "E4")
  clearCell.clearContent();
  clearCell.activate();}

Solution

  • For better performance, use just one trigger function, and use the event object to minimize the number of API calls, like this:

    function onEdit(e) {
      if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
      const config = {
        'A5': { clear: 'B5', activate: 'B5', },
        'E4': { clear: 'F4:G4', activate: 'F4', },
      };
      const editedCellA1 = String.fromCharCode(64 + e.range.columnStart) + e.range.rowStart;
      if (!Object.keys(config).includes(editedCellA1)) return;
      let sheet;
      if ((sheet = e.range.getSheet()).getName() !== 'Wave(FREQ) MS Calc') return;
      const conf = config[editedCellA1];
      sheet.getRange(conf.clear).clearContent();
      sheet.getRange(conf.activate).activate();
    }
    

    This code won't use any API calls when the edited cell is some other than A5 or E4. The code in the question will use eight calls, while the code in the other answer will use four. See these onEdit(e) optimization tips.