How to Create an Excel Spreadsheet with Dynamic Row & Cell Data in Power Automate
Automating Excel-related workflows in Power Automate can make our lives considerably easier. That includes generating entirely new Excel workbooks from scratch — something we can easily pull off with a single Cloudmersive Document Conversion connector action.
Create Excel Spreadsheets with Rows and Cells in Power Automate
In this article, we’ll learn how to create a new Excel spreadsheet from scratch in our flow, and we’ll incorporate a few initial rows and cells within that spreadsheet — all in a single step.
This process stands in contrast to another flow we demonstrated recently which resulted in creating a completely blank Excel document. In that flow, we ended up with a “template” workbook containing two empty worksheets (the result of that flow is shown in the below screenshot).
Creating blank spreadsheets is useful when we’re only interested in generating a “shell” document for business users to later fill in. Creating a new spreadsheet WITH initial row and cell data means we’ll have the option to place dynamic content (returned from other Power Automate actions) directly into our new workbook.
In this walkthrough, we’ll build an Instant cloud flow with a few manually triggered inputs. We’ll use these manual inputs to simulate dynamic content entering our flow from other connected applications; this way, we can avoid testing our flow with an external request (which is a much slower and more monotonous process).
When we eventually test our Instant cloud flow, we’ll simply enter a few example values into our manual inputs, and those values will become cells in our new spreadsheet.
Once we’ve set up our manual inputs, we’ll add a new action and search for Cloudmersive connectors. We’re looking for the Cloudmersive Document Conversion connector; once we find it, we’ll click “see more” to view the actions list.
From there, we’ll search for an action called Create a new Excel XLSX spreadsheet from column and row data (in the previous walkthrough, we used the action just above this labeled Create a blank Excel XLSX spreadsheet).
Before we can go about configuring our request parameters, we’ll first need to create our connection — and we’ll need an API key for that. To get one, we’ll simply visit the Cloudmersive website and create a free account. Free API keys allow a limit of 800 API calls per month with zero additional commitments, and I’m using one in my example flow. It’s also worth noting that we’ll need a premium Power Automate license to access Cloudmersive connectors; they aren’t available at the “trial” and “free” tiers.
After we’ve successfully created and authorized our connection, we’ll click “Show all” to view our request parameters.
We’ll first turn our attention to the second parameter of this action, which gives us the option to name the first worksheet in our new Excel document (the worksheet name will default to “Sheet1” if we leave this blank). In my example, I’ll give my initial worksheet the name “My First Worksheet”.
Next, we’ll turn our attention to the initial Input/Rows parameter. We’ll use this parameter to fill our new spreadsheet with dynamic content from the manual inputs we created initially.
We’ll click Add new item to begin defining our Input/Row array, and we’ll then click Add new item once again to customize our initial cell contents. Within our Cell array, we’ll find the option to reference a specific cell by the cell identifier, and we’ll also find parameters allowing us to set formulas, style indices, and cell text values (we’ll ignore the Cell Items Path parameter for now because we’re starting with a blank row).
In my example, I’ll add my first dynamic content value (External Data 1) into cell C3, my second dynamic content value (External Data 2) into D3, and so on to create a single, cohesive row of data with my manual inputs.
If we select the Switch to input entire array option, we’ll be able to see how these parameters are structuring this data for our API request.
It’s worth noting that we don’t have to line up all our Cell array inputs in a single row (in my example, row 3) like I did; we can just as easily add one input to C3 and the next to H10 within the same Cell array if we prefer. Doing it the way I did simply makes it easier to organize our request, which becomes important when we use this action to build spreadsheets with a higher volume of input data.
After we’ve configured the Create Excel from Rows and Cells request to our liking, we’ll turn our attention to generating a new Excel document in our file system.
Like many other Cloudmersive Document Conversion actions concerned with document editing, the Create Excel from Rows and Cells action loads our Excel file bytes into a temporary editing URL (stored in-memory for no longer than 30 minutes) which can be used to chain edits together rapidly across multiple document editing actions. We could, for example, pass the temporary URL from our Create Excel from Rows and Cells action to another action that adds an additional worksheet to our document.
To return the file bytes we need to generate the a new Excel document in our file system, we’ll need to incorporate one final Cloudmersive Document Conversion action — called Finish editing document, and download result from document editing — into our flow. This action does exactly what the name implies; it downloads file bytes from the temporary URL and makes that content available in our flow.
We’ll now add a Create file action into our flow. We’ll name our file appropriately, and we’ll then use OutputContent from the Finish Editing Document action to define our new Excel document contents.
We’ll now save and test our flow, adding example values in our manual trigger inputs. In my example, I’m adding the values Cars, Planes, Trains, and Boats.
After our flow runs successfully, we’ll find our new spreadsheet in our target folder, and we can review its contents.
Nice and easy, right? Now we can generate brand-new Excel documents in Power Automate and fill rows and cells with dynamic content.
