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](../images/powerautomate01.jpg)
Next, click "Create" on the left:
![The Create button in Power Automate](../images/powerautomate02.jpg)
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](../images/powerautomate03.jpg)
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](../images/powerautomate04.jpg)
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](../images/powerautomate05.jpg)
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](../images/powerautomate06.jpg)
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](../images/powerautomate07.jpg)
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](../images/powerautomate08.jpg)
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](../images/powerautomate09.jpg)
Click on that entry and the flow step changes to this:
![The List all files in a folder operation in a Power Automate flow](../images/powerautomate10.jpg)
Click the filing icon and select the folder you want the flow to process:
![Choosing which folder to process in a Power Automate flow](../images/powerautomate11.jpg)
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](../images/powerautomate12.jpg)
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](../images/powerautomate13.jpg)
Just select the Id item:
![Click in the Select output from previous step in a Power Automate flow](../images/powerautomate14.jpg)
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](../images/powerautomate15.jpg)
I renamed the step "IsExcelFile" and set it up like this:
![Condition operation set up in a Power Automate flow](../images/powerautomate16.jpg)
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](../images/powerautomate17.jpg)
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](../images/powerautomate18.jpg)
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](../images/powerautomate19.jpg)
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](../images/powerautomate20.jpg)
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](../images/powerautomate21.jpg)
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](../images/powerautomate22.jpg)
Next, you should get your conformation question. Set it to Yes and then
click Run flow:
![Confirm running the script in Power Automate](../images/powerautomate23.jpg)
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](../images/powerautomate24.jpg)
You'll get a list similar to this:
![Confirm whether a script in Power Automate has run successfully](../images/powerautomate25.jpg)
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