Populate 1 Excel template faster with Power Automate

Over the last week I’ve had multiple clients asking me about populating Excel template files with data.

Excel templates

There are a couple of things that we first need to have a look at. I’m going to split up all the requirements into smaller requirements.

For a lot of the work I do with Excel files I use Microsoft Graph. MS Graph is fast and once you understand how it works it even becomes easy to use. Then I use standard actions to turn a Excel template stored in SharePoint or OneDrive to an Excel file.

Looking at all the things that I need, i have the following requirements.

Technical Requirements

• Access MS graph

Functional Requirements

• Start when a button is pressed in Power Apps
• Copy a Excel template file to a new Excel file
• Update a single Excel cell
• Update a block of cells
• Add lines of data
• Add a second set of lines
• Insert Rows

Access MS Graph

I wrote of this before, in earlier posts about reading from Excel. You will need to create an App registration in Azure. So that you can use the Client ID, Tenant ID and Secret to access you MS Graph from Flow.

I always put these actions inside a scope box, so that it becomes easy to understand what the combination of boxes does in Power Automate.

Then the Get access token will be configured as shown below

The code in the URI looks like this:

https://login.microsoftonline.com/@{outputs('TenantID')}/oauth2/token

While the body look like:

client_id=@{outputs('ClientID')}&client_secret=@{outputs('SecretID')}&resource=https://graph.microsoft.com&grant_type=client_credentials

And the now i put the Access token returned into a compose box. I’m doing this mainly for debug purposes. You could get rid of this and use the access token from the dynamic content instead.

Start when a button is pressed in Power Apps

When I call a Flow from Power Apps I like to pass in all my data as a single text parameter. Then within my flow I will convert this text to json using the json function as shown below:

json(triggerBody()['Input_Inputs'])

Copy an Excel template file to a new Excel file

The next step is to create a copy of the Excel template file to create our unique Excel spreadsheet. In my case I put the Template file in a SharePoint Document Library and I put the resulting Excel files in a different library.

For most cases the following 5 steps will work.

In the above steps I’m first collecting the file content from the template file.

Then I will delete an existing file, so that the file can be created. The create file has run after settings configured so that the file will be created even if the file deletion failed. Not that in my requirements I want to overwrite any possible existing files with the same name.

Then as the file has been created the update file properties will set the name of the file. Before the flow gets the file by using a filter query.

Update a single Excel cell

Before we can update a cell or a range will will need to collect some details of the site and the library that we want to work with.

Get the team site

To get the site id for the team site that I’m interested in I use the following endpoint.

https://graph.microsoft.com/v1.0/sites/tenant.sharepoint.com:/sites/ITTeam

Note that we are using the access token earlier retrieved from MS graph.

Get the list ID

Using the id returned by the above action we can now get the details for the document library where our Excel file is stored.

https://graph.microsoft.com/v1.0/sites/@{body('Get_IT_Team_site')?['id']}/lists/Docs

Now we are ready to do our updates.

There are a few different ways that you can update a cell. I’m going to use the range end points to do this. This way i can update 1 or more cells in exactly the same way.

First of all I will call the following end point:

https://graph.microsoft.com/v1.0/sites/@{body('Get_IT_Team_site')?['id']}/lists/@{body('Get_the_List_ID')?['id']}/drive/root:/@{concat(outputs('Input')?['Customer'], '-', outputs('Input')?['IssueNo'], '.xlsx')}:/workbook/worksheets('Sheet1')/range(address='E7')

The name of the excel file that I am accessing is made up of a combination of customer name and issue number.

Now we should have a more details look at the body.

There are 3 sections.

• values
• numberFormat
• formulas

{
  "values": [
    [
      "@{body('Parse_JSON')?['ApplicationID']}"
    ]
  ],
  "numberFormat": [
    [
      "General"
    ]
  ],
  "formulas": [
    [
      "@{body('Parse_JSON')?['ApplicationID']}"
    ]
  ]
}

The values are easy enough. Just supply the value that you want to appear in the field.

The number formats are an array of numberFormats. You can use the following number formats for all Excel supported formats:

• General
• Number
• Currency
• Accounting
• Date
• Time
• Percentage
• Fraction
• Scientific
• Text
• Special
• Custom

I found creating an excel with these formats used and the getting the data from a cell range gave me the exact format that I would have to use for each cell.

Then finally the formulas section contains any calculations that you may need to do. This can be a copy of the value as shown above, but it could also be something like

=B4

Update multiple cells

The update of multiple rows is fairly similar as a single cell.

Rather than updating a single cell you will now have to update the URI to update a range using the following syntax:

.../range(address='E7:K10')

An important thing to note is that the body now needs to match the shape of this range.

So if the range covers a rectangle of 5×7 then the data specified for the values, numberFormats and formulas also needs to be 5×7.

Where earlier we saw that the values were set to

"values": [
    [
      "@{body('Parse_JSON')?['ApplicationID']}"
    ]
  ]

Now you will need to specify data fro each row and column as shown below.

"values": [
    [
      "row1 - column1",
      "row1 - column2"
    ]
,
    [
      "row2 - column1",
      "row2 - column2"
    ]
  ]

Insert rows in Excel

this is the first part of updating rows of data. Imagine if you have a product order and multiple products are ordered. You have all the data available and you now need to insert a number of rows.

Similar to the update a range will need to be specified. As you may have a flexible number of rows you mayb have to use some expressions to calculate the range.

Below a small example of how you could do this.

/range(address='A@{add(14, outputs('Count_number_of_updates_so_far'))}:K@{add(add(14, outputs('Count_number_of_updates_so_far')),variables('Index'))}')/insert

In the body you can decide if you want the cells to shift down or to the right

{
  "shift": "down"
}

Update multiple rows

now that the new empty cells have been created the cells will need to be filled. We can use the same method from before.

However there are a few important steps.

First I would generate the data that is needed in the Values, numberFromats and Formulas. using three compose actions.

Now the body data can be created using the Pieter’s method. where we refer the the compose actions inside the Apply to each from outside the apply to each.

But what if you want more?

Microsoft graph can still do a lot more, do you want to know more about using Microsoft graph then have a look at Working with Excel and Microsoft Graph in Power Automate