Help

Speed up my script for Vlookup

Topic Labels: Scripting extentions
1549 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Paul_Bridgman
4 - Data Explorer
4 - Data Explorer

I have a table (“Containers”) with 3000+ records and for each record I would like to do a Vlookup from another table (“F”) in the same way as is done in Excel. I came across the below script. The problem is the script runs out of time before it can update every record so I have to run the script multiple times. It seems the script is looking through all 3000+ records every time and not zoning in on the records that have not yet been updated. The result is I got about 2500 records updated but now the script only finds 10 or so to update each time compared to 150 at the beginning.

I tried to use “Paul view” to limit to running the script only on un-updated records but it does not speed things up at all so maybe it is still running on all records.

//Substitute “Containers” for table name which contains values

//on which you want to run the vlookup

let mainTable = base.getTable(“Containers”);

let view = mainTable.getView(“Paul view”);

let mainTableRecords = await mainTable.selectRecordsAsync();

//Substitute “F” for table which contains range to search in

let lookupTable = base.getTable(“F”);

let lookupRangeRecords = await lookupTable.selectRecordsAsync();

//Replace “Date loaded” with column name which has the values you want to look up

for (let record of mainTableRecords.records) {

let lookupValue = record.getCellValue(“Date loaded”);

//Replace “Date” with column name which is the range to search in

//Replace “All freights” with columnn name which value should be returned

for (let rangeRecord of lookupRangeRecords.records) {

if (rangeRecord.getCellValue(“Date”) === lookupValue) {

let returnValue = rangeRecord.getCellValue(“All freights”);

//Replace “Vlookup freight” with column name from mainTable which should contain the link

mainTable.updateRecordAsync(record, {

“Vlookup freight”: returnValue

});

}

}

}

1 Reply 1

image

Optimization ideas -

  1. The loop at line 18 is performed for every record in the main table; don’t do that.
  2. Load the lookup data once and transform it into a JSON object whose keys are the lookupValue (see this post for an example).
  3. Consider using script automation to update these values as they need updating. This makes the process real-time and eliminates overly lengthy update processes.