Auto hide row based on cell text (formula based)

Question

My plan is to hide an entire row if a specific cell in the row showed specific value. I already found the script and the script work amazing. But the problem now, since the script use onEdit function, it only worked when I input the cell manually. Since we handle a large amount of data, this manual input is not reliable and impossible to manage.

So, do google script have a function where a script can be triggered by formula based value? I already try onOpen, onChange, myFunction but all failed. Can anyone please help me?

Here is the script that I used for my sheet.

var SHEET = "Allocation";
var VALUE = "0";
var COLUMN_NUMBER = 1

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

  if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    if(cell.getColumn() == COLUMN_NUMBER){
      //If the cell matched the value we require,hide the row. 
      if(cellValue == VALUE){
        activeSheet.hideRow(cell);
      };
    };
  };
}

As for now, we are using the filter function. It works just fine, but its good to have this script to work.

Answer 1

Simple and installable triggers like on open, on edit and on change (installable only) only are fired when a user does the corresponding action by using the Google Sheets UI. For further details please read https://developers.google.com/apps-script/guides/triggers

One alternative is that you implement a "poll function" meaning something that every certain time check the value of the cell and if the condition is met, then do something. To do this you could use a time-driven trigger (they could be set to run every minute) or to use the HTML Service to create dialog/sidebar with client side code that does the poll job by using setInterval.

Related

• How to automatically set date in a cell when a different cell value is changed by a formula from data on another sheet?