Reading Excel Spreadsheet - What's the process?

[Anonymous]

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

[Koen5]

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.

Hope this helps, 

Happy flowing,

Koen

[Anonymous]

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

[Koen5]

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

[AndreasN]

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.