Power Automate - Suppress error from create table

[MikeWJS]

Hi All,

I have this flow to import data from a spreadsheet to a SP list,

The problems is that there is a varying amount of rows in the spreadsheet. The range A1:H30 is actually A1:~H1600.

I can set the range to be A1:H5000 however when the approx 1500 records are imported it tried to read the blank lines and when placing them into the shaarepoint list it errors as it is expecting a number instead of a blank record.

Although the flow works, it errors every time so I cannot detect real errors when this is placed live.

Does anyone know how I can stop when all the real, actual data is imported to prevent it trying to create blank records and erroring out please?

Any help would be much appreciated.

Thanks,

[DamoBird365]

Hi @MikeWJS 

A couple of options for you.  You could use office scripts to detect the used range of cells and create a table.  Something like the following script, which you can see in use here https://youtu.be/ZejstvPiFZw

function main(workbook: ExcelScript.Workbook,
TableName: string = "Table1",
SheetName: string = "Sheet1"
) {
// Get the worksheet by name
const selectedSheet = workbook.getWorksheet(SheetName);
// Alternatively, get the first worksheet (uncomment below and comment out above)
// const selectedSheet = workbook.getFirstWorksheet();
// Create a table using the data range.
let newTable = workbook.addTable(selectedSheet.getUsedRange(), true);
newTable.setName(TableName);
}

Or, once you have used your method to create a table larger than the actual data, you could run a "filter array" action on the value from the list rows, where a field is not null (assuming that all cells have data) and this would return an array of values for you to create items for.

Damien

[MikeWJS]

Thanks for your response!

The script works fantastically well and creates a table of the actual data range in the created spreadsheet as per the video, however when I then try to import into the sharepoint list, I cannot use the table name for "get tables".

I have created a second flow which looks for new spreadsheets in the shared documents area on Sharepoint Documents to import them into the SP list however it returns the following,

Could you please elaborate on how I can use a filter array action if that may work? If you look at my original flow, it uses the value not the array.

Any further help much appreciated, I am so tantalisingly close now 😞

[DamoBird365]

Hi @MikeWJS 

If your flow is based on the same excel file being created, it won’t yet have a table until you run the script? You might also need to add a delay of 6 minutes, it’s a limitation of excel actions.  

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#:~:text=An%20Excel%20file%20may%20b....

Damien

[MikeWJS]

Thank you so much again for responding!

I should clarify, the flow is in two steps,

The first one is this, which works perfectly (thanks for that!)

So now I have the data in sharepoint, as a spreadsheet in the document library, with a table created in it.

So far so good!

Now I am trying to actually import the data from the spreadsheet in the document library to the list.

I thought I could use the when a file is created in a folder to trigger the import (i.e. when the spreadsheet is created in the doc library to trigger the import but as you say, maybe it is the locks).

As earlier posted, here is the one (part 2) that fails,

Basically what I want to do is at a certain time, take the contents of the spreadsheet and import them into the list but I am unsure how to reference the spreadsheet as it will be recreated each time so it will be dynamic (statics will fail).

Maybe some logic like get the file name in the sharepoint document library, then get the table name, then use this table name in the list rows present in spreadsheet, then use this data to "create item" in sharepoint.

Thanks once again for taking the time to respond, your help is very much appreciated!

[DamoBird365 · Accepted solution]

Hi @MikeWJS 

You could simply list rows after the run script action, equally, you could pass back data from the script to PowerAutomate without even the need to create a table.  I don't think you need another flow here.

The following would return data to Power Automate in one action:

function main(workbook: ExcelScript.Workbook,

SheetName: string = "Sheet1"

) {

  const selectedSheet = workbook.getWorksheet(SheetName);

  let data=selectedSheet.getUsedRange().getValues()

  console.log(data)

  return(data)

}

Where I have compose2, insert a create item and for each of the values use the expression:

items('Apply_to_each')?[0]

1,2 etc...

If you want to skip the first row, use the following expression and insert that instead of the result.

skip(outputs('Run_script')?['body']?['result'],1)

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

[MikeWJS]

@DamoBird365 Thanks you so much for your help on this, I finally managed to get a working solution from the information you gave me.

The final solution looked like this,

Where the script used was yours (above) and the create item was of the form 

items('Apply_to_each')?[x] where x was the array identifier integer.

I cannot stress how appreciative I am of your help, in fact, please p.m. me and I would be happy to send you a contribution to buy yourself some beers! (seriously).

Once again, thanks so much for your help!