Apps Script for Google Sheets (onedit function)

2
votes

On google sheets trying to hide/unhide rows based on the values in particular cells, and trying to write a script in AppsScript for that. Found that one that works in isolation (if B55=NO it hides 64 next rows):

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

But I need the same for Multiple Cells and multiple rows, as soon as I expand it only last part of the code works and not the first:

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

From here Cell B121 is working, but my B55 Stops working. Any tips? thanks!

3
javascriptgoogle-apps-scriptgoogle-sheets

3 Answers

1
votes

You need to merge the 2 functions onEdit, try

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

Answers have already been provided, but consider the more general case:

function myFooBar() { 
  console.log("the first one") 
}

function myFooBar() { 
  console.log("the second one") 
}

function doMyFooBar() {
  myFooBar();
}

When you run doMyFooBar(), the console shows the second one. Reading from the top of the script to the bottom, the last declaration of function myFooBar() replaces any previous declarations of that function.

0
votes

Mike is correct, you can only have on onEdit() trigger, but cannot just merge as he shows since there are returns if the condition is not found

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