In this blog, we will examine how to retrieve rows of data from Excel using Encodian Flowr’s ‘Get Rows from Excel‘ action. For demonstration purposes, we’ve created a simple scenario to showcase how the ‘Get Rows from Excel‘ action can be used within Power Automate.
A ‘Supplier Notes’ xlsx (Excel) file gets updated with supplier information. Once a day a flow runs that looks at the data, if any of the rows have a status of ‘Information Needed’ an automated email is to be sent to the primary contact of the supplier asking for the information needed. This blog isn’t focussing on getting the data into Excel (which can easily be done with our ‘Add Rows to Excel‘ action), we will just look at how you can extract the data.
The data in the Excel file doesn’t need to be in table format when using Flowr’s Excel actions. This is what the file looks like:
For demo purposes, I have a manual trigger for my flow; however, in real life, for this scenario, it would make more sense to use a scheduled trigger.
The first step is to get the file content of the Suppliers Excel, I am using OneDrive for this scenario. Once you have the file content, you can use Flowr’s ‘Get Rows from Excel‘ action.
This is what the results look like from the ‘Get Rows from Excel’ action:
Because the results are in JSON format, we can use Parse JSON to make the data easier to access. To use ‘Generate from sample’, run the flow and copy and paste the Row Data result into the generator.
Now that we have our data in array format, we can apply filtering using ‘Filter array’. We will filter the data for when Status equals ‘Information Needed’. Once the array has been filtered, we need to check for any results. To do this, we need to add a condition: if there are no results, the length of the array will be 0; if there are results, the length will be greater than 0:
length(body('Filter_array')) is greater than 0
If the filtered array isn’t empty, that means that there are suppliers where more information is needed. We then need to loop through the outputs of the ‘Filter array’ step to email each individual primary contact.
In the email body, an expression trims the primary contact’s name only to use their first name rather than their full name. The expression splits the name at the space.
first(split(items('Apply_to_each')?['Primary Contact Name'], ' '))
This is the resulting email:
If you prefer to watch how Sophie creates this flow rather than read, check out her companion video:
Search 150+ Actions to see how Flowr can save you time
Sign up for your free 30-day trial; no cards, catches, or contracts.
No job is too big or too small for our Professional Services team!
UPDATE: We’re excited to announce some significant updates to Flowr for Power Automate! As of October 2024, we’ve improved by updating action names and splitting Flowr’s central Power Automate connector into nine specialized connectors. These changes will make your workflow faster, smoother, and more efficient. The new action names are more precise and intuitive, saving you time, while the focused connectors enhance performance and flexibility. This update also helps future-proof the platform for even more powerful features. Check out our updated action names blog.
Technical Evangelist