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:
"[2024-08-14T08:00:46.1800Z] 589402","[2024-08-14T08:00:46.2500Z] 589402","[2024-08-14T08:00:46.2660Z] 10000","[2024-08-14T08:01:11.2410Z] 599402"Iteration 11 of 15:
"[2024-08-14T08:08:54.4050Z] 609402","[2024-08-14T08:08:54.4680Z] 609402","[2024-08-14T08:08:54.4680Z] 10000","[2024-08-14T08:09:17.8400Z] 619402" Somehow between calling the script where the final table had 599402 rows and it being read again at the beginning of the next call (609402 rows), the table has become 10,000 rows bigger! I don't know if this is a problem with the .addRows function or some issue with the reading and writing to SharePoint but the behaviour should be deterministic and it clearly isn't! Any suggestions of what to look into would be much appreciated. P.S. When this issue has occurred, it always seems to be the 10/11 iteration of the loop.
