Reputation: 425
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
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