How to (Google Scripts) After dropdown selection, trigger a script when a formula changes a cell value and filter out those rows?

Question

In general, I have a google sheets where I have a dropdown in cell C2 that has multiple options. The options are 30 days, 60 days, 90 days. When 30 days is selected, I need certain rows of data to be filtered out from the same spreadsheet. IE: user chooses "30 days" in dropdown (cell C2). In same spreadsheet, rows 59 through 64 column 1, I have formula =C2 and change to "30 days" upon dropdown selection and these should get filtered out. If user chooses "60 days' in dropdown, rows 59 through 64 reappear.

I have researched and tried these 2 methods but can't get them to work for my use-case exactly.

The below does not read the values from the formula: https://yagisanatode.com/2018/05/26/how-to-hide-a-row-based-on-a-cell-value-in-google-sheets-with-filter-or-google-apps-script/

I cannot get the below to work for my use-case: Trigger a script when a formula changes a cell value

var SHEET = "check in template";
// The value that will cause the row to hide. 
var VALUE = "30 Day Check In";
// The column we will be using 
var COLUMN_NUMBER = 3

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();

//Ensure on correct sheet.
  if(SHEET == activeSheet.getName()){
    var activeCell = ss.getRange("C2");

    //Ensure we are looking at the correct column.
    if(activeCell.getColumn() == COLUMN_NUMBER){
      //If the cell matched the value we require,hide the row. 
      if(activeCell == VALUE){
        activeSheet.hideRows(59,5);
      };
    };
  };
}

I expect rows 59 to 64 to be filtered out but nothing is getting triggered.

Cooper Cooper · Accepted Answer · 2019-09-24T22:26:52

Try this:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()=='check in template' && e.range.columnStart==3 && e.range.rowStart==2){
    if(e.value=="30 Day Check In"){
      sh.hideRows(59,5);
    }
  }
}

Can't test this by running it without an event object. So don't try to run it out of the Script Editor.

How to (Google Scripts) After dropdown selection, trigger a script when a formula changes a cell value and filter out those rows?

1 Answers