Excel Tips & Tricks #478 - Introduction to Power Automate

No prior knowledge of Power Automate is required to follow the steps in this article. An introduction to Office Scripts has been covered further in Tip #470.

Please note that to follow the steps in the example you will need a work email address linked to a Microsoft 365 subscription.

Creating the office script

Before diving into Power Automate, we need an Office Script to work with.

Here we have a very simple script that will perform two actions for a given Excel file:

1. Insert two rows at the top of the active worksheet, and
2. Add the title “Excel Tips & Tricks Ltd” in cell “A1”.

To create this script, you can open Excel and use the “Record Actions” button under the “Automate” tab.

Insert two rows at the top of the spreadsheet then add the title and press “Stop recording”.

Excel will create a new script in your OneDrive folder with a default name e.g., “Script 1”.

In this example I have then changed the script name to “Insert title”.

Target files

To follow the example, you can save any collection of Excel files in a folder in your OneDrive.

For demonstration purposes I have five Excel files saved in a folder on my OneDrive that all contain data in a similar format as the example above.

Getting started with Power Automate

Now we have all the pieces in place ready to link them together with Power Automate!

Power Automate can be found by navigating to powerautomate.com in your web browser and signing in with your Microsoft 365 account.

If you are already logged in to any of the Microsoft 365 apps in your web browser clicking the 9 dots in the top left corner will bring up the app switcher, and from there you can find Power Automate.

Setting up the flow

Flow is the term used to describe a sequence of actions that link together in a repeatable way that we specify.

Once we are in Power Automate, we can choose “Create” to start building our flow.

Next choose “Instant cloud flow” to create a flow that will run on demand.

Now we need to specify how the flow will start. The first option to “Manually trigger a flow” is the standard first step for a flow that will be triggered on demand.

Choose “Create” and we will be taken to the flow builder page as follows:

Flows in Power Automate run from top to bottom with each step represented by a horizontal bar that can be clicked on to expand or collapse as required.

Connecting to connectors!

Connectors are the links between Power Automate and any app outside of Power Automate (e.g., Excel, OneDrive, Teams, Outlook etc.).

Choose “+ New step” and we are presented with all the connectors available to us. Let’s search by name and type in “Excel”.

The option we want is “Excel Online (Business)”.

Light, Camera... Action!

Actions are the things that you can do with connectors.

Here we are presented with a list of actions relevant to the “Excel Online (Business)” connector. We want to choose “Run script”.

You are then presented with four boxes with drop downs.

1. Location: click the drop-down and choose “OneDrive for Business”.

2. Document Library. This will be “OneDrive”.

3. File: Select the file against which we will run the script. Here we can use the browse icon on the right-hand side. Let’s choose the first Excel file in the folder.

4. Choose the script from the Office Scripts saved in our OneDrive.

I will choose “Insert title” as that is the script created earlier in this example.

Now let’s press Save and create the flow. A green banner should appear to confirm this.

Well done for creating a Power Automate flow!

Editing your Power Automate flow

Remember that we want to run this script against all files in the folder, not just the one we selected.

To change the behaviour of the script to update all Excel files in the folder we can create an intermediate step as follows.

Hover the mouse pointer over the arrow between the two steps and you will see a “+” icon appear.

Click on this and choose “Add an action”.

From here we can search for the “OneDrive for business” connector and the action to “List files in folder”.

In the same way as we selected the file before we can use the browse icon on the right-hand side to select our OneDrive folder containing the files we want to target.

Note: you will be presented with the “Root” parent folder for your OneDrive. Click on the greater than symbol to drill down into your OneDrive folders.

After you have selected your folder, you can go back into the green “Run script” step and delete the file location in the “File” field.

In its place we need to reference the individual file IDs for each file in our OneDrive folder.

From the right-hand side, you have a separate menu to insert dynamic content.

(Dynamic content is the term used to refer to information gathered in previous steps.)

Click on “Id” to bring in a reference to all your files in the OneDrive folder.

You will notice that Power Automate has created an “Apply to each” box around our “Run script” action.

And that’s it! We have now updated our flow with desired behaviour. Now we can choose to save and take it for a spin. Choose “Test” in the top right corner.

A task pane will appear on the right-hand side to test the flow. Click on “Manually” and continue through to the next screen.

Power Automate will check that all your connections are live and ready to run the flow. If not, you will have the option to sign in.

And finally, the moment of truth, you can run the flow.

Now that it has started you can click into the “Flow Runs Page” to monitor the progress of it. And of course, you can open your Excel files on another tab in your browser to see the magic happening in the background!

Power Automate and Excel (and other apps…)

The techniques shown in this example can generate huge potential time savings when dealing with more complex scripts and a larger number of files.

As well as the “Run script” action Power Automate can create flows that for example update a spreadsheet every time an email is received in Outlook.

I would highly recommend exploring the different connectors and actions within Power Automate and start experimenting with your own flows!