Excel Office Script; Call from a Flow in power automate

Content

Introduction

I've already posted an article that shows you how to get started with Office Script and one with some example scripts. On this page, I will show you how you can apply an Office Script to all Excel files in a OneDrive (for business) folder.

Video

I've presented parts of this page during Excel Virtually Global 2022. Here's the Youtube recording:

Our goal

Let's assume you are the person responsible for the quality of Excel files on a particular folder in OneDrive for business. One aspect of these files is that they must all contain an up-to-date Table of Contents. In a previous post I already showed you a script that adds a ToC worksheet to the active workbook. Now lets put this script to work.

Creating a Flow in Power Automate

In comes Power Automate. Go to your Office.com page and click the Power Automate icon:

The Power Automate button on Office.com

Next, click "Create" on the left:

The Create button in Power Automate

A new page opens with quite a number of options. For simplicity's sake, I'm going to show you how to create a Flow which you can start at the click of a button, not one which is triggered by some event. Click on "Instant cloud flow":

The Instant cloud flow button in Power Automate

A new window opens. I've entered a descriptive name for the flow (top box) and I've selected "Manually trigger a flow":

The Instant cloud flow setup screen in Power Automate

If you are happy with these initial settings, click Create to have the flow created.

To set up details of the first block, click on the block to expand it. It shows "+ Add an input":

The first step of our flow opened in Power Automate so we can set options

If you click "Add an input", Power automate presents you with a couple of available choices:

Types of user input available to a manually triggered flow in Power Automate

Click the Yes/No icon. Overwrite the Yes/No that appears in the Title box with your question, I entered this:

Make sure the Power Automate flow asks for permission to perform its action

This ensures that once you start the flow, you are first asked to confirm you actually want the flow to run.

Now click the "+ New step" button:

Click the New step button of the Power Automate flow

What we need now is a list of all files in a specific OneDrive for Business folder. To retrieve that list, click the Standard tab and in the Actions tab, scroll down to find the "List files in folder" for OneDrive for Business:

Finding the List all files in a folder operation in a Power Automate flow

Click on that entry and the flow step changes to this:

The List all files in a folder operation in a Power Automate flow

Click the filing icon and select the folder you want the flow to process:

Choosing which folder to process in a Power Automate flow

After clicking a folder, the Folder box should contain the path to the folder. Click the "+ New step" button once more.

The previous step provides us with a list of files from that folder. Now we need to run through that list. Click the "Built-in" tab and then the "Actions" tab and find the "Apply to each" operation:

Finding the Apply to each operation in a Power Automate flow

Click in the "Select an output from previous steps" box as indicated below:

Click in the Select output from previous step in a Power Automate flow

Just select the Id item:

Click in the Select output from previous step in a Power Automate flow

A OneDrive folder does not necessarily contain Excel files only. This means we need a test to determine whether or not to process a file. Inside the "Apply to each step, click "Add an action" and from the "All" tab and in the "Actions" tab, choose "Condition":

Select Condition operation in a Power Automate flow

I renamed the step "IsExcelFile" and set it up like this:

Condition operation set up in a Power Automate flow

The step which is applied to all files in the folder now looks like this:

The step which is applied to all files in the folder in a Power Automate flow

In the "If yes" box, click "Add an action" as indicated above. Click on the "All" tab and activate "Actions" and click "Run script":

Selecting Run script action in a Power Automate flow

Make sure you set it up similar to this (of course your script may be named differently, simply use the drop-down arrow to choose your script):

Run script settings in a Power Automate flow

Now click Save. If there are no issues, you'll get a green bar suggesting your flow is ready to test:

Saving your script succeeded in Power Automate

Now it is time to run your flow. Click the All flows menu item and then click the run button of the flow you want to run:

Running your script in Power Automate

The first thing Power automate does is check whether all credentials are in place. It shows you the results. Click Continue:

Sign in results in your script in Power Automate

Next, you should get your conformation question. Set it to Yes and then click Run flow:

Confirm running the script in Power Automate

That is it! If you want to know whether the flow ran without issues, go back to your list of flows, click the ellipses button next to the appropriate flow and choose "Run history":

Confirm whether a script in Power Automate has run successfully

You'll get a list similar to this:

Confirm whether a script in Power Automate has run successfully

Further reading

Here are some links to the Microsoft documentation on Office Scripts:

Office Scripts documentation

Office Scripts API reference

Introduction to Office Scripts in Excel


Comments

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].