How to Use Office Scripts with Power Automate

Office Scripts can be thought of as macros for running on the Web.

You can record or program a set of steps and actions to occur on a dataset or other online file.

These steps can be executed utilizing a schedule, and event that occurs in another app, or manually from your mobile device.

We’ve seen Office Scripts tutorials in previous videos along with Power Automate tutorials.  Now it’s time to see these two powerhouses work together.

If you haven’t seen the previous posts (links below), don’t worry.  This tutorial will give you inspiration and insight into what is possible when you combine these two tools into a super-tool.

Here’s the scenario we’re dealing with: I want to create a script that applies special formatting to ALL my Excel sheets.

Not just one sheet, but all the sheets in the Excel file.

But that’s not all.  I want this script to run not just on one file, but on ANY Excel file I have in a folder.

Basically, the script will loop through the folder, and if it’s an Excel file, it will run on every sheet in the Excel file.

OBJECTIVE #1 – Looping Through Sheets

Our first objective is to create a macro that processes each sheet in an active workbook.

To start our script, we begin by logging in to office.com and open a data file we have created called “To Format.xlsx”.

The number of records in each file used by this script will change.  Some files will have few records while other files will have many records.  “Few” & “many” are imprecise terms, but that’s the point.

The few constants we can state about these data files are:

  • We will have two columns of data
  • The column labels will be on the first row of the data set
  • The first column will contain text while the second column will contain numbers

The data in the file will change; there could be Salary information in one file while Profit information in another file.

Our objective is to create a macro that will perform the formatting steps on every sheet in the file.

Excel basics course Leila Gharani

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

Recording the Steps

Begin by selecting the Automate tab and click Record Actions.

You can see in the Record Actions panel (right) that the recording has started.

Clear Existing Formatting

The first thing our macro should do is to remove any existing formatting from the data.

Select columns “A” & “B” and click Home (tab) -> Editing (group) -> Clear -> Clear Formatting.

Format the “B” Column for Large Numbers

The next step is to apply a formatting style to the “B” column that places commas every 3 place values and shows whole numbers only.

Select column “B” and select Home (tab) -> Number (group) -> General (dropdown) -> More Number Formats… -> Number (category) and apply the below settings.

Format the Headings

Next, select the headings on row 1 and format the background color (i.e., cell color) to light gray, bold, and add a thin line to the bottom border.

Resize the Columns to Show All Data

Making sure to not obscure any of the data if the columns are too narrow, select columns “A” & “B” and perform an AutoFit to resize the columns based on the data.

Viewing the Recorded Steps

We can see in the Record Actions panel the list of steps performed during the recording session.

Stop the Recording

Stop the recording of the steps by clicking the Stop button in the Record Actions panel.

Renaming the Recording

Before we test the macro, let’s give the macro a more understandable name, “Formatting”.

Viewing the Recording’s Code

Click the Edit button in the Code Editor panel to reveal the underlying code for the macro.

You don’t need to understand any of this code, but you should be able to use a bit of common sense coupled with experience to deduce the general ideas contained in the code.

The code generator is thoughtful enough to include comments throughout the code.  The comments are displayed using a green font color.

Executing the Code for Every Sheet in the Workbook

All the steps are being applied to the active worksheet.

We don’t want to limit the code execution to only the active sheet.  We want the code to be executed on every sheet in the workbook.

To figure out how to work with the sheets as a collection, we need to visit the Microsoft documentation website for Office Scripts.

Office Scripts Documentation

Scroll down the webpage to get to the Collections section.

We need to learn a bit about working with Collections since we are needing to manipulate a collection of worksheets.

We can see from the beginning of the “Iterate over collections” section a sample line of code to get all worksheets in the workbook.

Select the code below and copy it to memory (CTRL-C).

let sheets = workbook.getWorksheets();

Return to the recorded script code and replace the existing LET statement with the copied code from above.

This modification will permit us to work with every sheet in the workbook, but we still need a line of code to loop through the sheets performing the formatting instructions.

This code is also in that same sample code talking about Collections.

