Reputation: 425
I have 2 sheets, both of which have rows with the same ID's.
Both sheets have an ID column, column B in the Internal Audit Register, and column A in the SOP Register. When a date is entered into column G of the Internal Audit Register, I want that date pasted/overwritten into column H of the SOP Register where the ID's match. In the same row, I also want column E changed to false and columns F and G cleared.
I have partly written the following onEdit(e) Script but I am struggling with the VLOOKUP or INDEX/MATCH script.
// Edit SOP Register sheet after Internal Audit completed
{var spreadsheet = e.source;
var originsheetname = "Internal Audit Register"
var range = e.range;
var sheet = range.getSheet();
var row = range.getRow();
var column = range.getColumn();
var editedColumn = range.getColumn();
var editedRow = range.getRow();
var column = 7;
var date = range.getValue();
if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 2 && sheet.getName() == originsheetname) {
var objectsheetname = "SOP Register";
var object = e.source.getSheetByName(objectsheetname);
object.showColumns(5,2);
object.getColumn(6,1),copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}}}}}}//End of onEdit Functions
I would really appreciate some help.
Upvotes: 0
Views: 2011
Reputation: 64140
This function requires an installable trigger.
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,8).setValue(e.value).setNumberFormat("MM/dd/yyyy");//you can change the format here
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]');
}
It's working. I tested it.
You can test it with this:
function runTwo() {
const ss=SpreadsheetApp.getActive();
const sh=ss.getSheetByName("Internal Audit Register");
const e={};
e.range=sh.getRange('G1');
e.value=new Date();
e.source=ss;
e.range.columnStart=7;
onMyEdit(e);
}
and you can create a trigger with these:
function createOnMyEditTrigger() {
createOnEditTriggerForSpreadsheet('onMyEdit');
}
function createOnEditTriggerForSpreadsheet(funcname) {
var ssid=SpreadsheetApp.getActive().getId();
if(!isTrigger(funcname)) {
ScriptApp.newTrigger(funcname).forSpreadsheet(ssid).onEdit().create();
}
}
Or you can just test it by using it correctly.
Upvotes: 1