CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

Additional criteria to Index/Match Google Apps Script

I have the following script thanks to Cooper:

function onMyEdit(e) {
  e.source.toast('Entry');
  const sh=e.range.getSheet();
  if(sh.getName()=="Internal Audit Register" && e.range.columnStart==7 && isDate(new Date(e.value))) { 
    e.source.toast('Flag1');
    const id=e.range.offset(0,-5).getDisplayValue();
    const osh=e.source.getSheetByName("SOP Register");
    const oshsr=3;
    const org=osh.getRange(oshsr,1,osh.getLastRow()-oshsr+1,osh.getLastColumn());
    const ovs=org.getDisplayValues();
    for(var i=0;i<ovs.length;i++) {
      if(ovs[i][0]==id) {
        osh.getRange(oshsr+i,4).setValue(e.value).setNumberFormat("dd/MM/yyyy");
        osh.getRange(oshsr+i,5).setValue("FALSE");
        osh.getRange(oshsr+i,6).setValue("");
        osh.getRange(oshsr+i,7).setValue("");
        break;
      }
    }
  }
}

function isDate(date){
  return(Object.prototype.toString.call(date) === '[object Date]');
}

function createOnEditTriggerForSpreadsheet(funcname) {
  var ssid=SpreadsheetApp.getActive().getId();
  if(!isTrigger(funcname)) {
    ScriptApp.newTrigger(funcname).forSpreadsheet(ssid).onEdit().create();
  }   
}

It works as I wanted but I now want to add additional criteria to the lookup function and I can't for the life of me work out how to do it. The script currently triggers when a date is entered into column G. I now want it to trigger when a date is entered into column G and column A of the same row = "SOP". I tried changing:

if(sh.getName()=="Internal Audit Register" && e.range.columnStart==7 && isDate(new Date(e.value)) && e.range.offset(0,-6)=="SOP") {

But this doesn't work.

Upvotes: 1

Views: 102

Answers (1)

raina77ow
raina77ow

Reputation: 106483

You're almost there actually. offset returns a new Range (doc), and you cannot just directly compare it against a string; you need to extract a value first. For example:

e.range.offset(0,-6).getValue() == "SOP"

You might consider replacing getValue() call with getDisplayValue() if 'SOP' is a computed value (result of formula etc).

Upvotes: 2

Related Questions