CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

Google Apps Script for INDEX/MATCH

I have 2 sheets, both of which have rows with the same ID's.

Internal Audit Register

SOP Register

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

Sample Spreadsheet

I would really appreciate some help.

Upvotes: 0

Views: 2011

Answers (1)

Cooper
Cooper

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

Related Questions