How to affect multiple sheets from a script

Question

On Google Sheets, I want to hide rows with the value 'Del' across my whole document, meaning multiple sheets. Additionally, I would like this to only happen when I update cell E39 from Sheet 'Pricing breakdown'.

My current code is working perfectly for sheet 'Pricing breakdown', but I would like it to also affect sheets 'Project Plan' and 'Payment Plan', plus bonus points if you can help me only run this script when I change E39 from sheet 'Pricing breakdown'.

function onEdit(event){
      var s = SpreadsheetApp.getActive().getSheetByName('Pricing breakdown');
      s.showRows(1, s.getMaxRows());

  s.getRange('A:A')
    .getValues()
    .forEach( function (r, i) {
    if (r[0] == 'Del') 
      s.hideRows(i + 1);
    });
}

Cooper Cooper · Accepted Answer · 2019-02-26T22:57:34

Try this:

function onEdit(e){
  var rg=e.range;
  var sh=rg.getSheet();
  if(sh.getName()!='Pricing breakdown'){return;}
  if(e.range.getA1Notation() == 'E39') {
    var shA=['Pricing breakdown','Project Plan','Payment Plan'];
    for(var j=0;j<shA.length;j++) {
      var s=e.source.getSheetByName(shA[j]);
      s.showRows(1,s.getMaxRows());
      //e.source.toast(s.getName());
      s.getRange('A:A').getValues()
      .forEach( function (r, i) {
        if (r[0] == 'Del') 
          s.hideRows(i + 1);
      });
    }
  }
}

How to affect multiple sheets from a script

1 Answers