Forum Discussion
Issue with appending to a table in Office Scripts
I have automated the updating of a table in Existing Excel file with data from a table in new file. Both files are Excel online files. The process amounts to:
1. Find the date/time of the oldest record (row) of the Update table
2. Delete all the records (rows) in the Existing table that are on or after the date from 1.
3. Read the Update data
4. Append Update data to the Existing table
Because I am dealing with large tables (Existing is ~650,000 rows, Update is 150,000 rows), the automation has to do steps 3 & 4 in a loop of 10,000 at a time.
The problem I am seeing is that every now and again, one of the append iterations seems to be adding the chunk of data twice. Here is the script for step 4:
function main(workbook: ExcelScript.Workbook, data: string[][] ) {
// get the first worksheet
const sheet = workbook.getWorksheets()[0];
const seatsTable = workbook.getTable("AllSeatsData");
// get reference to the seats table
const tableRange = seatsTable.getRange();
// Get the boundaries of the table's range.
const lastColumnIndex = tableRange.getLastColumn().getColumnIndex();
const lastRowindex = tableRange.getLastRow().getRowIndex();
console.log(lastRowindex);
console.log(seatsTable.getRowCount());
console.log(data.length);
// Now add the rows of the update data to the end (-1) of the table
seatsTable.addRows(-1, data);
console.log(seatsTable.getRowCount());
}
Apart from finding that part the data is duplicated in the resulting table I am seeing the following console logs on these successive loops:
Iteration 10 of 15:
Iteration 11 of 15:
P.S. When this issue has occurred, it always seems to be the 10/11 iteration of the loop.
6 Replies
- MarkBOWLERCopper ContributorI have just noticed in the headers returned by the PA call to run the script that is writing too many rows that it has: "x-ms-apihub-cached-response": "false". Whereas every other call has "true" in the response.
I'm guessing this is a clue to what is going wrong but I've no idea what this means- JKPieterseSilver ContributorOffice script has limitations in how many records you can read/write in one go indeed. You'll simply have to experiment I'm afraid.
- MarkBOWLERCopper Contributor
JKPieterse, yes I will look at making the block size smaller but this behaviour (ie random silent failure) shouldn't be tolerated in a professional product. Ideally I want someone from Microsoft to look into this to see what the underlying issue is.
- JKPieterseSilver ContributorWith that many records, wouldn't you be better served with a database solution?
- MarkBOWLERCopper Contributor
I would love to use a DB but that's not an option for several reasons, I don't have access to a cloud DB, the update data is a spreadsheet and does not have anything that could be used as or to create a unique key.