Google-sheets – Auto hide row based on cell text (formula based)

formulasgoogle sheetsgoogle-apps-scriptgoogle-apps-script-triggers

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.

Best Answer

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

Related Topic