Simple Excel Report ​

This example will show you how to use Microsoft Power Automate to automatically get all apprentices and build a simple Excel report.

At the end of this example you should be able to create your own scheduled Power Automate flow to query the ACE360 Connect API, get apprentice data, and add data to an Excel report. This is just an example feel free to try different things and pick data that would be relevant to you.

Contents ​

• Getting Started
• Your First Flow
  • Configuring Your Flow
  • Getting Your Data
  • Processing Your Data
  • Building Your Excel Report
• Summary
• Next Steps

Getting Started ​

Before you can create a Power Automate flow, you'll need access Power Automate itself. It is included with some Office 365 licenses, so if you're already an Office 365 customer check to see if access is included. Otherwise visit here and sign up. Keep in mind that this guide makes use of premium Power Automate features, if you don't have access to Power Automate Pro, you won't be able to follow along.

Your First Flow ​

Once your account is set up and you've logged in you should see something similar to this:

This dashboard makes things look pretty complicated. There's a lot going on! In this demo, we're only going to focus on setting up a single automated flow. We'll cover some of the other features and options in later demos.

Configuring Your Flow ​

Click "Create" and under the start from blank heading, select "Scheduled cloud flow".

Add a name for your flow, for this example we'll use "Automated Excel Demo". Set the date, time, and the frequency you'd like this flow to run at. For this example we'll go with every week, on a monday, at 10:00 AM.

Getting Your Data ​

To request data from Connect you'll need to add a new step to your flow. Search for "Http" and select the the first option under the green HTTP heading.

Fill out the HTTP step as follows. You'll need a method, URI, and some headers.

Be sure to enter your own token like so "Bearer 2|abcd...." (don't include the quotes).

Note: ACE360 Connect returns paginated responses. This means we limit the amount of data returned in each request, to get all of your data you'll need to request each page of results. The HTTP step can accomodate pagination, click settings and scroll down until you see "Pagination". Turn this on and optionally set a threshold. Keep in mind if you have a huge data set Power Automate may time out.

Processing Your Data ​

Before you can work with your data, you'll need to do a bit of processing. This involves selecting the data that the API has sent us, and parsing that response so we can manipulate it. Don't worry, Power Automate can do this for us.

Add a new step and search for json like so.

Select the "Parse JSON" option under the purple "Data Operation" heading.

You need to tell the JSON parser to collect the data or "body" from the Http request.

TIP: Use the little lightning bolt to select available options.

You'll also need to generate a schema for it to interpret the data. Click the link under the big empty box that says "Use sample payload to generate schema" and paste the following into the window that will have opened.

{
    "total": null,
    "currentPage": null,
    "perPage": null,
    "totalPages": null,
    "data": [
        {
            "id": null,
            "ulnNumber": null,
            "firstName": null,
            "lastName": null,
            "dateOfBirth": null,
            "standardId": null,
            "status": {
                "id": null,
                "text": null
            },
            "tpoId": null,
            "estimatedStartDate": null,
            "estimatedEndDate": null,
            "esfaCertificateClaimedAt": null,
            "estimatedGatewayAtEpaoRegistration": null,
            "approvedForEpaDate": null,
            "provisionalGrade": null,
            "epaOverallGrade": null,
            "gradeDate": null,
            "createdAt": null,
            "archivedAt": null,
            "epaoInternalId": null,
            "epaoId": null
        }
    ]   
}

It should look something like this:

Clicking done will fill the previously empty box with a schema that Power Automate can understand.

Building Your Excel Report ​

The json response from Connect will contain many apprentices. You will want to write each of these apprentices to your Excel report. To do that, you'll need to add a new step and search for "apply to each".

Select the "Dody data" from the parse json step as your output.

Then add an action and search for add a row.

In this example we're making use of an Excel workbook with a table that lives on our Sharepoint site. Though you can make use of OneDrive if you'd prefer.

Our Excel workbook started life looking as a blank table with the name "Table1".

Once the table has been found by Power Automate you'll be able to assign data to fields, your config should look something like this:

Once you've assigned the right json fields to correct columns save your flow.

Assuming your flow saved without errors, it's time to test your new flow.

Using the toolbar at the top right of the screen click test.

In the new sidebar click manual, then test.

It'll take a minute but your flow should run. Keep an eye on your Excel workbook and on the status of your flow.

After a minute or so you should start seeing entries appearing in your Excel report.

And that's the basics of getting data from ACE360 Connect into a simple Excel workbook.

Summary ​

By following this tutorial you should now be able to create simple Excel reports using Power Automate. The example we provided here won't be particularly clever but serves to give you a basic understanding of how to get data from ACE360 Connect and start manipulating it.

Next Steps ​

Now you know how to build simple Excel reports. We'd recommend that you duplicate the report and experiment with checking for existing values, removing older unwanted data, and filling out more than one table. You could even start experimenting with our API filters to trim down the data you receive from Connect.

Need Inspiriation? ​

We've got an automated email guide coming soon!

Are you a PowerBi user?

Check out our basic PowerBi report tutorial.