How to do VLOOKUP in google app script with a concat value from 2 columns as the parameter-CodePudding

I just want to make a vlookup in google app script with the result of concat from 2 columns as parameter to match with a column from different spreadsheet and then take the values of the matched row. Here is the example:

Spreadsheet 1:

Number	Type	Name	City
12345	num1
6789	num2
12345	num1

Spreadsheet 2:

ID	Age	Name	City
12345num1	22	Andrea	New York
6789num2	23	Roman	California

CASE: I want to concat the 2 columns (Number & Type) from spreadsheet 1 and check it if the result of the concat same as the "ID" column from spreadsheet 2, then get the values of column "Name" and "City" from spreadsheet 2 and set the value to the column of spreadsheet 1. Note: The data in the spreadsheet 1 can be a duplicate data and dynamic.

From my code, I already made the matching methods but the problem is the set values has an error that said "The number of columns in the data does not match the number of columns in the range. The data has 2 but the range has 1." and if I change the range of numColumns to 2, then it will show the error again (the range has 2, but the data has 1).

Is there anyone can fix this problem from my code? You guys can just make your own code as solution for the case, it can really help as well. Thanks

function main3() {
  var target_file = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var concat = target_file.getRange(2, 1, target_file.getLastRow() - 1, target_file.getLastColumn()).getValues();
  var source_file = SpreadsheetApp.openById("").getSheetByName("");
  var source_data = source_file.getRange(2, 1, source_file.getLastRow() - 1, source_file.getLastColumn()).getValues();
  var temp = [];
  var temp2 = [];
  for (var i = 0; i < concat.length; i  ) {
    temp[i] = [concat[i][0]   concat[i][1]];
    temp2.push(temp[i]);
  }
  var matching = temp2.map(row => {
    var row_match = source_data.find(r => r[0] == row[0])
    return row_match ? [row_match[2], row_match[3]] : [null]
    
  })
  target_file.getRange(2, 2, matching.length, matching[0].length).setValues(matching); error
}

CodePudding user response：

You're creating unequal number of columns here:

return row_match ? [row_match[2], row_match[3]] : [null]

If it finds a row match, the function returns two column elements per row, else it returns only one. You need a uniform array:

return row_match ? [row_match[2], row_match[3]] : [null, null]