for (let sheet of sheets) {

Sheets” is the collection of worksheets as defined by the let sheets = workbook.getWorksheets() statement.  “Sheet” is each sheet in the worksheet when examining them one at a time.

Copy the above code (for (let sheet of sheets) {) into memory and return to the recorded code.

Add a blank line after the LET statement and paste the copied code onto the new line.

Because we have used an open-brace character (i.e., curly brace) at the end of the FOR line of code, we need to add a closed-brace character to the end of the code for completeness.

Updating the Existing Code Pointers

Our current code is pointing to “selectedSheet” for each recorded action.

We need to replace each instance of a “selectedSheet” reference with a “sheet” reference.

Press CTRL-H to open the Find/Replace option.

Locate all instances of “selectedSheet” and replace them with “sheet”.

Click the Replace All button to update the code.

The updated code appears as follows.

Click the Save Script button to commit the changes to the code.

Testing the Recorded Code

Although the first sheet has the desired formatting updates, we’ll return to the first sheet and run the code.

We see that nothing appears to have changed on the first sheet, but a look at the second sheet reveals a newly revised dataset that mimics the formatting of the first sheet’s dataset.

All excel courses Leila Gharani

Master it ALL!

Whether you're a beginner or advanced Excel user, there is a course for you in our academy

GET ACCESS

OBJECTIVE #2 – Looping Through Files in a Folder

Our second objective is to use Power Automate to loop through all sheets in a series of files in a folder.

In addition, this automation solution needs to be scheduled to run at a specified time.

We’ve set up a folder named “Reporting” on our OneDrive where we have stored the file “To Format.xlsx” that holds the previously recorded formatting code.

Alongside this file in the same folder is a file named “Salaries.xlsx” that has several sheets with data like the following.

We want to create a Power Automate script that will open the desired files and loop through each sheet applying the desired formatting.

Be Careful!  We need to ensure the Power Automate script does not execute on non-Excel files located in the same folder.

Opening Power Automate

Return to the office.com main page and open Power Automate.

In Power Automate, click the Create option (left panel) to begin the creation process.

We will select the option to create a “Scheduled Cloud Flow”.

We name the flow “Monthly Formatting” and set the scheduling option for execution each month.  Click Create when finished.

Adding the Steps for Monthly Execution

To start the list of scripting instructions, click “+ New Step”.

Let’s search for all operations that deal with OneDrive by typing “onedrive” in the operation search box.

We’ll select the operation labeled “OneDrive for Business”.

Selecting an Action

In the list of “OneDrive for Business” operations, we’ll select the action labeled “List files in folder”.

Browse to select the “Reporting” folder as the desired scan location.

Click “New Step” to continue the scripting process.

Running the Script on the Selected Folder

The next step is to select the formatting script we created earlier to run on the previously selected folder.

We’ll select the connector labeled “Excel Online (Business)”.

In the list of Actions, select the action “Run Script”.

We define the following arguments for the Run Script panel:

  • Location – “OneDrive for Business”
  • Document Library – “OneDrive”
  • Filesee the following note
  • Script – “Formatting”

NOTE: For the File argument, select the Dynamic Content category and click the “Id” option.

This will create a new control named Apply to Each that will nest the Run Script step within it.

The script is smart enough to identify that we have the potential for multiple files coming from the previous “List files in folder” step that needs to be processed by the script.

The Run Script panel will appear as follows.

Excel Dynamic Arrays Course Leila Gharani

Master Excel Functions in Office 365 & Office 2021 - Complete Course

Excel has Changed Forever! DON'T MISS OUT!

GET ACCESS

Picking Out the Excel Files

If we stop here with our script, the script will run and execute the formatting steps on each file in the selected folder.

The problem is that the script does not discriminate between Excel files and any other type of files.

We only want to process Excel files (i.e., files with an .xlsx file extension.)

Select Add Action at the bottom of the script and select Control from the Choose an Operation panel.

In the Control panel, select the Action labeled Condition.

Our Condition will be to check to see if the file’s name ends with .XLSX.

The Condition should be checked before the Run Script step is executed.

As it stands, the script is running before the file check condition is executed.

We solve this issue by dragging and dropping the Run Script step into the If Yes box.

Click Save at the bottom to save the script.

Testing the Flow

We can test the newly created Flow by clicking the Test button in the upper-right corner of the window.

We will test the flow Manually and click Test at the bottom of the screen.

Select Run Flow.

If all goes well we will be presented with a success message.

We see that each step ran successfully along with the amount of time (in seconds) to process each step.

Checking the Results

If we navigate to the OneDrive “Reporting” folder and open the “Salaries.xlsx” file, we see that the table formatting has been applied to each sheet in the file.

If a new Excel file is added to the “Reporting” folder, the formatting will be applied to all sheets in the file when the schedule is met.

Currently, we are updating monthly.  We can make this update schedule more aggressive to perform updates more frequently.

We can also set this script to run manually allowing us to execute the script on demand from our mobile app.

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials