Copy Excel cell value and add rows to another table

Question

In a table (in excel) in a column I have some number(A).

I want the flow to take that number (A) and to create number of rows equels to Number (A)

For example if number(A) is 4, then in another table to be added 4 rows

Answer 1

I've made an assumption on the source and destination tables. This concept can be adjusted and applied to suit your own scenario.

I'd be using Office Scripts to do this. If you've never used it then feel free to consult the Microsoft documentation to get you going ...

https://learn.microsoft.com/en-us/office/dev/scripts/tutorials/excel-tutorial

This is the script you need to create (change the name of your tables accordingly) ...

function main(workbook: ExcelScript.Workbook)
{
  var addRowsTable = workbook.getTable('TableRowsToAdd');
  var addRowsToTable = workbook.getTable('TableAddRowsToTable');

  var addRowsTableDataRange = addRowsTable.getRangeBetweenHeaderAndTotal();
  var addRowsTableDataRangeValues = addRowsTableDataRange.getValues();

  // Sum the values so we can determine how many more rows need to be added
  // to the destination table.
  var sumOfAllRowsToBeInExistence = 0;
  
  for (var i = 0; i < addRowsTableDataRangeValues.length; i++) {
    if (!isNaN(addRowsTableDataRangeValues[i][0])) {
      sumOfAllRowsToBeInExistence += Number(addRowsTableDataRangeValues[i][0]);
    }
  }

  var currentRowCount = addRowsToTable.getRangeBetweenHeaderAndTotal().getRowCount();
  var rowsToAdd = sumOfAllRowsToBeInExistence - currentRowCount;
  
  console.log(`Current row count = ${currentRowCount}`);
  console.log(`Rows to add = ${rowsToAdd}`);

  if (rowsToAdd > 0) {
    /*
      The approach below is contentious given the performance impact but this approach ...
         for (var i = 1; i <= rowsToAdd; i++) {
      ... didn't always yield the correct result.  May be a bug but needs investigation.
      Ultimately, there are a few ways to achieve the same result, like using the resize method.
      This was the easiest option for a StackOverflow answer.
    */
    while (addRowsToTable.getRangeBetweenHeaderAndTotal().getRowCount() < 
        sumOfAllRowsToBeInExistence) {
      addRowsToTable.addRows();
    }
  }
}

You can then call that from PowerAutomate using the Run script action under Excel Online (Business) ...

You can use that approach or all of the actions that are available in PowerAutomate which will achieve the same sort of thing.

IMO, Using Office Scripts is much easier. Creating a large flow can be a real pain in the backside to deal with given there'll be a whole heap of actions that you'll need to throw in to reach the same outcome.

Answer 2

I would pass the number of rows to add in an office scripts script as a parameter. Once you have the value, create a JSON string of a 2d array. You want to create a loop using the number of rows to add. In the loop you continue to concatenate the 2d array. Once you've exited the loop, parse the JSON string and add the 2d array to the table. You can see how you code might look below:

    function main(workbook: ExcelScript.Workbook, rowsToAdd: number)
    {
      //set table name
      let tbl = workbook.getTable("table2")
      //initialize json string with open bracket
      let jsonArrString = "["
      //set the temp json string with a 2d array
      let tempJsonArr = '["",""],'
    
      //concatenate json string equal to the number of rows to add
      for (let i = 0; i < rowsToAdd; i++){
        jsonArrString += tempJsonArr
      }
      //remove extra comma from JSON string
      jsonArrString = jsonArrString.slice(0, jsonArrString.length-1)
      //add closing bracket to JSON string
      jsonArrString += "]"
      //parse json string into array
      let jsonArr: string[][] = JSON.parse(jsonArrString)
      //add array to table to add the number of rows
      tbl.addRows(null,jsonArr)
    }