Create excel table if it does not exist

(0) Share

Report

Posted on by DavidFdez

Hello!

This is my first message I hope I don't break any rule...

The flow I need to make is just "excel to planner task". I need to collect the information based in one column, and then create a task if it does not exist yet.

I have a column (number) to identify the task.

The point is that the excel is downloaded daily in my onedrive, automatically and overwritting the last file. This occur only if a change occurs in the system, so not every day I may have a new file. The file have no format, so no table. I have tried to create a table, and there is no problem if there is no table inside this excel file (when it is a new file), however, if the day before I have created the table (and no new file that day), then my flow fails.

I don't know how to check in my flow if the table already exists. Maybe you can give me any clue.

On the other hand, I have made a research about how to create a table with no empty rows. An easy one is to create a large table and then to delate every empty row. Any other suggestion is welcome.

And finally, I need to check if a task in planner already exist with the number from the proper excel column, in order to not create repeated tasks. Same than before: if you have any clue, it would be more than welcome.

Thanks in advance, and sorry for my poor English.

Best regards,

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

sumurthy Microsoft Employee on at

Like (0)

Report
I think you can achieve this using Office Scripts.

Do you know for the file that is created in OneDrive whether the data appears in a constant worksheet? Such as Sheet1?
If so, you can easily check if table is present or not and do the necessary:
1. IF table is present, return "Skip" and you can add condition in the next step to skip this instance.
2. If table is not present, create the table and return "Continue" as the value so that next condition can read that and sync with planner.

See source and screen shot of the Office Scripts below.
Checkout this video to see how to read value from Office Script Run script action --
https://youtu.be/dVwqBf483qo
function main(workbook: ExcelScript.Workbook): string { let sheet = workbook.getWorksheet('Sheet2'); let tables = sheet.getTables(); if (tables.length > 0) { console.log('Table is present: ' + tables[0].getName()) // Optionally you can delete data -- // tables[0].delete(); return 'Skip'; } else { console.log('No table is present'); sheet.addTable(sheet.getUsedRange().getAddress(), true); } return "Continue"; }

Was this reply helpful? Yes No
DavidFdez 2 on at

Like (0)

Report

Hello sumurthy,

Thanks for your solution. The sheet has a name that is known. No problem with that. What I don't know is how to insert the script. I cannot modify the file, it is automatically downloaded in my onedrive everyday that a change in the system occurs, overwritting the old file.

Is it possible to add the script by power automate? I don't find the way...

Any clue is welcome.

Best regards,

Was this reply helpful? Yes No
sumurthy Microsoft Employee on at

Like (0)

Report

Scripts are not 'attached' to file. The scripts live independently in your own OneDrive and you can run it against any Excel file stored in OneDrive or SharePoint sites using Power Automate (Run Script action in Excel online).

See here for details: https://www.youtube.com/watch?v=dVwqBf483qo&feature=youtu.be&ab_channel=SudhiMurthy

Was this reply helpful? Yes No
Community Power Pla... on at

Like (3)

Report

is there a way to check within the powerautomate flow without having to run a script?

Was this reply helpful? Yes No