Mastering Excel Office Scripts from Beginner to Expert: Creating a Well-Designed Excel Report

-

Excel Office Scripts allow you to automate and format Excel workbooks through code, similar to macros, but designed for the cloud. In this step-by-step guide, we’ll start from the basics of creating and recording an Office Script, then integrate scripts with Power Automate to generate a well-designed Excel document stored in SharePoint. By the end, you’ll know how to go from a simple recorded script to an advanced workflow that creates dynamic Excel reports with ease.

What are Office Scripts and Why Use Them?

Office Scripts in Excel are a powerful automation feature that let you record or write scripts (in TypeScript/JavaScript) to perform repetitive tasks. They operate much like traditional Excel macros, but work in the cloud (Excel for Web and modern Excel desktop):contentReference[oaicite:0]{index=0}. This means your Excel files can live in SharePoint or OneDrive and still run automated scripts, enabling new scenarios that VBA macros (which only run in desktop Excel) cannot:contentReference[oaicite:1]{index=1}. Office Scripts are especially useful for integrating Excel with other services via Power Automate flows, bridging gaps that built-in Power Automate Excel actions can’t cover:contentReference[oaicite:2]{index=2}:contentReference[oaicite:3]{index=3}.

Prerequisites: To use Office Scripts, you need a Microsoft 365 commercial or educational subscription (they are not available for consumer plans):contentReference[oaicite:4]{index=4}. Ensure you have the Automate tab visible in Excel (it’s enabled by default for eligible users; if not, check under File > Options > Customize Ribbon to enable the Automate tab). Also, for running scripts through Power Automate, a Microsoft 365 work or school account with the appropriate license (such as Office 365 E3/E5 or equivalent) is required:contentReference[oaicite:5]{index=5}.

Beginner: Recording and Writing Your First Office Script

Getting started with Office Scripts is easy – you can use the Action Recorder to capture your steps without writing code, or write a simple script yourself. Let’s create a basic script and get familiar with the environment:

:contentReference[oaicite:6]{index=6} *Excel’s Automate tab in the ribbon, highlighting the Scripting Tools group with Record Actions and New Script (Office Scripts):contentReference[oaicite:7]{index=7}.*

  1. Open the Automate tab: In an Excel workbook (Excel for Web or latest Excel desktop for Microsoft 365), go to the Automate tab on the ribbon. In the Scripting Tools group, you’ll see options for Record Actions and New Script:contentReference[oaicite:8]{index=8}. If you click Record Actions, Excel will begin recording your actions (entering data, formatting cells, creating tables, etc.) and generate a script from those steps:contentReference[oaicite:9]{index=9}.
  2. Use the Action Recorder (optional): For a first script, try recording a simple task. For example, enter some sample data, convert it into a table, add a Total row, or apply formatting. The Action Recorder will list each step in a pane as you perform them:contentReference[oaicite:10]{index=10}. Click the Stop button when done, and the recorded TypeScript code will appear in the Code Editor pane:contentReference[oaicite:11]{index=11}. You don’t need coding experience to do this – it’s a great way to generate a script that you can later edit if needed:contentReference[oaicite:12]{index=12}.
  3. Create a New Script (Code Editor): Alternatively, click New Script to open the Code Editor with a blank script. A default template appears with a function main(workbook: ExcelScript.Workbook). This is where you write your code. For instance, to sum values in two columns into a third column, you might write a loop or use Excel formulas via script. Here’s a very simple example of an Office Script that writes a value to cell A1:
function main(workbook: ExcelScript.Workbook) { let sheet = workbook.getActiveWorksheet(); sheet.getRange("A1").setValue("Hello, Office Scripts!"); }

This script gets the active worksheet and sets cell A1’s value. You can run the script by clicking the Run button in the Code Editor. After running, you should see the text appear in the worksheet. (If you recorded a script instead, clicking Run will replay all the recorded steps on the workbook.)

Save and Name the Script: Click the Save button in the Code Editor. By default, scripts are named “Script 1”, “Script 2”, etc., but you can rename it to something meaningful (click the script name in the gallery or the ... menu and choose Rename):contentReference[oaicite:13]{index=13}. The script is saved as an .osts file in your OneDrive under Documents/Office Scripts/ by default:contentReference[oaicite:14]{index=14}. (You can verify this by checking your OneDrive; scripts are stored here automatically.)

At this point, you’ve created a basic Office Script. You can run it anytime from the Automate tab’s script gallery. Now, let’s move on to integrating this with Power Automate to run the script in a flow.

Intermediate: Running Office Scripts with Power Automate

