0
97
Visualizaçõesvlookup for different columns simultaneously filling the whole sheet
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 optimize 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?
/* 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 Respostas
Responde à pergunta0
You can do iterate with a loop, e.g. a for loop
Assuming you would like to loop through all rows from index + 3 to the last row, you can modify your code as following:
...
var row = index + 3;
var lastRow = s.getLastRow();
for (var i = row; i <= lastRow; i++){
var foundValue = data.getRange("E"+i).getValue();
var foundValue1 = data.getRange("F"+i).getValue();
var foundValue2 = data.getRange("G"+i).getValue();
var foundValue3 = data.getRange("H"+i).getValue();
var foundValue4 = data.getRange("M"+i).getValue();
s.getRange("K" + (2+i-row)).setValue(foundValue);
s.getRange("L" + (2+i-row)).setValue(foundValue1);
s.getRange("M" + (2+i-row)).setValue(foundValue2);
s.getRange("N" + (2+i-row)).setValue(foundValue3);
s.getRange("O" + (2+i-row)).setValue(foundValue4);
}
Note that later on you might want to progress from using getValue() and setValue() to getValues() and setValues() - that will make your code execution faster.