Edit: Realized the "Exclude" column in my original question wasn't contributing to the issue at all. Simplified things even more and added full code that reproduces the error.
In Excel I have a table of data that I'm pulling into a script. One of the columns contains values from a lookup table.
Simplified sample tables:
DataTable:
| Value | Category |
|---|---|
| 123 | A |
| 456 | A |
| 789 | B |
| 12 | C |
| 34 | D |
| 56 | D |
LookupTable:
| Category | Description |
|---|---|
| A | Something |
| B | Some other thing |
| D | Something else |
I'm trying to verify that all non-excluded categories in the data table exist in the lookup table. This is what I ended up with:
/**
*
*/
function main(workbook: ExcelScript.Workbook) {
let messages: string[] = [];
// Get active sheet and import map sheet.
let selectedSheet = workbook.getActiveWorksheet();
let dataTable = selectedSheet.getTables()[0].getRange().getValues() as string[][];
let dataHeader = dataTable.shift();
let category = dataHeader.indexOf("Category");
let value = dataHeader.indexOf("Value");
let lookupTable = selectedSheet.getTables()[1].getRange().getValues() as string[][];
let lookupHeader = lookupTable.shift();
let lkpCategory = lookupHeader.indexOf("Category");
let lkpDescription = lookupHeader.indexOf("Description");
dataTable
.map((e) => e[category]) // only look at the category column
.filter((e, ind, arr) => arr.indexOf(e) == ind) // filter to unique categories
.every((e) => {
if (lookupTable.findIndex((x) => x[lkpCategory] == e) < 0) {
console.log(e); // Magic console log function
messages.push("Mapping not found for " + e);
return false;
}
});
if (messages.length) {
console.log(messages);
return; //If there's any messages, we found something invalid and shouldn't continue.
}
for (let row of dataTable){
let catRow = lookupTable.findIndex( (x) => x[lkpCategory] == row[category]);
console.log(row[category],row[value],row[exclude],lookupTable[catRow][lkpDescription]);
}
}
The gist is that we're validating that all categories have a lookup value before we attempt to use them. If anything failed validation we don't want to actually process the table.
If the "Magic console log function" is included, we get the log for C and "Mapping not found for C" as expected. If it's commented out, it tries processing the data and spits out an error when it tries to access it.
It also works if the log is outside of the if statement but still within the every function.
It seems like maybe it's not waiting for the "every" call to return before moving on (so when it checks for messages.length, it hasn't been added to it yet) but the console.log forces it to wait as Alexander suggests?
As mentioned in the comments, it's hard to know for sure what's happening based on the partial script. However, I can explain why a console.log statement might change the script behavior.
Behind the scenes, Office Scripts is trying to batch read/write operations. This reduces the number of calls to the workbook (particularly important when using Excel on the web). However, if your script needs the data being read, the batch runs with whatever it has at that point - synchronizing the local client and the workbook. console.log statements always force a sync.
The exact batching mechanics are intentionally obfuscated, but the article Improve the performance of your Office Scripts talks about this behavior from a performance perspective. Might provide some insight.
It's possible you've uncovered a bug in Office Scripts. Iterating over a filtered map might not be syncing the data correctly (and the log statement is forcing the sync at a different time). If that's the case, please send feedback through the in-client help.