Iterator Only Repeating Its Max Potential Value in Nested For Loop (Office Scripts)

Question

I'm using Power Automate & Office Scripts to build a way to do batch creates in Excel. Essentially a way to send an array of values to a script in Excel that then updates an Excel table with all the values in the array. Office Scripts use the JavaScript language.

However, I started getting errors in my results where it is just posting duplicates of the last value in the array the flow sends:

There's nothing wrong with the cloud flow and I've checked the Office Script. The error occurs on the part that generates the new rows in the in-memory table. Specifically this code:

    //-1 to 0 index the RowNum
RowNum = RowNum - 1;
//Iterate through each object item in the array from the flow
for (let i = 0; i < CreateData.length; i++) {
  //Create an empty row at the end of the 2D table array & increase RowNum by 1 for the next line updating that row
  TableData.push(EmptyRow);
  RowNum++;
  //Iterate through each item or line of the current object
  for (let j = 0; j < Object.keys(CreateData[i]).length; j++) {
    //Create each value for each item or column given
    TableData[RowNum][Number(Object.keys(CreateData[i])[j])] = CreateData[i][Object.keys(CreateData[i])[j]];
  }
}

Which is very strange because essentially the same code works for another method of batch create for larger tables where I can't do things in memory:

    //Iterate through each object item in the array from the flow
for (let i = 0; i < CreateData.length; i++) {
  //Create an empty row at the end of the 2D table array & increase RowNum by 1 for the next line updating that row
  table.addRow()
  RowNum = RowNum + 1
  //Iterate through each item or line of the current object
  for (let j = 0; j < Object.keys(CreateData[i]).length; j++) {
    //Create each value for each item or column given
    TableRange.getCell(RowNum, Number(Object.keys(CreateData[i])[j])).setValue(CreateData[i][Object.keys(CreateData[i])[j]])
  }
}

And similar code works for the a similar process in a batch update script:

    //Iterate through each object item in the array from the flow
for (let i = 0; i < UpdatedData.length; i++) {
  //If the record's Primary Key value is found continue, else post to error log
  if (ArrayPK.indexOf(UpdatedData[i].PK) > 0) {
    //Get the row number for the line to update by matching the foreign key from the other datasource to the primary key in Excel
    RowNum = ArrayPK.indexOf(UpdatedData[i].PK)

    //Iterate through each item or line of the current object
    for (let j = 0; j < Object.keys(UpdatedData[i]).length - 1; j++) {
      //Update each value for each item or column given
      TableData[RowNum][Number(Object.keys(UpdatedData[i])[j])] = UpdatedData[i][Object.keys(UpdatedData[i])[j]]
    }
  }

And I'm not seeing anything logically wrong with it.

In fact, if I change out the i reference in the code for a static number so it references the same item each time, then it duplicates the values at that other static row index. It's as if in this specific case when the i reference is in a nested loop, it is just going to the last value in the array of values I pass it from Power Automate (usually the maximum value i will ever have in this loop) and never changing from that value in each loop run even though each run should add one to i (i++).

Why is this happening?

EDIT: It seems if I manually fill out the EmptyRow bit with an array of empties like ["", "", "", ""] then the error goes away and it enters the new rows normally. But it makes no sense. I pulled both the manual entry & the auto0generate entry of a blank row and they both have the exact same structure, output, & length,

score 0 · Answer 1 · answered Jun 28 '22 at 04:19

It just doesn't seem to work if I try to use an EmptyRow array variable for an in-memory table. I had to adjust that part of the code to calculate a new array of blanks in each loop. See the updated code below:

    //Iterate through each object item in the array from the flow
for (let i = 0; i < CreateData.length; i++) {
  //Create an empty row at the end of the 2D table array & increase RowNum by 1 for the next line updating that row
  TableData.push(",".repeat(TableNumberOfColumns - 1).split(","));
  RowNum++;

  //Iterate through each item or line of the current object
  for (let j = 0; j < Object.keys(CreateData[i]).length; j++) {
    //Create each value for each item or column given
    TableData[RowNum][Number(Object.keys(CreateData[i])[j])] = CreateData[i][Number(Object.keys(CreateData[i])[j])];
  }
}

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

Iterator Only Repeating Its Max Potential Value in Nested For Loop (Office Scripts)

1 Answers1