How to run an Office Script on all Excel workbooks in a folder with Power Automate

In a previous post, we explored how to use Office Script to automate formatting and enhance features in an Excel workbook:

But how do you extend this automation to multiple Excel workbooks? That’s what this post explores, with the help of Power Automate. To get started, copy the script below and save it in your Office Scripts folder under the name “Convert Worksheets to Table.”

Let’s explore how to use this script to transform the data in each worksheet of a workbook into a table format and apply this conversion to multiple workbooks simultaneously. If you need some files to practice with, feel free to download the test files from the following link:

Creating the flow in Power Automate

Office Scripts in Excel are primarily tailored for executing one script per instance. However, we can leverage Power Automate to extend this functionality across multiple files in a workbook. Begin by navigating to Power Automate. Once there, click on ?Create” followed by “Instant Cloud Flow.”

To initiate this flow, it’s essential to decide on the trigger mechanism. For this setup, I’ll opt for a manual trigger. Simply choose “Manually trigger a flow” and then click on “Create:”

Next, expand your flow by clicking the ‘+’ button below your trigger. Assuming your files are stored in a OneDrive folder, look for the ‘List files in folder’ action under OneDrive for Business:

Next, you will need to specify the location of this folder:

To enhance this flow with error handling, we’ll ensure that the Office Script only operates on Excel workbooks within the folder. Next, add a Condition by selecting a new step and searching for “Condition,” found under the Control category:

To configure this condition, we need to adjust the condition expression to specifically target the Name of each file in the folder. You can dynamically retrieve the file names within this flow by clicking on the lightning bolt icon next to “Choose a value” in the expression setup.

To proceed, choose “ends with” as the condition criteria and enter ‘.xlsx’ as the search term. This will determine whether the condition is evaluated as true or false based on this criteria.

Next, we’ll configure our script to run only on files identified as .xlsx workbooks, where the condition evaluates to True. In the True branch, add a new action:

Select the “Run script” action from Excel Online:

Set up the following parameters to ensure your specified Office Script runs on each workbook in this folder:

• Location: OneDrive for Business
• Document Library: OneDrive
• File: Use the ‘Id’ parameter (this ensures the script runs based on each file’s unique ID)
• Script: Select the “Convert worksheets to table” script you previously created

Once you have these settings in place, go ahead and save your flow. It should appear as follows:

Now that you have saved your script, it’s time to test it out! Make sure to save your script before testing or running it. To do this, use the ‘Test’ button on the flow editor page. You’ll see a checkmark next to each step, along with the time elapsed, indicating that the flow has successfully run and how long it took.

Feel free to open your source files to verify that they have all been successfully converted to tables. If you wish to further enhance your document, such as by autofitting the column widths or applying specific formatting, you are certainly welcome to continue refining the script.

You are welcome to continue using this script as needed, or you could set it up on a schedule. For instance, if you receive these files weekly from a colleague or an external system, you can automate the process to convert all files into tables regularly. This will facilitate your downstream reporting, management, and analysis tasks.

Conclusion

In this post, we explored how to enhance Office Scripts by leveraging Power Automate to apply transformations across multiple Excel workbooks. I encourage you to experiment with this method using the provided exercise and test files. Feel free to adapt the scripts and customize the workflow to suit your specific needs.

Do you have any questions about integrating Power Automate with Office Scripts? Please feel free to ask in the comments below.