How to Add New Worksheets to Excel XLSX Documents in Power Automate
Excel workbooks are easy to build out. We can add entirely new worksheets to any Excel document with the click of a button, expanding the contents of our original workbooks with relevant (yet separate) data sets.
There’s a way to automate that process, too. In Power Automate, we can add brand new worksheet tabs to existing Excel documents at scale with just a few clicks. We can simply use the Cloudmersive Document Conversion connector to set the name (and, optionally, the path) of a blank new worksheet and tack that worksheet onto the end of our existing workbooks.
Below, we’ll walk through a quick example flow in Power Automate that adds one new worksheet to three existing Excel workbooks in a folder. We’ll create an instant cloud flow in this context so we can have full control over our data.
In this example, the three workbooks in question contain datasets related to a few (fake) companies’ financials. As shown in the below screenshot, the existing worksheets in these example workbooks are named “Expenses” and “Budgeting”, and the new blank worksheet added to will be named “Forecasting”.
To build our flow, we’ll start by selecting the option to create an Instant cloud flow and trigger it manually.
Our first two flow actions will 1) get an array of information about the Excel workbooks we’re modifying and 2) get file bytes for each workbook.
If our files live in a SharePoint folder, we’ll first add the List folder action and configure our site address & folder path; if our files live in a OneDrive for Business folder, we’ll use the List files in folder action instead. I’m using the former option in my example.
Next, we’ll add a Get file content action, and we’ll configure it to get file bytes using the unique File Identifiers retrieved by the List folder (or List files in folder) action. Because the IDs are returned as an array, Power Automate will automatically wrap our Get file content action in a For each control.
Now that we have Excel file bytes in our flow, we can add worksheets to each of those files by adding the correct Cloudmersive Document Conversion connector action in the For each control.
To find the action we need, we’ll first search for Cloudmersive connectors and locate the Cloudmersive Document Conversion connector with the green logo.
We’ll then click “See more” to view the actions list, and from there, we’ll search for the Insert a new worksheet into an Excel XLSX spreadsheet action.
After we select this action, we’ll need to create our Cloudmersive Document Conversion connection. We’ll need a free Cloudmersive API key to do this, which we can get by visiting the Cloudmersive website and creating a free account. Free API keys allow a limit of 800 API calls per month (with no additional commitments). It’s worth noting that we’ll also need a premium Power Automate license to access Cloudmersive connectors.
To configure our Insert new worksheet request, we’ll first click “Show all” to view the advanced parameters. Right after that, we’ll add our Excel documents’ file bytes in the InputFileBytes parameter, and we’ll add the name of our new worksheet in the WorksheetToInsert/WorksheetName parameter.
When we use Cloudmersive Document Conversion actions like this one to modify files, our file contents are automatically loaded into secure, temporary editing URLs (expiring after 30 minutes) which can be passed downstream to subsequent file-modifying actions. This drastically improves the efficiency of our flow when multiple file-modifying steps are used at once. To download file bytes from the temporary URL, a final Cloudmersive Document Conversion action is required, and it’s called Finish editing document, and download result from document editing.
Since we aren’t chaining any additional actions in this flow, we’ll simply incorporate the Finish editing action in our For each control, and we’ll send it the temporary URL created by our Insert new worksheet action.
Now that we’ve downloaded our file bytes, we’ll add an Update file action within the For each control, and we’ll use this action to update each of our original Excel workbooks (via the File Identifier) with the new worksheet tab.
We’ll now save and test our flow, and after our test is complete, we’ll open one of our original Excel documents to confirm the new workbook was added.
We’ll find a blank new worksheet in each of our original workbooks with the appropriate name. Relevant reporting teams can now add data in a new worksheet that follows a single, standardized naming convention.
