CODEWITHSUNDEEP
google-chromegoogle-apps-scriptgoogle-sheetsexcel-formulagoogle-sheets-formula
RafaNHS
RafaNHS

Reputation: 21

Automatically hide rows based on other cell value

We have a spreadsheet that automatically attributes status for each row based on information enteredin cells located in column T. The stauts appear on column M showing open, pending and closed. I'm using the following code:

// Sheet the data is on.
var SHEET = "DLT";
// The value that will cause the row to hide. 
var VALUE = "Closed"
// The column we will be using 
var COLUMN_NUMBER = 14

function onEdit (e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  
  //Ensure on correct sheet.
  if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();
    
    //Ensure we are looking at the correct column.
    if(cell.getColumn() == COLUMN_NUMBER){
      //If the cell matched the value we require,hide the row. 
      if(cellValue == VALUE){
        activeSheet.hideRow(cell);
      };
    };
  };
}

I understand that there is a mistake on the code, as the spreadsheet changes automatically the "stauts" value on column M as soon as information is entered on column T, and this code this won't work because technically the Active.cell bit is telling the script to look at the current cell being edited (which in this case will be the cell on column T as this is the last field to be filled before triggering the spreadsheet change status to closed.)

Doubt is, how to make the script still looks into the current row being edited but consider the value on column M and not the active cell.

Upvotes: 0

Views: 833

Answers (1)

Cooper
Cooper

Reputation: 64120

function onEdit (e) {
  var sh = e.range.getSheet();
  //column 20 is T
  if(sh.getName()== 'DLT' && e.range.columnStart == 20 ){
    SpreadsheetApp.flush();//This will provide enough time to allow the spreadsheet to complete all of it's calculations.
    //column 13 is M
    if(e.range.offset(0,-7).getValue()=="Closed") {
      sh.hideRows(e.range.rowStart);
    }
  };
}

Upvotes: 1

Related Questions