How to Get a Cell from an Excel XLSX Spreadsheet in Power Automate
In a prior article on this blog, we walked through a quick Power Automate flow which retrieved every row and cell from an Excel spreadsheet at once. We returned that data to our flow as a JSON object with a Columns array containing information about each cell in our workbook (pictured below).
While this volume of response information is excellent for data migrations and other large-scale Excel endeavors, it’s also overkill when our needs are more limited in scope. If we only need to migrate one or two cells from a spreadsheet, sorting through such a large Columns array will become a bit cumbersome.
In this article, we’ll walk through another version of this flow that limits the scope of our output considerably. Instead of pulling all rows and cells from a spreadsheet, we’ll only target one specific cell using either the cell identifier (e.g., A1, B1, etc.) or the zero-based cell index (e.g., 0, 1; 0, 2; etc.).
As shown above, this flow will return a much simpler response object containing ONLY the specific data point we need, rather than a mountain of extra information we’ll need to sort through.
In the following example flow, we’ll retrieve an Excel file from a SharePoint folder and return the contents of one cell using both the Cell Identifier and the Cell Index. We’ll use a manually triggered instant cloud flow in this context.
We’ll start by adding a Get file content action to retrieve an Excel file from a SharePoint folder. The file I’ll be using in this demonstration contains several worksheets with data related to a fake company’s expenses, budgeting, and forecasting.
I’ll be retrieving one specific data point from the Expenses worksheet in my example document. That data point is the dollar amount spent on a client meeting in New York (I’ve highlighted this cell in the screenshot below).
We’ll now add the cell-retrieving action to our flow. To find it, we’ll first search for Cloudmersive connectors and locate the Cloudmersive Document Conversion connector (with the green logo).
We’ll click “See more” to view the entire actions list, and from there, we’ll CTRL+F search “Get cell from an Excel XLSX spreadsheet”.
This search brings up two different connector actions — one that retrieves a cell by the Cell Identifier, and one that retrieves a cell by the Cell Index.
We’ll use both of these actions in our example flow (this means we’ll need to add one to our flow, navigate back to the Cloudmersive Document Conversion connector actions list again, and then add the second action).
After we select an action, we’ll need to create our Cloudmersive Document Conversion API connection. To do that, we’ll need a premium Power Automate account (this is because Cloudmersive connectors aren’t built into Power Automate) and a free Cloudmersive API key. We can get our free API key by creating a free account on the Cloudmersive website, and it’ll allow us to make up to 800 API calls per month with zero additional commitments.
With our connection created & authorized, we’ll open the Get Cell by Cell Identifier action and click “Show all” to view the request parameters.
To configure a request that retrieves cell content using a Cell Identifier, we’ll start by adding that identifier in the Input/CellIdentifier parameter. In my case, that’s cell D3.
Once we’ve done that, we’ll add our Excel file bytes in the Input/InputFileBytes parameter. That’s the extent of our mandatory parameters here, but if we’re retrieving data from any worksheet other than the first one, we’ll need to provide the name of that worksheet in the Input/WorksheetToQuery/WorksheetName parameter.
Next, we’ll open up the Get Cell by Index action and target the exact same cell with a different set of inputs. This time, we’ll use zero-based cell and row indices to target our cell contents. Zero-based means the first cell on any spreadsheet — A1 — translates to 0,0 rather than 1,1.
Continuing with my example, the zero-based Cell Index of D3 is 3, 2.
We’ll now save and test our flow. When our flow finishes running, we’ll take a look at the responses from both actions.
We’ll find that both response objects contain identical information about our target cell. We can now use the TextValue from our cell anywhere downstream in our flow as dynamic content.
