Skip to main content
Community site session details

Community site session details

Session Id : 6tlZ3rnldG1TXigIM/GcdD
Power Automate - Building Flows
Unanswered

Reading Excel Spreadsheet - What's the process?

Like (0) ShareShare
ReportReport
Posted on 4 Apr 2023 20:13:37 by

I found numerous ways to get data from an excel spreadsheet in this forum, but each seems to address a specific spin on the process.

 

I have a pretty straight forward need, open a spreadsheet, go to a specific tab (Sheet), then get some columns into some array variables.

 

Wondering if someone could explain, real simple like, the actions to go through to get a column into an array variable from a excel document that has three sheets in it.

 

I expect the first step is to Get Worksheets, Apply to Each, then...?

I don't see an obvious action that will let me specify or filter for a specific sheet.

 

So if you type slow and use small words, perhaps I can figure out.

 

Thx

  • AndreasN Profile Picture
    307 Super User 2024 Season 1 on 05 Apr 2023 at 12:27:15
    Re: Reading Excel Spreadsheet - What's the process?

    Sure it can, but it requires you to make tables. Are you not able to edit anything in the excel sheet?

    There's multiple solutions here:

    If you are not able to edit the excel document, simply have power automate copy the content of the excel document, into one that you can edit in. If the amount of cells used remains the same, you can have power automate create the tables for you, by specifying the range of cells it has to include.

    If the cells are dynamically changing, meaning some documents might have more data than others, create an office script, that selects all cells with data inside and constructs a table. 

    function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet. let selectedSheet = workbook.getActiveWorksheet();
    // Create a table with the used cells.
    let usedRange = selectedSheet.getUsedRange();
    let newTable = selectedSheet.addTable(usedRange, true);
    // Sort the table using the first column.
    newTable.getSort().apply([{ key:
    0, ascending: true }]); }


    That's the script I would use, then just have the flow copy the content, create a file in e.g. onedrive or sharepoint with the content of the excel document. Run the script through power automate, place a small delay until the next action, since running scripts can be slow in Power Automate. 

    This gives you a fully editable excel sheet, with the content sent to you, with a table constructed. Then run the rest of your actions.

  • Unknown geen idee Profile Picture
    1,755 on 05 Apr 2023 at 12:24:40
    Re: Reading Excel Spreadsheet - What's the process?

    Dear Dons,

     

    If you use Power Automate Desktop you can read excel spreadsheets without a table ... but PAD has again other restrictions.

     

    Alternatively, if you cannot change the original ... but you are able to get a copy of the file (save it to a temp location on OneDrive) ,you could still perform the steps you want. You can use an action to create a table in an excel sheet ... (action: Create Table) and then use this for the following standard actions in Excel. I have used it successfully in flows which did not have tables defined in the original data.

    If you do this though ... put in some delay between creation of the table and actually reading this table ... OneDrive will need some time to 'rest' before the table in the excel can be read successfully.

     

    Hope the suggestion helps,

    Happy flowing,

    Koen

  • Community Power Platform Member Profile Picture
    on 05 Apr 2023 at 12:12:24
    Re: Reading Excel Spreadsheet - What's the process?

    You are correct. I have never run across "Tables" in Excel, which seem to be nothing but a sheet.

    Regardless, I am not in control of the source so I cannot make changes.

     

    Should I take this as a confirmation that Power Automate cannot read the contents of a normal, everyday spreadsheet you might find in any business?

     

    Thank You

  • Unknown geen idee Profile Picture
    1,755 on 05 Apr 2023 at 08:38:11
    Re: Reading Excel Spreadsheet - What's the process?

    Dear donsthompson,

     

    Based on your description it seems to me that you have not yet realized the importance of the 'Table' in Excel (assuming you are using Power Automate ... not Power Automate Desktop).

    The excel actions in Power Automate all (almost all) work with defined tables in Excel ... once you have the tables defined, the rest flows from this. You do not need to bother with sheets and stuff.

     

    View example below where I have 'defined' the coloured table with the name 'Table1' in Excel.

    This table can be recognized by the Excel actions in Power Automate (provided the file is saved in a OneDrive/Sharepoint location). The table on the left side (which is not specifically defined as a table) is not visible.

    Koen5_0-1680683788316.png

     

    Hope this helps, 

    Happy flowing,

    Koen

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 1

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 1

Loading complete