I’m performing a vlookup essentially at a spesific time each day. It worked the first couple of times, but now keeps giving me an error, any advice/guidance would be greatly appreciated!
//"Time Sheet" contains values on which you want to run the vlookup
let mainTable = base.getTable("Time Sheet");
let mainTableRecords = await mainTable.selectRecordsAsync({fields:["Title Name"]});
//"Volunteers" contains range to search in
let lookupTable = base.getTable("Volunteers");
let lookupRangeRecords = await lookupTable.selectRecordsAsync({fields:["Name"]});
//"Title Name" has the values you want to look up
for (let record of mainTableRecords.records) {
let lookupValue = record.getCellValue("Title Name");
//"Name" is the range to search in
for (let rangeRecord of lookupRangeRecords.records) {
if (rangeRecord.getCellValue("Name") === lookupValue) {
let linkID = rangeRecord.id;
//"Volunteers" is column name from mainTable which should contain the link
await mainTable.updateRecordAsync(record, {
"Volunteers": [{id: linkID}]
});
}
}
}
These are great troubleshooting suggestions. For people new to coding, I suggest adding console.log as a first step before using try-catch. While try-catch is extremely useful when dealing with an external system that can produce temperamental results, it isn’t as helpful when trying to debug a system without outside dependencies.
It would be easier to troubleshoot with more information, such as the actual behavior being seen.
Is this a automation script?
When the script stopped working, were there more records to be updated compared to when the script worked?
Are you finding that some records are updated, but not all of them?
Is the base schema (field types) exactly the same as it was when the script worked?
If the answers to all of these questions is yes, it is likely that you ran into a time limit for automation scripts. The solution is to rewrite the script so that it does not have nested loops and to update records in batches. Take a look at Bill’s link.
