How to Get Worksheets from an Excel XLSX Document in Power Automate
Building dynamic Excel workflows in Power Automate starts with gathering structured information from within the Open Office Excel (XSLX) file structure.
If we’re performing specific worksheet-related actions, we’ll first want to retrieve worksheet information from within our Excel workbooks and use that information dynamically in subsequent steps of our flow.
Using the Cloudmersive Document Conversion connector, we can easily retrieve the names and paths of worksheets in any given Excel document. Once we have worksheet name and path information, we can extract or modify specific sets of data in our spreadsheets with ease.
In this article, we’ll quickly run through an example flow to demonstrate the process of getting worksheet name & path information from an example spreadsheet document. We’ll build an instant cloud flow in this context so we can have full control over our data in a quick & efficient test.
We’ll start by adding a new action and searching for Cloudmersive connectors.
When we type “Cloudmersive” into the connector search bar, we’ll bring up a list of several different Cloudmersive connectors with different colored logos.
We’re looking for the Cloudmersive Document Conversion connector, which has a green logo.
After we click “See more” to view the full actions list, we’ll hit CTRL+F on our keyboard and search for an action called Get worksheets from a Excel XLSX spreadsheet. We’ll then select this action once we find it.
Selecting this action will prompt us to create a Cloudmersive Document Conversion connection if we haven’t already. To authorize our connection, we’ll need a Cloudmersive API key, and we can get one for free (with a limit of 800 API calls per month) by visiting the Cloudmersive website and creating a free account.
After we’ve successfully created and authorized our connection, we’ll click “Show all” to view our two (advanced) request parameters.
As we can see, this action is asking for Excel file bytes to process in a request.
To bring Excel file bytes into our flow, we’ll add a Get file content action prior to our Get worksheets action and retrieve an example workbook (ideally one with multiple worksheets) from our file system.
We’ll then place our dynamic Excel content in the Input/InputFileBytes parameter.
Now we’ll save our flow and run a quick test.
When we open our Get worksheets action, we’ll find a “Worksheets” array containing the path and name of each worksheet in our document.
The “Path” values contain the zero-based worksheet indices in brackets (e.g., [0], [1], [2]), and the “WorksheetName” values contain the string names assigned to the worksheet by the document creator (or generically assigned by Excel).
We can now use collection functions or Data Operation actions in our flow to sort through our worksheet paths and/or names to interact with worksheet data in various ways.
