Office Script table to Sharepoint List

[Impossibru28]

I have an Office Script that creates a table in an Excel sheet, and I am trying to add the rows from that table to a Sharepoint list. Is this possible? When I try to build the flow, the field value for Table is blank since the table technically doesn't exist until the script finishes running.

[DamoBird365]

Hi @Impossibru28 

You can use a single script like follows:

function main(workbook: ExcelScript.Workbook) {
  
  // Get the first worksheet 
  const sheet = workbook.getFirstWorksheet();
  
  //get active range of WorkSheet
  let range = workbook.getActiveWorksheet().getUsedRange();

  // Get last used row of WorkSheet
  let lastrow = range.getRowCount();

  //get table range  
  const TableRange = `A9:L${lastrow}`;

  // Create a table using the data range.
  let newTable = workbook.addTable(sheet.getRange(TableRange), true);
  newTable.setName("TestTable");
  
  // Get the first (and only) table in the worksheet.
  let table = sheet.getTables()[0];

  // Get the data from the table.
  let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();

  console.log(lastrow);
  return tableValues

}

Not only does it create a table but it returns the table as an array.  You can take your pick to either use the results from the script action or call the table using the get rows action.  In the example above it creates the table from A: row9 to L: last row in Sheet.  You can adjust this to fit your requirements.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Impossibru28]

How can I call the table in the flow if it doesn't exist until the script action is completed? I also don't know how to get the rows from the newly created table and assign them to the appropriate fields in the Sharepoint list. 

[GeoffRen]

Since the table doesn't exist until the script finishes running, what DamoBird365 is suggesting is to return the newly created table's values (aka the rows in the table) from the same Office Script you use to create the table. So you don't need the List rows present in a table step at all, you can return the table's values and operate on them the same way you would operate on the return value of List rows present in a table.

[DamoBird365 · Accepted solution]

Hi @Impossibru28 

I've done a couple of videos this morning to cover both scenarios. 

How to load data into excel that doesn't contain a table using excel scripts

and

How to retrieve data from excel in 2 ways, create a table and returning a result using excel scripts.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