Excel Office Script; Call from a Flow in power automate

Content

• Introduction
• Video
• Our goal
• Creating a Flow in Power Automate
• Further reading

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:

Next, click "Create" on the left:

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":

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

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":

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

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

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:

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:

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

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

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:

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

Just select the Id item:

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":

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

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

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

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):

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

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:

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

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

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":

You'll get a list similar to this:

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