Power Automate allows you to trigger your Office Scripts as part of automated workflows. For example, you could run a script on a schedule or in response to an event (like a new email or form submission), then take further actions (such as emailing results). The Excel Online (Business) connector in Power Automate provides two actions to run scripts:

  • Run script – for scripts stored in your own OneDrive (default location):contentReference[oaicite:15]{index=15}.
  • Run script from SharePoint library – for scripts stored in a SharePoint site (for team or shared scenarios):contentReference[oaicite:16]{index=16}.

We’ll first use the basic Run script action (OneDrive). Suppose you have an Excel file on OneDrive and a script (like our “Hello, Office Scripts” or any other script) saved in your Office Scripts folder. Here’s how to set up a simple flow:

  1. Create a Power Automate Flow: Go to Power Automate (flow.microsoft.com) and create a new flow (e.g., an Instant cloud flow for testing, triggered manually or on a schedule). Add a new step and select the **Excel Online (Business)** connector.
  2. Add "Run script" Action: Choose the Run script action. You’ll be prompted to select:
    • Location: e.g., OneDrive for Business.
    • Document Library: usually “OneDrive”.
    • File: browse to the Excel workbook on OneDrive that you want the script to run on (e.g., a template report workbook).
    • Script: choose the script by name from the list (Power Automate will list the scripts available in that workbook or in your OneDrive Office Scripts folder):contentReference[oaicite:17]{index=17}:contentReference[oaicite:18]{index=18}.
  3. (Optional) Provide Script Parameters: If your script’s main function has parameters (besides the workbook), the action will show additional input fields for each parameter so you can pass data from the flow to the script:contentReference[oaicite:19]{index=19}. For example, if we modified our earlier script to main(workbook, text: string) and use setValue(text), a field for “text” would appear in the Power Automate action where we could enter a value or dynamic content.
  4. Run the Flow: Save and test the flow. The flow will open the Excel file and execute the Office Script. For instance, if the script adds “Hello” to cell A1, after the flow runs, you should see that value in the Excel file:contentReference[oaicite:20]{index=20}. You can then continue the flow (e.g., send an email with the workbook attached or move the file, etc.) based on your needs.

:contentReference[oaicite:21]{index=21} *Configuring the Power Automate Run script action to run an Office Script on a OneDrive-hosted Excel file:contentReference[oaicite:22]{index=22}.*

This basic integration shows how Power Automate can run your script on an existing Excel file. Now, let’s take it to the next level: storing scripts in SharePoint for team use, and creating new Excel files and populating them via scripts – an advanced scenario ideal for generating reports.

Expert: Advanced Automation – Generating Excel Reports via SharePoint and Office Scripts

In an advanced workflow, you might want to query data from a system, create a new Excel report file, insert and format the data, and distribute the file – all automatically. Office Scripts can be stored in a SharePoint library (so they can be shared across your team) and run on newly created workbooks. Let’s break down how to achieve a full automation, similar to the Power Automate workflow provided in the question:

1. Store Office Scripts in SharePoint for Team Use: By moving your .osts script file to a SharePoint site (or saving it there to begin with), the script becomes accessible to others with site access:contentReference[oaicite:23]{index=23}. You can move a script from your OneDrive to SharePoint by editing the script and using the Move command, or using Save as to copy it to a SharePoint document library:contentReference[oaicite:24]{index=24}. Once in SharePoint, the script will appear in the All Scripts list under Automate for users who have access, and can be run via the Run script from SharePoint library action in Power Automate:contentReference[oaicite:25]{index=25}:contentReference[oaicite:26]{index=26}. (Note: At the time of writing, running scripts from SharePoint in Power Automate is a relatively new feature and may be labeled as “Preview”.)

2. Designing an Office Script for Reporting: Instead of a simple script that writes one cell, your script can be more complex – for example, take a dataset and format it into a report. You might design a script that accepts parameters for things like:

  • A data table (e.g., an array of arrays representing rows and columns)
  • A header or title for the report
  • Metadata like report date range or filters used
  • Options like whether to format the data as an Excel Table, include a header row, etc.
This script (written in TypeScript) would create a new sheet or use a template sheet, insert the title, maybe some metadata info (like “Start Date: X, End Date: Y” in cells), then write the data table into the sheet. It could then format that range as a Table, apply styles or formulas (e.g., autosum, formatting of columns), and do any other polishing (freeze panes, adjust column widths, etc.). By encapsulating all these steps in one Office Script, you centralize the report formatting logic.

Example: The advanced script provided (referenced in the JSON code) likely had a function signature like:

function main(workbook: ExcelScript.Workbook, includesHeader: boolean, formatAsTable: boolean, sheetName: string, headerTitle: string, Metadata: string[][], worksheetData: (string | number)[][]) { // 1. Create or clear target worksheet // 2. Write the header title at top (e.g., merge cells A1:D1, set value to headerTitle) // 3. Write metadata (each entry of Metadata array) in subsequent rows (e.g., "Start Date: ", "End Date: ", etc.) // 4. If includesHeader is true, treat first row of worksheetData as column headers. // 5. Write the worksheetData into the sheet (e.g., starting from A5). // 6. If formatAsTable is true, format the inserted range as an Excel Table for easy filtering. // 7. Apply any additional formatting (column widths, date formats, bold headers, etc.). }

This approach separates the data (passed in via parameters) from the presentation (handled by the script). It makes the flow of data cleaner: Power Automate gathers data and calls the script, and the script handles how the Excel file looks.

3. Building the Power Automate Flow: Now, to create a flow that uses this script for report generation, here are the steps you would implement (similar to the advanced example given):

  1. Trigger & Data Retrieval: Define how the flow is triggered – it could be manual, on a schedule (e.g., monthly report), or based on some event. Then add actions to gather the data for the report. In the provided example, a Power BI dataset query was used to retrieve data (using a DAX query):contentReference[oaicite:27]{index=27}, but you could use any data source (SQL query, SharePoint list, etc.). The result is a set of records that will go into Excel.
  2. Compose Data for Excel: Use data operations in Power Automate to prepare the data for Excel. In the example, they created an array for the header row (column names) and an array of arrays for the data rows (using the Select action to shape the query output) – then combined them using union():contentReference[oaicite:28]{index=28}:contentReference[oaicite:29]{index=29}. Essentially, you want a two-dimensional array representing the table (including header as first row). They also created an array of “metadata” (like Start Date, End Date, etc.) to pass to the script:contentReference[oaicite:30]{index=30}.
  3. Create a New Excel File: If the goal is to have a new Excel file report every time, you can use a SharePoint or OneDrive connector action to create a file. For example, the flow might generate a unique filename (like Report_20250821.xlsx) using the current timestamp:contentReference[oaicite:31]{index=31}, then call a SharePoint Create File (or in the code, they used a SharePoint HTTP request to add the file in a document library) to create an empty Excel file:contentReference[oaicite:32]{index=32}. This file will serve as the output report.
  4. Run Script on the New File: Next, use the Run script from SharePoint library action (Excel Online (Business) connector):contentReference[oaicite:33]{index=33}. In this action, you specify:
    • Workbook Location: URL of the SharePoint site (e.g., https://<yourtenant>.sharepoint.com/sites/<SiteName>).
    • Workbook Library: Document library name (e.g., Documents).
    • Workbook: The path to the Excel file (e.g., /Reports/Report_20250821.xlsx).
    • Script Location: (if different site, specify the site URL where script is stored, otherwise same as workbook site).
    • Script Library: The library containing the .osts script file (e.g., Documents or a specific scripts library).
    • Script: The path to the script file (e.g., /Reports/MyReportScript.osts or simply select the script name if it’s in the same library):contentReference[oaicite:34]{index=34}.
    • Once you select the script, Power Automate will show fields for the script parameters. Here is where you pass in the data prepared in previous steps:
      • includesHeader: a boolean (e.g., true, since our first row of data is headers).
      • formatAsTable: boolean for formatting as Table (true/false as desired).
      • sheetName: a string for the worksheet name (e.g., “Sheet1” or “Report”).
      • headerTitle: the report title (e.g., “Sales Report – All Data”).
      • Metadata: the array of metadata (e.g., an array of [Key, Value] pairs like [["Start Date", "2025-08-01"], ["End Date", "2025-08-21"], ["Status", "All"]] which you composed earlier).
      • worksheetData: the 2D array of the actual table data (including header row) that you prepared.
  5. Send or Use the Report: After the script runs, your Excel file is populated and formatted. You can add steps to utilize this file – for example, attach it in an email to the requester or stakeholders:contentReference[oaicite:35]{index=35}, or simply notify someone that the file is ready. In the provided workflow, if an email was requested, they constructed an email with a nicely formatted HTML body (including some of the input details) and a link to download the report:contentReference[oaicite:36]{index=36}. You could also upload it to a specific SharePoint folder for record-keeping.

:contentReference[oaicite:37]{index=37} *Power Automate’s Run script from SharePoint library action, configured with workbook and script locations on SharePoint (Note: script file Colour current cell Yellow.osts selected as an example):contentReference[oaicite:38]{index=38}.*

Once this flow is set up, generating a report is as easy as triggering the flow – it will pull fresh data, create a workbook, run the Office Script to format everything, and deliver the output. This is a **fully automated solution** for producing consistent Excel reports.

Tips for Success and Best Practices

  • Plan Your Script’s Inputs and Outputs: When writing an advanced Office Script, think about what data it needs (inputs) and what it produces. For instance, your script might not need to return anything to Power Automate if the goal is just to produce a file. But if needed, you can have your script return values back to the flow (the Excel connector will capture the return value). Keep the parameter list logical and only as complex as necessary:contentReference[oaicite:39]{index=39}:contentReference[oaicite:40]{index=40}.
  • Error Handling: Consider what happens if the data is empty or too large, or if the script runs into an unexpected condition. While Office Scripts might not have advanced error handling like try/catch (currently), you can use conditional logic and checks in your TypeScript code to handle known potential issues (e.g., “if no rows returned, set a cell to ‘No data found’”). In Power Automate, you can also add parallel branches or additional checks (like verifying the file was created, etc.).
  • Testing: Test your Office Script independently in Excel first with sample data. Then test the Power Automate flow step by step. Use small datasets to ensure the flow runs quickly and the script does what you expect. Once it works, you can try larger data and see if any performance tweaks are needed (for example, writing values to a range in one go is faster than cell-by-cell). The example advanced script makes use of formatting the entire dataset as a table at once, which is efficient.
  • Office Scripts vs Traditional VBA: If you’re coming from a VBA background, remember Office Scripts run in a cloud context. They cannot do certain things like access local resources or make web requests (with some exceptions):contentReference[oaicite:41]{index=41}. They also require internet connectivity since they run on the server side. But the advantage is they can be triggered by cloud events and run on files stored online, enabling automation end-to-end without manual intervention.

Conclusion

By progressing from recording a simple script to deploying an advanced Power Automate flow, you’ve seen how Office Scripts can elevate your Excel automation to a new level. We started with basic action recordings (no coding needed) and moved to writing custom TypeScript code for more flexibility. We then integrated these scripts into Power Automate, first in a straightforward way and finally in a sophisticated scenario where data is dynamically collected and a polished Excel report is generated and delivered.

Office Scripts, combined with Power Automate and SharePoint/OneDrive, provide a robust platform for building automation around Excel. This means no more repetitive manual report updates – your “expert-level” solution can create a well-designed Excel document at the click of a button or on a schedule. With practice, you can extend this approach to many other use cases (data audits, dashboards, etc.), empowering your team to focus on insights rather than tedious Excel tasks.

Happy scripting and automating!

References:

Comments

Post a Comment

Popular posts from this blog

Why there is a shortage of SharePoint experts

-
Image
Microsoft has been very successful to promoting their SharePoint product, even though SharePoint has been around for a while, there 2007 and now 2010 version is something every organisation wants. With Microsoft practically giving away SharePoint licenses to organisations via EA agreements – we are reaching a very unique and rather unexpected situation where the demand for SharePoint developers is surpassing the demand for Web developers. This shift in demand are forcing IT departments and Web Development companies to reduce the size of the web development teams and increase the size of their SharePoint team, in other words, take staff originally hired for web development and get them into SharePoint development. This is currently the "normal" approach in getting SharePoint experts, many IT Recruitment companies still don’t understand this shift in demand, and are not yet focusing in the SharePoint space, some recruiting companies haven’t even heard of SharePoint and...
Read more

Using SharePoint’s validateUpdateListItem in Power Automate: A Beginner’s Guide

-
Image
When working with SharePoint Online and Power Automate (Flow), you might reach a point where the standard “Update item” action isn’t enough. Perhaps you need to update system-managed fields (like Modified or Created By ), or you want to update an item without triggering other flows or incrementing version history. Enter the SharePoint REST API’s validateUpdateListItem endpoint – a powerful method to update list items with fine control. In this beginner-friendly guide, we’ll walk through how to use validateUpdateListItem via the Send an HTTP request to SharePoint action in Power Automate. We’ll cover how to set up the HTTP request, the JSON body format (with examples for various column types), and explain the benefits of this approach. What is validateUpdateListItem and Why Use It? The validateUpdateListItem is a SharePoint REST API method that validates and updates list item fields in one call. It’s often used for “system updates,” meaning changes that don’t trigger flows/al...
Read more

What Are SharePoint Architects?

-
Image
When we look at the minimum resources needed for a SharePoint environment, we think SharePoint administrator and maybe the “can-do-it-all” SharePoint Developer.    Don’t get me wrong, these resources are great and are vital for the stability and growth of the SharePoint environment, but they are essentially the workers, or the term I like using, the brick layers (don’t take it as an offensive term, that’s not my intention), they have a strong focus on the technical component of the SharePoint environment, and although they can achieve greatness on their own, they can achieve so much more if they had guidance from someone with a broad understanding of SharePoint (not necessary focused on the technical side) with the ability to map a business problem to the SharePoint technology, that can engage with the business user and plan an approach that will achieve the greatness that SharePoint can provide.  They are the SharePoint Architect. The reason why I am focusing on this r...
Read more