Below is a code that I found here :

And here is how the destination sheet shall look like after the vlookup based on ID "Column B" have been made.

CodePudding user response：

I believe your goal is as follows.

• You want to copy the columns "E" to "H" and "M" of the source Spreadsheet to the columns "K" to "O" in the destination Spreadsheet. In this case, you want to copy the rows that IDs of the column "A" of the source Spreadsheet are the same as the column "B" of the destination Spreadsheet.

In this case, how about the following modification?

Modified script:

function khalookup() {
  var srcSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dstSheet = SpreadsheetApp.openById("mysheetid").getSheets()[0];
  var srcObj = srcSheet.getRange("A2:M"   srcSheet.getLastRow()).getValues().reduce((o, r) => {
    if (r[0].toString() != "") {
      o[r[0]] = [r[4], r[5], r[6], r[7], r[12]];
    }
    return o;
  }, {});
  var dstIds = dstSheet.getRange("B2:B"   dstSheet.getLastRow()).getValues();
  var dstValues = dstIds.map(([b]) => srcObj[b] || Array(5).fill(null));
  dstSheet.getRange(2, 11, dstValues.length, dstValues[0].length).setValues(dstValues);
}

• When this script is run, the above flow is run.

• In this modification, from your script, the 1st tab of the destination Spreadsheet is used as the destination sheet. If you want to put the values to other sheet, please modify var dstSheet = SpreadsheetApp.openById("mysheetid").getSheets()[0];.

References:

• map()
• reduce()