cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidFdez
New Member

Create excel table if it does not exist

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,

 

4 REPLIES 4
sumurthy
Microsoft
Microsoft

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";
}

 

Table delete.png

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,

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

JenniferFGI
Frequent Visitor

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,940)