Write Your First Office Script in Excel

Previously, I wrote an article about writing a VBA macro in Excel:

Write Your First VBA Macro in Excel | by Kyle Gibson | Jan, 2023 | Medium

However, VBA has a big limitation: it can only be written and executed in the Excel desktop app. It can’t be used in Excel Online, which leads to some obvious limitations for its usage.

Microsoft has now introduced Office Scripts that can be written and executed in both the Excel desktop app and Excel Online. The documentation for Office Scripts: Office Scripts documentation — Office Scripts | Microsoft Learn

Now I know what you’re asking yourself: “Do I have to learn a new language to use Office Scripts?”. If you don’t know JavaScript, then I’m sure you are delighted to know that the answer is YES. Per their documentation: “Office Scripts are written in TypeScript, which is a subset of JavaScript.”

Before you panic and give up on learning how to write these scripts, I encourage you to keep reading. Until recently, I had never written a single line of TypeScript or JavaScript code, and I find Office Scripts to be pretty intuitive and user-friendly.

Let’s look at an example for how to write your first Office Script.

Step 1: Make sure you have the Automate tab available

Note: Per Microsoft, at the time of this writing, you will need a commercial or educational version of Microsoft 365 to have the option to use Office Scripts.

The Automate tab can be found in the top ribbon of Excel:

If you don’t see it, navigate to File > Options > Customize Ribbon, and make sure the Automate box is checked:

Step 2: Create the sample data to add two columns of numbers

For this example, our Office Script will add the numbers from two columns. Enter some sample data like this:

Also, so that our example below makes sense, rename the tab/sheet for this sample data to Example:

We could write an Excel formula to add these two columns together, but let’s do it as a script instead.

Step 3: Create new script

Navigate to the Automate tab, and select New Script:

This action will open up the code editor, with an empty script named something like Script 1, Script 2, etc.:

Click inside of the script name to change it to something more specific. Then click Save script:

Note: These scripts will typically save to the Documents folder in your OneDrive.

All Office Scripts need this main function, with ExcelScript.Workbook as the first parameter. This is mentioned in the official Office Scripts documentation, linked at the beginning of this article.

We will add some additional code to the main function in our next step.

Step 4: Add code to the main function

There is a faster way to do this, but for purposes of this example, let’s look at how to add these two columns by iterating through each row. I have put comments on each step of the code to explain what each step is doing.

Add in this code to your script, so that your main function now looks this:

function main(workbook: ExcelScript.Workbook) {
    // Step 1: Get your sheet object
    let sheet =
        workbook
            .getWorksheet('Example')
    
    // Step 2: Get the amount of used rows in the first column,
    // including the header. This tells us how many rows need to be summed
    let row_count =
        sheet
            .getRange("A1")
            .getEntireColumn()
            .getUsedRange()
            .getRowCount()
    
    // Step 3: Loop through each row
    for (let i = 1; i < row_count; i++) {
        // Step 3a: Get the cell address of first column on the current row
        let first_number_address =
                sheet
                    .getCell(i, 0)
                    .getAddress()
        
        // Step 3b: Get the cell address of second column on the current row
        let second_number_address =
            sheet
                .getCell(i, 1)
                .getAddress()
        
        // Step 3c: Apply SUM formula to third column on the current row, 
        // summing the values from the first and second columns
        sheet
            .getCell(i, 2)
            .setFormula(`=SUM(${first_number_address}, ${second_number_address})`)
    }
}

As you can see, if you have a knowledge of Excel already, some of the phrasing in the syntax looks pretty familiar. Cell, Address, Formula, Range, Worksheet, and Column are all words that Excel users may recognize.

This script is pretty basic, but also useful in learning how Office Scripts work. Now make sure to save your script:

Step 5: Run your script

After saving your script, now click Run:

You should now see that your sample data has been summed in the Total column:

That’s it! You have now successfully written and implemented your first Office Script.

Since the sample data was only three rows, there were no performance issues with this script. However, if summing thousands of rows, and looping through each row one at a time, this script may take too long for our requirements.

If you want to see a better way to write the script to avoid those peformance issues, see my next article on this topic:

Using AutoFill to Speed Up Excel Office Scripts | by Kyle Gibson | Feb, 2023 | Medium

Thanks for reading!