Allowing for multiple values in onEdit function in Google Sheets Apps Script

0
votes

I am trying to onEdit multiple values in the same sheet yet based on the script it will only edit the last value referenced (in this case "Withdrew - Onsite").

How do I write the script so that if any of these values are selected in the dropdown menu off this column, Sheets will hide that row also?

IE: var VALUE = "Rejected - Coding Interview" OR var VALUE = "Rejected - Manager Screen" OR var VALUE = "Rejected - Onsite";, etc.

var SHEET = "Candidate_Funnel";

 // The value that will cause the row to hide. 
   var VALUE = "Rejected - Coding Interview"; 
   var VALUE = "Rejected - Manager Screen"; 
   var VALUE = "Rejected - Onsite"; 
   var VALUE = "Rejected - Recruiter Screen";
   var VALUE = "Withdrew - Coding Interview";
   var VALUE = "Withdrew - Manager Screen"; 
   var VALUE = "Withdrew - Recruiter Screen";
   var VALUE = "Withdrew - Onsite";

   // The column we will be using 
   var COLUMN_NUMBER = 1

   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);
       };
      };
     };
    }
2
google-apps-scriptgoogle-sheets
It only took the last one in your code because those are all assignments and the last one is the only one that matters for the rest of the script. By the way, using global assignments the way you did is a bad idea because they are going to run every time you run any function. – Cooper
You would save yourself a lot of effort if you would just do a Logger.log(e) and learn about what the event object provides you for no additional effort and no additional function call. It can save you a lot of time as well which is important because simple triggers must complete in 30 seconds. – Cooper

2 Answers

0
votes
function onEdit(e) {
  var sh=e.range.getSheet()
  if(e.range.columnStart==1 && sh.getName()=='Candidate_Funnel'){
    if(e.value=='Rejected - Coding Interview' || e.value=='Rejected - Manager Screen'|| e.value=='Rejected - Onsite'|| e.value=='Rejected - Recruiter Screen'|| e.value=='Withdrew - Coding Interview'|| e.value=='Withdrew - Manager Screen'|| e.value=='Withdrew - Recruiter Screen'|| e.value=='Withdrew - Onsite'){
      sh.hideRow(e.range.rowStart);
    }
  }
}

By the way let me add that you cannot run this function unless you provide it with an event object which does not happen automatically in the script editor. I just added that because many people just try to run the function from the script editor and return to tell me that they get the error of cannot find method range of undefined or something like that.

0
votes

As a user mentioned, of course it's going to take only the last value because you're using the same variable name for each string. I put them in an array an use indexOf() [1] function to check if the value is found in the array. Also, you can use the e event object [2] to get the edited range and its new value. 

var SHEET = "Candidate_Funnel";

// The value that will cause the row to hide. 
var VALUES = ["Rejected - Coding Interview", "Rejected - Manager Screen","Rejected - Onsite", "Rejected - Recruiter Screen", "Withdrew - Coding Interview", "Withdrew - Manager Screen", "Withdrew - Recruiter Screen", "Withdrew - Onsite"]

// The column we will be using 
var COLUMN_NUMBER = 1

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();
    var cell = e.range;
    var cellNewValue = e.value;

    //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(VALUES.indexOf(cellNewValue) != (-1)){
        activeSheet.hideRow(cell);
      };
    };
  };
}

[1] https://www.w3schools.com/jsref/jsref_indexof_array.asp

[2] https://developers.google.com/apps-script/guides/triggers/events