0
241
Viewsvlookup between two google sheets copying multiple columns fast no for loop
Below is a code that I found here : https://webapps.stackexchange.com/questions/123670/is-there-a-way-to-emulate-vlookup-in-google-script
I tried to optimise it to my use case in which
to vlookup from source sheet 'data', and fill in values in destination sheet 's'. The problem is that this code does this only for one row. Is there a way to loop over all rows and vlookup and fill in efficiently?
Second problem : the indexing of the source sheet is wrong namely the variables dataValues and index
is there way to solve this efficiently, without a for loop and correctly index source sheet?
/* recall that we want the follwoing columns => E, F, G, H, M
/*/
function khalookup(){
var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = SpreadsheetApp.openById("mysheetid");
var searchValue = s.getRange("B2:B").getValues();
var dataValues = data.getRange("A3:A").getValues();
var dataList = dataValues.join("ღ").split("ღ");
var index = dataList.indexOf([searchValue]);
var newRange = []
var row = index + 3;
var foundValue = data.getRange("E"+row).getValue();
var foundValue1 = data.getRange("F"+row).getValue();
var foundValue2 = data.getRange("G"+row).getValue();
var foundValue3 = data.getRange("H"+row).getValue();
var foundValue4 = data.getRange("M"+row).getValue();
s.getRange("K2").setValue(foundValue);
s.getRange("L2").setValue(foundValue1);
s.getRange("M2").setValue(foundValue2);
s.getRange("N2").setValue(foundValue3);
s.getRange("O2").setValue(foundValue4);
}
here is the source sheet where the vlookup shall happen based on the ID "Column A"
And here is how the destination sheet shall look like after the vlookup based on ID "Column B" have been made.
1 answers
Answer question0
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];.