0% found this document useful (0 votes)

399 views317 pages

Office Dev Scripts

The document provides comprehensive guidance on using Office Scripts in Excel to automate tasks, including creating, editing, and sharing scripts. It covers tools like the Action Recorder and Code Editor, and explains how to integrate scripts with Power Automate for enhanced workflow automation. Tutorials and sample scripts are included to help users learn and implement these functionalities effectively.

Uploaded by

k7govindarajan

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

399 views317 pages

Office Dev Scripts

Uploaded by

k7govindarajan

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

You are on page 1/ 317

Tell us about your PDF experience.

Office Scripts documentation

Use Office Scripts in Excel to automate your common tasks. Explore the following
articles to learn how to create and edit Office Scripts and get started automating today.

About Office Scripts

ｅ OVERVIEW

Code Editor and Action Recorder

Integrate with Power Automate

Script fundamentals

ｉ REFERENCE

Office Scripts API reference

Get started

ｇ TUTORIAL

Create and format a table with Office Scripts

Update a spreadsheet from a Power Automate flow

ｓ SAMPLE

Convert CSV files to Excel workbooks

Set conditional formatting for cross-column comparisons

Email images of a chart and table

More samples

Share scripts

ｃ HOW-TO GUIDE

Sharing Office Scripts in Excel

Run Scripts from buttons

Manage Office Scripts admin settings

Office Scripts in Excel
Article • 08/08/2024

Office Scripts in Excel let you automate your day-to-day tasks. Use the Action Recorder
to turn manual steps into reusable scripts. Edit those scripts or create new ones with the
Code Editor. Let others in the workbook run these scripts with a single button. Then,
share them with coworkers so everyone can improve their workflow.

This series of documents teaches you how to use these tools. You'll find a wealth of
samples covering different Excel scenarios. Use the tutorials to introduce yourself to the
Action Recorder and Code Editor. These provide step-by-step guidance on how to
record your frequent Excel actions, edit those scripts, and create new scripts from
scratch.

https://www.microsoft.com/en-us/videoplayer/embed/RE4qdFF?postJsllMsg=true

When to use Office Scripts

Scripts allow you to record and replay your Excel actions on different workbooks and
worksheets. If you find yourself doing the same things over and over again, you can turn
all that work into an easy-to-run Office Script. Run your script with a button in Excel or
combine it with Power Automate to streamline your entire workflow.

As an example, imagine at the start of each work day you open a .csv file from an
accounting site in Excel. You then spend several minutes deleting unnecessary columns,
formatting a table, adding formulas, and creating a PivotTable in a new worksheet.
Those actions you repeat daily can be recorded once with the Action Recorder. From
then on, running the script will take care of your entire .csv conversion. You'll not only
remove the risk of forgetting steps, but be able to share your process with others
without having to teach them anything. Office Scripts allows you to automate your
common tasks so you and your workplace can be more efficient and productive.

Action Recorder
The Action Recorder records actions you take in Excel and saves them as a script. With
the Action recorder running, you can capture the Excel actions as you edit cells, change
formatting, and create tables. The resulting script can be run on other worksheets and
workbooks to recreate your original actions.

More information about the Action Recorder can be found in the article Record your
actions as Office Script .

Code Editor
Use the Code Editor to edit scripts recorded with the Action Recorder or make a brand
new script. This tool lets you tweak and customize scripts to better suit your exact
needs. You can also add logic and functionality that is not directly accessible through
the Excel UI, such as conditional statements (if/else) and loops.

 Tip

The Action Recorder has a Copy as code button to record actions into script code
without saving the entire script.
Our tutorials provide a guided and structured way learn the capabilities of Office Scripts.
After completing the tutorials, read Fundamentals for Office Scripts in Excel to learn
more about the Code Editor and how to write and edit your own scripts. For additional
information about the Code Editor and how your script code is interpreted, read Office
Scripts Code Editor environment.

Share Office Scripts

Office Scripts can be shared with other users in your organization. When you share a
script in a shared workbook, team members with access to the workbook can also view
and run your script. For more details about sharing and unsharing scripts, see Sharing
Office Scripts in Excel .

Add buttons that run scripts to help your colleagues discover your valuable solutions
and let them run scripts straight from the workbook. Learn more about script buttons in
Run Office Scripts with buttons.
７ Note

Learn more about how scripts are stored in your OneDrive in Office Scripts file
storage and ownership.

Schedule scripts to run automatically

） Important

Script scheduling is temporarily disabled within Office Scripts. Existing scheduled

scripts will continue to run. In the interim, use Power Automate to create a flow and
schedule your scripts to run in that flow. To learn more, see Run scripts with Power
Automate.
Set your scripts to run every day and keep your workbook up-to-date. Once you have
your script, you can set it to automatically run on the workbook at regular intervals. A
behind-the-scenes Power Automate flow ensures everything happens, even when the
workbook is closed.

To schedule a script, open the script in the Code Editor. Open the Script scheduling
section and complete the sign in process to Excel through Power Automate. Set how
often you want the script to run and select Create flow to begin.

Connect Office Scripts to Power Automate

Power Automate is a service that helps you create automated workflows between
multiple apps and services. Office Scripts can be used in these workflows, giving you
control of your scripts outside of the workbook. You can run your scripts on a schedule,
trigger them in response to emails, and much more. Visit the Run Office Scripts with
Power Automate tutorial to learn the basics of connecting these automation services.

Next steps
Complete the Office Scripts in Excel tutorial to learn how to create your first script.

See also
Fundamentals for Office Scripts in Excel
Office Scripts API reference
Platform limits and requirements with Office Scripts
Office Scripts settings in M365
Sharing Office Scripts in Excel

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Tutorial: Create and format an Excel
table
Article • 09/20/2024

This tutorial teaches you the basics of recording, editing, and writing an Office Script for
Excel. You'll record a script that applies some formatting to a sales record worksheet.
You'll then edit the recorded script to apply more formatting, create a table, and sort
that table. This record-then-edit pattern is an important tool to see what your Excel
actions look like as code.

Prerequisites
You'll need access to Office Scripts for this tutorial. Please review the Platform support if
the Automate tab doesn't appear.

） Important

This tutorial is intended for people with beginner to intermediate-level knowledge

of JavaScript or TypeScript. If you're new to JavaScript, we recommend starting with
the Mozilla JavaScript tutorial . Visit Office Scripts Code Editor environment to
learn more about the script environment.

Add data and record a basic script

First, you'll need some data and a small starting script.

1. Create a new Excel workbook.

2. Copy the following fruit sales data and paste it into the worksheet, starting at cell
A1.

ﾉ Expand table

Fruit 2018 2019

Oranges 1000 1200

Lemons 800 900

Limes 600 500

Fruit 2018 2019

Grapefruits 900 700

3. Open the Automate tab. If you don't see the Automate tab, check the ribbon
overflow by selecting the drop-down arrow. If it's still not there, follow the advice
in the article Troubleshoot Office Scripts.

4. Select the Record Actions button.

5. Select cells A2:C2 (the "Oranges" row) and set the fill color to orange.

6. Stop the recording by selecting the Stop button.

Your worksheet should look like this (don't worry if the color is different):

Edit an existing script

The previous script colored the "Oranges" row to be orange. Add a yellow row for the
"Lemons".

1. From the now-open Details pane, select the Edit button.

2. You should see something similar to this code:

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Set fill color to FFC000 for range Sheet1!A2:C2
let selectedSheet = workbook.getActiveWorksheet();

selectedSheet.getRange("A2:C2").getFormat().getFill().setColor("FFC000"
);
}

This code gets the current worksheet from the workbook. Then, it sets the fill color
of the range A2:C2.
Ranges are a fundamental part of Office Scripts in Excel. A range is a contiguous,
rectangular block of cells that contains values, formula, and formatting. They are
the basic structure of cells through which you'll perform most of your scripting
tasks.

3. Add the following line to the end of the script (between where the color is set and
the closing } ):

TypeScript

selectedSheet.getRange("A3:C3").getFormat().getFill().setColor("yellow"
);

4. Test the script by selecting Run. Your workbook should now look like this:

Create a table
Next, convert this fruit sales data into a table. You'll keep modifying the first script for
the entire tutorial.

1. Add the following line to the end of the script (before the closing } ):

TypeScript

let table = selectedSheet.addTable("A1:C5", true);

2. That call returns a Table object. Use that table to sort the data. Sort the data in
ascending order based on the values in the "Fruit" column. Add the following line
after the table creation:

TypeScript

table.getSort().apply([{ key: 0, ascending: true }]);

Your script should look like this:

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Set fill color to FFC000 for range Sheet1!A2:C2
let selectedSheet = workbook.getActiveWorksheet();

selectedSheet.getRange("A2:C2").getFormat().getFill().setColor("FFC000"
);

selectedSheet.getRange("A3:C3").getFormat().getFill().setColor("yellow"
);
let table = selectedSheet.addTable("A1:C5", true);
table.getSort().apply([{ key: 0, ascending: true }]);
}

Tables have a TableSort object, accessed through the Table.getSort method. You
can apply sorting criteria to that object. The apply method takes in an array of
SortField objects. In this case, you only have one sorting criteria, so you only use

one SortField . The key: 0 value sets the column with the sort-defining values to
"0" (which is the first column on the table, column A in this case). The ascending:
true value sorts the data in ascending order (instead of descending order).

3. Run the script. You should see a table like this:

７ Note

If you re-run the script, you'll get an error. This is because you can't create a
table on top of another table. However, you can run the script in a different
worksheet or workbook.

Re-run the script

1. Create a new worksheet in the current workbook.
2. Copy the fruit data from the beginning of the tutorial and paste it into the new
worksheet, starting at cell A1.
3. Run the script.
Next steps
Complete Tutorial: Clean and normalize Excel workbook data. It teaches you how to read
data from a workbook with an Office Script.
Tutorial: Clean and normalize Excel
workbook data
Article • 11/29/2023

This tutorial teaches you how to read data from a workbook with an Office Script for
Excel. You'll be writing a new script that formats a bank statement and normalizes the
data in that statement. As part of that data clean-up, your script will read values from
the transaction cells, apply a simple formula to each value, and write the resulting
answer to the workbook. Reading data from the workbook lets you automate some of
your decision making processes in the script.

 Tip

If you're new to Office Scripts, we recommend starting with Tutorial: Create and
format an Excel table. Office Scripts use TypeScript and this tutorial is intended for
people with beginner to intermediate-level knowledge of JavaScript or TypeScript.
If you're new to JavaScript, we recommend starting with the Mozilla JavaScript
tutorial .

Prerequisites
You'll need access to Office Scripts for this tutorial. Please review the Platform support if
the Automate tab doesn't appear.

Read a cell
Scripts made with the Action Recorder can only write information to the workbook. With
the Code Editor, you can edit and make scripts that also read data from a workbook.

Start by making a script that reads data and acts based on what was read. Throughout
the tutorial, you'll work with a sample banking statement. This statement is a combined
checking and credit statement. Unfortunately, the bank reports balance changes
differently. The checking statement gives income as positive credit and costs as negative
debit. The credit statement does the opposite.

Over the rest of the tutorial, you'll normalize this data using a script. First, you need to
read data from the workbook.

1. Create a new worksheet in the workbook you've used for the rest of the tutorial.
2. Copy the following data and paste it into the new worksheet, starting at cell A1.

ﾉ Expand table

Date Account Description Debit Credit

10/10/2019 Checking Coho Vineyard -20.05

10/11/2019 Credit The Phone Company 99.95

10/13/2019 Credit Coho Vineyard 154.43

10/15/2019 Checking External Deposit 1000

10/20/2019 Credit Coho Vineyard - Refund -35.45

10/25/2019 Checking Best For You Organics Company -85.64

11/01/2019 Checking External Deposit 1000

3. Go to the Automate tab and select New Script.

4. Clean up the formatting. This is a financial document, so have your script change
the number formatting in the Debit and Credit columns to show values as dollar
amounts. Also have your script fit the column width to the data.

Replace the script contents with the following code:

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
let selectedSheet = workbook.getActiveWorksheet();

// Format the range to display numerical dollar amounts.

selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");

// Fit the width of all the used columns to the data.

selectedSheet.getUsedRange().getFormat().autofitColumns();
}

5. Now read a value from one of the number columns. Add the following code to the
end of the script (before the closing } ).

TypeScript

// Get the value of cell D2.

let range = selectedSheet.getRange("D2");
console.log(range.getValues());

6. Run the script.

7. You should see [Array[1]] in the console. This is not a number because ranges are
two-dimensional arrays of data. That two-dimensional range is being logged to the
console directly. Luckily, the Code Editor lets you see the contents of the array.

8. When a two-dimensional array is logged to the console, it groups column values

under each row. Expand the array log by selecting the blue triangle.

9. Expand the second level of the array by selecting the newly revealed blue triangle.
You should now see this:

Modify the value of a cell

Now that your script can read data, use that data to modify the workbook. Make the
value of the cell D2 positive with the Math.abs function. The Math object contains
many functions to which your scripts have access. More information about Math and
other built-in objects can be found at Using built-in JavaScript objects in Office Scripts.

1. Use getValue and setValue methods to change the value of the cell. These
methods work on a single cell. When handling multi-cell ranges, you'll want to use
getValues and setValues . Add the following code to the end of the script.

TypeScript

// Run the `Math.abs` method with the value at D2 and apply that value
back to D2.
let positiveValue = Math.abs(range.getValue() as number);
range.setValue(positiveValue);

７ Note
We are casting the returned value of range.getValue() to a number by
using the as keyword. This is necessary because a range could be strings,
numbers, or booleans. In this instance, we explicitly need a number.

2. The value of cell D2 should now be positive.

Modify the values of a column

Now that you know how to read and write to a single cell, you can generalize the script
to work on the entire Debit and Credit columns.

1. Remove the code that affects only a single cell (the previous absolute value code),
such that your script now looks like this:

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
let selectedSheet = workbook.getActiveWorksheet();

// Format the range to display numerical dollar amounts.

selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");

// Fit the width of all the used columns to the data.

selectedSheet.getUsedRange().getFormat().autofitColumns();
}

2. Add a loop to the end of the script that iterates through the rows in the last two
columns. For each cell, the script sets the value to the current value's absolute
value.

Note that the array defining cell locations is zero-based. That means cell A1 is
range[0][0] .

TypeScript

// Get the values of the used range.

let range = selectedSheet.getUsedRange();
let rangeValues = range.getValues();

// Iterate over the fourth and fifth columns and set their values to
their absolute value.
let rowCount = range.getRowCount();
for (let i = 1; i < rowCount; i++) {
// The column at index 3 is column "4" in the worksheet.
if (rangeValues[i][3] != 0) {
let positiveValue = Math.abs(rangeValues[i][3] as number);
selectedSheet.getCell(i, 3).setValue(positiveValue);
}

// The column at index 4 is column "5" in the worksheet.

if (rangeValues[i][4] != 0) {
let positiveValue = Math.abs(rangeValues[i][4] as number);
selectedSheet.getCell(i, 4).setValue(positiveValue);
}
}

This portion of the script does several important tasks. First, it gets the values and
row count of the used range. This lets the script look at values and know when to
stop. Second, it iterates through the used range, checking each cell in the Debit or
Credit columns. Finally, if the value in the cell is not 0, it is replaced by its absolute
value. The script ignores zeroes, so you can leave the blank cells as they were.

3. Run the script.

Your banking statement should now have properly formatted positive numbers.

Next steps
Open the Code Editor and try out some of our Sample scripts for Office Scripts in Excel.
You can also visit Fundamentals for Office Scripts in Excel to learn more about creating
Office Scripts.

The next series of Office Scripts tutorials focus on using Office Scripts with Power
Automate. Learn more about the advantages combining the two platforms in Run Office
Scripts with Power Automate or try Tutorial: Update a spreadsheet from a Power
Automate flow to create a Power Automate flow that uses an Office Script.
Tutorial: Update a spreadsheet from a
Power Automate flow
Article • 12/05/2023

This tutorial teaches you how to run an Office Script for Excel through Power
Automate . You'll make a script that updates the values of two cells with the current
time. You'll then connect that script to a manually triggered Power Automate flow, so
that the script is run whenever a button in Power Automate is selected. Once you
understand the basic pattern, you can expand the flow to include other applications and
automate more of your daily workflow.

 Tip

If you are new to Office Scripts, we recommend starting with Tutorial: Create and
format an Excel table. Office Scripts use TypeScript and this tutorial is intended for
people with beginner to intermediate-level knowledge of JavaScript or TypeScript.
If you're new to JavaScript, we recommend starting with the Mozilla JavaScript
tutorial .

Prerequisites
You'll need access to Office Scripts and Power Automate for this tutorial. Please review
the Platform support if the Automate tab doesn't appear. The Power Automate sign-up
FAQ has information on getting started with Power Automate.

Prepare the workbook

Power Automate shouldn't use relative references like Workbook.getActiveWorksheet to
access workbook components. So, you need a workbook and worksheet with consistent
names that Power Automate can reference.

1. Create a new workbook named MyWorkbook.

2. In the MyWorkbook workbook, create a worksheet called TutorialWorksheet.

Create an Office Script

1. Go to the Automate tab and select New Script.
2. Replace the default script with the following script. This script adds the current
date and time to the first two cells of the TutorialWorksheet worksheet.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the "TutorialWorksheet" worksheet from the workbook.
let worksheet = workbook.getWorksheet("TutorialWorksheet");

// Get the cells at A1 and B1.

let dateRange = worksheet.getRange("A1");
let timeRange = worksheet.getRange("B1");

// Get the current date and time using the JavaScript Date object.
let date = new Date(Date.now());

// Add the date string to A1.

dateRange.setValue(date.toLocaleDateString());

// Add the time string to B1.

timeRange.setValue(date.toLocaleTimeString());
}

3. Rename the script to Set date and time. Select the script name to change it.

4. Save the script by selecting Save Script.

Create an automated workflow with Power

Automate
1. Sign in to the Power Automate site .

2. In the menu that's displayed on the left side of the screen, select Create. This
brings you to a list of ways to create new workflows.

3. In the Start from blank section, select Instant flow. This creates a manually
activated workflow. You can also make scheduled flows and flows that start based
on events. These are covered in the next tutorials.
4. In the dialog window that appears, enter a name for your flow in the Flow name
text box. Under Choose how to trigger the flow, select Manually trigger a flow
from the list of options. Select Create to finish the initial setup.

Note that a manually triggered flow is just one of many types of flows. In the next
tutorial, you'll make a flow that automatically runs when you receive an email.

5. In the flow builder, select the + button and Add an action.

6. In the Add an action task pane, search for "Excel run script". Choose the Excel
Online (Business) connector's Run script action. This action runs a script from your
OneDrive on a workbook. If you want to use a script stored in your team's
SharePoint library, you should use the Run script from a SharePoint library action.
7. You may be asked to sign in to your Microsoft 365 account. Do so to continue the
tutorial.

8. Next, you'll select the workbook and script to use in the flow step. For the tutorial,
you'll use the workbook you created in your OneDrive, but you could use any
workbook in a OneDrive or SharePoint site. Specify the following parameters for
the Run script action:

Location: OneDrive for Business

Document Library: OneDrive
File: MyWorkbook.xlsx (Chosen through the file browser)
Script: Set date and time
9. Select Save.

Your flow is now ready to be run through Power Automate. You can test it using the Test
button in the flow editor or follow the remaining tutorial steps to run the flow from your
flow collection.

Run the script through Power Automate

1. From the main Power Automate page, select My flows.

2. Select My tutorial flow from the list of flows displayed in the My flows tab. This
shows the details of the flow we previously created.

3. Select Run.

4. A task pane will appear for running the flow. If you are asked to Sign in to Excel
Online, do so by selecting Continue.

5. Select Run flow. This runs the flow, which runs the related Office Script.

6. Select Done. You should see the run history update accordingly.
7. Refresh the page to see the results of the Power Automate. If it failed, verify the
flow's settings and run it a second time.

8. Open the workbook to see the updated cells. You should see the current date in
cell A1 and the current time in cell B1. Power Automate uses Coordinated Universal
Time (UTC), so the time will likely be offset from your current time zone.

Next steps
Complete Tutorial: Automatically save content from emails in a workbook. It teaches you
how to pass data from a workflow service to your Office Script and run the Power
Automate flow when certain events occur.
Tutorial: Automatically save content
from emails in a workbook
Article • 12/05/2023

This tutorial teaches you how to use an Office Script for Excel with an automated Power
Automate workflow. Your script will automatically run each time you receive an email,
recording information from the email in an Excel workbook. Being able to pass data
from other applications into an Office Script gives you a great deal of flexibility and
freedom in your automated processes.

 Tip

If you're new to Office Scripts, we recommend starting with Tutorial: Create and
format an Excel table. If you're new to Power Automate, we recommend starting
with Tutorial: Update a spreadsheet from a Power Automate flow. Office Scripts
use TypeScript and this tutorial is intended for people with beginner to
intermediate-level knowledge of JavaScript or TypeScript. If you're new to
JavaScript, we recommend starting with the Mozilla JavaScript tutorial .

Prepare the workbook

1. Create a new workbook named MyWorkbook.

2. Go to the Automate tab and select New Script.

3. Replace the existing code with the following script and select Run. This will setup
the workbook with consistent worksheet, table, and PivotTable names.

TypeScript
function main(workbook: ExcelScript.Workbook) {
// Add a new worksheet to store the email table.
let emailsSheet = workbook.addWorksheet("Emails");

// Add data and create a table

emailsSheet.getRange("A1:D1").setValues([
["Date", "Day of the week", "Email address", "Subject"]
]);
let newTable = workbook.addTable(emailsSheet.getRange("A1:D2"),
true);
newTable.setName("EmailTable");

// Add a new PivotTable to a new worksheet

let pivotWorksheet = workbook.addWorksheet("Subjects");
let newPivotTable = workbook.addPivotTable("Pivot", "EmailTable",
pivotWorksheet.getRange("A3:C20"));

// Setup the pivot hierarchies

newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Day of the
week"));
newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Email
address"));

newPivotTable.addDataHierarchy(newPivotTable.getHierarchy("Subject"));
}

Create an Office Script

Create a script that logs information from an email. You'll want to track which days of
the week you receive the most mail and how many unique senders are sending that
mail. Your workbook has a table with Date, Day of the week, Email address, and Subject
columns. Your worksheet also has a PivotTable that is pivoting on the Day of the week
and Email address (those are the row hierarchies). The count of unique Subjects is the
aggregated information being displayed (the data hierarchy). The script will refresh that
PivotTable after it updates the email table.

1. From within the Code Editor task pane, select New Script.

2. The flow that you'll create later in the tutorial sends the script information about
each email that's received. The script needs to accept that input through
parameters in the main function. Replace the default script with the following
script.

TypeScript

function main(
workbook: ExcelScript.Workbook,
from: string,
dateReceived: string,
subject: string) {

3. The script needs access to the workbook's table and PivotTable. Add the following
code to the body of the script, after the opening { .

TypeScript

// Get the email table.

let emailWorksheet = workbook.getWorksheet("Emails");
let table = emailWorksheet.getTable("EmailTable");

// Get the PivotTable.

let pivotTableWorksheet = workbook.getWorksheet("Subjects");
let pivotTable = pivotTableWorksheet.getPivotTable("Pivot");

4. The dateReceived parameter is of type string . Convert that to a Date object so

you can easily get the day of the week. After doing that, you'll need to map the
day's number value to a more readable version. Add the following code to the end
of your script, before the closing } .

TypeScript

// Parse the received date string to determine the day of the week.
let emailDate = new Date(dateReceived);
let dayName = emailDate.toLocaleDateString("en-US", { weekday: 'long'
});

5. The subject string may include the "RE:" reply tag. Remove that from the string so
that emails in the same thread have the same subject for the table. Add the
following code to the end of your script, before the closing } .

TypeScript

// Remove the reply tag from the email subject to group emails on the
same thread.
let subjectText = subject.replace("Re: ", "");
subjectText = subjectText.replace("RE: ", "");

6. Now that the email data has been formatted, add a row to the email table. Add the
following code to the end of your script, before the closing } .

TypeScript
// Add the parsed text to the table.
table.addRow(-1, [dateReceived, dayName, from, subjectText]);

7. Finally, make sure the PivotTable is refreshed. Add the following code to the end of
your script, before the closing } :

TypeScript

// Refresh the PivotTable to include the new row.

pivotTable.refresh();

8. Rename your script Record Email and select Save script.

Your script is now ready for a Power Automate workflow. It should look like the
following script.

TypeScript

function main(
workbook: ExcelScript.Workbook,
from: string,
dateReceived: string,
subject: string) {
// Get the email table.
let emailWorksheet = workbook.getWorksheet("Emails");
let table = emailWorksheet.getTable("EmailTable");

// Get the PivotTable.

let pivotTableWorksheet = workbook.getWorksheet("Subjects");
let pivotTable = pivotTableWorksheet.getPivotTable("Pivot");

// Parse the received date string to determine the day of the week.
let emailDate = new Date(dateReceived);
let dayName = emailDate.toLocaleDateString("en-US", { weekday: 'long' });

// Remove the reply tag from the email subject to group emails on the same
thread.
let subjectText = subject.replace("Re: ", "");
subjectText = subjectText.replace("RE: ", "");

// Add the parsed text to the table.

table.addRow(-1, [dateReceived, dayName, from, subjectText]);

// Refresh the PivotTable to include the new row.

pivotTable.refresh();
}
Create an automated workflow with Power
Automate
1. Sign in to the Power Automate site .

2. In the menu that's displayed on the left side of the screen, select Create. This
brings you to list of ways to create new workflows.

3. In the Start from blank section, select Automated flow. This creates a workflow
triggered by an event, such as receiving an email.

4. In the dialog window that appears, enter a name for your flow in the Flow name
text box. Under Choose your flow's trigger, select When a new email arrives from
the list of options. You may need to search for the option using the search box.
Finally, select Create.

７ Note

This tutorial uses Outlook. Feel free to use your preferred email service
instead, though some options may be different.
5. In the flow builder, select the + button and Add an action.

7. You may be asked to sign in to your Microsoft 365 account. Do so to continue the
tutorial.

Location: OneDrive for Business

Document Library: OneDrive
File: MyWorkbook.xlsx (Chosen through the file browser)
Script: Record Email
ScriptParameters/from: From (dynamic content from Outlook)
ScriptParameters/dateReceived: Received Time (dynamic content from
Outlook)
ScriptParameters/subject: Subject (dynamic content from Outlook)

Note that the parameters for the script will only appear once the script is selected.
9. Select Save.

Your flow is now enabled. It will automatically run your script each time you receive an
email through Outlook.

Manage the script in Power Automate

1. From the main Power Automate page, select My flows.

2. Select your flow. Here you can see the run history. You can refresh the page or
select the refresh All runs button to update the history. The flow will trigger shortly
after an email is received. Test the flow by sending yourself mail.

When the flow is triggered and successfully runs your script, you should see the
workbook's table and PivotTable update.
Troubleshooting
Receiving multiple emails at the same time can cause merge conflicts in Excel. This risk is
mitigated by setting the email connector to only act on one email at a time. To do this:

1. Select the "email arrives" action, and then select Settings.

2. In the Settings options that pop up, set Concurrency Control to On. Then, set the
Degree of Parallelism to 1.
Next steps
Complete Tutorial: Send weekly email reminders based on spreadsheet data. It teaches
you how to return data from a script to the flow.

You can also check out the Automated task reminders sample scenario to learn how to
combine Office Scripts and Power Automate with Teams Adaptive Cards.
Tutorial: Send weekly email reminders
based on spreadsheet data
Article • 12/05/2023

This tutorial teaches you how to return information from an Office Script for Excel as
part of an automated Power Automate workflow. You'll make a script that looks
through a schedule and works with a flow to send reminder emails. This flow will run on
a regular schedule, providing these reminders on your behalf.

 Tip

If you're new to Office Scripts, we recommend starting with Tutorial: Create and
format an Excel table.

If you're new to Power Automate, we recommend starting with Tutorial: Update a

spreadsheet from a Power Automate flow and Tutorial: Automatically save
content from emails in a workbook.

Office Scripts use TypeScript and this tutorial is intended for people with beginner
to intermediate-level knowledge of JavaScript or TypeScript. If you're new to
JavaScript, we recommend starting with the Mozilla JavaScript tutorial .

Prepare the workbook

1. Download the workbook on-call-rotation.xlsx to your OneDrive.

2. Open on-call-rotation.xlsx in Excel.

3. Add a row to the table with your name, email address, and start and end dates that
overlap with the current date.

） Important
The script you'll write uses the first matching entry in the table, so make sure
your name is above any row with the current week.

Create an Office Script

1. Go to the Automate tab and select New Script.

2. Name the script Get On-Call Person.

3. You should now have an empty script. You want a script that gets an email address
from the spreadsheet. Change main to return a string, like this:

TypeScript

function main(workbook: ExcelScript.Workbook) : string {

}
4. Next, you need to get all the data from the table. That lets the script look at each
row. Add the following code inside the main function.

TypeScript

// Get the H1 worksheet.

let worksheet = workbook.getWorksheet("H1");

// Get the first (and only) table in the worksheet.

let table = worksheet.getTables()[0];

// Get the data from the table.

let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();

5. The dates in the table are stored using Excel's date serial number . You need to
convert those dates to JavaScript dates in order to compare them. Add the
following helper function outside of the main function.

TypeScript

// Convert the Excel date to a JavaScript Date object.

function convertDate(excelDateValue: number) {
let javaScriptDate = new Date(Math.round((excelDateValue - 25569) *
86400 * 1000));
return javaScriptDate;
}

6. Now, you need to figure out which person is on call right now. Their row will have
a start and end date surrounding the current date. The script will assume only one
person is on call at a time. Scripts can return arrays to handle multiple values, but
you can return the first matching email address for this tutorial. Add the following
code to the end of the main function.

TypeScript

// Look for the first row where today's date is between the row's start
and end dates.
let currentDate = new Date();
for (let row = 0; row < tableValues.length; row++) {
let startDate = convertDate(tableValues[row][2] as number);
let endDate = convertDate(tableValues[row][3] as number);
if (startDate <= currentDate && endDate >= currentDate) {
// Return the first matching email address.
return tableValues[row][1].toString();
}
}
7. The final script should look like this:

TypeScript

function main(workbook: ExcelScript.Workbook) : string {

// Get the H1 worksheet.
let worksheet = workbook.getWorksheet("H1");

// Get the first (and only) table in the worksheet.

let table = worksheet.getTables()[0];

// Get the data from the table.

let tableValues =
table.getRangeBetweenHeaderAndTotal().getValues();

// Look for the first row where today's date is between the row's
start and end dates.
let currentDate = new Date();
for (let row = 0; row < tableValues.length; row++) {
let startDate = convertDate(tableValues[row][2] as number);
let endDate = convertDate(tableValues[row][3] as number);
if (startDate <= currentDate && endDate >= currentDate) {
// Return the first matching email address.
return tableValues[row][1].toString();
}
}
}

// Convert the Excel date to a JavaScript Date object.

function convertDate(excelDateValue: number) {
let javaScriptDate = new Date(Math.round((excelDateValue - 25569) *
86400 * 1000));
return javaScriptDate;
}

Create an automated workflow with Power

Automate
1. Sign in to the Power Automate site .

2. In the menu that's displayed on the left side of the screen, select Create. This
brings you to list of ways to create new workflows.

3. Under the Start from blank section, select Scheduled cloud flow.
4. Next, set the schedule for this flow. Your spreadsheet has a new on-call assignment
starting every Monday in the first half of 2024. Set the flow to run first thing
Monday mornings. Use the following options to configure the flow to run on
Monday each week.

Flow name: Notify On-Call Person

Starting: 11/27/23 at 1:00am
Repeat every: 1 Week
On these days: M

5. Select Create.

6. In the flow builder, select the + button and Add an action.

7. In the Add an action task pane, search for "Excel run script". Choose the Excel
Online (Business) connector's Run script action. This action runs a script from your
OneDrive on a workbook. If you want to use a script stored in your team's
SharePoint library, you should use the Run script from a SharePoint library action.
8. You may be asked to sign in to your Microsoft 365 account. Do so to continue the
tutorial.

9. Next, you'll select the workbook and script to use in the flow step. For the tutorial,
you'll use the workbook you created in your OneDrive, but you could use any
workbook in a OneDrive or SharePoint site. Specify the following parameters for
the Run script action:

Location: OneDrive for Business

Document Library: OneDrive
File: on-call-rotation.xlsx (Chosen through the file browser)
Script: Get On-Call Person
10. In the flow builder, select the + button and Add an action.

11. End the flow by sending the reminder email. In the Add an action task pane,
search for "send an email". Choose the Office 365 Outlook connector's Send an
email (V2) action.
７ Note

This tutorial uses Outlook. Feel free to use your preferred email service
instead, though some options may be different.

12. For the To parameter, select the text box and select Enter custom value. Use the
dynamic content control to add the email address returned by the script. This will
be labelled result with the Excel icon next to it. You can provide whatever subject
and body text you'd like.

13. Select Save.

Test the script in Power Automate

Your flow will run every Monday morning. You can test the script now by selecting the
Test button in the upper-right corner of the screen. Select Manually, then select Run
Test to run the flow now and test the behavior. You may need to grant permissions to
Excel and Outlook to continue.
 Tip

If your flow fails to send an email, double-check in the spreadsheet that a valid
email is listed for the current date range at the top of the table.

Next steps
Visit Run Office Scripts with Power Automate to learn more about connecting Office
Scripts with Power Automate.

You can also check out the Automated task reminders sample scenario to learn how to
combine Office Scripts and Power Automate with Teams Adaptive Cards.
Fundamentals for Office Scripts in Excel
Article • 10/24/2023

This article will introduce you to the technical aspects of Office Scripts. You'll learn the
critical parts of the TypeScript-based script code and how the Excel objects and APIs
work together.

If you would prefer to get started with an interactive experience, try Tutorial: Create and
format an Excel table or visit our samples.

TypeScript: The language of Office Scripts

Office Scripts are written in TypeScript , which is a superset of JavaScript . If you're
familiar with JavaScript, your knowledge will carry over because much of the code is the
same in both languages. We recommend you have some beginner-level programming
knowledge before starting your Office Scripts coding journey. The following resources
can help you understand the coding side of Office Scripts.

Learn the basics of JavaScript. You should feel comfortable with concepts like
variables, control flow, functions, and data types. Mozilla offers a good,
comprehensive tutorial on JavaScript .
Learn about types in TypeScript. TypeScript builds on JavaScript by ensuring at
compile-time that the right types are used for method calls and assignments. The
TypeScript documentation on object types , type inference , and type
compatibility will be the most useful.

main function: The script's starting point

Each script must contain a main function with the ExcelScript.Workbook type as its first
parameter. When the function runs, the Excel application invokes the main function by
providing the workbook as its first parameter. An ExcelScript.Workbook should always
be the first parameter.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Your code goes here
}
The code inside the main function runs when the script is run. main can call other
functions in your script, but code that's not contained in a function will not run. Scripts
cannot invoke or call other Office Scripts.

Power Automate allows you to connect scripts in flows. Data is passed between the
scripts and the flow through the parameters and returns of the main function. How to
integrate Office Scripts with Power Automate is covered in detail in Run Office Scripts
with Power Automate.

Object model overview

To write a script, you need to understand how the Office Scripts APIs fit together. The
components of a workbook have specific relations to one another. In many ways, these
relations match those of the Excel UI.

A Workbook contains one or more Worksheets.

A Worksheet gives access to cells through Range objects.
A Range represents a group of contiguous cells.
Ranges are used to create and place Tables, Charts, Shapes, and other data
visualization or organization objects.
A Worksheet contains collections of those data objects that are present in the
individual sheet.
Workbooks contain collections of some of those data objects (such as Tables) for
the entire Workbook.

The complete list of Office Scripts API objects is detailed in the ExcelScript package.

Workbook
Every script is provided a workbook object of type Workbook by the main function. This
represents the top level object through which your script interacts with the Excel
workbook.

The following script gets the active worksheet from the workbook and logs its name.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the active worksheet.
let sheet = workbook.getActiveWorksheet();

// Display the current worksheet's name.

console.log(sheet.getName());
}

Ranges
A range is a group of contiguous cells in the workbook. Scripts typically use A1-style
notation (e.g., B3 for the single cell in column B and row 3 or C2:F4 for the cells from
columns C through F and rows 2 through 4) to define ranges.

Ranges have three core properties: values, formulas, and format. These properties get or
set the cell values, formulas to be evaluated, and the visual formatting of the cells. They
are accessed through getValues , getFormulas , and getFormat . Values and formulas can
be changed with setValues and setFormulas , while the format is a RangeFormat object
comprised of several smaller objects that are individually set.

Ranges use two-dimensional arrays to manage information. For more information on

handling arrays in the Office Scripts framework, see Work with ranges.

Range sample
The following sample shows how to create sales records. This script uses Range objects
to set the values, formulas, and parts of the format.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the active worksheet.
let sheet = workbook.getActiveWorksheet();

// Create the headers and format them to stand out.

let headers = [["Product", "Quantity", "Unit Price", "Totals"]];
let headerRange = sheet.getRange("B2:E2");
headerRange.setValues(headers);
headerRange.getFormat().getFill().setColor("#4472C4");
headerRange.getFormat().getFont().setColor("white");

// Create the product data rows.

let productData = [
["Almonds", 6, 7.5],
["Coffee", 20, 34.5],
["Chocolate", 10, 9.54],
];
let dataRange = sheet.getRange("B3:D5");
dataRange.setValues(productData);

// Create the formulas to total the amounts sold.

let totalFormulas = [
["=C3 * D3"],
["=C4 * D4"],
["=C5 * D5"],
["=SUM(E3:E5)"],
];
let totalRange = sheet.getRange("E3:E6");
totalRange.setFormulas(totalFormulas);
totalRange.getFormat().getFont().setBold(true);

// Display the totals as US dollar amounts.

totalRange.setNumberFormat("$0.00");
}

Running this script creates the following data in the current worksheet:

The types of Range values

Each cell has value. This value is the underlying value entered into the cell, which may be
different from the text displayed in Excel. For example, you might see "5/2/2021"
displayed in the cell as a date, but the actual value is 44318. This display can be changed
with the number format, but the actual value and type in the cell only changes when a
new value is set.

When you are using the cell value, it's important to tell TypeScript what value you are
expecting to get from a cell or range. A cell contains one of the following types: string ,
number , or boolean . In order for your script to treat the returned values as one of those

types, you must declare the type.

The following script gets the average price from the table in the previous sample. Note
the code priceRange.getValues() as number[][] . This asserts the type of the range
values to be a number[][] . All the values in that array can then be treated as numbers in
the script.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the active worksheet.
let sheet = workbook.getActiveWorksheet();
// Get the "Unit Price" column.
// The result of calling getValues is declared to be a number[][] so that
we can perform arithmetic operations.
let priceRange = sheet.getRange("D3:D5");
let prices = priceRange.getValues() as number[][];

// Get the average price.

let totalPrices = 0;
prices.forEach((price) => totalPrices += price[0]);
let averagePrice = totalPrices / prices.length;
console.log(averagePrice);
}

Charts, tables, and other data objects

Scripts can create and manipulate the data structures and visualizations within Excel.
Tables and charts are two of the more commonly used objects, but the APIs support
PivotTables, shapes, images, and more. These are stored in collections, which will be
discussed later in this article.

Create a table
Create tables by using data-filled ranges. Formatting and table controls (such as filters)
are automatically applied to the range.

The following script creates a table using the ranges from the previous sample.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the active worksheet.
let sheet = workbook.getActiveWorksheet();

// Add a table that has headers using the data from B2:E5.
sheet.addTable("B2:E5", true);
}

Running this script on the worksheet with the previous data creates the following table:
Create a chart
Create charts to visualize the data in a range. Scripts allow for dozens of chart varieties,
each of which can be customized to suit your needs.

The following script creates a simple column chart for three items and places it 100
pixels below the top of the worksheet.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the active worksheet.
let sheet = workbook.getActiveWorksheet();

// Create a column chart using the data from B3:C5.

let chart = sheet.addChart(
ExcelScript.ChartType.columnStacked,
sheet.getRange("B3:C5")
);

// Set the margin of the chart to be 100 pixels from the top of the
screen.
chart.setTop(100);
}

Running this script on the worksheet with the previous table creates the following chart:
Collections
When an Excel object has a collection of one or more objects of the same type, it stores
them in an array. For example, a Workbook object contains a Worksheet[] . This array is
accessed by the Workbook.getWorksheets() method. get methods that are plural, such
as Worksheet.getCharts() , return the entire object collection as an array. You'll see this
pattern throughout the Office Scripts APIs: the Worksheet object has a getTables()
method that returns a Table[] , the Table object has a getColumns() method that
returns a TableColumn[] , as so on.

The returned array is a normal array, so all the regular array operations are available for
your script. You can also access individual objects within the collection using the array
index value. For example, workbook.getTables()[0] returns the first table in the
collection. For more information on using the built-in array functionality with the Office
Scripts framework, see Work with collections.

Individual objects are also accessed from the collection through a get method. get
methods that are singular, such as Worksheet.getTable(name) , return a single object and
require an ID or name for the specific object. This ID or name is usually set by the script
or through the Excel UI.

The following script gets all tables in the workbook. It then ensures the headers are
displays, the filter buttons are visible, and the table style is set to "TableStyleLight1".

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table collection.
let tables = workbook.getTables();
// Set the table formatting properties for every table.
tables.forEach(table => {
table.setShowHeaders(true);
table.setShowFilterButton(true);
table.setPredefinedTableStyle("TableStyleLight1");
})
}

Add Excel objects with a script

You can programmatically add document objects, such as tables or charts, by calling the
corresponding add method available on the parent object.

） Important

Do not manually add objects to collection arrays. Use the add methods on the
parent objects For example, add a Table to a Worksheet with the
Worksheet.addTable method.

The following script creates a table in Excel on the first worksheet in the workbook. Note
that the created table is returned by the addTable method.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the first worksheet.
let sheet = workbook.getWorksheets()[0];

// Add a table that uses the data in A1:G10.

let table = sheet.addTable(
"A1:G10",
true /* True because the table has headers. */
);

// Give the table a name for easy reference in other scripts.

table.setName("MyTable");
}

 Tip

Most Excel objects have a setName method. This gives you an easy way to access
Excel objects later in the script or in other scripts for the same workbook.
Verify an object exists in the collection
Scripts often need to check if a table or similar object exists before continuing. Use the
names given by scripts or through the Excel UI to identify necessary objects and act
accordingly. get methods return undefined when the requested object is not in the
collection.

The following script requests a table named "MyTable" and uses an if...else statement
to check if the table was found.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table named "MyTable".
let myTable = workbook.getTable("MyTable");

// If the table is in the workbook, myTable will have a value.

// Otherwise, the variable will be undefined and go to the else clause.
if (myTable) {
let worksheetName = myTable.getWorksheet().getName();
console.log(`MyTable is on the ${worksheetName} worksheet`);
} else {
console.log(`MyTable is not in the workbook.`);
}
}

A common pattern in Office Scripts is to recreate a table, chart, or other object every
time the script is run. If you don't need the old data, it's best to delete the old object
before creating the new one. This avoids name conflicts or other differences that may
have been introduced by other users.

The following script removes the table named "MyTable", if it is present, then adds a
new table with the same name.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table named "MyTable" from the first worksheet.
let sheet = workbook.getWorksheets()[0];
let tableName = "MyTable";
let oldTable = sheet.getTable(tableName);

// If the table exists, remove it.

if (oldTable) {
oldTable.delete();
}

// Add a new table with the same name.

let newTable = sheet.addTable("A1:G10", true);
newTable.setName(tableName);
}

Remove Excel objects with a script

To delete an object, call the object's delete method.

７ Note

As with adding objects, do not manually remove objects from collection arrays. Use
the delete methods on the collection-type objects. For example, remove a Table
from a Worksheet using Table.delete .

The following script removes the first worksheet in the workbook.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get first worksheet.
let sheet = workbook.getWorksheets()[0];

// Remove that worksheet from the workbook.

sheet.delete();
}

Tutorial: Update a spreadsheet from a Power Automate flow

Tutorial: Automatically save content from emails in a workbook
Tutorial: Send weekly email reminders based on spreadsheet data

Create a flow from Excel

You can get started with Power Automate in Excel with a variety of flow templates.
Under the Automate tab, select Automate a Task.

This opens a task pane with several options to begin connecting your Office Scripts to
larger automated solutions. Select any option to begin. Your flow is supplied with the
current workbook.

 Tip

You can also start making a flow from the More options (…) menu on an individual
script.
Excel connector
The Excel Online (Business) connector gives your flows access to Excel workbooks. There
are two actions that call Office Scripts.

Run script. This is the action to use with scripts stored in the default location of
your OneDrive.
Run script from SharePoint library. This is the action to use when scripts are
stored in your team's SharePoint site.

Run script

For the Run script action, the script location is always in your OneDrive.

Data security in Office Scripts with Power Automate

The "Run script" action gives people who use the Excel connector significant access to
your workbook and its data. Additionally, there are security risks with scripts that make
external API calls, as explained in External calls from Power Automate. If your admin is
concerned with the exposure of highly sensitive data, they can either turn off the Excel
Online connector or restrict access to Office Scripts through the Office Scripts
administrator controls.

For admins who have enabled Conditional Access policies for unmanaged devices in
their tenant, it's a best practice to disable Power Automate on unmanaged devices. This
process is detailed in the blog post Control Access to Power Apps and Power Automate
with Azure AD Conditional Access Policies .

Data transfer in flows for scripts

Power Automate lets you pass pieces of data between flow actions. Scripts can be
configured to accept the information you need and return what you want from your
workbook to your flow. Data is passed to scripts as static values, expressions, or dynamic
content. Details on an individual service's connector can be found in the Power
Automate Connector documentation.

Learn the details of how to pass data to and from your scripts with the following
documentation.

Learn by doing with Tutorial: Automatically save content from emails in a

workbook and Tutorial: Send weekly email reminders based on spreadsheet data.
Try the Automated task reminders sample scenario to see everything in action.
Read Pass data to and from scripts in Power Automate for more usage scenarios
and the technical TypeScript details.

Example
The following screenshot shows a Power Automate flow that's triggered whenever a new
response to a Form is submitted. The flow runs a script that adds the satisfaction value
from the Form to a table. The current average satisfaction is returned and sent as an
email.
The main function of the script specifies the new satisfaction value as an input
parameter ( newData ). The script returns the average satisfaction value from the table.

TypeScript

function main(workbook: ExcelScript.Workbook, newData: string): number {

// Add the new data to the table.
const table = workbook.getTable("SurveyTable");
table.addRow(-1, [newData]);

// Get the current satisfaction total.

const satisfactionColumn = table.getColumnByName("Current Satisfaction");
const values =
satisfactionColumn.getRangeBetweenHeaderAndTotal().getValues();
let total = 0.0;
values.forEach((value) => {
total += value[0] as number;
});

// Return the average satisfaction.

return total / values.length;
}

７ Note

The script parameter is of type string because that is the type the Forms action
returns for all values.

Find full examples with step-by-step walkthroughs in Office Scripts samples and
scenarios. Many use Office Scripts in the center of Power Automate flows.

See also
Tutorial: Update a spreadsheet from a Power Automate flow
Pass data to and from scripts in Power Automate
Troubleshooting information for Power Automate with Office Scripts
Get started with Power Automate
Excel Online (Business) connector reference documentation

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Best practices in Office Scripts
Article • 03/30/2023

These patterns and practices are designed to help your scripts run successfully every
time. Use them to avoid common pitfalls as you start automating your Excel workflow.

Use the Action Recorder to learn new features

Excel does a lot of things. Most of them can be scripted. The Action Recorder records
your Excel actions and translates them into code. This is the easiest way to learn about
how different features work with Office Scripts. If you need code for a specific action,
switch to the Action Recorder, perform the actions, select Copy as code, and paste the
resulting code into your script.

） Important

Occasionally, the Action Recorder may use an API that isn't supported outside of
Excel on the web. Users of that script on other platforms receive a warning when
viewing that script.

Verify an object is present

Scripts often rely on a certain worksheet or table being present in the workbook.
However, they might get renamed or removed between script runs. By checking if those
tables or worksheets exist before calling methods on them, you can make sure the script
doesn't end abruptly.
The following sample code checks if the "Index" worksheet is present in the workbook. If
the worksheet is present, the script gets a range and proceeds. If it isn't present, the
script logs a custom error message.

TypeScript

// Make sure the "Index" worksheet exists before using it.

let indexSheet = workbook.getWorksheet('Index');
if (indexSheet) {
let range = indexSheet.getRange("A1");
// Continue using the range...
} else {
console.log("Index sheet not found.");
}

The TypeScript ? operator checks if the object exists before calling a method. This can
make your code more streamlined if you don't need to do anything special when the
object doesn't exist.

TypeScript

// The ? ensures that the delete() API is only called if the object exists.
workbook.getWorksheet('Index')?.delete();

Validate data and workbook state first

Make sure all your worksheets, tables, shapes, and other objects are present before
working on the data. Using the previous pattern, check to see if everything is in the
workbook and matches your expectations. Doing this before any data is written ensures
your script doesn't leave the workbook in a partial state.

The following script requires two tables named "Table1" and "Table2" to be present. The
script first checks if the tables are present and then ends with the return statement and
an appropriate message if they're not.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// These tables must be in the workbook for the script.
const TargetTableName = 'Table1';
const SourceTableName = 'Table2';

// Get the table objects.

// Continue...
}

If the verification is happening in a separate function, you still must end the script by
issuing the return statement from the main function. Returning from the subfunction
doesn't end the script.

The following script has the same behavior as the previous one. The difference is that
the main function calls the inputPresent function to verify everything. inputPresent
returns a boolean ( true or false ) to indicate whether all required inputs are present.
The main function uses that boolean to decide on continuing or ending the script.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table objects.

if (!inputPresent(workbook)) {
return;
}

// Continue...
}

function inputPresent(workbook: ExcelScript.Workbook): boolean {

// These tables must be in the workbook for the script.
const TargetTableName = 'Table1';
const SourceTableName = 'Table2';

// Get the table objects.

let targetTable = workbook.getTable(TargetTableName);
let sourceTable = workbook.getTable(SourceTableName);

// Check if the tables are there.

if (!targetTable || !sourceTable) {
console.log(`Required tables missing - Check that both the source
(${TargetTableName}) and target (${SourceTableName}) tables are present
before running the script.`);
return false;
}
return true;
}

When to use a throw statement

A throw statement indicates an unexpected error has occurred. It ends the code
immediately. For the most part, you don't need to throw from your script. Usually, the
script automatically informs the user that the script failed to run due to an issue. In most
cases, it's sufficient to end the script with an error message and a return statement
from the main function.

However, if your script is running as part of a Power Automate flow, you may want to
stop the flow from continuing. A throw statement stops the script and tells the flow to
stop as well.

The following script shows how to use the throw statement in the table-checking
example.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// These tables must be in the workbook for the script.
const TargetTableName = 'Table1';
const SourceTableName = 'Table2';

// Get the table objects.

let targetTable = workbook.getTable(TargetTableName);
let sourceTable = workbook.getTable(SourceTableName);

// Check if the tables are there.

if (!targetTable || !sourceTable) {
// Immediately end the script with an error.
throw `Required tables missing - Check that both the source
(${TargetTableName}) and target (${SourceTableName}) tables are present
before running the script.`;
}

When to use a try...catch statement

The try...catch statement is a way to detect if an API call fails and continue running the
script.

Consider the following snippet that performs a large data update on a range.
TypeScript

range.setValues(someLargeValues);

If someLargeValues is larger than Excel on the web can handle, the setValues() call fails.
The script then also fails with a runtime error. The try...catch statement lets your script
recognize this condition, without immediately ending the script and showing the default
error.

One approach for giving the script user a better experience is to present them a custom
error message. The following snippet shows a try...catch statement logging more
error information to better help the reader.

TypeScript

try {
range.setValues(someLargeValues);
} catch (error) {
console.log(`The script failed to update the values at location
${range.getAddress()}. Please inspect and run again.`);
console.log(error);
return; // End the script (assuming this is in the main function).
}

Another approach to dealing with errors is to have fallback behavior that handles the
error case. The following snippet uses the catch block to try an alternate method break
up the update into smaller pieces and avoid the error.

 Tip

For a full example on how to update a large range, see Write a large dataset.

TypeScript

try {
range.setValues(someLargeValues);
} catch (error) {
console.log(`The script failed to update the values at location
${range.getAddress()}. Trying a different approach.`);
handleUpdatesInSmallerBatches(someLargeValues);
}

// Continue...
}
７ Note

Using try...catch inside or around a loop slows down your script. For more
performance information, see Avoid using try...catch blocks.

See also
Troubleshooting Office Scripts
Troubleshooting information for Power Automate with Office Scripts
Platform limits with Office Scripts
Improve the performance of your Office Scripts

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Use built-in JavaScript objects in Office
Scripts
Article • 11/17/2022

JavaScript provides several built-in objects that you can use in your Office Scripts,
regardless of whether you're scripting in JavaScript or TypeScript (a superset of
JavaScript). This article describes how to use some of the built-in JavaScript objects in
Office Scripts for Excel.

７ Note

For a complete list of all built-in JavaScript objects, see Mozilla's Standard built-in
objects article.

Array
The Array object provides a standardized way to work with arrays in your script. While
arrays are standard JavaScript constructs, they relate to Office Scripts in two major ways:
ranges and collections.

Work with ranges

Ranges contain several two-dimensional arrays that directly map to the cells in that
range. These arrays contain specific information about each cell in that range. For
example, Range.getValues returns all the values in those cells (with the rows and
columns of the two-dimensional array mapping to the rows and columns of that
worksheet subsection). Range.getFormulas and Range.getNumberFormats are other
frequently used methods that return arrays like Range.getValues .

The following script searches the A1:D4 range for any number format containing a "$".
The script sets the fill color in those cells to "yellow".

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range From A1 to D4.
let range = workbook.getActiveWorksheet().getRange("A1:D4");

// Get the number formats for each cell in the range.

Work with collections

Many Excel objects are contained in a collection. The collection is managed by the Office
Scripts API and exposed as an array. For example, all Shapes in a worksheet are
contained in a Shape[] that is returned by the Worksheet.getShapes method. You can
use this array to read values from the collection, or you can access specific objects from
the parent object's get* methods.

７ Note

Do not manually add or remove objects from these collection arrays. Use the add
methods on the parent objects and the delete methods on the collection-type
objects. For example, add a Table to a Worksheet with the Worksheet.addTable
method and remove the Table using Table.delete .

The following script logs the type of every shape in the current worksheet.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
let selectedSheet = workbook.getActiveWorksheet();

// Get the shapes in this worksheet.

let shapes = selectedSheet.getShapes();

// Log the type of every shape in the collection.

shapes.forEach((shape) => {
console.log(shape.getType());
});
}
The following script deletes the oldest shape in the current worksheet.

Typescript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
let selectedSheet = workbook.getActiveWorksheet();

// Get the first (oldest) shape in the worksheet.

// Note that this script will thrown an error if there are no shapes.
let shape = selectedSheet.getShapes()[0];

// Remove the shape from the worksheet.

shape.delete();
}

Date
The Date object provides a standardized way to work with dates in your script.
Date.now() generates an object with the current date and time, which is useful when
adding timestamps to your script's data entry.

The following script adds the current date to the worksheet. Note that by using the
toLocaleDateString method, Excel recognizes the value as a date and changes the

number format of the cell automatically.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range for cell A1.
let range = workbook.getActiveWorksheet().getRange("A1");

// Get the current date and time.

let date = new Date(Date.now());

// Set the value at A1 to the current date, using a localized string.

range.setValue(date.toLocaleDateString());
}

The Work with dates section of the samples has more date-related scripts.

Math
The Math object provides methods and constants for common mathematical
operations. These provide many functions also available in Excel, without the need to
use the workbook's calculation engine. This saves your script from having to query the
workbook, which improves performance.

The following script uses Math.min to find and log the smallest number in the A1:D4
range. Note that this sample assumes the entire range contains only numbers, not
strings.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range from A1 to D4.
let comparisonRange = workbook.getActiveWorksheet().getRange("A1:D4");

// Load the range's values.

let comparisonRangeValues = comparisonRange.getValues();

// Set the minimum values as the first value.

let minimum = comparisonRangeValues[0][0];

// Iterate over each row looking for the smallest value.

comparisonRangeValues.forEach((rowItem, rowIndex) => {
// Iterate over each column looking for the smallest value.
comparisonRangeValues[rowIndex].forEach((columnItem) => {
// Use `Math.min` to set the smallest value as either the current
cell's value or the previous minimum.
minimum = Math.min(minimum, columnItem);
});
});

console.log(minimum);
}

Use of external JavaScript libraries is not

supported
Office Scripts don't support the use of external, third-party libraries. Your script can only
use the built-in JavaScript objects and the Office Scripts APIs.

See also
Standard built-in objects
Office Scripts Code Editor environment
Office Scripts Code Editor environment
Article • 11/01/2023

Office Scripts are written in either TypeScript or JavaScript and use the Office Scripts
JavaScript APIs to interact with an Excel workbook. The Code Editor is based on Visual
Studio Code, so if you've used that environment before, you'll feel right at home.

Scripting language: TypeScript or JavaScript

Office Scripts are written in TypeScript , which is a superset of JavaScript . The Action
Recorder generates code in TypeScript and the Office Scripts documentation uses
TypeScript. Since TypeScript is a superset of JavaScript, any scripting code that you write
in JavaScript will work just fine.

Office Scripts are largely self-contained pieces of code. Only a small part of TypeScript's
functionality is used. Therefore, you can edit scripts without having to learn the
intricacies of TypeScript. The Code Editor also handles the installation, compilation, and
execution of code, so you don't need to worry about anything but the script itself. It's
possible to learn the language and create scripts without previous programming
knowledge. However, if you're new to programming, we recommend learning some
fundamentals before proceeding with Office Scripts.

Office Scripts JavaScript API

Office Scripts use a specialized version of the Office JavaScript APIs for Office Add-ins.
While there are similarities in the two APIs, you should not assume code can be ported
between the two platforms. The differences between the two platforms are described in
the Differences between Office Scripts and Office Add-ins article. You can view all the
APIs available to your script in the Office Scripts API reference documentation.

External library support

Office Scripts does not support the usage of external, third-party JavaScript libraries.
Currently, you cannot call any library other than the Office Scripts APIs from a script. You
do still have access to any built-in JavaScript object, such as Math .

IntelliSense
IntelliSense is a set of Code Editor features that help you write code. It provides auto-
complete, syntax error highlighting, and inline API documentation.

IntelliSense gives suggestions as you type, similar to the suggested text in Excel.
Pressing the Tab or Enter key inserts the suggested member. Trigger IntelliSense at the
current cursor location by pressing the Ctrl+Space keys. These suggestions are
especially useful when completing a method. The method signature displayed by
IntelliSense contains a list of arguments it needs, each argument's type, whether a given
argument is required or optional, and the return type of the method.

Hover the cursor over a method, class, or other code object to see more information.
Hover over a syntax error or code suggestion, represented by a red or yellow squiggly
line, to see suggestions on how to fix the problem. Often, IntelliSense provides a "Quick
Fix" option to automatically change the code.

The Office Scripts Code Editor uses the same IntelliSense engine as Visual Studio Code.
To learn more about the feature, visit Visual Studio Code's IntelliSense Features .

Keyboard shortcuts
Most of the keyboard shortcuts for Visual Studio Code also work in the Office Scripts
Code Editor. Use the following PDFs to learn about the available options and get the
most out of the Code Editor:

Keyboard shortcuts for macOS .

Keyboard shortcuts for Windows .
See also
Office Scripts API reference
Troubleshooting Office Scripts
Using built-in JavaScript objects in Office Scripts
External API call support in Office
Scripts
Article • 05/04/2023

Scripts support calls to external services. Use these services to supply data and other
information to your workbook.

Ｕ Caution

External calls may result in sensitive data being exposed to undesirable endpoints.
Your admin can establish Information Rights Management (IRM) or firewall
protection against such calls.

） Important

Calls to external APIs can only be made through the Excel application, not through
Power Automate under normal circumstances. External calls are also not supported
for scripts stored on a SharePoint site.

Configure your script for external calls

External calls are asynchronous and require that your script is marked as async . Add
the async prefix to your main function and have it return a Promise , as shown here:

TypeScript

async function main(workbook: ExcelScript.Workbook) : Promise <void>

７ Note

Scripts that return other information can return a Promise of that type. For
example, if your script needs to return an Employee object, the return signature
would be : Promise <Employee>

You'll need to learn the external service's interfaces to make calls to that service. If you
are using fetch or REST APIs , you need to determine the JSON structure of the
returned data. For both input to and output from your script, consider making an
interface to match the needed JSON structures. This gives the script more type safety.

You can see an example of this in Using fetch from Office Scripts.

Limitations with external calls from Office Scripts

There is no way to sign in or use OAuth2 type of authentication flows. All keys and
credentials have to be hardcoded (or read from another source).
There is no infrastructure to store API credentials and keys. This will have to be
managed by the user.
Document cookies, localStorage , and sessionStorage objects are not supported.
External calls may result in sensitive data being exposed to undesirable endpoints,
or external data to be brought into internal workbooks. Your admin can establish
firewall protection against such calls. Be sure to check with local policies prior to
relying on external calls.
Be sure to check the amount of data throughput prior to taking a dependency. For
instance, pulling down the entire external dataset may not be the best option and
instead pagination should be used to get data in chunks.

Retrieve information with fetch

The fetch API retrieves information from external services. It is an async API, so you
need to adjust the main signature of your script. Make the main function async . You
should also be sure to await the fetch call and json retrieval. This ensures those
operations complete before the script ends.

Any JSON data retrieved by fetch must match an interface defined in the script. The
returned value must be assigned to a specific type because Office Scripts do not support
the any type. You should refer to the documentation for your service to see what the
names and types of the returned properties are. Then, add the matching interface or
interfaces to your script.

The following script uses fetch to retrieve JSON data from the test server in the given
URL. Note the JSONData interface to store the data as a matching type.

TypeScript

async function main(workbook: ExcelScript.Workbook) {

// Retrieve sample JSON data from a test server.
let fetchResult = await
fetch('https://jsonplaceholder.typicode.com/todos/1');

// Convert the returned data to the expected JSON structure.

let json : JSONData = await fetchResult.json();

// Display the content in a readable format.

console.log(JSON.stringify(json));
}

/**
* An interface that matches the returned JSON structure.
* The property names match exactly.
*/
interface JSONData {
userId: number;
id: number;
title: string;
completed: boolean;
}

Other fetch samples

The Use external fetch calls in Office Scripts sample shows how to get basic
information about a user's GitHub repositories.
Samples in the Use JSON to pass data to and from Office Scripts article show how
to pass data to and from fetch commands as JSON.
The Office Scripts sample scenario: Graph water-level data from NOAA
demonstrates the fetch command being used to retrieve records from the
National Oceanic and Atmospheric Administration's Tides and Currents database.
The second sample in Add images to a workbook contains a fetch call to get an
image from a website.

Restrict external calls with Information Rights

Management (IRM)
You can apply IRM settings to a workbook to prevent external calls being made by
scripts. Disable the Copy/EXTRACT policy to prevent this behavior.

External calls from Power Automate

External API calls fail when a script is run through Power Automate. A fetch call will give
the error message "Runtime error: Line X: fetch is not defined". Be sure to check your
scripts for such references before building them into a flow.

You'll have to use HTTP with Azure AD or other equivalent actions to pull data from or
push it to an external service.
２ Warning

External calls made through the Power Automate Excel Online connector fail in
order to help uphold existing data loss prevention policies. However, scripts that
are run through Power Automate are done so outside of your organization, and
outside of your organization's firewalls. For additional protection from malicious
users in this external environment, your admin can control the use of Office Scripts.
Your admin can either disable the Excel Online connector in Power Automate or
turn off Office Scripts for Excel through the Office Scripts administrator controls.

See also
Use JSON to pass data to and from Office Scripts
Using built-in JavaScript objects in Office Scripts
Use external fetch calls in Office Scripts
Office Scripts sample scenario: Graph water-level data from NOAA
Office Scripts file storage and ownership
Article • 01/11/2024

The details of how scripts are stored and shared depend on your Microsoft 365
subscription. Select the relevant tab to learn more.

） Important

Office Scripts is in preview for personal and family Microsoft 365 subscriptions. If
you encounter any issues, please report them through the Feedback button in
Excel.

File storage
For business and education

Office Scripts are stored in your OneDrive by default. The .osts files are found in the
/Documents/Office Scripts/ folder. Any edits made to these .osts files, such as
renaming or deleting files, will be reflected in the Code Editor and Script Gallery.
Excel only recognizes and runs a script if it's in your OneDrive folder, a Sharepoint
folder, or shared with the workbook. This means Excel needs internet connectivity
to access Office Scripts.

OneDrive
Scripts that are shared with one of your workbooks remain in the script creator's
OneDrive. They are not copied to any of your local or OneDrive folders when you
run the shared script in Excel. The Move button shown when renaming a script
moves the script to a new location. The Make a Copy button of the Code Editor
saves a separate copy of the script in your OneDrive. Changes to the copy don't
affect the original script.

Unless you share your personal scripts, no one else can access them. Your OneDrive
settings control the shared access and permissions for all script .osts files,
independent of any Excel settings. Scripts can't be linked from a local disk or
custom cloud locations.

SharePoint
Office Scripts that are saved to a SharePoint site are owned by your team. You and
members of your organization with the appropriate access can run and edit scripts
from SharePoint. You'll also see these scripts appear in the Automate tab's Script
Gallery.

To load a script from SharePoint, go to All scripts and select View more scripts at
the bottom of the list. This brings up a file picker where you can choose .osts files
from any SharePoint site to which you have access. Note that scripts from
SharePoint that you've already opened will be displayed in the list of recent scripts.

To move a script to SharePoint, open a script in the Code Editor and click on the
script name, as if you're renaming it. In the callout, click the Move button. This
opens a file picker. Select the destination folder in your SharePoint site. Moving the
script to the new location can take up to a minute.

To save a copy of a script to SharePoint, go to the More options (…) menu and
select Save as. This opens a file picker where you can select folders in your
SharePoint site. Saving to a new location creates a copy of the script at that
location. The original version is still on your OneDrive or other SharePoint location.

） Important

Scripts with external calls can't be run from SharePoint. You'll receive an error
saying "Network access calls are not supported at this time for scripts saved to
a SharePoint site".

７ Note

Power Automate supports running scripts stored on SharePoint with the Run
script from SharePoint library (Preview) action. This action is currently in
preview and is subject to change based on feedback. If you encounter any
issues with this action, please report them through the Help > Give Feedback
option in Power Automate.

Share scripts
For business and education
To give users who are outside of the SharePoint site access to the script, share the
script with an Excel workbook. This means you're linking the script with the file, not
attaching it. Whoever has access to the Excel file will also be able to view, run, or
make a copy of the script.

７ Note

Admin settings for Conditional Access in OneDrive and SharePoint affect Office
Scripts. For more information, see the Conditional Access section of Platform
limits and requirements with Office Scripts.

Restore deleted scripts

For business and education

When you delete a script in Excel, it goes to your OneDrive or SharePoint recycle
bin. To restore a deleted script, follow the steps listed in How to recover missing,
deleted or corrupted items in SharePoint and OneDrive for work or school .
Restoring an .osts file returns it to the All scripts list.

A deleted script is unshared with the workbook. When you restore a script, it does
not retain its script access. You will need to share the script again.

Restored scripts still work as expected with Power Automate flows. You don't need
to recreate the flow connector.

File ownership and retention

For business and education

Office Scripts follow the retention and deletion policies specified by Microsoft
OneDrive and Microsoft SharePoint. To learn how to handle scripts that were
created and shared by a user being removed from your organization, see Learn
about retention for SharePoint and OneDrive.

During editing, files are temporarily stored in the browser. You must save the script
before closing the Excel window to save it to the OneDrive location. Don't forget to
save the file after edits, or else those edits will only be in the browser's version of
the file.

Audit Office Scripts usage at the admin level

Discover who is using Office Scripts in your organization with the compliance center
audit log. Details about the audit log are found in Search the audit log in the Security &
Compliance Center.

To specifically audit Office Scripts related activity as an admin, take the following steps.

1. In a InPrivate browser window (or Incognito or other browser-specific limited-

tracking mode), open and log into the Compliance center .

2. Go to the Audit page.

3. (One time only) On the Search tab, select Start recording user and admin activity.

） Important

It may take an hour or two after turning on recording before all activities
across the tenant are recorded.

4. Set the desired search options and select Search. Filter the File, folder, or site field
to .osts . This reveals who in your organization is creating or modifying scripts.

See also
Sharing Office Scripts in Excel
Troubleshooting Office Scripts
Office Scripts settings in M365
Undo the effects of Office Scripts
Get user input for scripts
Article • 09/14/2023

Adding parameters to your script lets other users provide data for the script, without
needing to edit code. When your script is run through the ribbon or a button, a prompt
pops up that asks for input.

） Important

Currently, only Excel on the web users will be prompted to enter data for
parameterized scripts. Power Automate flows also support giving data to scripts
through parameters.

Example - Highlight large values

The following example shows a script that takes a number and string from the user. To
test it, open an empty workbook and enter some numbers into several cells.

TypeScript
/**
* This script applies a background color to cells over a certain value.
* @param highlightThreshold The value used for comparisons.
* @param color A string representing the color to make the high value
cells.
* This must be a color code representing the color of the background,
* in the form #RRGGBB (e.g., "FFA500") or a named HTML color (e.g.,
"orange").
*/
function main(
workbook: ExcelScript.Workbook,
highlightThreshold: number,
color: string) {
// Get the used cells in the current worksheet.
const currentSheet = workbook.getActiveWorksheet();
const usedRange = currentSheet.getUsedRange();

const rangeValues = usedRange.getValues();

for (let row = 0; row < rangeValues.length; row++) {
for (let column = 0; column < rangeValues[row].length; column++) {
if (rangeValues[row][column] >= highlightThreshold) {
usedRange.getCell(row,
column).getFormat().getFill().setColor(color);
}
}
}
}

main parameters: Pass data to a script

All script input is specified as additional parameters for the main function. New
parameters are added after the mandatory workbook: ExcelScript.Workbook parameter.
For example, if you wanted a script to accept a string that represents a name as input,
you would change the main signature to function main(workbook:
ExcelScript.Workbook, name: string) .

Optional parameters
Optional parameters don't need the user to provide a value. This implies your script
either has default behavior or this parameter is only needed in a corner case. They're
denoted in your script with the optional modifier ? . For example, in function

main(workbook: ExcelScript.Workbook, Name?: string) the parameter Name is optional.

Default parameter values

Default parameter values automatically fill the action's field with a value. To set a
default value, assign a value to the parameter in the main signature. For example, in
function main(workbook: ExcelScript.Workbook, location: string = "Seattle") the

parameter location has the value "Seattle" unless something else is provided.

Dropdown lists for parameters

Help others using your script in their flow by providing a list of acceptable parameter
choices. If there's a small subset of values that your script uses, create a parameter that
is those literal values. Do this by declaring the parameter type to be a union of literal
values . For example, in function main(workbook: ExcelScript.Workbook, location:
"Seattle" | "Redmond") the parameter location can only be "Seattle" or "Redmond" .

When the script is run, users get a dropdown list with those two options.

Document the script

Code comments that follow JSDoc standards will be shown to people when they run
your script. The more details you put in the descriptions, the easier it'll be for others to
the scripts. Describe the purpose of each input parameter and any restrictions or limits.
The following sample JSDoc shows how to document a script with a number parameter
called taxRate .

TypeScript

/**
* A script to apply the current tax rate to sales figures.
* @param taxRate The current sales tax rate in the region as a decimal
number (enter 12% as .12).
*/
function main(workbook: ExcelScript.Workbook, taxRate: number)

７ Note

You don't need to document the ExcelScript.Workbook parameter in every script.

Type restrictions
When adding input parameters and return values, consider the following allowances and
restrictions.
1. The first parameter must be of type ExcelScript.Workbook . Its parameter name
doesn't matter.

2. The types string , number , boolean , unknown , and object .

3. Arrays (both [] and Array<T> styles) of the previously listed types are supported.
Nested arrays are also supported.

4. Union types are allowed if they're a union of literals belonging to a single type
(such as "Left" | "Right" , not "Left" | 5 ).

5. Object types are allowed if they contain properties of type string , number ,
boolean , supported arrays, or other supported objects. The following example

shows nested objects that are supported as parameter types.

TypeScript

// The Employee object is supported because Position is also composed

of supported types.
interface Employee {
name: string;
job: Position;
}

interface Position {
id: number;
title: string;
}

6. Objects must have their interface or class definition defined in the script. An object
can also be defined anonymously inline, as in the following example.

TypeScript

function main(workbook: ExcelScript.Workbook, contact: {name: string,

email: string})

See also
Pass data to and from scripts in Power Automate
Run Office Scripts in Excel with buttons
Set conditional formatting for cross-column comparisons
Pass data to and from scripts in Power
Automate
Article • 11/29/2023

Power Automate chains together separate programs into a single automated workflow.
Each connector has different parameters it accepts and different values it returns. Your
scripts can be written to expand the "Run script" Power Automate action to get
additional input or give output.

Input for your script is specified by adding parameters to the main function. Output
from the script is declared by adding a return type to main .

７ Note

When you create a "Run script" block in your flow, the accepted parameters and
returned types are populated. If you change the parameters or return types of your
script, you'll need to redo the "Run script" block of your flow. This ensures the data
is being parsed correctly.

Pass data to scripts with parameters

Add parameters to scripts to provide input from other parts of the flow. It's the same
process to add parameters for flow-based scripts as it is for scripts run through the Excel
client. Learn about providing input to scripts in Get user input for scripts.

The following screenshot shows what a script with the signature function
main(workbook: ExcelScript.Workbook, location: string = "Seattle") would display.
The dropdown menus created by type unions also function the same in Power
Automate.
Return data from a script
Scripts can return data from the workbook to be used as dynamic content in a Power
Automate flow. To return an object, add the return type syntax to the main function. For
example, if you wanted to return a string value from the script, your main signature
would be function main(workbook: ExcelScript.Workbook): string .

Returned values are shown as dynamic content from the Run script action in the flow.
The dynamic content is named "result".

Acceptable types for returning data are the same as for parameters. Details on type
restrictions are found in the article Get user input for scripts.
See also
Tutorial: Update a spreadsheet from a Power Automate flow
Get user input for scripts
Run Office Scripts with Power Automate
Troubleshooting information for Power Automate with Office Scripts
Run Office Scripts in Excel from buttons
Article • 06/11/2024

Help your colleagues find and run your scripts by adding script buttons to a workbook.

７ Note

Script buttons aren't available during the Office Scripts preview for personal and
family Microsoft 365 subscriptions.

Create script buttons

When viewing a script, select Add in workbook. This creates a button in the workbook
that runs the associated script. It also shares the script with the workbook, so everyone
with write permissions to the workbook can use your helpful automation.
） Important

If OneDrive sharing is restricted by organizational policies, you can't create a script

button.

Remove script buttons

To stop sharing a script through a button, select the arrow next to Add in workbook and
choose the option Remove from workbook. This removes all the buttons that run the
script. Deleting a single button removes the script from that one button, even if the
operation is undone or the button is cut and pasted.
Older versions of Excel
Script buttons won't work when opened in versions of Excel that don't support Office
Scripts. In that case, the button still appears, but selecting it has no effect.

See also
Platform limits and requirements with Office Scripts

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
be found on GitHub, where you Select a link to provide feedback:
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Use JSON to pass data to and from
Office Scripts
Article • 11/29/2023

JSON (JavaScript Object Notation) is a format for storing and transferring data. Each
JSON object is a collection of name/value pairs that can be defined when created. JSON
is useful with Office Scripts because it can handle the arbitrary complexity of ranges,
tables, and other data patterns in Excel. JSON lets you parse incoming data from web
services and pass complex objects through Power Automate flows.

This article focuses on using JSON with Office Scripts. We recommend you first learn
more about the format from articles such as JSON Introduction from W3 Schools.

Parse JSON data into a range or table

Arrays of JSON objects provide a consistent way to pass rows of table data between
applications and web services. In these cases, each JSON object represents a row, while
the properties represent the columns. An Office Script can loop over a JSON array and
reassemble it as a 2D array. This array is then set as the values of a range and stored in a
workbook. The property names can also be added as headers to create a table.

The following script shows JSON data being converted to a table. Note that the data is
not taken from an external source. That is covered later in this article.

TypeScript

/**
* Sample JSON data. This would be replaced by external calls or
* parameters getting data from Power Automate in a production script.
*/
const jsonData = [
{ "Action": "Edit", /* Action property with value of "Edit". */
"N": 3370, /* N property with value of 3370. */
"Percent": 17.85 /* Percent property with value of 17.85. */
},
// The rest of the object entries follow the same pattern.
{ "Action": "Paste", "N": 1171, "Percent": 6.2 },
{ "Action": "Clear", "N": 599, "Percent": 3.17 },
{ "Action": "Insert", "N": 352, "Percent": 1.86 },
{ "Action": "Delete", "N": 350, "Percent": 1.85 },
{ "Action": "Refresh", "N": 314, "Percent": 1.66 },
{ "Action": "Fill", "N": 286, "Percent": 1.51 },
];

/**
* This script converts JSON data to an Excel table.
*/
function main(workbook: ExcelScript.Workbook) {
// Create a new worksheet to store the imported data.
const newSheet = workbook.addWorksheet();
newSheet.activate();

// Determine the data's shape by getting the properties in one object.

// This assumes all the JSON objects have the same properties.
const columnNames = getPropertiesFromJson(jsonData[0]);

// Create the table headers using the property names.

const headerRange = newSheet.getRangeByIndexes(0, 0, 1,
columnNames.length);
headerRange.setValues([columnNames]);

// Create a new table with the headers.

const newTable = newSheet.addTable(headerRange, true);

// Add each object in the array of JSON objects to the table.

const tableValues = jsonData.map(row => convertJsonToRow(row));
newTable.addRows(-1, tableValues);
}

/**
* This function turns a JSON object into an array to be used as a table
row.
*/
function convertJsonToRow(obj: object) {
const array: (string | number)[] = [];

// Loop over each property and get the value. Their order will be the same
as the column headers.
for (let value in obj) {
array.push(obj[value]);
}
return array;
}

/**
* This function gets the property names from a single JSON object.
*/
function getPropertiesFromJson(obj: object) {
const propertyArray: string[] = [];

// Loop over each property in the object and store the property name in an
array.
for (let property in obj) {
propertyArray.push(property);
}

return propertyArray;
}
 Tip

If you know the structure of the JSON, you can create your own interface to make
getting specific properties easier. You can replace the JSON-to-array conversion
steps with type-safe references. The following code snippet shows those steps (now
commented out) replaced by calls that use a new ActionRow interface. Note that
this makes the convertJsonToRow function no longer necessary.

TypeScript

// const tableValues = jsonData.map(row => convertJsonToRow(row));

// newTable.addRows(-1, tableValues);
// }

const actionRows: ActionRow[] = jsonData as ActionRow[];

// Add each object in the array of JSON objects to the table.
const tableValues = actionRows.map(row => [row.Action, row.N,
row.Percent]);
newTable.addRows(-1, tableValues);
}

interface ActionRow {
Action: string;
N: number;
Percent: number;
}

Get JSON data from external sources

There are two ways to import JSON data into your workbook through an Office Script.

As a parameter with a Power Automate flow.

With a fetch call to an external web service.

Modify the sample to work with Power Automate

JSON data in Power Automate can be passed as a generic object array. Add an object[]
property to the script to accept that data.

TypeScript

// For Power Automate, replace the main signature in the previous sample
with this one
// and remove the sample data.
function main(workbook: ExcelScript.Workbook, jsonData: object[]) {

You'll then see an option in the Power Automate connector to add jsonData to the Run
script action.

Modify the sample to use a fetch call

Web services can reply to fetch calls with JSON data. This gives your script the data it
needs while keeping you in Excel. Learn more about fetch and external calls by reading
External API call support in Office Scripts.

TypeScript

// For external services, replace the main signature in the previous sample
with this one,
// add the fetch call, and remove the sample data.
async function main(workbook: ExcelScript.Workbook) {
// Replace WEB_SERVICE_URL with the URL of whatever service you need to
call.
const response = await fetch('WEB_SERVICE_URL');
const jsonData: object[] = await response.json();

Create JSON from a range

The rows and columns of a worksheet often imply relationships between their data
values. A row of a table conceptually maps to a programming object, with each column
being a property of that object. Consider the following table of data. Each row
represents a transaction recorded in the spreadsheet.

ﾉ Expand table

ID Date Amount Vendor

1 6/1/2022 $43.54 Best for you Organics Company

2 6/3/2022 $67.23 Liberty Bakery and Cafe

3 6/3/2022 $37.12 Best for you Organics Company

4 6/6/2022 $86.95 Coho Vineyard

5 6/7/2022 $13.64 Liberty Bakery and Cafe

Each transaction (each row) has a set of properties associated with it: "ID", "Date",
"Amount", and "Vendor". This can be modeled in an Office Script as an object.

TypeScript

// An interface that wraps transaction details as JSON.

interface Transaction {
"ID": string;
"Date": number;
"Amount": number;
"Vendor": string;
}

The rows in the sample table correspond to the properties in the interface, so a script
can easily convert each row into a Transaction object. This is useful when outputting
the data for Power Automate. The following script iterates over each row in the table
and adds it to a Transaction[] .

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table on the current worksheet.
const table = workbook.getActiveWorksheet().getTables()[0];

// Create an array of Transactions and add each row to it.

let transactions: Transaction[] = [];
const dataValues = table.getRangeBetweenHeaderAndTotal().getValues();
for (let i = 0; i < dataValues.length; i++) {
let row = dataValues[i];
let currentTransaction: Transaction = {
ID: row[table.getColumnByName("ID").getIndex()] as string,
Date: row[table.getColumnByName("Date").getIndex()] as number,
Amount: row[table.getColumnByName("Amount").getIndex()] as number,
Vendor: row[table.getColumnByName("Vendor").getIndex()] as string
};
transactions.push(currentTransaction);
}

// Do something with the Transaction objects, such as return them to a

Power Automate flow.
console.log(transactions);
}

// An interface that wraps transaction details as JSON.

interface Transaction {
"ID": string;
"Date": number;
"Amount": number;
"Vendor": string;
}

Export JSON with fetch

Much like importing data with fetch , you can send data from your workbook with a
similar command. A POST command takes any stringified JSON data and sends it to the
specified endpoint.
To see this in action, replace the console.log(transactions); line in the previous sample
with the following code. This issues a POST command to a testing server, then reads the
data back.

TypeScript

const response = await fetch('https://jsonplaceholder.typicode.com/posts',

{
method: 'POST',
body: JSON.stringify(transactions),
headers: {
'Content-type': 'application/json; charset=UTF-8',
},
});
const jsonData: object[] = await response.json();
console.log(jsonData);

Use a generic object

The previous sample assumes the table header values are consistent. If your table has
variable columns, you'll need to create a generic JSON object. The following script
shows a script that logs any table as JSON.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table on the current worksheet.
const table = workbook.getActiveWorksheet().getTables()[0];

// Use the table header names as JSON properties.

const tableHeaders = table.getHeaderRowRange().getValues()[0] as string[];

// Get each data row in the table.

const dataValues = table.getRangeBetweenHeaderAndTotal().getValues();
let jsonArray: object[] = [];

// For each row, create a JSON object and assign each property to it based
on the table headers.
for (let i = 0; i < dataValues.length; i++) {
// Create a blank generic JSON object.
let jsonObject: { [key: string]: string } = {};
for (let j = 0; j < dataValues[i].length; j++) {
jsonObject[tableHeaders[j]] = dataValues[i][j] as string;
}

jsonArray.push(jsonObject);
}

// Do something with the objects, such as return them to a Power Automate

flow.
console.log(jsonArray);
}

See also
External API call support in Office Scripts
Sample: Use external fetch calls in Office Scripts
Run Office Scripts with Power Automate
How to use macro-enabled files in
Power Automate flows
Article • 05/14/2024

Power Automate flows support .xlsm files in the Excel Online (Business) connector .
However, only the Run script action lets you select .xlsm files through the file browser.
Other connector actions require you use the file ID.

Get the file ID with a Get File Metadata action from either the OneDrive for Business
or SharePoint connectors. Use the ID dynamic content returned from the Get file
metadata action as the "File" argument for Excel connector actions. Note that
parameters such as "Table" in the Add a row into a table action won't automatically
propagate a dropdown list of tables to choose. You'll need to enter the name of the
table or other Excel object.

） Important

The macros contained in the .xlsm files cannot be run through Power Automate.
Only Office Scripts are enabled there.

２ Warning

Some .xlsm files, especially those with ActiveX or Form controls, may not work in
the Excel online connector. Be sure to test before deploying your solution.

See also
Excel Online (Business) connector reference
Run Office Scripts with Power Automate

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our  Provide product feedback
contributor guide.
Work with PivotTables in Office Scripts
Article • 05/01/2024

PivotTables let you analyze large collections of data quickly. With their power comes
complexity. The Office Scripts APIs let you customize a PivotTable to suit your needs, but
the scope of the API set makes getting started a challenge. This article demonstrates
how to perform common PivotTable tasks and explains important classes and methods.

７ Note

To better understand context for the terms used by the APIs, read Excel's PivotTable
documentation first. Start with Create a PivotTable to analyze worksheet data .

Object model
The PivotTable is the central object for PivotTables in the Office Scripts API.

The Workbook object has a collection of all the PivotTables. Each Worksheet also
contains a PivotTable collection that's local to that sheet.
A PivotTable contains PivotHierarchies. A hierarchy can be thought of as a column
in a table.
PivotHierarchies can be added as rows or columns (RowColumnPivotHierarchy),
data (DataPivotHierarchy), or filters (FilterPivotHierarchy).
Each PivotHierarchy contains exactly one PivotField. PivotTable structures outside
of Excel may contain multiple fields per hierarchy, so this design exists to support
future options. For Office Scripts, fields and hierarchies map to the same
information.
A PivotField contains multiple PivotItems. Each PivotItem is a unique value in the
field. Think of each item as a value in the table column. Items could also be
aggregated values, such as sums, if the field is being used for data.
The PivotLayout defines how the PivotFields and PivotItems are displayed.
PivotFilters filter data from the PivotTable using different criteria.

To look at how these relationships work in practice, start by downloading the sample
workbook. That data describes fruit sales from various farms. It's the base for all the
examples in this article. Run the sample scripts throughout the article to create and
explore PivotTables.

Download the sample workbook

Create a PivotTable with fields

PivotTables are created with references to existing data. Both ranges and tables can be
the source for a PivotTable. They also need a place to exist in the workbook. Since the
size of a PivotTable is dynamic, only the upper-left corner of the destination range is
specified.

The following code snippet creates a PivotTable based on a range of data. The
PivotTable has no hierarchies, so the data is not yet grouped in any way.

TypeScript

const dataSheet = workbook.getWorksheet("Data");

const pivotSheet = workbook.getWorksheet("Pivot");

const farmPivot = pivotSheet.addPivotTable(

"Farm Pivot", /* The name of the PivotTable. */
dataSheet.getUsedRange(), /* The source data range. */
pivotSheet.getRange("A1") /* The location to put the new PivotTable.
*/);

Hierarchies and fields

PivotTables are organized through hierarchies. Those hierarchies are used to pivot data
when added as a specific type of hierarchy. There are four types of hierarchies.
Row: Displays items in horizontal rows.
Column: Displays items in vertical columns.
Data: Displays aggregates of values based on the rows and columns.
Filter: Add or removes items from the PivotTable.

A PivotTable can have as many or as few of its fields assigned to these specific
hierarchies. A PivotTable needs at least one data hierarchy to show summarized
numerical data and at least one row or column to pivot that summary on. The following
code snippet adds two row hierarchies and two data hierarchies.

TypeScript

farmPivot.addRowHierarchy(farmPivot.getHierarchy("Farm"));
farmPivot.addRowHierarchy(farmPivot.getHierarchy("Type"));
farmPivot.addDataHierarchy(farmPivot.getHierarchy("Crates Sold at Farm"));
farmPivot.addDataHierarchy(farmPivot.getHierarchy("Crates Sold
Wholesale"));
Layout ranges
Each part of the PivotTable maps to a range. This lets your script get data from the
PivotTable for use later in the script or to be returned in a Power Automate flow. These
ranges are accessed through the PivotLayout object acquired from
PivotTable.getLayout() . The following diagram shows the ranges that are returned by

the methods in PivotLayout .

PivotTable total output
The location of the total row is based on the layout. Use
PivotLayout.getBodyAndTotalRange and get the last row of the column to use the data
from the PivotTable in your script.

The following sample finds the first PivotTable in the workbook and logs the values in
the "Grand Total" cells (as highlighted in green in the image below).

TypeScript
function main(workbook: ExcelScript.Workbook) {
// Get the first PivotTable in the workbook.
const pivotTable = workbook.getPivotTables()[0];

// Get the names of each data column in the PivotTable.

const pivotColumnLabelRange =
pivotTable.getLayout().getColumnLabelRange();

// Get the range displaying the pivoted data.

const pivotDataRange = pivotTable.getLayout().getBodyAndTotalRange();

// Get the range with the "grand totals" for the PivotTable columns.
const grandTotalRange = pivotDataRange.getLastRow();

// Print each of the "Grand Totals" to the console.

grandTotalRange.getValues()[0].forEach((column, columnIndex) => {
console.log(`Grand total of ${pivotColumnLabelRange.getValues()[0]
[columnIndex]}: ${grandTotalRange.getValues()[0][columnIndex]}`);
// Example log: "Grand total of Sum of Crates Sold Wholesale: 11000"
});
}

Filters and slicers

There are three ways to filter a PivotTable.

FilterPivotHierarchies
PivotFilters
Slicers

FilterPivotHierarchies
FilterPivotHierarchies add an additional hierarchy to filter every data row. Any row

with an item that is filtered out is excluded from the PivotTable and its summaries. Since
these filters are based on items, they only work on discrete values. If "Classification" is a
filter hierarchy in the sample, users can select the values of "Organic" and
"Conventional" for the filter. Similarly, if "Crates Sold Wholesale" is selected, the filter
options would be the individual numbers, such as 120 and 150, instead of numerical
ranges.

FilterPivotHierarchies are created with all values selected. This means that nothing is

filtered until the user manually interacts with the filter control or a PivotManualFilter is
set on the field belonging to the FilterPivotHierarchy .

The following code snippet adds "Classification" as a filter hierarchy.

TypeScript

farmPivot.addFilterHierarchy(farmPivot.getHierarchy("Classification"));

PivotFilters
The PivotFilters object is a collection of filters applied to a single field. Since each
hierarchy has exactly one field, you should always use the first field in
PivotHierarchy.getFields() when applying filters. There are four filter types.

Date filter: Calendar date-based filtering.

Label filter: Text comparison filtering.
Manual filter: Custom input filtering.
Value filter: Number comparison filtering. This compares items in the associated
hierarchy with values in a specified data hierarchy.

Typically, only one of the four types of filters is created and applied to the field. If the
script tries to use incompatible filters, an error is thrown with the text "The argument is
invalid or missing or has an incorrect format."

The following code snippet adds two filters. The first is a manual filter that selects items
in an existing "Classification" filter hierarchy. The second filter removes any farms that
have fewer than 300 "Crates Sold Wholesale". Note that this filters out the "Sum" of
those farms, not the individual rows from the original data.

TypeScript

const classificationField =
farmPivot.getFilterHierarchy("Classification").getFields()[0];
classificationField.applyFilter({
manualFilter: {
selectedItems: ["Organic"] /* The included items. */
}
});

const farmField = farmPivot.getHierarchy("Farm").getFields()[0];

farmField.applyFilter({
valueFilter: {
condition: ExcelScript.ValueFilterCondition.greaterThan, /* The
relationship of the value to the comparator. */
comparator: 300, /* The value to which items are compared. */
value: "Sum of Crates Sold Wholesale" /* The name of the data
hierarchy. Note the "Sum of" prefix. */
}
});

Slicers
Slicers filter data in a PivotTable (or standard table). They are moveable objects in the
worksheet that allow for quick filtering selections. A slicer operates in a similar fashion
to the manual filter and PivotFilterHierarchy . Items from the PivotField are toggled
to include or exclude them from the PivotTable.

The following code snippet adds a slicer for the "Type" field. It sets the selected items to
be "Lemon" and "Lime", then moves the slicer 400 pixels to the left.

TypeScript
const fruitSlicer = pivotSheet.addSlicer(
farmPivot, /* The table or PivotTale to be sliced. */
farmPivot.getHierarchy("Type").getFields()[0] /* What source to use as
the slicer options. */
);
fruitSlicer.selectItems(["Lemon", "Lime"]);
fruitSlicer.setLeft(400);

Value field settings for summaries

Change how the PivotTable summarizes and displays data with these settings. The field
in each data hierarchy can display the data in different ways, such as percentages,
standard deviations, and relative comparisons.

Summarize by

The default summarization of a data hierarchy field is as a sum.

DataPivotHierarchy.setSummarizeBy lets you combine the data for each row or column

in a different way. AggregationFunction lists the all the available options.

The following code snippet changes "Crates Sold Wholesale" to show each item's
standard deviation, instead of the sum.

TypeScript

const wholesaleSales = farmPivot.getDataHierarchy("Sum of Crates Sold

Wholesale");

wholesaleSales.setSummarizeBy(ExcelScript.AggregationFunction.standardDeviat
ion);
Show values as
DataPivotHierarchy.setShowAs applies a calculation to the values of a data hierarchy.

Instead of the default sum, you can show values or percentages relative to other parts of
the PivotTable. Use a ShowAsRule to set how data hierarchy values are shown.

The following code snippet changes the display for "Crates Sold at Farm". The values will
be shown as a percentage of the grand total for the field.

TypeScript

const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at

Farm");

const rule : ExcelScript.ShowAsRule = {

calculation: ExcelScript.ShowAsCalculation.percentOfGrandTotal
};
farmSales.setShowAs(rule);

Some ShowAsRule s need another field or item in that field as a comparison. The
following code snippet again changes the display for "Crates Sold at Farm". This time,
the field will show each value's difference from the value of the "Lemons" in that farm
row. If a farm has not sold any lemons, the field shows "#N/A".

TypeScript

const typeField = farmPivot.getRowHierarchy("Type").getFields()[0];

const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at
Farm");

const rule: ExcelScript.ShowAsRule = {

calculation: ExcelScript.ShowAsCalculation.differenceFrom,
baseField: typeField, /* The field to use for the difference. */
baseItem: typeField.getPivotItem("Lemon") /* The item within that field
that is the basis of comparison for the difference. */
};
farmSales.setShowAs(rule);
farmSales.setName("Difference from Lemons of Crates Sold at Farm");

See also
Fundamentals for Office Scripts in Excel
Office Scripts API reference
６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Troubleshoot Office Scripts
Article • 01/26/2024

As you develop Office Scripts, you may make mistakes. It's okay. You have the tools to
help find the problems and get your scripts working perfectly.

７ Note

For troubleshooting advice specific to Office Scripts with Power Automate, see
Troubleshoot Office Scripts running in Power Automate.

Platform limits and requirements

For a detailed list of limitations on the Office Scripts platform due to Excel, Power
Automate, or other technologies, see Platform limits and requirements with Office
Scripts.

Script errors
Office Scripts errors fall into one of two categories:

Compile-time errors or warnings

Runtime errors

Compile-time errors
Compile-time errors and warnings are initially shown in the Code Editor. These are
shown by the wavy red underlines in the editor. They're also displayed under the
Problems tab at the bottom of the Code Editor task pane. Selecting the error gives more
details about the problem and suggests solutions. Compile-time errors should be
addressed before running the script.
You may also see orange warning underlines and grey informational messages. These
indicate performance suggestions or other possibilities where the script may have
unintentional effects. Such warnings should be examined closely before dismissing
them.

Runtime errors
Runtime errors happen because of logic issues in the script. This could be because an
object used in the script isn't in the workbook, a table is formatted differently than
anticipated, or some other slight discrepancy between the script's requirements and the
current workbook. The following script generates an error when a worksheet named
"TestSheet" is not present.

TypeScript

function main(workbook: ExcelScript.Workbook) {

let mySheet = workbook.getWorksheet('TestSheet');

// This will throw an error if there is no "TestSheet".

mySheet.getRange("A1");
}

Some runtime errors are caused by exceeding the limits of the platform, such as trying
to write too much data at once with Excel on the web. See Platform limits and
requirements with Office Scripts for a full list of potential pitfalls.

Console messages
Both compile-time and runtime errors display error messages in the console when a
script runs. They give a line number where the problem was encountered. Keep in mind
that the root cause of any issue may be a different line of code than what is indicated in
the console.

The following image shows the console output for the explicit any compiler error. Note
the text [5, 16] at the beginning of the error string. This indicates the error is on line 5,
starting at character 16.

The follow image shows the console output for a runtime error. Here, the script tries to
add a worksheet with a the name of an existing worksheet. Again, note the "Line 2"
preceding the error to show which line to investigate.

Console logs
Print messages to the screen with the console.log statement. These logs can show you
the current value of variables or which code paths are being triggered. To do this, call
console.log with any object as a parameter. Usually, a string is the easiest type to read

in the console.

TypeScript

console.log("Logging myRange's address.");

console.log(myRange.getAddress());

Strings passed to console.log are displayed in the Code Editor's logging console, at the
bottom of the task pane. Logs are found on the Output tab, though the tab
automatically gains focus when a log is written.

Logs do not affect the workbook.

Automate tab not appearing or Office Scripts

unavailable
The following steps should help troubleshoot any problems related to the Automate tab
not appearing in Excel.

1. Make sure your Microsoft 365 license includes Office Scripts.

2. Ensure third-party cookies are enabled (when using Excel on the web).
3. Ensure that your admin has not disabled Office Scripts in the Microsoft 365 admin
center.
4. Ensure that your admin has not configured a group policy to block Office Scripts
(Windows only).
5. Install WebView2 (Windows only).
6. Ensure you're not logged in as an external or guest user to your tenant.

） Important

When using Excel with Teams, Office Scripts are only supported in Teams on the
web (not Teams for Windows, Mac, iOS, or Android).

７ Note

There is a known issue that prevents scripts stored in SharePoint from always
appearing in the recently used list. This occurs when your admin turns off Exchange
Web Services (EWS). Your SharePoint-based scripts are still accessible and usable
through the file dialog.

Problems scheduling a script

Scheduling a script requires a business license for Microsoft 365. This is needed for the
Power Automate flow that powers the scheduled script. Errors from this flow are shown
when there are problems scheduling. You may need to manually update your flow in
Power Automate , where it will be listed under My flows.

The following list shows common errors you might encounter.

The access token expiry UTC time is earlier than current UTC time.: Too much
time has passed between signing into the service and scheduling the script.
Reopen the script in the Code Editor or reload the workbook and try to schedule
again.

Help resources
Stack Overflow is a community of developers willing to help with coding problems.
Often, you'll be able to find the solution to your problem through a quick Stack
Overflow search. If not, ask your question and tag it with the "office-scripts" tag. Be sure
to mention you're creating an Office Script, not an Office Add-in.

See also
Best practices in Office Scripts
Platform limits with Office Scripts
Improve the performance of your Office Scripts
Troubleshoot Office Scripts running in PowerAutomate
Undo the effects of Office Scripts

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Troubleshoot Office Scripts running in
Power Automate
Article • 08/15/2024

Power Automate runs scripts on your behalf in independent Excel sessions. This causes
some behavioral changes that may create issues with certain scripts or scenarios. There
are also limitations and behaviors from the Power Automate platform script writers
should know. Be sure to read the articles Troubleshoot Office Scripts and Platform limits
and requirements with Office Scripts, as much of that information also applies to scripts
in flows.

 Tip

If you're just starting to use Office Scripts with Power Automate, please start with
Run Office Scripts with Power Automate to learn about the platforms.

） Important

Avoid relative references

Power Automate runs your script in the chosen Excel workbook on your behalf. The
workbook might be closed when this happens. Any API that relies on the user's current
state, such as Workbook.getActiveWorksheet , may behave differently in Power Automate.
This is because the APIs are based on a relative position of the user's view or cursor and
that reference doesn't exist in a Power Automate flow.

Some relative reference APIs throw errors in Power Automate. Others have a default
behavior that implies a user's state. When designing your scripts, be sure to use
absolute references for worksheets and ranges. This makes your Power Automate flow
consistent, even if worksheets are rearranged.

Script methods that fail in Power Automate flows

The following methods throw an error and fail when called from a script in a Power
Automate flow.

ﾉ Expand table

Class Method

Chart activate

Range select

Workbook getActiveCell

Workbook getActiveChart

Workbook getActiveSlicer

Workbook getSelectedRange

Workbook getSelectedRanges

Script methods with a default behavior in Power

Automate flows
The following methods use a default behavior, in lieu of any user's current state.

ﾉ Expand table

Class Method Power Automate behavior

Workbook getActiveWorksheet Returns either the first worksheet in the workbook or the
worksheet currently activated by the Worksheet.activate
method.

Worksheet activate Marks the worksheet as the active worksheet for purposes of
Workbook.getActiveWorksheet .

Refresh not fully supported in Power Automate

Office Scripts can't refresh most data when run in Power Automate. Most refresh
methods, such as PivotTable.refresh , do nothing when called in a flow.
Workbook.refreshAllDataConnections only refreshes when PowerBI is the source.

Additionally, Power Automate doesn't trigger a data refresh for formulas that use
workbook links.
Script methods that do nothing in Power Automate flows
The following methods do nothing in a script when called through Power Automate.
They still return successfully and don't throw any errors.

ﾉ Expand table

Class Method

PivotTable refresh

Workbook refreshAllPivotTables

Worksheet refreshAllPivotTables

Script methods with a different behavior in Power

Automate
The following methods act differently in Power Automate flows than they do when run
through Excel.

ﾉ Expand table

Class Method Power Automate behavior

Workbook refreshAllDataConnections Only refreshes PowerBI sources. For other sources, the
method returns successfully but does nothing.

Select workbooks with the file browser control

When building the Run script step of a Power Automate flow, you need to select which
workbook is part of the flow. Use the file browser to select your workbook, instead of
manually typing the workbook's name.
For more context on the Power Automate limitation and a discussion of potential
workarounds for the dynamic selection of workbooks, see this thread in the Microsoft
Power Automate Community .

Pass entire arrays as script parameters

Power Automate allows users to pass arrays to connectors as a variable or as single
elements in the array. The default is to pass single elements, which builds the array in
the flow. For scripts or other connectors that take entire arrays as arguments, you need
to select the Switch to input entire array button to pass the array as one complete
object. This button is in the upper-right corner of each array parameter input field.
Time zone differences
Excel files don't have an inherent location or timezone. Every time a user opens the
workbook, their session uses that user's local timezone for date calculations. Power
Automate always uses UTC.

If your script uses dates or times, there may be behavioral differences when the script is
tested locally versus when it is run through Power Automate. Power Automate allows
you to convert, format, and adjust times. See Working with Dates and Times inside of
your flows for instructions on how to use those functions in Power Automate and Pass
data to and from scripts in Power Automate to learn how to provide that time
information for the script.

Script parameter fields or returned output not

appearing in Power Automate
There are two reasons that the parameters or returned data of a script are not accurately
reflected in the Power Automate flow builder.
The script signature (the parameters or return value) has changed since the Excel
Business (Online) connector was added.
The script signature uses unsupported types. Verify your types against the
restrictions for Office Scripts parameter and return types.

The signature of a script is stored with the Excel Business (Online) connector when it is
created. Remove the old connector and create a new one to get the latest parameters
and return values for the Run script action.

Some web APIs not available with Power

Automate flows
Some web APIs, such as TextEncoder and Crypto , may not be available when running
Office Scripts in Power Automate flows. See MDN Web APIs for a full list of web APIs.

Power Automate returns the error *API* is not defined , where *API* specifies a library
such as TextEncoder , when running a script that uses an unsupported API.

See also
Troubleshoot Office Scripts
Run Office Scripts with Power Automate
Excel Online (Business) connector reference documentation

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Improve the performance of your Office
Scripts
Article • 04/11/2024

The purpose of Office Scripts is to automate commonly performed series of tasks to

save you time. A slow script can feel like it doesn't speed up your workflow. Most of the
time, your script will be perfectly fine and run as expected. However, there are a few,
avoidable scenarios that can affect performance.

Reduce the number of read or write calls

The most common reason for a slow script is excessive communication with the
workbook. This is particularly noticeable when using Excel on the web. At certain times,
your script synchronizes its local data with that of the workbook. This means that any
write operations (such as workbook.addWorksheet() ) are only applied to the workbook
when this behind-the-scenes synchronization happens. Likewise, any read operations
(such as myRange.getValues() ) only get data from the workbook for the script at those
times. In either case, the script fetches information before it acts on the data. For
example, the following code will accurately log the number of rows in the used range.

TypeScript

let usedRange = workbook.getActiveWorksheet().getUsedRange();

let rowCount = usedRange.getRowCount();
// The script will read the range and row count from
// the workbook before logging the information.
console.log(rowCount);

Office Scripts APIs ensure any data in the workbook or script is accurate and up-to-date
when necessary. You don't need to worry about these synchronizations for your script to
run correctly. However, an awareness of this script-to-cloud communication can help
you avoid unneeded network calls.

Read workbook data outside of a loop

Any method that gets data from the workbook can trigger a network call. Rather than
repeatedly making the same call, you should save data locally whenever possible. This is
especially true when dealing with loops.
Consider a script to get the count of negative numbers in the used range of a
worksheet. The script needs to iterate over every cell in the used range. To do that, it
needs the range, the number of rows, and the number of columns. You should store
those as local variables before starting the loop. Otherwise, each iteration of the loop
will force a return to the workbook.

TypeScript

/**
* This script provides the count of negative numbers that are present
* in the used range of the current worksheet.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the working range.
let usedRange = workbook.getActiveWorksheet().getUsedRange();

// Save the values locally to avoid repeatedly asking the workbook.

let usedRangeValues = usedRange.getValues();

// Start the negative number counter.

let negativeCount = 0;

// Iterate over the entire range looking for negative numbers.

for (let i = 0; i < usedRangeValues.length; i++) {
for (let j = 0; j < usedRangeValues[i].length; j++) {
if (usedRangeValues[i][j] < 0) {
negativeCount++;
}
}
}

// Log the negative number count to the console.

console.log(negativeCount);
}

７ Note

As an experiment, try replacing usedRangeValues in the loop with

usedRange.getValues() . You may notice the script takes considerably longer to run

when dealing with large ranges.

Avoid using try...catch blocks in or

surrounding loops
We don't recommend using try...catch statements either in loops or surrounding
loops. This is for the same reason you should avoid reading data in a loop: each
iteration forces the script to synchronize with the workbook to make sure no error has
been thrown. Most errors can be avoided by checking objects returned from the
workbook. For example, the following script checks that the table returned by the
workbook exists before trying to add a row.

TypeScript

/**
* This script adds a row to "MyTable", if that table is present.
*/
function main(workbook: ExcelScript.Workbook) {
let table = workbook.getTable("MyTable");

// Check if the table exists.

if (table) {
// Add the row.
table.addRow(-1, ["2012", "Yes", "Maybe"]);
} else {
// Report the missing table.
console.log("MyTable not found.");
}
}

Remove unnecessary console.log statements

Console logging is a vital tool for debugging your scripts. However, it does force the
script to synchronize with the workbook to ensure the logged information is up-to-date.
Consider removing unnecessary logging statements (such as those used for testing)
before sharing your script. This typically won't cause a noticeable performance issue,
unless the console.log() statement is in a loop.

Pause calculations while the scripts runs

If your script changes a lot of values, it may trigger excessive recalculations. Control the
Excel calculation engine by setting the calculation mode to "manual" while your script
runs. Use Application.setCalculation to switch Excel to manually recalculate formulas. Be
sure to return the workbook to the original calculation mode when finished.

The following sample shows how to change the calculation mode. It also demonstrates
how to manually recalculate the workbook with Application.calculate.

TypeScript
/**
* This script adjusts the calculation mode of the workbook and makes a
manual recalculation.
* Wrap the CalculationMode changes around code that repeatedly updates
values.
*/
function main(workbook: ExcelScript.Workbook) {
const application = workbook.getApplication();

// Turn off automatic calculations during the script.

application.setCalculationMode(ExcelScript.CalculationMode.manual);

// ...

// Perform a manual recalculation of the workbook.

application.calculate(ExcelScript.CalculationType.fullRebuild);

// ...

// Resume automatic calculations after the script finishes.

application.setCalculationMode(ExcelScript.CalculationMode.automatic);
}

Case-by-case help
As the Office Scripts platform expands to work with Power Automate , Adaptive Cards,
and other cross-product features, the details of the script-workbook communication
become more intricate. If you need help making your script run faster, please reach out
through Microsoft Q&A. Be sure to tag your question with "office-scripts-dev" so
experts can find it and help.

See also
Fundamentals for Office Scripts in Excel
MDN web docs: Loops and iteration

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our  Provide product feedback
contributor guide.
Platform limits and requirements with
Office Scripts
Article • 10/03/2024

There are some platform limitations of which you should be aware when developing
Office Scripts. This article details the browser support and data limits for Office Scripts
for Excel.

Platform support
To use Office Scripts, you'll need the following.

For business and education

1. Excel on the web, Excel for Windows (Version 2210 or higher), or Excel for Mac.
2. OneDrive for Business.
3. Any of the following Microsoft 365 subscription licenses:

Office 365 Business

Office 365 Business Premium
Office 365 ProPlus
Office 365 ProPlus for Devices
Office 365 A3
Office 365 A5
Office 365 Enterprise E1
Office 365 Enterprise E3
Office 365 Enterprise E5
Office 365 F3

4. Internet connectivity with connected experiences enabled.

７ Note

If you meet these requirements and are still not seeing the Automate tab, it's
possible that your admin has disabled the feature or there's some other problem
with your environment. Please follow the steps under Automate tab not appearing
or Office Scripts unavailable to start using Office Scripts.
） Important

Data limits
There are limits on how much Excel data can be transferred at once and how many
individual Power Automate transactions can be conducted.

Excel
Excel on the web has the following limitations when making calls to the workbook
through a script.

Requests and responses are limited to 5MB.

A range is limited to five million cells.

When you exceed one of the Excel data limits, you receive this error message: "The
response payload size has exceeded the limit."

If you're encountering errors when dealing with large datasets, try using multiple smaller
ranges instead of larger ranges. For an example, see the Write a large dataset sample.
You can also use APIs like Range.getSpecialCells to target specific cells instead of large
ranges.

Excel limits that aren't specific to Office Scripts can be found in the article Excel
specifications and limits .

Power Automate
The following limitations with the Power Automate platform are the ones you're most
likely to encounter.

Each user is limited to 1,600 calls to the Run script action per day. This limit resets
at 12:00 AM UTC.
There's a 120-second timeout for synchronous Power Automate operations. For
long-running scripts, you must either optimize your script or split your Excel
automation into multiple script calls.
The maximum size of parameters passed to the Run script action is 30,000,000
bytes (28.6MB).

Additional Power Automate platform usage limitations can be found in the following
articles.

Limits and configuration in Power Automate

Known issues and limitations for the Excel Online (Business) connector

Power Automate specific restrictions

There are a few important differences between running a script in the Excel application
and running a script as part of a Power Automate flow.

No external calls from a script

You'll have to use HTTP with Azure AD or other equivalent actions to pull data from or
push it to an external service.

２ Warning

API behavior differences

Some APIs behave differently when run with Power Automate. Others fail due to their
reliance on the Excel UI. The full lists are found in Troubleshoot Office Scripts running in
Power Automate.

ISO strict Open XML workbooks aren't supported

The Excel Business (Online) connector's Run script action doesn't support workbooks
with the ISO strict version of the Excel Workbook file format . Flows with this type of
workbook return a "BadGateway" error when trying to run a script. This is due to
coauthoring restrictions. Please save workbooks as the standard Excel Workbook format
for use with Power Automate.

Teams support

） Important

When using Excel with Teams, Office Scripts are only supported in Teams on the
web (not Teams for Windows, Mac, iOS, or Android).

Government cloud support

Office Scripts aren't supported on GCC High or above. External calls from scripts may be
affected by firewall settings in other government clouds.

Third-party cookies for Excel on the web

Your browser needs third-party cookies enabled to show the Automate tab in Excel on
the web. Check your browser settings if the tab isn't being displayed. If you're using a
private browser session, you may need to re-enable this setting each time.

７ Note

Some browsers refer to this setting as "all cookies", instead of "third-party cookies".

How to adjust cookie settings in popular browsers

Chrome
Edge
Firefox
Safari

Conditional Access
Conditional Access policies restrict access to SharePoint and OneDrive for unmanaged
devices. If your device isn't managed by the tenant, you may not have access to specific
scripts, or may only be able to access them through the browser.

If you script is blocked by Conditional Access policies, you receive one of two error
messages. These messages also surface in Power Automate if your flow is run from an
unmanaged device.

"Due to organizational policies, you can’t access this resource from this untrusted
device."
"We can't find this script. It may have been deleted by another user." (If your
version of Excel is older.)

） Important

Administrators should consider blocking all access to Power Automate from

unmanaged devices. This process is detailed in the blog post Control Access to
Power Apps and Power Automate with Azure AD Conditional Access Policies .

API support on older Excel versions

Some Office Scripts APIs may not be supported by Excel for Windows or Excel for Mac,
especially older builds. These include newer APIs and APIs for web-only features. If a
script contains unsupported APIs, the Code Editor displays a warning. If you try to run
such a script, it won't run. Instead, the Script Run Status task pane displays a warning
message that says, "This script currently must be run on Excel for the web. Open the
workbook in the browser then try again, or contact the script owner for help."

Using an older version of Excel to open workbooks with scripts shared in them has no
effect on the script itself.

See also
Excel specifications and limits
Troubleshoot Office Scripts
Undo the effects of Office Scripts
Improve the performance of your Office Scripts
TypeScript restrictions in Office Scripts
Article • 11/17/2022

Office Scripts use the TypeScript language. For the most part, any TypeScript or
JavaScript code will work in Office Scripts. However, there are a few restrictions enforced
by the Code Editor to ensure your script works consistently and as intended with your
Excel workbook.

No 'any' type in Office Scripts

Writing types is optional in TypeScript, because the types can be inferred. However,
Office Scripts requires that a variable can't be of type any . Both explicit and implicit
any are not allowed in Office Scripts. These cases are reported as errors.

Explicit any
You cannot explicitly declare a variable to be of type any in Office Scripts (that is, let
value: any; ). The any type causes issues when processed by Excel. For example, a

Range needs to know that a value is a string , number , or boolean . You will receive a
compile-time error (an error prior to running the script) if any variable is explicitly
defined as the any type in the script.

In the previous screenshot, [2, 14] Explicit Any is not allowed indicates that line #2,
column #14 defines any type. This helps you locate the error.

To get around this issue, always define the type of the variable. If you are uncertain
about the type of a variable, you can use a union type . This can be useful for variables
that hold Range values, which can be of type string , number , or boolean (the type for
Range values is a union of those: string | number | boolean ).

Implicit any
TypeScript variable types can be implicitly defined. If the TypeScript compiler is unable
to determine the type of a variable (either because type is not defined explicitly or type
inference isn't possible), then it's an implicit any and you will receive a compilation-time
error.

The most common case on any implicit any is in a variable declaration, such as let
value; . There are two ways to avoid this:

Assign the variable to an implicitly identifiable type ( let value = 5; or let value
= workbook.getWorksheet(); ).

Explicitly type the variable ( let value: number; )

No inheriting Office Script classes or interfaces

Classes and interfaces that are created in your Office Script cannot extend or
implement Office Scripts classes or interfaces. In other words, nothing in the
ExcelScript namespace can have subclasses or subinterfaces.

Incompatible TypeScript functions

Office Scripts APIs cannot be used in the following:

Generator functions
Array.sort

eval is not supported

The JavaScript eval function is not supported for security reasons.

Restricted identifiers
The following words can't be used as identifiers in a script. They are reserved terms.

Excel
ExcelScript

console

Only arrow functions in array callbacks

Your scripts can only use arrow functions when providing callback arguments for
Array methods. You cannot pass any sort of identifier or "traditional" function to these
methods.

TypeScript

const myArray = [1, 2, 3, 4, 5, 6];

let filteredArray = myArray.filter((x) => {
return x % 2 === 0;
});
/*
The following code generates a compiler error in the Office Scripts Code
Editor.
filteredArray = myArray.filter(function (x) {
return x % 2 === 0;
});
*/

Unions of ExcelScript types and user-defined

types aren't supported
Office Scripts are converted at runtime from synchronous to asynchronous code blocks.
The communication with the workbook through promises is hidden from the script
creator. This conversion doesn't support union types that include ExcelScript types
and user-defined types. In that case, the Promise is returned to the script, but the Office
Script compiler doesn't expect it and the script creator can't interact with the Promise .

The following code sample shows an unsupported union between ExcelScript.Table

and a custom MyTable interface.
TypeScript

function main(workbook: ExcelScript.Workbook) {

const selectedSheet = workbook.getActiveWorksheet();

// This union is not supported.

const tableOrMyTable: ExcelScript.Table | MyTable =
selectedSheet.getTables()[0];

// `getName` returns a promise that can't be resolved by the script.

const name = tableOrMyTable.getName();

// This logs "{}" instead of the table name.

console.log(name);
}

interface MyTable {
getName(): string
}

Constructors don't support Office Scripts APIs

and console statements
console statements and many Office Scripts APIs require synchronization with the Excel
workbook. These synchronizations use await statements in compiled runtime version of
the script. await is not supported in constructors . If you need classes with
constructors, avoid using Office Scripts APIs or console statements in those code blocks.

The following code sample demonstrates this scenario. It generates an error that says
failed to load [code] [library] .

TypeScript

function main(workbook: ExcelScript.Workbook) {

class MyClass {
constructor() {
// Console statements and Office Scripts APIs aren't supported in
constructors.
console.log("This won't print.");
}
}

let test = new MyClass();

}
Performance warnings
The Code Editor's linter gives warnings if the script might have performance issues.
The cases and how to work around them are documented in Improve the performance
of your Office Scripts.

External API calls

See External API call support in Office Scripts for more information.

See also
Fundamentals for Office Scripts in Excel
Improve the performance of your Office Scripts
Undo the changes made by Office
Scripts
Article • 06/18/2024

You cannot undo changes made to the Excel workbook by a script with the Excel's Undo
command. Instead, you must restore a previous version of the workbook from your
cloud storage.

Version history
Office's version history is an easy way to restore an older workbook through the Excel
UI. The feature only works for files stored in OneDrive or SharePoint Online.

From the Excel workbook in which the script was run, use the following steps to undo
the effects.

1. Go to File > Info > Version History.

2. Select a version saved prior to the running the script.
3. Select Restore.

See also
View previous versions of Office files

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
be found on GitHub, where you Select a link to provide feedback:
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Office Scripts samples and scenarios
Article • 12/28/2023

This section contains Office Scripts based solutions that help end users achieve
automation of daily tasks. It contains realistic scenarios that users face and provides
detailed solutions.

Basics showcase common examples that make up larger scripts. They're

categorized based on a common Excel feature.
Quick scenarios are samples that are more involved or solve a particular problem.
Cross-application scenarios are samples that use Power Automate with one or
more Office Scripts as integral parts of the flow.
Real-world scenarios are a few larger samples framed as complete, real-world
solutions.
Contributions from the community are samples from members of the Office Scripts
community, often light-hearted in nature.

） Important

Be sure you meet the prerequisites for Office Scripts before trying the samples. The
requirements for your Microsoft 365 subscription and account are found under
Platform support.

Basics
ﾉ Expand table

Project Details

Ranges: Work with the grid These samples show how to work the Range object, which is
central to most scripts.

Conditional formatting samples These samples show how to apply different styles of
conditional formatting to a range.

Data validation samples: These samples show how to use data validation to mandate
Dropdown lists, prompts, and specific conditions for cell data and how the user is alerted
warning pop-ups to these rules.

Date samples A collection of samples that show how to translate between

JavaScript and Excel date formats.
Project Details

Image samples This sample adds an image to a workbook and copies an

image across sheets.

Row and column visibility samples A collection of samples that demonstrate how to show,
hide, and freeze rows and columns.

Table samples A collection of samples that show common interactions

with Excel tables.

Quick scenarios
Be sure to download the sample workbooks included with each sample. That lets you
skip setup and understand the core concept the sample demonstrates.

ﾉ Expand table

Project Details

Copy multiple Excel tables This sample combines data from multiple Excel tables into a single
into a single table table that includes all the rows.

Create a workbook table of This sample creates a table of contents with links to each
contents worksheet.

Count blank rows in a This sample detects if there are any blank rows in sheets where you
specific sheet or in all anticipate data to be present and then report the blank row count
sheets for usage in a Power Automate flow.

Manage calculation mode This sample shows how to use the calculation mode and calculate
in Excel methods in Excel using Office Scripts.

Move rows across tables This sample shows how to move rows across tables by saving
filters, then processing and reapplying the filters.

Notify people with This sample adds comments to a cell including @mentioning a
comments colleague.

Output Excel data as JSON This solution shows how to output Excel table data as JSON to use
in Power Automate.

Remove hyperlinks from This sample clears all of the hyperlinks from the current worksheet.
each cell in an Excel
worksheet

Set conditional formatting This sample applies formatting based on values in adjacent
for cross-column columns. It also gets user input through script parameters.
comparisons
Project Details

Use external fetch calls This sample uses fetch to get information from GitHub for the
script.

Cross-application scenarios
Use Power Automate to connect other applications to Excel. Have your scripts power
emails, forms, Teams messages, and more. These samples include step-by-step
instructions to create each Power Automate flow.

ﾉ Expand table

Project Details

Combine worksheets This sample uses Office Scripts and Power Automate to pull data from
into a single workbook other workbooks into a single workbook.

Convert CSV files to This sample uses Office Scripts and Power Automate to create .xlsx files
Excel workbooks from .csv files.

Cross-reference This sample uses Office Scripts and Power Automate to cross-reference
workbooks and validate information in different workbooks.

Email chart and table This sample uses Office Scripts and Power Automate actions to create a
images chart and send that chart as an image by email.

Record day-to-day This sample uses a scheduled Power Automate flow to record daily
changes in Excel and readings and report the changes.
report them with a flow

Run a script on all Excel This sample performs a set of automation tasks on all files situated in a
files in a folder folder on OneDrive for Business (can also be used for a SharePoint
folder). It performs calculations on the Excel files, adds formatting, and
inserts a comment that @mentions a colleague.

Write a large dataset This sample shows how to work with a large range as smaller
subranges. This helps flows handle data transfer limits and operation
timeouts.

Real-world scenarios
Office Scripts can automate parts of your daily routine. These day-to-day tasks often
exist in unique ecosystems, with Excel workbooks that are set up in particular ways.
These larger scenario samples demonstrate such real-world use-cases. They include
both the Office Scripts and the workbooks, so you can see the scenario from end to end.
ﾉ Expand table

Scenario Details

Analyze web This scenario features a script that parses web traffic records to determine
downloads a user's country of origin. It showcases the skills of text parsing, using
subfunctions in scripts, applying conditional formatting, and working with
tables.

Fetch and graph This scenario uses an Office Script to pull data from an external source
water-level data (the NOAA Tides and Currents database ) and graph the resulting
from NOAA information. It highlights the skills of using fetch to get data and using
charts.

Grade calculator This scenario features a script that validates an instructor's record for their
class's grades. It showcases the skills of error checking, cell formatting, and
regular expressions.

Schedule interviews This scenario shows how to use an Excel spreadsheet to manage interview
in Teams meeting times and make a flow to schedules meetings in Teams.

Task reminders as This scenario uses an Office Script in a Power Automate flow to send
Teams cards reminders to coworkers to update a project's status. It highlights the skills
of Power Automate integration and data transfer to and from scripts.

Community contributions and fun samples

We welcome contributions from our Office Scripts community! Feel free to create a
pull request for review.

ﾉ Expand table

Project Details

Game of Life The "Ready Player Zero" blog by Yutao Huang on the Excel Tech Community
includes a script to model John Conway's The Game of Life .

Punch clock This script was contributed by Brian Gonzalez . The scenario features a
button script and a script button that records the current time.

Seasons greetings This script was contributed by Leslie Black in the spirit of the holiday
animation season! It's a fun script that shows a singing Christmas tree in Excel using
Office Scripts.

Excel Automation A series of scripts contributed by Peter Kalmström , including a recurring

tasks management solution which involves Office Scripts, Power Automate,
and Power Apps. The series includes videos and articles describing the
solution.
Leave a comment
Feel free to leave a comment, make a suggestion, or log an issue by using the Feedback
section at the bottom of the specific sample's documentation page.
Ranges: Work with the grid in Office
Scripts
Article • 12/05/2023

The following samples are simple scripts for you to try on your own workbooks. They
form the building blocks to larger solutions. Expand these scripts to extend your
solution and solve common problems.

Read and log one cell

This sample reads the value of A1 and prints it to the console.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
let selectedSheet = workbook.getActiveWorksheet();

// Get the value of cell A1.

let range = selectedSheet.getRange("A1");

// Print the value of A1.

console.log(range.getValue());
}

Read the active cell

This script logs the value of the current active cell. If multiple cells are selected, the top-
leftmost cell will be logged.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current active cell in the workbook.
let cell = workbook.getActiveCell();

// Log that cell's value.

console.log(`The current cell's value is ${cell.getValue()}`);
}

Add data to a range

This script adds a set of values to a new worksheet. The values start in cell A1. The data
used in this script is predefined, but could be sourced from other places in or out of the
workbook.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// The getData call could be replaced by input from Power Automate or a
fetch call.
const data = getData();

// Create a new worksheet and switch to it.

const newWorksheet = workbook.addWorksheet("DataSheet");
newWorksheet.activate();

// Get a range matching the size of the data.

const dataRange = newWorksheet.getRangeByIndexes(
0,
0,
data.length,
data[0].length);

// Set the data as the values in the range.

dataRange.setValues(data);
}

function getData(): string[][] {

return [["Abbreviation", "State/Province", "Country"],
["AL", "Alabama", "USA"],
["AK", "Alaska", "USA"],
["AZ", "Arizona", "USA"],
["AR", "Arkansas", "USA"],
["CA", "California", "USA"],
["CO", "Colorado", "USA"],
["CT", "Connecticut", "USA"],
["DE", "Delaware", "USA"],
["DC", "District of Columbia", "USA"],
["FL", "Florida", "USA"],
["GA", "Georgia", "USA"],
["HI", "Hawaii", "USA"],
["ID", "Idaho", "USA"],
["IL", "Illinois", "USA"],
["IN", "Indiana", "USA"],
["IA", "Iowa", "USA"],
["KS", "Kansas", "USA"],
["KY", "Kentucky", "USA"],
["LA", "Louisiana", "USA"],
["ME", "Maine", "USA"],
["MD", "Maryland", "USA"],
["MA", "Massachusetts", "USA"],
["MI", "Michigan", "USA"],
["MN", "Minnesota", "USA"],
["MS", "Mississippi", "USA"],
["MO", "Missouri", "USA"],
["MT", "Montana", "USA"],
["NE", "Nebraska", "USA"],
["NV", "Nevada", "USA"],
["NH", "New Hampshire", "USA"],
["NJ", "New Jersey", "USA"],
["NM", "New Mexico", "USA"],
["NY", "New York", "USA"],
["NC", "North Carolina", "USA"],
["ND", "North Dakota", "USA"],
["OH", "Ohio", "USA"],
["OK", "Oklahoma", "USA"],
["OR", "Oregon", "USA"],
["PA", "Pennsylvania", "USA"],
["RI", "Rhode Island", "USA"],
["SC", "South Carolina", "USA"],
["SD", "South Dakota", "USA"],
["TN", "Tennessee", "USA"],
["TX", "Texas", "USA"],
["UT", "Utah", "USA"],
["VT", "Vermont", "USA"],
["VA", "Virginia", "USA"],
["WA", "Washington", "USA"],
["WV", "West Virginia", "USA"],
["WI", "Wisconsin", "USA"],
["WY", "Wyoming", "USA"],
["AB", "Alberta", "CAN"],
["BC", "British Columbia", "CAN"],
["MB", "Manitoba", "CAN"],
["NB", "New Brunswick", "CAN"],
["NL", "Newfoundland and Labrador", "CAN"],
["NT", "Northwest Territory", "CAN"],
["NS", "Nova Scotia", "CAN"],
["NU", "Nunavut Territory", "CAN"],
["ON", "Ontario", "CAN"],
["PE", "Prince Edward Island", "CAN"],
["QC", "Quebec", "CAN"],
["SK", "Saskatchewan", "CAN"],
["YT", "Yukon Territory", "CAN"]];
}

Change an adjacent cell

This script gets adjacent cells using relative references. Note that if the active cell is on
the top row, part of the script fails because it references the cell above the currently
selected one.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the currently active cell in the workbook.
let activeCell = workbook.getActiveCell();
console.log(`The active cell's address is: ${activeCell.getAddress()}`);

// Get the cell to the right of the active cell and set its value and
color.
let rightCell = activeCell.getOffsetRange(0,1);
rightCell.setValue("Right cell");
console.log(`The right cell's address is: ${rightCell.getAddress()}`);
rightCell.getFormat().getFont().setColor("Magenta");
rightCell.getFormat().getFill().setColor("Cyan");

// Get the cell to the above of the active cell and set its value and
color.
// Note that this operation will fail if the active cell is in the top
row.
let aboveCell = activeCell.getOffsetRange(-1, 0);
aboveCell.setValue("Above cell");
console.log(`The above cell's address is: ${aboveCell.getAddress()}`);
aboveCell.getFormat().getFont().setColor("White");
aboveCell.getFormat().getFill().setColor("Black");
}

Change all adjacent cells

This script copies the formatting in the active cell to the neighboring cells. Note that this
script only works when the active cell isn't on an edge of the worksheet.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the active cell.
let activeCell = workbook.getActiveCell();

// Get the cell that's one row above and one column to the left of the
active cell.
let cornerCell = activeCell.getOffsetRange(-1,-1);

// Get a range that includes all the cells surrounding the active cell.
let surroundingRange = cornerCell.getResizedRange(2, 2)

// Copy the formatting from the active cell to the new range.
surroundingRange.copyFrom(
activeCell, /* The source range. */
ExcelScript.RangeCopyType.formats /* What to copy. */
);
}

Change each individual cell in a range

This script loops over the currently selected range. It clears the current formatting and
sets the fill color in each cell to a random color.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the currently selected range.
let range = workbook.getSelectedRange();

// Get the size boundaries of the range.

let rows = range.getRowCount();
let cols = range.getColumnCount();

// Clear any existing formatting.

range.clear(ExcelScript.ClearApplyTo.formats);

// Iterate over the range.

for (let row = 0; row < rows; row++) {
for (let col = 0; col < cols; col++) {
// Generate a random hexadecimal color code.
let colorString = `#${Math.random().toString(16).substr(-6)}`;

// Set the color of the current cell to that random hexadecimal code.
range.getCell(row, col).getFormat().getFill().setColor(colorString);
}
}
}

Get groups of cells based on special criteria

This script gets all the blank cells in the current worksheet's used range. It then
highlights all those cells with a yellow background.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current used range.
let range = workbook.getActiveWorksheet().getUsedRange();

// Get all the blank cells.

let blankCells =
range.getSpecialCells(ExcelScript.SpecialCellType.blanks);

// Highlight the blank cells with a yellow background.

blankCells.getFormat().getFill().setColor("yellow");
}
Formulas
Ranges have both values and formulas. The formula is the expression to be evaluated.
The value is the result of that expression.

Single formula
This script sets a cell's formula, then displays how Excel stores the cell's formula and
value separately.

TypeScript

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Set A1 to 2.
let a1 = selectedSheet.getRange("A1");
a1.setValue(2);

// Set B1 to the formula =(2*A1), which should equal 4.

let b1 = selectedSheet.getRange("B1");
b1.setFormula("=(2*A1)");

// Log the current results for `getFormula` and `getValue` at B1.

console.log(`B1 - Formula: ${b1.getFormula()} | Value: ${b1.getValue()}`);
}

Handle a #SPILL! error returned from a formula

This script transposes the range "A1:D2" to "A4:B7" by using the TRANSPOSE function. If
the transpose results in a #SPILL error, it clears the target range and applies the formula
again.

TypeScript

function main(workbook: ExcelScript.Workbook) {

let sheet = workbook.getActiveWorksheet();
// Use the data in A1:D2 for the sample.
let dataAddress = "A1:D2"
let inputRange = sheet.getRange(dataAddress);

// Place the transposed data starting at A4.

let targetStartCell = sheet.getRange("A4");

// Compute the target range.

let targetRange =
targetStartCell.getResizedRange(inputRange.getColumnCount() - 1,
inputRange.getRowCount() - 1);

// Call the transpose helper function.

targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`);

// Check if the range update resulted in a spill error.

let checkValue = targetStartCell.getValue() as string;
if (checkValue === '#SPILL!') {
// Clear the target range and call the transpose function again.
console.log("Target range has data that is preventing update. Clearing
target range.");
targetRange.clear();
targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`);
}

// Select the transposed range to highlight it.

targetRange.select();
}

Replace all formulas with their result values

This script replaces every cell in the current worksheet that contains a formula with the
result of that formula. This means there won't be any formulas after the script is run,
only values.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the ranges with formulas.
let sheet = workbook.getActiveWorksheet();
let usedRange = sheet.getUsedRange();
let formulaCells =
usedRange.getSpecialCells(ExcelScript.SpecialCellType.formulas);

// In each formula range: get the current value, clear the contents, and
set the value as the old one.
// This removes the formula but keeps the result.
formulaCells.getAreas().forEach((range) => {
let currentValues = range.getValues();
range.clear(ExcelScript.ClearApplyTo.contents);
range.setValues(currentValues);
});
}

Suggest new samples

We welcome suggestions for new samples. If there's a common scenario that would
help other script developers, please tell us in the Feedback section at the bottom of the
page.

See also
Sudhi Ramamurthy's "Range basics" on YouTube
Office Scripts samples and scenarios
Tutorial: Create and format an Excel table
Conditional formatting samples
Article • 12/29/2023

Conditional formatting in Excel applies formatting to cells based on specific conditions

or rules. These formats automatically adjust when the data changes, so your script
doesn't need to be run multiple times. This page contains a collection of Office Scripts
that demonstrate various conditional formatting options.

This sample workbook contains worksheets ready to test with the sample scripts.

Download the sample workbook

Cell value
Cell value conditional formatting applies a format to every cell that contains a value
meeting a given criteria. This helps quickly spot important data points.

The following sample applies cell value conditional formatting to a range. Any value less
than 60 will have the cell's fill color changed and the font made italic.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range to format.
const sheet = workbook.getWorksheet("CellValue");
const ratingColumn = sheet.getRange("B2:B12");
sheet.activate();

// Add cell value conditional formatting.

const cellValueConditionalFormatting =

ratingColumn.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValu
e).getCellValue();

// Create the condition, in this case when the cell value is less than
60
let rule: ExcelScript.ConditionalCellValueRule = {
formula1: "60",
operator: ExcelScript.ConditionalCellValueOperator.lessThan
};
cellValueConditionalFormatting.setRule(rule);

// Set the format to apply when the condition is met.

let format = cellValueConditionalFormatting.getFormat();
format.getFill().setColor("yellow");
format.getFont().setItalic(true);
}

Color scale
Color scale conditional formatting applies a color gradient across a range. The cells with
the minimum and maximum values of the range use the colors specified, with other cells
scaled proportionally. An optional midpoint color provides more contrast.

This following sample applies a red, white, and blue color scale to the selected range.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range to format.
const sheet = workbook.getWorksheet("ColorScale");
const dataRange = sheet.getRange("B2:M13");
sheet.activate();

// Create a new conditional formatting object by adding one to the

range.
const conditionalFormatting =
dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.colorScale)
;

// Set the colors for the three parts of the scale: minimum, midpoint,
and maximum.
conditionalFormatting.getColorScale().setCriteria({
minimum: {
color: "#5A8AC6", /* A pale blue. */
type:
ExcelScript.ConditionalFormatColorCriterionType.lowestValue
},
midpoint: {
color: "#FCFCFF", /* Slightly off-white. */
formula: '=50', type:
ExcelScript.ConditionalFormatColorCriterionType.percentile
},
maximum: {
color: "#F8696B", /* A pale red. */
type:
ExcelScript.ConditionalFormatColorCriterionType.highestValue
}
});
}

Data bar
Data bar conditional formatting adds a partially-filled bar in the background of a cell.
The fullness of the bar is defined by the value in the cell and the range specified by the
format.

The following sample creates data bar conditional formatting on the selected ranged.
The scale of the data bar goes from 0 to 1200.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range to format.
const sheet = workbook.getWorksheet("DataBar");
const dataRange = sheet.getRange("B2:D5");
sheet.activate();

// Create new conditional formatting on the range.

const format =
dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.dataBar);
const dataBarFormat = format.getDataBar();

// Set the lower bound of the data bar formatting to be 0.

const lowerBound: ExcelScript.ConditionalDataBarRule = {
type: ExcelScript.ConditionalFormatRuleType.number,
formula: "0"
};
dataBarFormat.setLowerBoundRule(lowerBound);
// Set the upper bound of the data bar formatting to be 1200.
const upperBound: ExcelScript.ConditionalDataBarRule = {
type: ExcelScript.ConditionalFormatRuleType.number,
formula: "1200"
};
dataBarFormat.setUpperBoundRule(upperBound);
}

Icon set
Icon set conditional formatting adds icons to each cell in a range. The icons come from
a specified set. Icons are applied based on an ordered array of criteria, with each
criterion mapping to a single icon.

The following sample applies the "three traffic light" icon set conditional formatting to a
range.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range to format.
const sheet = workbook.getWorksheet("IconSet");
const dataRange = sheet.getRange("B2:B12");
sheet.activate();

// Create icon set conditional formatting on the range.

const conditionalFormatting =
dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.iconSet);

// Use the "3 Traffic Lights (Unrimmed)" set.

conditionalFormatting.getIconSet().setStyle(ExcelScript.IconSet.threeTraffic
Lights1);
conditionalFormatting.getIconSet().setCriteria([
{ // Use the red light as the default for positive values.
formula: '=0', operator:
ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
type: ExcelScript.ConditionalFormatIconRuleType.number
},
{ // The yellow light is applied to all values 6 and greater. The
replaces the red light when applicable.
formula: '=6', operator:
ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
type: ExcelScript.ConditionalFormatIconRuleType.number
},
{ // The green light is applied to all values 8 and greater. As with
the yellow light, the icon is replaced when the new criteria is met.
formula: '=8', operator:
ExcelScript.ConditionalIconCriterionOperator.greaterThanOrEqual,
type: ExcelScript.ConditionalFormatIconRuleType.number
}
]);
}

Preset
Preset conditional formatting applies a specified format to a range based on common
scenarios, such as blank cells and duplicate values. The full list of preset criteria is
provided by the ConditionalFormatPresetCriterion enum.

The following sample gives a yellow fill to any blank cell in the range.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range to format.
const sheet = workbook.getWorksheet("Preset");
const dataRange = sheet.getRange("B2:D5");
sheet.activate();

// Add new conditional formatting to that range.

const conditionalFormat = dataRange.addConditionalFormat(
ExcelScript.ConditionalFormatType.presetCriteria);

// Set the conditional formatting to apply a yellow fill.

const presetFormat = conditionalFormat.getPreset();
presetFormat.getFormat().getFill().setColor("yellow");

// Set a rule to apply the conditional format when cells are left blank.
const blankRule: ExcelScript.ConditionalPresetCriteriaRule = {
criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks
};
presetFormat.setRule(blankRule);
}

Text comparison
Text comparison conditional formatting formats cells based on their text content. The
formatting is applied when the text begins with, contains, ends with, or doesn't contain
the given substring.

The following sample marks any cell in the range that contains the text "review".

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range to format.
const sheet = workbook.getWorksheet("TextComparison");
const dataRange = sheet.getRange("B2:B6");
sheet.activate();

// Add conditional formatting based on the text in the cells.

const textConditionFormat = dataRange.addConditionalFormat(
ExcelScript.ConditionalFormatType.containsText).getTextComparison();

// Set the conditional format to provide a light red fill and make the
font bold.
textConditionFormat.getFormat().getFill().setColor("#F8696B");
textConditionFormat.getFormat().getFont().setBold(true);

// Apply the condition rule that the text contains with "review".
const textRule: ExcelScript.ConditionalTextComparisonRule = {
operator: ExcelScript.ConditionalTextOperator.contains,
text: "review"
};
textConditionFormat.setRule(textRule);
}
Top/bottom
Top/bottom conditional formatting marks the highest or lowest values in a range. The
highs and lows are based on either raw values or percentages.

The following sample applies conditional formatting to show the two highest numbers
in the range.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range to format.
const sheet = workbook.getWorksheet("TopBottom");
const dataRange = sheet.getRange("B2:D5");
sheet.activate();

// Set the fill color to green and the font to bold for the top 2 values
in the range.
const topBottomFormat =
dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.topBottom).
getTopBottom();
topBottomFormat.getFormat().getFill().setColor("green");
topBottomFormat.getFormat().getFont().setBold(true);
topBottomFormat.setRule({
rank: 2, /* The numeric threshold. */
type: ExcelScript.ConditionalTopBottomCriterionType.topItems /* The
type of the top/bottom condition. */
});
}

Custom conditions
Custom conditional formatting allows for complex formulas to define when formatting is
applied. Use this when the other options aren't enough.

The following sample sets a custom conditional formatting on the selected range. A
light-green fill and bold font are applied to a cell if the value is larger than the value in
the row's previous column.
TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the range to format.
const sheet = workbook.getWorksheet("Custom");
const dataRange = sheet.getRange("B2:H2");
sheet.activate();

// Apply a rule for positive change from the previous column.

const positiveChange =
dataRange.addConditionalFormat(ExcelScript.ConditionalFormatType.custom).get
Custom();
positiveChange.getFormat().getFill().setColor("lightgreen");
positiveChange.getFormat().getFont().setBold(true);
positiveChange.getRule().setFormula(
`=${dataRange.getCell(0,
0).getAddress()}>${dataRange.getOffsetRange(0, -1).getCell(0,
0).getAddress()}`
);
}
Data validation: dropdown lists,
prompts, and warning pop-ups
Article • 09/26/2023

Data validation helps the user ensure consistency in a worksheet. Use these features to
limit what can be entered into a cell and provide warnings or errors to users when those
conditions aren't met. To learn more about data validation in Excel, see Apply data
validation to cells .

Create a dropdown list using data validation

The following sample creates a dropdown selection list for a cell. It uses the existing
values of the selected range as the choices for the list.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the values for data validation.
const selectedRange = workbook.getSelectedRange();
const rangeValues = selectedRange.getValues();

// Convert the values into a comma-delimited string.

let dataValidationListString = "";
rangeValues.forEach((rangeValueRow) => {
rangeValueRow.forEach((value) => {
dataValidationListString += value + ",";
});
});

// Clear the old range.

selectedRange.clear(ExcelScript.ClearApplyTo.contents);

// Apply the data validation to the first cell in the selected range.
const targetCell = selectedRange.getCell(0,0);
const dataValidation = targetCell.getDataValidation();

// Set the content of the dropdown list.

dataValidation.setRule({
list: {
inCellDropDown: true,
source: dataValidationListString
}
});
}

Add a prompt to a range

This example creates a prompt note that appears when a user enters the given cells. This
is used to remind users about input requirements, without strict enforcement.

TypeScript

/**
* This script creates a text prompt that's shown in C2:C8 when a user
enters the cell.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the data validation object for C2:C8 in the current worksheet.
const selectedSheet = workbook.getActiveWorksheet();
const dataValidation =
selectedSheet.getRange("C2:C8").getDataValidation();

// Clear any previous validation to avoid conflicts.

dataValidation.clear();

// Create a prompt to remind users to only enter first names in this

column.
const prompt: ExcelScript.DataValidationPrompt = {
showPrompt: true,
title: "First names only",
message: "Only enter the first name of the employee, not the full
name."
}
dataValidation.setPrompt(prompt);
}
Alert the user when invalid data is entered
The following sample script prevents the user from entering anything other than
positive numbers into a range. If they try to put anything else, an error message pops up
and indicates the problem.

TypeScript

/**
* This script creates a data validation rule for the range B2:B5.
* All values in that range must be a positive number.
* Attempts to enter other values are blocked and an error message appears.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the range B2:B5 in the active worksheet.
const currentSheet = workbook.getActiveWorksheet();
const positiveNumberOnlyCells = currentSheet.getRange("B2:B5");

// Create a data validation rule to only allow positive numbers.

const positiveNumberValidation: ExcelScript.BasicDataValidation = {
formula1: "0",
operator: ExcelScript.DataValidationOperator.greaterThan
};
const positiveNumberOnlyRule: ExcelScript.DataValidationRule = {
wholeNumber: positiveNumberValidation
};

// Set the rule on the range.

const rangeDataValidation = positiveNumberOnlyCells.getDataValidation();
rangeDataValidation.setRule(positiveNumberOnlyRule);

// Create an alert to appear when data other than positive numbers are
entered.
const positiveNumberOnlyAlert: ExcelScript.DataValidationErrorAlert = {
message: "Positive numbers only.",
showAlert: true,
style: ExcelScript.DataValidationAlertStyle.stop,
title: "Invalid data"
};
rangeDataValidation.setErrorAlert(positiveNumberOnlyAlert);
}
JavaScript Date samples
Article • 09/11/2023

These samples show how to use the JavaScript Date object.

Write the current date and time

The following sample gets the current date and time and then writes those values to
two cells in the active worksheet.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the cells at A1 and B1.
let dateRange = workbook.getActiveWorksheet().getRange("A1");
let timeRange = workbook.getActiveWorksheet().getRange("B1");

// Get the current date and time with the JavaScript Date object.
let date = new Date(Date.now());

// Add the date string to A1.

dateRange.setValue(date.toLocaleDateString());

// Add the time string to B1.

timeRange.setValue(date.toLocaleTimeString());
}

Read an Excel date

This sample reads a date that's stored in Excel and translates it to a JavaScript Date
object. It uses the date's numeric serial number as input for the JavaScript Date . This
serial number is described in the NOW() function article.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Read a date at cell A1 from Excel.
let dateRange = workbook.getActiveWorksheet().getRange("A1");

// Convert the Excel date to a JavaScript Date object.

let excelDateValue = dateRange.getValue() as number;
let javaScriptDate = new Date(Math.round((excelDateValue - 25569) * 86400
* 1000));
console.log(javaScriptDate);
}

See also
Use built-in JavaScript objects in Office Scripts
Add images to a workbook
Article • 10/25/2023

This sample shows how to work with images using an Office Script in Excel.

Scenario
Images help with branding, visual identity, and templates. They help make a workbook
more than just a giant table.

The first sample copies an image from one worksheet to another. This could be used to
put your company's logo in the same position on every sheet.

The second sample copies an image from a URL. This could be used to copy photos that
a colleague stored in a shared folder to a related workbook. Please note that this sample
can't be adapted to work with a local image file, as that isn't supported by Office Scripts.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Copy an image across worksheets

Add the following script to the sample workbook and try the sample yourself!

TypeScript

/**
* This script transfers an image from one worksheet to another.
*/
function main(workbook: ExcelScript.Workbook)
{
// Get the worksheet with the image on it.
let firstWorksheet = workbook.getWorksheet("FirstSheet");

// Get the first image from the worksheet.

// If a script added the image, you could add a name to make it easier to
find.
let image: ExcelScript.Image;
firstWorksheet.getShapes().forEach((shape, index) => {
if (shape.getType() === ExcelScript.ShapeType.image) {
image = shape.getImage();
return;
}
});

// Copy the image to another worksheet.

image.getShape().copyTo("SecondSheet");
}

Sample code: Add an image from a URL to a

workbook

） Important

This sample won't work in Power Automate because of the fetch call.

TypeScript

async function main(workbook: ExcelScript.Workbook) {

// Fetch the image from a URL.
const link = "https://raw.githubusercontent.com/OfficeDev/office-scripts-
docs/master/docs/images/git-octocat.png";
const response = await fetch(link);

// Store the response as an ArrayBuffer, since it is a raw image file.

const data = await response.arrayBuffer();

// Convert the image data into a base64-encoded string.

const image = convertToBase64(data);

// Add the image to a worksheet.

workbook.getWorksheet("WebSheet").addImage(image);
}

/**
* Converts an ArrayBuffer containing a .png image into a base64-encoded
string.
*/
function convertToBase64(input: ArrayBuffer) {
const uInt8Array = new Uint8Array(input);
const count = uInt8Array.length;

// Allocate the necessary space up front.

const charCodeArray = new Array(count) as string[];

// Convert every entry in the array to a character.

for (let i = count; i >= 0; i--) {
charCodeArray[i] = String.fromCharCode(uInt8Array[i]);
}
// Convert the characters to base64.
const base64 = btoa(charCodeArray.join(''));
return base64;
}
Row and column visibility samples
Article • 09/11/2023

These samples demonstrate how to show, hide, and freeze rows and columns.

Hide columns
This script hides columns "D", "F", and "J".

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
const sheet = workbook.getActiveWorksheet();

// Hide columns D, F, and J.

sheet.getRange("D:D").setColumnHidden(true);
sheet.getRange("F:F").setColumnHidden(true);
sheet.getRange("J:J").setColumnHidden(true);
}

Show all rows and columns

This script gets the worksheet's used range, checks if there are any hidden rows and
columns, and shows them.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the currently selected sheet.
const selectedSheet = workbook.getActiveWorksheet();

// Get the entire data range.

const range = selectedSheet.getUsedRange();

// If the used range is empty, end the script.

if (!range) {
console.log(`No data on this sheet.`)
return;
}

// If no columns are hidden, log message, else show columns.

if (range.getColumnHidden() == false) {
console.log(`No columns hidden`);
} else {
range.setColumnHidden(false);
}

// If no rows are hidden, log message, else, show rows.

if (range.getRowHidden() == false) {
console.log(`No rows hidden`);
} else {
range.setRowHidden(false);
}
}

Freeze currently selected cells

This script checks what cells are currently selected and freezes that selection, so those
cells are always visible.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the currently selected sheet.
const selectedSheet = workbook.getActiveWorksheet();

// Get the current selected range.

const selectedRange = workbook.getSelectedRange();

// If no cells are selected, end the script.

if (!selectedRange) {
console.log(`No cells in the worksheet are selected.`);
return;
}

// Log the address of the selected range

console.log(`Selected range for the worksheet:
${selectedRange.getAddress()}`);

// Freeze the selected range.

selectedSheet.getFreezePanes().freezeAt(selectedRange);
}
Table samples
Article • 10/03/2024

These samples showcase common interactions with Excel tables.

Create a sorted table

This sample creates a table from the current worksheet's used range, then sorts it based
on the first column.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
const selectedSheet = workbook.getActiveWorksheet();

// Create a table with the used cells.

const usedRange = selectedSheet.getUsedRange();
const newTable = selectedSheet.addTable(usedRange, true);

// Sort the table using the first column.

newTable.getSort().apply([{ key: 0, ascending: true }]);
}

Filter a table
This sample filters an existing table using the values in one of the columns.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table in the workbook named "StationTable".
const table = workbook.getTable("StationTable");

// Get the "Station" table column for the filter.

const stationColumn = table.getColumnByName("Station");

// Apply a filter to the table that will only show rows

// with a value of "Station-1" in the "Station" column.
stationColumn.getFilter().applyValuesFilter(["Station-1"]);
}

Filter out one value

The previous sample filters a table based on a list of included values. To exclude a
particular value from the table, you need to provide the list of every other value in the
column. This sample uses a function columnToSet to convert a column into a set of
unique values. That set then has the excluded value ("Station-1") removed.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table in the workbook named "StationTable".
const table = workbook.getTable("StationTable");

// Get the "Station" table column for the filter.

const stationColumn = table.getColumnByName("Station");

// Get a list of unique values in the station column.

const stationSet = columnToSet(stationColumn);

// Apply a filter to the table that will only show rows

// that don't have a value of "Station-1" in the "Station" column.
stationColumn.getFilter().applyValuesFilter(stationSet.filter((value) => {
return value !== "Station-1";
}));
}

/**
* Convert a column into a set so it only contains unique values.
*/
function columnToSet(column: ExcelScript.TableColumn): string[] {
const range = column.getRangeBetweenHeaderAndTotal().getValues() as
string[][];
const columnSet: string[] = [];
range.forEach((value) => {
if (!columnSet.includes(value[0])) {
columnSet.push(value[0]);
}
});

return columnSet;
}

Remove table column filters

This sample removes the filters from a table column, based on the active cell location.
The script detects if the cell is part of a table, determines the table column, and clears
any filters that are applied on it.

Download table-with-filter.xlsx for a ready-to-use workbook. Add the following script to

try the sample yourself!
TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the active cell.
const cell = workbook.getActiveCell();

// Get the tables associated with that cell.

// Since tables can't overlap, this will be one table at most.
const currentTable = cell.getTables()[0];

// If there's no table on the selection, end the script.

if (!currentTable) {
console.log("The selection is not in a table.");
return;
}

// Get the table header above the current cell by referencing its column.
const entireColumn = cell.getEntireColumn();
const intersect = entireColumn.getIntersection(currentTable.getRange());
const headerCellValue = intersect.getCell(0, 0).getValue() as string;

// Get the TableColumn object matching that header.

const tableColumn = currentTable.getColumnByName(headerCellValue);

// Clear the filters on that table column.

tableColumn.getFilter().clear();
}

Before clearing column filter (notice the active cell)

After clearing column filter

 Tip

If you wish to learn more about how to save the filter prior to clearing it (and re-
apply later), see Move rows across tables by saving filters, a more advanced
sample.

Dynamically reference table values

This script uses the "@COLUMN_NAME" syntax to set formulas in a table column. The
column names in the table can be changed without changing this script.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
const table = workbook.getTable("Profits");

// Get the column names for columns 2 and 3.

// Note that these are 1-based indices.
const nameOfColumn2 = table.getColumn(2).getName();
const nameOfColumn3 = table.getColumn(3).getName();

// Set the formula of the fourth column to be the product of the values
found
// in that row's second and third columns.
const combinedColumn = table.getColumn(4).getRangeBetweenHeaderAndTotal();
combinedColumn.setFormula(`=[@[${nameOfColumn2}]]*[@[${nameOfColumn3}]]`);
}

Before the script

ﾉ Expand table

Month Price Units Sold Total

Jan 45 5

Feb 45 3

Mar 45 6

After the script

ﾉ Expand table

Month Price Units Sold Total

Jan 45 5 225

Feb 45 3 135

Mar 45 6 270
Combine data from multiple Excel tables
into a single table
Article • 10/25/2023

This sample combines data from multiple Excel tables into a single table that includes all
the rows. It assumes that all tables being used have the same structure.

There are two variations of this script:

1. The first script combines all tables in the Excel file.

2. The second script selectively gets tables within a set of worksheets.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Combine data from multiple

Excel tables into a single table
Add the following script to the sample workbook and try the sample yourself!

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Delete the "Combined" worksheet, if it's present.
workbook.getWorksheet('Combined')?.delete();

// Create a new worksheet named "Combined" for the combined table.

const newSheet = workbook.addWorksheet('Combined');

// Get the header values for the first table in the workbook.
// This also saves the table list before we add the new, combined table.
const tables = workbook.getTables();
const headerValues = tables[0].getHeaderRowRange().getTexts();
console.log(headerValues);

// Copy the headers on a new worksheet to an equal-sized range.

const targetRange =
newSheet.getRange('A1').getResizedRange(headerValues.length-1,
headerValues[0].length-1);
targetRange.setValues(headerValues);
// Add the data from each table in the workbook to the new table.
const combinedTable = newSheet.addTable(targetRange.getAddress(), true);
for (let table of tables) {
let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts();
let rowCount = table.getRowCount();

// If the table is not empty, add its rows to the combined table.
if (rowCount > 0) {
combinedTable.addRows(-1, dataValues);
}
}
}

Sample code: Combine data from multiple

Excel tables in select worksheets into a single
table
Download the sample file tables-select-copy.xlsx and use it with the following script to
try it out yourself!

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Set the worksheet names to get tables from.
const sheetNames = ['Sheet1', 'Sheet2', 'Sheet3'];

// Delete the "Combined" worksheet, if it's present.

workbook.getWorksheet('Combined')?.delete();

// Create a new worksheet named "Combined" for the combined table.

const newSheet = workbook.addWorksheet('Combined');

// Create a new table with the same headers as the other tables.
const headerValues = workbook.getWorksheet(sheetNames[0]).getTables()
[0].getHeaderRowRange().getTexts();
const targetRange =
newSheet.getRange('A1').getResizedRange(headerValues.length-1,
headerValues[0].length-1);
targetRange.setValues(headerValues);
const combinedTable = newSheet.addTable(targetRange.getAddress(), true);

// Go through each listed worksheet and get their tables.

sheetNames.forEach((sheet) => {
const tables = workbook.getWorksheet(sheet).getTables();
for (let table of tables) {
// Get the rows from the tables.
let dataValues = table.getRangeBetweenHeaderAndTotal().getTexts();
let rowCount = table.getRowCount();
// If there's data in the table, add it to the combined table.
if (rowCount > 0) {
combinedTable.addRows(-1, dataValues);
}
}
});
}

Training video: Combine data from multiple

Excel tables into a single table
Watch Sudhi Ramamurthy walk through this sample on YouTube .
Create a workbook table of contents
Article • 10/25/2023

This sample shows how to create a table of contents for the workbook. Each entry in the
table of contents is a hyperlink to one of the worksheets in the workbook.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Create a workbook table of

contents
Add the following script to the sample workbook and try the sample yourself!

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Insert a new worksheet at the beginning of the workbook.
let tocSheet = workbook.addWorksheet();
tocSheet.setPosition(0);
tocSheet.setName("Table of Contents");

// Give the worksheet a title in the sheet.

tocSheet.getRange("A1").setValue("Table of Contents");
tocSheet.getRange("A1").getFormat().getFont().setBold(true);

// Create the table of contents headers.

let tocRange = tocSheet.getRange("A2:B2")
tocRange.setValues([["#", "Name"]]);

// Get the range for the table of contents entries.

let worksheets = workbook.getWorksheets();
tocRange = tocRange.getResizedRange(worksheets.length, 0);

// Loop through all worksheets in the workbook, except the first one.
for (let i = 1; i < worksheets.length; i++) {
// Create a row for each worksheet with its index and linked name.
tocRange.getCell(i, 0).setValue(i);
tocRange.getCell(i, 1).setHyperlink({
textToDisplay: worksheets[i].getName(),
documentReference: `'${worksheets[i].getName()}'!A1`
});
};

// Activate the table of contents worksheet.

tocSheet.activate();
}
Count blank rows on sheets
Article • 03/29/2022

This project includes two scripts:

Count blank rows on a given sheet: Traverses the used range on a given worksheet
and returns a blank row count.
Count blank rows on all sheets: Traverses the used range on all of the worksheets
and returns a blank row count.

７ Note

For your script, a blank row is any row where there's no data. The row can have
formatting.

This sheet returns count of 4 blank rows

This sheet returns count of 0 blank rows (all rows have some data)
Sample code: Count blank rows on a given
sheet
TypeScript

function main(workbook: ExcelScript.Workbook): number

{
// Get the worksheet named "Sheet1".
const sheet = workbook.getWorksheet('Sheet1');

// Get the entire data range.

const range = sheet.getUsedRange(true);

// If the used range is empty, end the script.

if (!range) {
console.log(`No data on this sheet.`);
return;
}

// Log the address of the used range.

console.log(`Used range for the worksheet: ${range.getAddress()}`);

// Look through the values in the range for blank rows.

const values = range.getValues();
let emptyRows = 0;
for (let row of values) {
let emptyRow = true;

// Look at every cell in the row for one with a value.

for (let cell of row) {
if (cell.toString().length > 0) {
emptyRow = false
}
}

// If no cell had a value, the row is empty.

if (emptyRow) {
emptyRows++;
}
}

// Log the number of empty rows.

console.log(`Total empty rows: ${emptyRows}`);

// Return the number of empty rows for use in a Power Automate flow.
return emptyRows;
}
Sample code: Count blank rows on all sheets
TypeScript

function main(workbook: ExcelScript.Workbook): number

{
// Loop through every worksheet in the workbook.
const sheets = workbook.getWorksheets();
let emptyRows = 0;
for (let sheet of sheets) {
// Get the entire data range.
const range = sheet.getUsedRange(true);

// If the used range is empty, skip to the next worksheet.

if (!range) {
console.log(`No data on this sheet.`);
continue;
}

// Log the address of the used range.

console.log(`Used range for the worksheet: ${range.getAddress()}`);

// Look through the values in the range for blank rows.

const values = range.getValues();
for (let row of values) {
let emptyRow = true;

// Look at every cell in the row for one with a value.

for (let cell of row) {
if (cell.toString().length > 0) {
emptyRow = false
}
}

// If no cell had a value, the row is empty.

if (emptyRow) {
emptyRows++;
}
}
}

// Log the number of empty rows.

console.log(`Total empty rows: ${emptyRows}`);

// Return the number of empty rows for use in a Power Automate flow.
return emptyRows;
}
６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Manage calculation mode in Excel
Article • 11/17/2022

This sample shows how to use the calculation mode and calculate methods in Excel
using Office Scripts. You can try the script on any Excel file.

Scenario
Workbooks with large numbers of formulas can take a while to recalculate. Rather than
letting Excel control when calculations happen, you can manage them as part of your
script. This will help with performance in certain scenarios.

The sample script sets the calculation mode to manual. This means that the workbook
will only recalculate formulas when the script tells it to (or you manually calculate
through the UI ). The script then displays the current calculation mode and fully
recalculates the entire workbook.

Sample code: Control calculation mode

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Set the calculation mode to manual.

workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.man
ual);
// Get and log the calculation mode.
const calcMode = workbook.getApplication().getCalculationMode();
console.log(calcMode);
// Manually calculate the file.
workbook.getApplication().calculate(ExcelScript.CalculationType.full);
}

Training video: Manage calculation mode

Watch Sudhi Ramamurthy walk through this sample on YouTube .
Move rows across tables
Article • 10/25/2023

This script does the following:

Selects rows from the source table where the value in a column is equal to some
value ( FILTER_VALUE in the script).
Moves all selected rows into the target table in another worksheet.
Reapplies the relevant filters to the source table.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Move rows using range values

Add the following script to the sample workbook and try the sample yourself!

TypeScript

function main(workbook: ExcelScript.Workbook) {

// You can change these names to match the data in your workbook.
const TARGET_TABLE_NAME = 'Table1';
const SOURCE_TABLE_NAME = 'Table2';

// Select what will be moved between tables.

const FILTER_COLUMN_INDEX = 1;
const FILTER_VALUE = 'Clothing';

// Get the Table objects.

let targetTable = workbook.getTable(TARGET_TABLE_NAME);
let sourceTable = workbook.getTable(SOURCE_TABLE_NAME);

// If either table is missing, report that information and stop the

script.
if (!targetTable || !sourceTable) {
console.log(`Tables missing - Check to make sure both source
(${TARGET_TABLE_NAME}) and target table (${SOURCE_TABLE_NAME}) are present
before running the script. `);
return;
}

// Save the filter criteria currently on the source table.

// Get all the data from the table.

const sourceRange = sourceTable.getRangeBetweenHeaderAndTotal();
const dataRows: (number | string | boolean)[][] =
sourceTable.getRangeBetweenHeaderAndTotal().getValues();

// Create variables to hold the rows to be moved and their addresses.

let rowsToMoveValues: (number | string | boolean)[][] = [];
let rowAddressToRemove: string[] = [];

// Get the data values from the source table.

for (let i = 0; i < dataRows.length; i++) {
if (dataRows[i][FILTER_COLUMN_INDEX] === FILTER_VALUE) {
rowsToMoveValues.push(dataRows[i]);

// Get the intersection between table address and the entire row where
we found the match. This provides the address of the range to remove.
let address =
sourceRange.getIntersection(sourceRange.getCell(i,0).getEntireRow()).getAddr
ess();
rowAddressToRemove.push(address);
}
}

// If there are no data rows to process, end the script.

if (rowsToMoveValues.length < 1) {
console.log('No rows selected from the source table match the filter
criteria.');
return;
}

console.log(`Adding ${rowsToMoveValues.length} rows to target table.`);

// Insert rows at the end of target table.

targetTable.addRows(-1, rowsToMoveValues)

// Remove the rows from the source table.

const sheet = sourceTable.getWorksheet();

// Remove all filters before removing rows.

sourceTable.getAutoFilter().clearCriteria();

// Important: Remove the rows starting at the bottom of the table.

// Otherwise, the lower rows change position before they are deleted.
console.log(`Removing ${rowAddressToRemove.length} rows from the source
table.`);
rowAddressToRemove.reverse().forEach((address) => {
sheet.getRange(address).delete(ExcelScript.DeleteShiftDirection.up);
});

// Reapply the original filters.

Object.keys(originalTableFilters).forEach((columnName) => {

sourceTable.getColumnByName(columnName).getFilter().apply(originalTableFilte
rs[columnName]);
});
}

Training video: Move rows across tables

Watch Sudhi Ramamurthy walk through this sample on YouTube . There are two scripts
shown in the video's solution. The main difference is how the rows are selected.

In the first variant, the rows are selected by applying the table filter and reading
the visible range.
In the second, the rows are selected by reading the values and extracting the row
values (which is what the sample on this page uses).
Notify people with comments
Article • 12/05/2023

This sample shows how to add comments to a cell including @mentioning a

colleague.

Example scenario
The team lead maintains the shift schedule. They assign an employee ID to the shift
record. If the team lead wishes to notify the employee, they add a comment that
@mentions the employee. The employee is emailed with a custom message from the
worksheet. Subsequently, the employee can view the workbook and respond to the
comment at their convenience.

Solution
1. The script extracts employee information from the employee worksheet.
2. The script then adds a comment (including the relevant employee email) to the
appropriate cell in the shift record.
3. Existing comments in the cell are removed before adding the new comment.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Add comments

Add the following script to the sample workbook and try the sample yourself!

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the list of employees.
const employees =
workbook.getWorksheet('Employees').getUsedRange().getTexts();

// Get the schedule information from the schedule table.

const scheduleSheet = workbook.getWorksheet('Schedule');
const table = scheduleSheet.getTables()[0];
const range = table.getRangeBetweenHeaderAndTotal();
const scheduleData = range.getTexts();

// Find old comments, so we can delete them later.

const oldCommentAddresses = scheduleSheet.getComments().map(oldComment =>
oldComment.getLocation().getAddress());

// Look through the schedule for a matching employee.

for (let i = 0; i < scheduleData.length; i++) {
const employeeId = scheduleData[i][3];

// Compare the employee ID in the schedule against the employee

information table.
const employeeInfo = employees.find(employeeRow => employeeRow[0] ===
employeeId);
if (employeeInfo) {
const adminNotes = scheduleData[i][4];
const commentCell = range.getCell(i, 5);

// Delete old comments, so we avoid conflicts.

if (oldCommentAddresses.find(oldCommentAddress => oldCommentAddress
=== commentCell.getAddress())) {
const comment = workbook.getCommentByCell(commentCell);
comment.delete();
}

// Add a comment using the admin notes as the text.

workbook.addComment(commentCell, {
mentions: [{
email: employeeInfo[1],
id: 0, // This ID maps this mention to the `id=0` text in the
comment.
name: employeeInfo[2]
}],
richContent: `<at id=\"0\">${employeeInfo[2]}</at> ${adminNotes}`
}, ExcelScript.ContentType.mention);
} else {
console.log("No match for: " + employeeId);
}
}
}
Output Excel table data as JSON for
usage in Power Automate
Article • 10/25/2023

Excel table data can be represented as an array of objects in the form of JSON . Each
object represents a row in the table. This helps extract the data from Excel in a
consistent format that is visible to the user. The data can then be given to other systems
through Power Automate flows.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

A variation of this sample also includes the hyperlinks in one of the table columns. This
allows additional levels of cell data to be surfaced in the JSON.

Sample code: Return table data as JSON

Add the following script to the sample workbook and try the sample yourself!
７ Note

You can change the interface TableData structure to match your table columns.
Note that for column names with spaces, be sure to place your key in quotation
marks, such as with "Event ID" in the sample. For more information about working
with JSON, read Use JSON to pass data to and from Office Scripts.

TypeScript

function main(workbook: ExcelScript.Workbook): TableData[] {

// Get the first table in the "PlainTable" worksheet.
// If you know the table name, use `workbook.getTable('TableName')`
instead.
const table = workbook.getWorksheet('PlainTable').getTables()[0];

// Get all the values from the table as text.

const texts = table.getRange().getTexts();

// Create an array of JSON objects that match the row structure.

let returnObjects: TableData[] = [];
if (table.getRowCount() > 0) {
returnObjects = returnObjectFromValues(texts);
}

// Log the information and return it for a Power Automate flow.

console.log(JSON.stringify(returnObjects));
return returnObjects
}

// This function converts a 2D array of values into a generic JSON object.

// In this case, we have defined the TableData object, but any similar
interface would work.
function returnObjectFromValues(values: string[][]): TableData[] {
let objectArray: TableData[] = [];
let objectKeys: string[] = [];
for (let i = 0; i < values.length; i++) {
if (i === 0) {
objectKeys = values[i]
continue;
}

let object: {[key: string]: string} = {}

for (let j = 0; j < values[i].length; j++) {
object[objectKeys[j]] = values[i][j]
}

objectArray.push(object as unknown as TableData);

}

return objectArray;
}
interface TableData {
"Event ID": string
Date: string
Location: string
Capacity: string
Speakers: string
}

Sample output from the "PlainTable" worksheet

JSON

[{
"Event ID": "E107",
"Date": "2020-12-10",
"Location": "Montgomery",
"Capacity": "10",
"Speakers": "Debra Berger"
}, {
"Event ID": "E108",
"Date": "2020-12-11",
"Location": "Montgomery",
"Capacity": "10",
"Speakers": "Delia Dennis"
}, {
"Event ID": "E109",
"Date": "2020-12-12",
"Location": "Montgomery",
"Capacity": "10",
"Speakers": "Diego Siciliani"
}, {
"Event ID": "E110",
"Date": "2020-12-13",
"Location": "Boise",
"Capacity": "25",
"Speakers": "Gerhart Moller"
}, {
"Event ID": "E111",
"Date": "2020-12-14",
"Location": "Salt Lake City",
"Capacity": "20",
"Speakers": "Grady Archie"
}, {
"Event ID": "E112",
"Date": "2020-12-15",
"Location": "Fremont",
"Capacity": "25",
"Speakers": "Irvin Sayers"
}, {
"Event ID": "E113",
"Date": "2020-12-16",
"Location": "Salt Lake City",
"Capacity": "20",
"Speakers": "Isaiah Langer"
}, {
"Event ID": "E114",
"Date": "2020-12-17",
"Location": "Salt Lake City",
"Capacity": "20",
"Speakers": "Johanna Lorenz"
}]

Sample code: Return table data as JSON with

hyperlink text

７ Note

The script always extracts hyperlinks from the 4th column (0 index) of the table. You
can change that order or include multiple columns as hyperlink data by modifying
the code under the comment // For the 4th column (0 index), extract the
hyperlink and use that instead of text.

TypeScript

function main(workbook: ExcelScript.Workbook): TableData[] {

// Get the first table in the "WithHyperLink" worksheet.
// If you know the table name, use `workbook.getTable('TableName')`
instead.
const table = workbook.getWorksheet('WithHyperLink').getTables()[0];

// Get all the values from the table as text.

const range = table.getRange();

// Create an array of JSON objects that match the row structure.

let returnObjects: TableData[] = [];
if (table.getRowCount() > 0) {
returnObjects = returnObjectFromValues(range);
}

// Log the information and return it for a Power Automate flow.

console.log(JSON.stringify(returnObjects));
return returnObjects
}

function returnObjectFromValues(range: ExcelScript.Range): TableData[] {

let values = range.getTexts();
let objectArray : TableData[] = [];
let objectKeys: string[] = [];
for (let i = 0; i < values.length; i++) {
if (i === 0) {
objectKeys = values[i]
continue;
}

objectArray.push(object as TableData);
}
return objectArray;
}

interface TableData {
"Event ID": string
Date: string
Location: string
Capacity: string
"Search link": string
Speakers: string
}

Sample output from the "WithHyperLink" worksheet

JSON

[{
"Event ID": "E107",
"Date": "2020-12-10",
"Location": "Montgomery",
"Capacity": "10",
"Search link": "https://www.google.com/search?q=Montgomery",
"Speakers": "Debra Berger"
}, {
"Event ID": "E108",
"Date": "2020-12-11",
"Location": "Montgomery",
"Capacity": "10",
"Search link": "https://www.google.com/search?q=Montgomery",
"Speakers": "Delia Dennis"
}, {
"Event ID": "E109",
"Date": "2020-12-12",
"Location": "Montgomery",
"Capacity": "10",
"Search link": "https://www.google.com/search?q=Montgomery",
"Speakers": "Diego Siciliani"
}, {
"Event ID": "E110",
"Date": "2020-12-13",
"Location": "Boise",
"Capacity": "25",
"Search link": "https://www.google.com/search?q=Boise",
"Speakers": "Gerhart Moller"
}, {
"Event ID": "E111",
"Date": "2020-12-14",
"Location": "Salt Lake City",
"Capacity": "20",
"Search link": "https://www.google.com/search?q=salt+lake+city",
"Speakers": "Grady Archie"
}, {
"Event ID": "E112",
"Date": "2020-12-15",
"Location": "Fremont",
"Capacity": "25",
"Search link": "https://www.google.com/search?q=Fremont",
"Speakers": "Irvin Sayers"
}, {
"Event ID": "E113",
"Date": "2020-12-16",
"Location": "Salt Lake City",
"Capacity": "20",
"Search link": "https://www.google.com/search?q=salt+lake+city",
"Speakers": "Isaiah Langer"
}, {
"Event ID": "E114",
"Date": "2020-12-17",
"Location": "Salt Lake City",
"Capacity": "20",
"Search link": "https://www.google.com/search?q=salt+lake+city",
"Speakers": "Johanna Lorenz"
}]

Use in Power Automate

For how to use such a script in Power Automate, see Create an automated workflow with
Power Automate.
Remove hyperlinks from each cell in an
Excel worksheet
Article • 10/25/2023

This sample clears all of the hyperlinks from the current worksheet. It traverses the
worksheet and if there is any hyperlink associated with the cell, it clears the hyperlink yet
retains the cell value as is. Also logs the time it takes to complete traversal.

７ Note

This only works if the cell count is < 10k.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Remove hyperlinks

Add the following script to the sample workbook and try the sample yourself!

TypeScript

function main(workbook: ExcelScript.Workbook, sheetName: string = 'Sheet1')

{
// Get the active worksheet.
let sheet = workbook.getWorksheet(sheetName);

// Get the used range to operate on.

// For large ranges (over 10000 entries), consider splitting the operation
into batches for performance.
const targetRange = sheet.getUsedRange(true);
console.log(`Target Range to clear hyperlinks from:
${targetRange.getAddress()}`);

const rowCount = targetRange.getRowCount();

const colCount = targetRange.getColumnCount();
console.log(`Searching for hyperlinks in ${targetRange.getAddress()} which
contains ${(rowCount * colCount)} cells`);

// Go through each individual cell looking for a hyperlink.

// This allows us to limit the formatting changes to only the cells with
hyperlink formatting.
let clearedCount = 0;
for (let i = 0; i < rowCount; i++) {
for (let j = 0; j < colCount; j++) {
const cell = targetRange.getCell(i, j);
const hyperlink = cell.getHyperlink();
if (hyperlink) {
cell.clear(ExcelScript.ClearApplyTo.hyperlinks);

cell.getFormat().getFont().setUnderline(ExcelScript.RangeUnderlineStyle.none
);
cell.getFormat().getFont().setColor('Black');
clearedCount++;
}
}
}

console.log(`Done. Cleared hyperlinks from ${clearedCount} cells`);

}

Training video: Remove hyperlinks from each

cell in an Excel worksheet
Watch Sudhi Ramamurthy walk through this sample on YouTube .
Table samples
Article • 10/03/2024

These samples showcase common interactions with Excel tables.

Create a sorted table

This sample creates a table from the current worksheet's used range, then sorts it based
on the first column.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
const selectedSheet = workbook.getActiveWorksheet();

// Create a table with the used cells.

const usedRange = selectedSheet.getUsedRange();
const newTable = selectedSheet.addTable(usedRange, true);

// Sort the table using the first column.

newTable.getSort().apply([{ key: 0, ascending: true }]);
}

Filter a table
This sample filters an existing table using the values in one of the columns.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table in the workbook named "StationTable".
const table = workbook.getTable("StationTable");

// Get the "Station" table column for the filter.

const stationColumn = table.getColumnByName("Station");

// Apply a filter to the table that will only show rows

// with a value of "Station-1" in the "Station" column.
stationColumn.getFilter().applyValuesFilter(["Station-1"]);
}

Filter out one value

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table in the workbook named "StationTable".
const table = workbook.getTable("StationTable");

// Get the "Station" table column for the filter.

const stationColumn = table.getColumnByName("Station");

// Get a list of unique values in the station column.

const stationSet = columnToSet(stationColumn);

// Apply a filter to the table that will only show rows

// that don't have a value of "Station-1" in the "Station" column.
stationColumn.getFilter().applyValuesFilter(stationSet.filter((value) => {
return value !== "Station-1";
}));
}

return columnSet;
}

Remove table column filters

Download table-with-filter.xlsx for a ready-to-use workbook. Add the following script to

try the sample yourself!
TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the active cell.
const cell = workbook.getActiveCell();

// Get the tables associated with that cell.

// Since tables can't overlap, this will be one table at most.
const currentTable = cell.getTables()[0];

// If there's no table on the selection, end the script.

if (!currentTable) {
console.log("The selection is not in a table.");
return;
}

// Get the TableColumn object matching that header.

const tableColumn = currentTable.getColumnByName(headerCellValue);

// Clear the filters on that table column.

tableColumn.getFilter().clear();
}

Before clearing column filter (notice the active cell)

After clearing column filter

 Tip

If you wish to learn more about how to save the filter prior to clearing it (and re-
apply later), see Move rows across tables by saving filters, a more advanced
sample.

Dynamically reference table values

This script uses the "@COLUMN_NAME" syntax to set formulas in a table column. The
column names in the table can be changed without changing this script.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the current worksheet.
const table = workbook.getTable("Profits");

// Get the column names for columns 2 and 3.

// Note that these are 1-based indices.
const nameOfColumn2 = table.getColumn(2).getName();
const nameOfColumn3 = table.getColumn(3).getName();

Before the script

ﾉ Expand table

Month Price Units Sold Total

Jan 45 5

Feb 45 3

Mar 45 6

After the script

ﾉ Expand table

Month Price Units Sold Total

Jan 45 5 225

Feb 45 3 135

Mar 45 6 270
Set conditional formatting for cross-
column comparisons
Article • 09/14/2023

This sample shows how to apply conditional formatting to a range. The conditions used
are comparing values to those in an adjacent column. Additionally, this sample uses
parameters to get user input. This lets the person running the script select the range, the
type of comparison, and the colors.

Sample code: Set conditional formatting

TypeScript

/**
* Formats a range on the current sheet based on values in an adjacent
column.
* @param rangeAddress The A1-notation range to format.
* @param compareTo The adjacent column to compare against.
* @param colorIfGreater The color of the cell if the value is greater than
the adjacent column.
* @param colorIfEqual The color of the cell if the value is equal to the
adjacent column.
* @param colorIfLess The color of the cell if the value is less than the
adjacent column.
*/
function main(
workbook: ExcelScript.Workbook,
rangeAddress: string, compareTo: "Left" | "Right",
colorIfGreater: "Red" | "Green" | "Yellow" | "None",
colorIfLess: "Red" | "Green" | "Yellow" | "None",
colorIfEqual: "Red" | "Green" | "Yellow" | "None"
) {
// Get the specified range.
const selectedSheet = workbook.getActiveWorksheet();
const range = selectedSheet.getRange(rangeAddress);

// Remove old conditional formatting.

range.clearAllConditionalFormats();

// Get the address of the first adjacent cell of the adjacent column.
let adjacentColumn: string;
if (compareTo == "Left") {
adjacentColumn = range.getColumnsBefore().getCell(0, 0).getAddress();
} else {
adjacentColumn = range.getColumnsAfter().getCell(0, 0).getAddress();
}

// Remove the worksheet name from the address to create a relative

formula.
let formula = "=$" +
adjacentColumn.substring(adjacentColumn.lastIndexOf("!") + 1);

// Set the conditional formatting based on the user's color choices.

setConditionalFormatting(

range.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue).getC
ellValue(),
colorIfGreater,
formula,
ExcelScript.ConditionalCellValueOperator.greaterThan);
setConditionalFormatting(

range.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue).getC
ellValue(),
colorIfEqual,
formula,
ExcelScript.ConditionalCellValueOperator.equalTo);
setConditionalFormatting(

range.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue).getC
ellValue(),
colorIfLess,
formula,
ExcelScript.ConditionalCellValueOperator.lessThan);
}

// Apply the conditional formatting.

conditionalFormat.setRule({
formula1: formula,
operator: operator
});
}
Use external fetch calls in Office Scripts
Article • 12/22/2022

This script gets basic information about a user's GitHub repositories. It shows how to
use fetch in a simple scenario. For more information about using fetch or other
external calls, read External API call support in Office Scripts. For information about
working with JSON objects, like what is returned by the GitHub APIs, read Use JSON
to pass data to and from Office Scripts.

Learn more about the GitHub APIs being used in the GitHub API reference . You can
also see the raw API call output by visiting
https://api.github.com/users/{USERNAME}/repos in a web browser (be sure to replace
the {USERNAME} placeholder with your GitHub ID).

Sample code: Get basic information about

user's GitHub repositories
TypeScript

async function main(workbook: ExcelScript.Workbook) {

// Call the GitHub REST API.
// Replace the {USERNAME} placeholder with your GitHub username.
const response = await
fetch('https://api.github.com/users/{USERNAME}/repos');
const repos: Repository[] = await response.json();

// Create an array to hold the returned values.

const rows: (string | boolean | number)[][] = [];

// Convert each repository block into a row.

for (let repo of repos) {
rows.push([repo.id, repo.name, repo.license?.name, repo.license?.url]);
}
// Create a header row.
const sheet = workbook.getActiveWorksheet();
sheet.getRange('A1:D1').setValues([["ID", "Name", "License Name", "License
URL"]]);

// Add the data to the current worksheet, starting at "A2".

const range = sheet.getRange('A2').getResizedRange(rows.length - 1,
rows[0].length - 1);
range.setValues(rows);
}

// An interface matching the returned JSON for a GitHub repository.

interface Repository {
name: string,
id: string,
license?: License
}

// An interface matching the returned JSON for a GitHub repo license.

interface License {
name: string,
url: string
}

Training video: How to make external API calls

Watch Sudhi Ramamurthy walk through this sample on YouTube .
Combine worksheets into a single
workbook
Article • 12/05/2023

This sample shows how to pull data from multiple workbooks into a single, centralized
workbook. It uses two scripts: one to retrieve information from a workbook and another
to create new worksheets with that information. It combines the scripts in a Power
Automate flow that acts on an entire OneDrive folder.

） Important

This sample only copies the values from the other workbooks. It does not preserve
formatting, charts, tables, or other objects.

Solution
1. Create a new Excel file in your OneDrive. The file name "Combination.xlsx" is used
in this sample.
2. Create and save the two scripts from this sample.
3. Create a folder in your OneDrive and add one or more workbooks with data to it.
The folder name "output" is used in this sample.
4. Build a flow (as described in the Power Automate flow section of this article) to
perform these steps:
a. List all the files the "output" folder.
b. Use the Return worksheet data script to get the data from every worksheet in
each of the workbooks.
c. Use the Add worksheets script to create a new worksheet in the
"Combination.xlsx" workbook for every worksheet in all the other files.

Sample code: Return worksheet data

TypeScript

/**
* This script returns the values from the used ranges on each worksheet.
*/
function main(workbook: ExcelScript.Workbook): WorksheetData[] {
// Create an object to return the data from each worksheet.
let worksheetInformation: WorksheetData[] = [];
// Get the data from every worksheet, one at a time.
workbook.getWorksheets().forEach((sheet) => {
let values = sheet.getUsedRange()?.getValues();
worksheetInformation.push({
name: sheet.getName(),
data: values as string[][]
});
});

return worksheetInformation;
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
name: string;
data: string[][];
}

Sample code: Add worksheets

TypeScript

/**
* This script creates a new worksheet in the current workbook for each
WorksheetData object provided.
*/
function main(workbook: ExcelScript.Workbook, workbookName: string,
worksheetInformation: WorksheetData[]) {
// Add each new worksheet.
worksheetInformation.forEach((value) => {
let sheet = workbook.addWorksheet(`${workbookName}.${value.name}`);

// If there was any data in the worksheet, add it to a new range.

if (value.data) {
let range = sheet.getRangeByIndexes(0, 0, value.data.length,
value.data[0].length);
range.setValues(value.data);
}
});
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
name: string;
data: string[][];
}
Power Automate flow: Combine worksheets
into a single workbook
1. Sign into Power Automate and create a new Instant cloud flow.

2. Choose Manually trigger a flow and select Create.

3. Get all the workbooks you want to combine from their folder. Add an action and
choose the OneDrive for Business connector's List files in folder action. For the
Folder field, use the file picker to select the "output" folder.

4. Add an action to run the Return worksheet data script to get all the data from
each of the workbooks. Choose the Excel Online (Business) connector's Run script
action. Use the following values for the action. Note that when you add the Id for
the file, Power Automate will wrap the action in a For each control, so the action
will be performed on every file.

Location: OneDrive for Business

Document Library: OneDrive
File: Id (dynamic content from List files in folder)
Script: Return worksheet data
5. Add an action to run the Add worksheets script on the new Excel file you created.
This will add the data from all the other workbooks. After the previous Run script
action and inside the For each control, add an action that uses the Excel Online
(Business) connector's Run script action. Use the following values for the action.

Location: OneDrive for Business

Document Library: OneDrive
File: "Combination.xlsx" (your file, as selected by the file picker)
Script: Add worksheets
workbookName: Name (dynamic content from List files in folder)
worksheetInformation (see the note following the next image): result
(dynamic content from Run script)
７ Note

Select the Switch to input entire array button to add the array object directly,
instead of individual items for the array. Do this before entering result.
6. Save the flow. The flow designer should look like the following image.
7. Use the Test button on the flow editor page or run the flow through your My flows
tab. Be sure to allow access when prompted.

8. The "Combination.xlsx" file should now have new worksheets.

Troubleshooting
A resource with the same name or identifier already exists: This error likely
indicates the "Combination.xlsx" workbook already has a worksheet with the same
name. This will happen if you run the flow multiple times with the same
workbooks. Create a new workbook each time to store the combined data or use
different file names in the "output" folder.
The argument is invalid or missing or has an incorrect format: This error can
mean that the generated worksheet name doesn't meet Excel's requirements .
This is likely because the name is too long. If the worksheet names will be more
than 30 characters replace the code in "Add worksheets" that calls addWorksheet
with something that shortens the string. Since the workbook name itself might be
too long, add an incrementing number to the end of the worksheet name. Declare
this number outside of the forEach loop.

TypeScript

Additionally, if the workbook names are longer than 30 characters, you'll need to
shorten them in the flow. First, you must create a variable in the flow to track the
workbook count. This will avoid identical shortened names being passed to the
script. Add an Initialize variable action before the flow (of Type "Integer") and an
Increment variable action between the two Run script actions. Then, instead of
using Name as the workbookName in "Run script 1", use the expression
substring(items('Apply_to_each')?['Name'],0,min(length(items('Apply_to_each')?

['Name']),20)) and the dynamic content from your variable. This shortens the

workbook names to 20 characters and appends the current workbook number to

the string being passed to the script.
７ Note

Rather than making the flow and script more complicated, it might be easier
to guarantee the file and worksheet names are short enough.
６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Convert CSV files to Excel workbooks
Article • 02/07/2024

Many services export data as comma-separated value (CSV) files. This solution
automates the process of converting those CSV files to Excel workbooks in the .xlsx file
format. It uses a Power Automate flow to find files with the .csv extension in a
OneDrive folder and an Office Script to copy the data from the .csv file into a new Excel
workbook.

Solution
1. Store the .csv files and a blank "Template" .xlsx file in a OneDrive folder.
2. Create an Office Script to parse the CSV data into a range.
3. Create a Power Automate flow to read the .csv files and pass their contents to the
script.

Sample files
Download convert-csv-example.zip to get the Template.xlsx file and two sample .csv
files. Extract the files into a folder in your OneDrive. This sample assumes the folder is
named "output".

Add the following script to the sample workbook. In Excel, use Automate > New Script
to paste the code and save the script. Save it as Convert CSV and try the sample
yourself!

Sample code: Insert comma-separated values

into a workbook
TypeScript

/**
* Convert incoming CSV data into a range and add it to the workbook.
*/
function main(workbook: ExcelScript.Workbook, csv: string) {
let sheet = workbook.getWorksheet("Sheet1");

// Remove any Windows \r characters.

csv = csv.replace(/\r/g, "");

// Split each line into a row.

// NOTE: This will split values that contain new line characters.
let rows = csv.split("\n");

/*
* For each row, match the comma-separated sections.
* For more information on how to use regular expressions to parse CSV
files,
* see this Stack Overflow post:
https://stackoverflow.com/a/48806378/9227753
*/
const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))/g
rows.forEach((value, index) => {
if (value.length > 0) {
let row = value.match(csvMatchRegex);

// Check for blanks at the start of the row.

if (row[0].charAt(0) === ',') {
row.unshift("");
}

// Remove the preceding comma and surrounding quotation marks.

row.forEach((cell, index) => {
cell = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
row[index] = cell.indexOf("\"") === 0 && cell.lastIndexOf("\"") ===
cell.length - 1 ? cell.substring(1, cell.length - 1) : cell;
});

// Create a 2D array with one row.

let data: string[][] = [];
data.push(row);

// Put the data in the worksheet.

let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
range.setValues(data);
}
});

// Add any formatting or table creation that you want.

}

Power Automate flow: Create new .xlsx files

1. Sign into Power Automate and create a new Scheduled cloud flow.

2. Set the flow to Repeat every "1" "Day" and select Create.

3. Get the template Excel file. This is the basis for all the converted .csv files. In the
flow builder, select the + button and Add an action. Select the OneDrive for
Business connector's Get file content action. Provide the file path to the
"Template.xlsx" file.
File: /output/Template.xlsx

4. Rename the Get file content step. Select the current title, "Get file content", in the
action task pane. Change the name to "Get Excel template".

5. Add an action that gets all the files in the "output" folder. Choose the OneDrive for
Business connector's List files in folder action. Provide the folder path that
contains the .csv files.

Folder: /output

6. Add a condition so that the flow only operates on .csv files. Add the Condition
control action. Use the following values for the Condition.

Choose a value: Name (dynamic content from List files in folder). Note that
this dynamic content has multiple results, so a For each control surrounds the
Condition.
ends with (from the dropdown list)
Choose a value: .csv
7. The rest of the flow is under the If yes section, since we only want to act on .csv
files. Get an individual .csv file by adding an action that uses the OneDrive for
Business connector's Get file content action. Use the Id from the dynamic content
from List files in folder.

File: Id (dynamic content from the List files in folder step)

8. Rename the new Get file content step to "Get .csv file". This helps distinguish this
file from the Excel template.

9. Make the new .xlsx file, using the Excel template as the base content. Add an
action that uses the OneDrive for Business connector's Create file action. Use the
following values.

Folder Path: /output

File Name: Name without extension.xlsx (choose the Name without extension
dynamic content from the List files in folder and manually type ".xlsx" after it)
File Content: File content (dynamic content from Get Excel template)
10. Run the script to copy data into the new workbook. Add the Excel Online
(Business) connector's Run script action. Use the following values for the action.

Location: OneDrive for Business

Document Library: OneDrive
File: Id (dynamic content from Create file)
Script: Convert CSV
csv: File content (dynamic content from Get .csv file)

11. Save the flow. The flow designer should look like the following image.
12. Use the Test button on the flow editor page or run the flow through your My flows
tab. Be sure to allow access when prompted.

13. You should find new .xlsx files in the "output" folder, alongside the original .csv
files. The new workbooks contain the same data as the CSV files.

Troubleshooting

Script testing
To test the script without using Power Automate, assign a value to csv before using it.
Add the following code as the first line of the main function and select Run.

TypeScript

csv = `1, 2, 3
4, 5, 6
7, 8, 9`;

Semicolon-separated files and other alternative

separators
Some regions use semicolons (';') to separate cell values instead of commas. In this case,
you need to change the following lines in the script.

1. Replace the commas with semicolons in the regular expression statement. This
starts with let row = value.match .

TypeScript

let row = value.match(/(?:;|\n|^)("(?:(?:"")[^"])"|[^";\n]|

(?:\n|$))/g);

2. Replace the comma with a semicolon in the check for the blank first cell. This starts
with if (row[0].charAt(0) .
TypeScript

if (row[0].charAt(0) === ';') {

3. Replace the comma with a semicolon in the line that removes the separation
character from the displayed text. This starts with row[index] = cell.indexOf .

TypeScript

row[index] = cell.indexOf(";") === 0 ? cell.substr(1) : cell;

７ Note

If your file uses tabs or any other character to separate the values, replace the ; in
the above substitutions with \t or whatever character is being used.

Large CSV files

If your file has hundreds of thousands of cells, you could reach the Excel data transfer
limit. You'll need to force the script to synchronize with Excel periodically. The easiest
way to do this is to call console.log after a batch of rows has been processed. Add the
following lines of code to make this happen.

1. Before rows.forEach((value, index) => { , add the following line.

TypeScript

let rowCount = 0;

2. After range.setValues(data); , add the following code. Note that depending on

the number of columns, you may need to reduce 5000 to a lower number.

TypeScript

rowCount++;
if (rowCount % 5000 === 0) {
console.log("Syncing 5000 rows.");
}

２ Warning
If your CSV file is very large, you may have problems timing out in Power
Automate. You'll need to divide the CSV data into multiple files before converting
them into Excel workbooks.

Accents and other unicode characters

Files with unicode-specific characters, such as accented vowels like é , need to be saved
with the correct encoding. Power Automate's OneDrive connector file creation defaults
to ANSI for .csv files. If you're creating the .csv files in Power Automate, you'll need to
add the byte order mark (BOM) before the comma-separated values. For UTF-8,
replace the file contents for the write .csv file operation with the expression
concat(uriComponentToString('%EF%BB%BF'), <CSV Input>) (where <CSV Input> is your

original CSV data).

Note that this sample doesn't create the .csv files in the flow, so this change needs to
happen in your custom part of the flow. You could also read and rewrite the .csv files
with the BOM, if you don't control how those files are created.

Surrounding quotation marks

This sample removes any quotation marks ("") that surround values. These are typically
added to comma-separated values to prevent commas in the data from being treated as
separation tokens. A .csv file that is opened in Excel, then saved as a .xlsx file, will never
have the those quotation marks shown to the reader. If you wish to keep the quotation
marks and have them be displayed in the final spreadsheets, replace lines 27-30 of the
script with the following code.

TypeScript

// Remove the preceding comma.

row.forEach((cell, index) => {
row[index] = cell.indexOf(",") === 0 ? cell.substring(1) : cell;
});

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our  Provide product feedback
contributor guide.
Cross-reference Excel files with Power
Automate
Article • 04/05/2024

This solution shows how to compare data across two Excel files to find discrepancies. It
uses Office Scripts to analyze data and Power Automate to communicate between the
workbooks.

This sample passes data between workbooks using JSON objects. For more
information about working with JSON, read Use JSON to pass data to and from Office
Scripts.

Example scenario
You're an event coordinator who is scheduling speakers for upcoming conferences. You
keep the event data in one spreadsheet and the speaker registrations in another. To
ensure the two workbooks are kept in sync, you use a flow with Office Scripts to
highlight any potential problems.

Sample Excel files

Download the following files to get ready-to-use workbooks for the sample.

1. event-data.xlsx
2. speaker-registrations.xlsx

Add the following scripts to try the sample yourself! In Excel, use Automate > New
Script to paste the code and save the scripts with the suggested names.

Sample code: Get event data

TypeScript

function main(workbook: ExcelScript.Workbook): string {

// Get the first table in the "Keys" worksheet.
let table = workbook.getWorksheet('Keys').getTables()[0];

// Get the rows in the event table.

let range = table.getRangeBetweenHeaderAndTotal();
let rows = range.getValues();
// Save each row as an EventData object. This lets them be passed through
Power Automate.
let records: EventData[] = [];
for (let row of rows) {
let [eventId, date, location, capacity] = row;
records.push({
eventId: eventId as string,
date: date as number,
location: location as string,
capacity: capacity as number
})
}

// Log the event data to the console and return it for a flow.
let stringResult = JSON.stringify(records);
console.log(stringResult);
return stringResult;
}

// An interface representing a row of event data.

interface EventData {
eventId: string
date: number
location: string
capacity: number
}

Sample code: Validate speaker registrations

TypeScript

function main(workbook: ExcelScript.Workbook, keys: string): string {

// Get the first table in the "Transactions" worksheet.
let table = workbook.getWorksheet('Transactions').getTables()[0];

// Clear the existing formatting in the table.

let range = table.getRangeBetweenHeaderAndTotal();
range.clear(ExcelScript.ClearApplyTo.formats);

// Compare the data in the table to the keys passed into the script.
let keysObject = JSON.parse(keys) as EventData[];
let speakerSlotsRemaining = keysObject.map(value => value.capacity);
let overallMatch = true;

// Iterate over every row looking for differences from the other
worksheet.
let rows = range.getValues();
for (let i = 0; i < rows.length; i++) {
let row = rows[i];
let [eventId, date, location, capacity] = row;
let match = false;
// Look at each key provided for a matching Event ID.
for (let keyIndex = 0; keyIndex < keysObject.length; keyIndex++) {
let event = keysObject[keyIndex];
if (event.eventId === eventId) {
match = true;
speakerSlotsRemaining[keyIndex]--;
// If there's a match on the event ID, look for things that don't
match and highlight them.
if (event.date !== date) {
overallMatch = false;
range.getCell(i, 1).getFormat()
.getFill()
.setColor("FFFF00");
}
if (event.location !== location) {
overallMatch = false;
range.getCell(i, 2).getFormat()
.getFill()
.setColor("FFFF00");
}

break;
}
}

// If no matching Event ID is found, highlight the Event ID's cell.

if (!match) {
overallMatch = false;
range.getCell(i, 0).getFormat()
.getFill()
.setColor("FFFF00");
}
}

// Choose a message to send to the user.

let returnString = "All the data is in the right order.";
if (overallMatch === false) {
returnString = "Mismatch found. Data requires your review.";
} else if (speakerSlotsRemaining.find(remaining => remaining < 0)){
returnString = "Event potentially overbooked. Please review."
}

console.log("Returning: " + returnString);

return returnString;
}

// An interface representing a row of event data.

interface EventData {
eventId: string
date: number
location: string
capacity: number
}
Power Automate flow: Check for
inconsistencies across the workbooks
This flow extracts the event information from the first workbook and uses that data to
validate the second workbook.

1. Sign into Power Automate and create a new Instant cloud flow.

2. Choose Manually trigger a flow and select Create.

3. In the flow builder, select the + button and Add an action. Select the Excel Online
(Business) connector's Run script action. Use the following values for the action.

Location: OneDrive for Business

Document Library: OneDrive
File: event-data.xlsx (selected with the file chooser)
Script: Get event data

4. Rename this step. Select the current name "Run script" in the task pane and
change it to "Get event data".

5. Add a second action that uses the Excel Online (Business) connector's Run script
action. This action uses the returned values from the Get event data script as input
for the Validate event data script. Use the following values for the action.

Location: OneDrive for Business

Document Library: OneDrive
File: speaker-registration.xlsx (selected with the file chooser)
Script: Validate speaker registration
keys: result (dynamic content from Get event data)

6. Rename this step as well. Select the current name "Run script 1" in the task pane
and change it to "Validate speaker registration".

7. This sample uses Outlook as the email client. For this sample, add the Office 365
Outlook connector's Send and email (V2) action. You could use any email
connector that Power Automate supports. This action uses the returned values
from the Validate speaker registration script as the email body content. Use the
following values for the action.

To: Your test email account (or personal email)

Subject: Event validation results
Body: result (dynamic content from Validate speaker registration)
8. Save the flow. The flow designer should look like the following image.
9. Use the Test button on the flow editor page or run the flow through your My flows
tab. Be sure to allow access when prompted.

10. You should receive an email saying "Mismatch found. Data requires your review."
This indicates there are differences between rows in speaker-registrations.xlsx and
rows in event-data.xlsx. Open speaker-registrations.xlsx to see several highlighted
cells where there are potential problems with the speaker registration listings.

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Use Office Scripts and Power Automate
to email images of a chart and table
Article • 06/11/2024

This sample uses Office Scripts and Power Automate to create a chart. It then emails
images of the chart and its base table.

Example scenario
Calculate to get latest results.
Create chart.
Get chart and table images.
Email the images with Power Automate.

Input data

Output chart
Email that was received through Power Automate flow

Solution
This solution has two parts:

1. An Office Script to calculate and extract Excel chart and table

2. A Power Automate flow to invoke the script and email the results. For an example
on how to do this, see Create an automated workflow with Power Automate.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Calculate and extract Excel chart
and table
Add the following script to the sample workbook. In Excel, use Automate > New Script
to paste the code and save the script. Save it as Get chart image and try the sample
yourself!

TypeScript

function main(workbook: ExcelScript.Workbook): ReportImages {

// Recalculate the workbook to ensure all tables and charts are updated.
workbook.getApplication().calculate(ExcelScript.CalculationType.full);

// Get the data from the "InvoiceAmounts" table.

const sheet1 = workbook.getWorksheet("Sheet1");
const table = workbook.getWorksheet('InvoiceAmounts').getTables()[0];
const rows = table.getRange().getTexts();

// Get only the "Customer Name" and "Amount due" columns, then remove the
"Total" row.
const selectColumns = rows.map((row) => {
return [row[2], row[5]];
});
table.setShowTotals(true);
selectColumns.splice(selectColumns.length - 1, 1);
console.log(selectColumns);

// Delete the "ChartSheet" worksheet if it's present, then recreate it.

workbook.getWorksheet('ChartSheet')?.delete();
const chartSheet = workbook.addWorksheet('ChartSheet');

// Add the selected data to the new worksheet.

const targetRange =
chartSheet.getRange('A1').getResizedRange(selectColumns.length - 1,
selectColumns[0].length - 1);
targetRange.setValues(selectColumns);

// Insert the chart on sheet 'ChartSheet' at cell "D1".

const chart = chartSheet.addChart(ExcelScript.ChartType.columnClustered,
targetRange);
chart.setPosition('D1');

// Get images of the chart and table, then return them for a Power
Automate flow.
const chartImage = chart.getImage();
const tableImage = table.getRange().getImage();
return { chartImage, tableImage };
}

// The interface for table and chart images.

interface ReportImages {
chartImage: string
tableImage: string
}

Power Automate flow: Email the chart and

table images
This flow runs the script and emails the returned images.

1. Create a new Instant cloud flow.

2. Choose Manually trigger a flow and select Create.

3. Add a New step that uses the Excel Online (Business) connector with the Run
script action. Use the following values for the action.

Location: OneDrive for Business

Document Library: OneDrive
File: email-chart-table.xlsx (selected with the file chooser)
Script: Get chart image

4. This sample uses Outlook as the email client. You could use any email connector
Power Automate supports, but the rest of the steps assume that you chose
Outlook. Add a New step that uses the Office 365 Outlook connector and the
Send and email (V2) action. Use the following values for the action.

To: Your test email account (or personal email)

Subject: Please Review Report Data
For the Body field, select "Code View" ( <> ) and enter the following:

HTML

Please review the following report data:

Chart: 
 
<img src="data:image/png;base64,@{outputs('Run_script')?
['body/result/chartImage']}"/>
 
Data: 
 
<img src="data:image/png;base64,@{outputs('Run_script')?
['body/result/tableImage']}"/>
 

5. Save the flow and try it out. Use the Test button on the flow editor page or run the
flow through your My flows tab. Be sure to allow access when prompted.

Training video: Extract and email images of

chart and table
Watch Sudhi Ramamurthy walk through this sample on YouTube .

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Record day-to-day changes in Excel and
report them with a Power Automate
flow
Article • 04/05/2024

Power Automate and Office Scripts combine to handle repetitive tasks for you. In this
sample, you're tasked with recording a single numerical reading in a workbook every
day and reporting the change since yesterday. You'll build a flow to get that reading, log
it in the workbook, and report the change through an email.

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Record and report daily readings

Add the following script to the sample workbook. In Excel, use Automate > New Script
to paste the code and save the script. Save it as Record daily value and try the sample
yourself!

TypeScript

function main(workbook: ExcelScript.Workbook, newData: string): string {

// Get the table by its name.
const table = workbook.getTable("ReadingTable");

// Read the current last entry in the Reading column.

const readingColumn = table.getColumnByName("Reading");
const readingColumnValues = readingColumn.getRange().getValues();
const previousValue = readingColumnValues[readingColumnValues.length - 1]
[0] as number;

// Add a row with the date, new value, and a formula calculating the
difference.
const currentDate = new Date(Date.now()).toLocaleDateString();
const newRow = [currentDate, newData, "=[@Reading]-
OFFSET([@Reading],-1,0)"];
table.addRow(-1, newRow);

// Return the difference between the newData and the previous entry.
const difference = Number.parseFloat(newData) - previousValue;
console.log(difference);
return difference.toString();
}

Sample flow: Report day-to-day changes

Follow these steps to build a Power Automate flow for the sample.

1. Create a new Scheduled cloud flow.

2. Schedule the flow to repeat every 1 Day.

3. Select Create.

4. In a real flow, you'll add a step that gets your data. The data can come from
another workbook, a Teams adaptive card, or any other source. To test the sample,
make a test number. Add an action and choose the Initialize variable action. Give
it the following values.
a. Name: Input
b. Type: Integer
c. Value: 190000
5. Add an action and choose the Excel Online (Business) connector's Run script
action. Use the following values for the action.
a. Location: OneDrive for Business
b. Document Library: OneDrive
c. File: daily-readings.xlsx (Chosen through the file browser)
d. Script: Record daily value
e. newData: Input (dynamic content)

6. The script returns the daily reading difference as dynamic content named "result".
For the sample, you can email the information to yourself. Add an action and
choose the Outlook connector's Send an email (V2) action (or whatever email
client you prefer). Use the following values to complete the action.
a. To: Your email address
b. Subject: Daily reading change
c. Body: "Difference from yesterday:" result (dynamic content from Excel)

7. Save the flow and try it out. Use the Test button on the flow editor page. Be sure to
allow access when prompted.

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Run a script on all Excel files in a folder
Article • 04/05/2024

This project performs a set of automation tasks on all files situated in a folder on
OneDrive for Business. It could also be used on a SharePoint folder. It performs
calculations on the Excel files, adds formatting, and inserts a comment that
@mentions a colleague.

Sample Excel files

Download highlight-alert-excel-files.zip for all the workbooks you'll need for this
sample. Extract those files to a folder titled Sales. Add the following script to your script
collection to try the sample yourself!

Sample code: Add formatting and insert

comment
This is the script that runs on each individual workbook. In Excel, use Automate > New
Script to paste the code and save the script. Save it as Review script and try the sample
yourself!

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the table named "Table1" in the workbook.
const table1 = workbook.getTable("Table1");

// If the table is empty, end the script.

const rowCount = table1.getRowCount();
if (rowCount === 0) {
return;
}

// Force the workbook to be completely recalculated.

workbook.getApplication().calculate(ExcelScript.CalculationType.full);

// Get the "Amount Due" column from the table.

const amountDueColumn = table1.getColumnByName('Amount Due');
const amountDueValues =
amountDueColumn.getRangeBetweenHeaderAndTotal().getValues();

// Find the highest amount that's due.

let highestValue = amountDueValues[0][0];
let row = 0;
for (let i = 1; i < amountDueValues.length; i++) {
if (amountDueValues[i][0] > highestValue) {
highestValue = amountDueValues[i][0];
row = i;
}
}

let highestAmountDue = table1.getColumn("Amount

due").getRangeBetweenHeaderAndTotal().getRow(row);

// Set the fill color to yellow for the cell with the highest value in the
"Amount Due" column.
highestAmountDue.getFormat().getFill().setColor("FFFF00");

// Insert an @mention comment in the cell.

workbook.addComment(highestAmountDue, {
mentions: [{
email: "AdeleV@M365x904181.OnMicrosoft.com",
id: 0,
name: "Adele Vance"
}],
richContent: "<at id=\"0\">Adele Vance</at> Please review this amount"
}, ExcelScript.ContentType.mention);
}

Power Automate flow: Run the script on every

workbook in the folder
This flow runs the script on every workbook in the "Sales" folder.

1. Create a new Instant cloud flow.

2. Choose Manually trigger a flow and select Create.

3. In the flow builder, select the + button and Add an action. Use the OneDrive for
Business connector's List files in folder action. Use the following values for the
action.

Folder: /Sales (selected by the file picker)

4. Ensure only workbooks are selected. Add a new Condition control action. Use the
following values for the condition.

Choose a value: Name (dynamic content from List files in folder)

ends with: (from the dropdown list)
Choose a value: .xlsx

5. Under the True branch, add a new action. Select the Excel Online (Business)
connector's Run script action. Use the following values for the action.

Location: OneDrive for Business

Document Library: OneDrive
File: Id (dynamic content from List files in folder)
Script: Review script

6. Save the flow. The flow designer should look like the following image.
7. Try it out! Use the Test button on the flow editor page or run the flow through
your My flows tab. Be sure to allow access when prompted.

Training video: Run a script on all Excel files in

a folder
Watch Sudhi Ramamurthy walk through this sample on YouTube .
６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Write a large dataset
Article • 02/08/2024

The Range.setValues() API puts data in a range. This API has limitations depending on
various factors, such as data size and network settings. This means that if you attempt to
write a massive amount of information to a workbook as a single operation, you'll need
to write the data in smaller batches in order to reliably update a large range.

The first part of the sample shows how to write a large dataset in Excel. The second part
expands the example to be part of a Power Automate flow. This is necessary if your
script takes longer to run than the Power Automate action timeout.

For performance basics in Office Scripts, please read Improve the performance of your
Office Scripts.

Sample 1: Write a large dataset in batches

This script writes rows of a range in smaller parts. It selects 1000 cells to write at a time.
Run the script on a blank worksheet to see the update batches in action. The console
output gives further insight into what's happening.

７ Note

You can change the number of total rows being written by changing the value of
SAMPLE_ROWS . You can change the number of cells to write as a single action by

changing the value of CELLS_IN_BATCH .

TypeScript

function main(workbook: ExcelScript.Workbook) {

const SAMPLE_ROWS = 100000;
const CELLS_IN_BATCH = 10000;

// Get the current worksheet.

const sheet = workbook.getActiveWorksheet();

console.log(`Generating data...`)
let data: (string | number | boolean)[][] = [];
// Generate six columns of random data per row.
for (let i = 0; i < SAMPLE_ROWS; i++) {
data.push([i, ...[getRandomString(5), getRandomString(20),
getRandomString(10), Math.random()], "Sample data"]);
}
console.log(`Calling update range function...`);
const updated = updateRangeInBatches(sheet.getRange("B2"), data,
CELLS_IN_BATCH);
if (!updated) {
console.log(`Update did not take place or complete. Check and run
again.`);
}
}

function updateRangeInBatches(
startCell: ExcelScript.Range,
values: (string | boolean | number)[][],
cellsInBatch: number
): boolean {

const startTime = new Date().getTime();

console.log(`Cells per batch setting: ${cellsInBatch}`);

// Determine the total number of cells to write.

const totalCells = values.length * values[0].length;
console.log(`Total cells to update in the target range: ${totalCells}`);
if (totalCells <= cellsInBatch) {
console.log(`No need to batch -- updating directly`);
updateTargetRange(startCell, values);
return true;
}

// Determine how many rows to write at once.

const rowsPerBatch = Math.floor(cellsInBatch / values[0].length);
console.log("Rows per batch: " + rowsPerBatch);
let rowCount = 0;
let totalRowsUpdated = 0;
let batchCount = 0;

// Write each batch of rows.

for (let i = 0; i < values.length; i++) {
rowCount++;
if (rowCount === rowsPerBatch) {
batchCount++;
console.log(`Calling update next batch function. Batch#:
${batchCount}`);
updateNextBatch(startCell, values, rowsPerBatch, totalRowsUpdated);

// Write a completion percentage to help the user understand the

progress.
rowCount = 0;
totalRowsUpdated += rowsPerBatch;
console.log(`${((totalRowsUpdated / values.length) * 100).toFixed(1)}%
Done`);
}
}

console.log(`Updating remaining rows -- last batch: ${rowCount}`)

if (rowCount > 0) {
updateNextBatch(startCell, values, rowCount, totalRowsUpdated);
}

let endTime = new Date().getTime();

console.log(`Completed ${totalCells} cells update. It took: ${((endTime -
startTime) / 1000).toFixed(6)} seconds to complete. ${((((endTime -
startTime) / 1000)) / cellsInBatch).toFixed(8)} seconds per ${cellsInBatch}
cells-batch.`);

return true;
}

/**
* A helper function that computes the target range and updates.
*/
function updateNextBatch(
startingCell: ExcelScript.Range,
data: (string | boolean | number)[][],
rowsPerBatch: number,
totalRowsUpdated: number
) {
const newStartCell = startingCell.getOffsetRange(totalRowsUpdated, 0);
const targetRange = newStartCell.getResizedRange(rowsPerBatch - 1,
data[0].length - 1);
console.log(`Updating batch at range ${targetRange.getAddress()}`);
const dataToUpdate = data.slice(totalRowsUpdated, totalRowsUpdated +
rowsPerBatch);
try {
targetRange.setValues(dataToUpdate);
} catch (e) {
throw `Error while updating the batch range: ${JSON.stringify(e)}`;
}
return;
}

/**
* A helper function that computes the target range given the target range's
starting cell
* and selected range and updates the values.
*/
function updateTargetRange(
targetCell: ExcelScript.Range,
values: (string | boolean | number)[][]
) {
const targetRange = targetCell.getResizedRange(values.length - 1,
values[0].length - 1);
console.log(`Updating the range: ${targetRange.getAddress()}`);
try {
targetRange.setValues(values);
} catch (e) {
throw `Error while updating the whole range: ${JSON.stringify(e)}`;
}
return;
}
// Credit: https://www.codegrepper.com/code-
examples/javascript/random+text+generator+javascript
function getRandomString(length: number): string {
var randomChars =
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
var result = '';
for (var i = 0; i < length; i++) {
result += randomChars.charAt(Math.floor(Math.random() *
randomChars.length));
}
return result;
}

Training video: Write a large dataset

Watch Sudhi Ramamurthy walk through this sample on YouTube .

Sample 2: Write data in batches from a Power

Automate flow
For this sample, you'll need to complete the following steps.

1. Create a workbook in OneDrive named SampleData.xlsx.

2. Create a second workbook in OneDrive named TargetWorkbook.xlsx.
3. Open SampleData.xlsx with Excel.
4. Add sample data. You can use the script from the Write a large dataset in batches
section to generate this data.
5. Create and save both of the following scripts. Use Automate > New Script to paste
the code and save the scripts with the suggested names.
6. Follow the steps under Power Automate flow: Read and write data in a loop to
create the flow.

Sample code: Read selected rows

TypeScript

function main(
workbook: ExcelScript.Workbook,
startRow: number,
batchSize: number
): string[][] {
// This script only reads the first worksheet in the workbook.
const sheet = workbook.getWorksheets()[0];

// Get the boundaries of the range.

// Note that we're assuming usedRange is too big to read or write as a
single range.
const usedRange = sheet.getUsedRange();
const lastColumnIndex = usedRange.getLastColumn().getColumnIndex();
const lastRowindex = usedRange.getLastRow().getRowIndex();

// If we're starting past the last row, exit the script.

if (startRow > lastRowindex) {
return [[]];
}

// Get the next batch or the rest of the rows, whichever is smaller.
const rowCountToRead = Math.min(batchSize, (lastRowindex - startRow + 1));
const rangeToRead = sheet.getRangeByIndexes(startRow, 0, rowCountToRead,
lastColumnIndex + 1);
return rangeToRead.getValues() as string[][];
}

Sample code: Write data at row location

TypeScript

function main(
workbook: ExcelScript.Workbook,
data: string[][],
currentRow: number,
batchSize: number
): boolean {
// Get the first worksheet.
const sheet = workbook.getWorksheets()[0];

// Set the given data.

if (data && data.length > 0) {
sheet.getRangeByIndexes(currentRow, 0, data.length,
data[0].length).setValues(data);
}

// If the script wrote less data than the batch size, signal the end of
the flow.
return batchSize > data.length;
}

Power Automate flow: Read and write data in a loop

1. Sign into Power Automate and create a new Instant cloud flow.

2. Choose Manually trigger a flow and select Create.

3. Create a variable to track the current row being read and written. In the flow
builder, select the + button and Add an action. Select the Initialize variable action
and give it the following values.

Name: currentRow
Type: Integer
Value: 0

4. Add an action to set the number of rows to be read in a single batch. Depending
on the number of columns, this may need to be smaller to avoid the data transfer
limits. Make a new Initialize variable action with the following values.

Name: batchSize
Type: Integer
Value: 10000
5. Add a Do until control. The flow will read chunks of the data until it has all been
copied. You'll use the value of -1 to indicate the end of the data has been reached.
Give the control the following values.

Choose a value: currentRow (dynamic content)

is equal to (from the dropdown list)
Choose a value: -1

6. The remaining steps are added inside the Do control. Next, call the script to read
the data. Add the Excel Online (Business) connector's Run script action. Rename it
to Read data. Use the following values for the action.

Location: OneDrive for Business

Document Library: OneDrive
File: "SampleData.xlsx" (as selected by the file picker)
Script: Read selected rows
startRow: currentRow (dynamic content)
batchSize: batchSize (dynamic content)
7. Call the script to write the data. Add a second Run script action. Rename it to
Write data. Use the following values for the action.

Location: OneDrive for Business

Document Library: OneDrive
File: "TargetWorkbook.xlsx" (as selected by the file picker)
Script: Write data at row location
data: result (dynamic content from Read data)
Press Switch input to entire array first.
startRow: currentRow (dynamic content)
batchSize: batchSize (dynamic content)
8. Update the current row to reflect that a batch of data has been read and written.
Add an Increment variable action with the following values.

Name: currentRow
Value: batchSize (dynamic content)
9. Add a Condition control to check if the scripts have read everything. The "Write
data at row location" script returns true when it has written fewer rows than the
batch size allows. This means it's at the end of the data set. Create the Condition
control action with the following values.

Choose a value: result (dynamic content from Write data)

is equal to (from the dropdown list)
Choose a value: true (expression)

10. Under the True section of the Condition control, set the currentRow variable to be
-1. Add a Set variable action with the following values.

Name: currentRow
Value: -1

13. The "TargetWorkbook.xlsx" file should now have the data from "SampleData.xlsx".

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Office Scripts sample scenario: Analyze
web downloads
Article • 10/25/2023

In this scenario, you're tasked with analyzing download reports from your company's
website. The goal of this analysis is to determine if the web traffic is coming from the
United States or elsewhere in the world.

Your colleagues upload the raw data to your workbook. Each week's set of data has its
own worksheet. There is also the Summary worksheet with a table and chart that shows
week-over-week trends.

You'll develop a script that analyzes weekly downloads data in the active worksheet. It
will parse the IP address associated with each download and determine whether or not
it came from the US. The answer will be inserted in the worksheet as a boolean value
("TRUE" or "FALSE") and conditional formatting will be applied to those cells. The IP
address location results will be totaled on the worksheet and copied to the summary
table.

Scripting skills covered

Text parsing
Subfunctions in scripts
Conditional formatting
Tables

Setup instructions
1. Download sample workbook to your OneDrive.

Download the sample workbook

2. Open the workbook in Excel.

3. Under the Automate tab, select New Script and paste the following script into the
editor.

TypeScript

function main(workbook: ExcelScript.Workbook) {

/* Get the Summary worksheet and table.
* End the script early if either object is not in the workbook.
*/
let summaryWorksheet = workbook.getWorksheet("Summary");
if (!summaryWorksheet) {
console.log("The script expects a worksheet named \"Summary\".
Please download the correct template and try again.");
return;
}
let summaryTable = summaryWorksheet.getTable("Table1");
if (!summaryTable) {
console.log("The script expects a summary table named \"Table1\".
Please download the correct template and try again.");
return;
}

// Get the current worksheet.

let currentWorksheet = workbook.getActiveWorksheet();
if (currentWorksheet.getName().toLocaleLowerCase().indexOf("week")
!== 0) {
console.log("Please switch worksheet to one of the weekly data
sheets and try again.")
return;
}

// Get the values of the active range of the active worksheet.

let logRange = currentWorksheet.getUsedRange();

if (logRange.getColumnCount() !== 8) {
console.log(`Verify that you are on the correct worksheet. Either
the week's data has been already processed or the content is incorrect.
The following columns are expected: ${[
"Time Stamp", "IP Address", "kilobytes", "user agent code",
"milliseconds", "Request", "Results", "Referrer"
]}`);
return;
}
// Get the range that will contain TRUE/FALSE if the IP address is
from the United States (US).
let isUSColumn = logRange
.getLastColumn()
.getOffsetRange(0, 1);

// Get the values of all the US IP addresses.

let ipRange = workbook.getWorksheet("USIPAddresses").getUsedRange();
let ipRangeValues = ipRange.getValues() as number[][];
let logRangeValues = logRange.getValues() as string[][];
// Remove the first row.
let topRow = logRangeValues.shift();
console.log(`Analyzing ${logRangeValues.length} entries.`);

// Create a new array to contain the boolean representing if this is

a US IP address.
let newCol: (boolean | string)[][] = [];

// Go through each row in worksheet and add Boolean.

for (let i = 0; i < logRangeValues.length; i++) {
let curRowIP = logRangeValues[i][1];
if (findIP(ipRangeValues, ipAddressToInteger(curRowIP)) > 0) {
newCol.push([true]);
} else {
newCol.push([false]);
}
}

// Remove the empty column header and add proper heading.

newCol = [["Is US IP"], ...newCol];

// Write the result to the spreadsheet.

console.log(`Adding column to indicate whether IP belongs to US
region or not at address: ${isUSColumn.getAddress()}`);
console.log(newCol.length);
console.log(newCol);
isUSColumn.setValues(newCol);

// Call the local function to add summary data to the worksheet.

addSummaryData();

// Call the local function to apply conditional formatting.

applyConditionalFormatting(isUSColumn);

// Autofit columns.
currentWorksheet.getUsedRange().getFormat().autofitColumns();

// Get the calculated summary data.

let summaryRangeValues =
currentWorksheet.getRange("J2:M2").getValues();

// Add the corresponding row to the summary table.

summaryTable.addRow(null, summaryRangeValues[0]);
console.log("Complete.");
return;

/**
* A function to add summary data on the worksheet.
*/
function addSummaryData() {
// Add a summary row and table.
let summaryHeader = [["Year", "Week", "US", "Other"]];
let countTrueFormula =
"=COUNTIF(" + isUSColumn.getAddress() + ', "=TRUE")/' +
(newCol.length - 1);
let countFalseFormula =
"=COUNTIF(" + isUSColumn.getAddress() + ', "=FALSE")/' +
(newCol.length - 1);

let summaryContent = [
[
'=TEXT(A2,"YYYY")',
'=TEXTJOIN(" ", FALSE, "Wk", WEEKNUM(A2))',
countTrueFormula,
countFalseFormula
]
];
let summaryHeaderRow = currentWorksheet.getRange("J1:M1");
let summaryContentRow = currentWorksheet.getRange("J2:M2");
console.log("2");

summaryHeaderRow.setValues(summaryHeader);
console.log("3");

summaryContentRow.setValues(summaryContent);
console.log("4");

let formats = [[".000", ".000"]];

summaryContentRow
.getOffsetRange(0, 2)
.getResizedRange(0, -2).setNumberFormats(formats);
}
}
/**
* Apply conditional formatting based on TRUE/FALSE values of the Is US
IP column.
*/
function applyConditionalFormatting(isUSColumn: ExcelScript.Range) {
// Add conditional formatting to the new column.
let conditionalFormatTrue = isUSColumn.addConditionalFormat(
ExcelScript.ConditionalFormatType.cellValue
);
let conditionalFormatFalse = isUSColumn.addConditionalFormat(
ExcelScript.ConditionalFormatType.cellValue
);
// Set TRUE to light blue and FALSE to light orange.

conditionalFormatTrue.getCellValue().getFormat().getFill().setColor("#8
FA8DB");
conditionalFormatTrue.getCellValue().setRule({
formula1: "=TRUE",
operator: ExcelScript.ConditionalCellValueOperator.equalTo
});

conditionalFormatFalse.getCellValue().getFormat().getFill().setColor("#
F8CCAD");
conditionalFormatFalse.getCellValue().setRule({
formula1: "=FALSE",
operator: ExcelScript.ConditionalCellValueOperator.equalTo
});
}
/**
* Translate an IP address into an integer.
* @param ipAddress: IP address to verify.
*/
function ipAddressToInteger(ipAddress: string): number {
// Split the IP address into octets.
let octets = ipAddress.split(".");
// Create a number for each octet and do the math to create the
integer value of the IP address.
let fullNum =
// Define an arbitrary number for the last octet.
111 +
parseInt(octets[2]) * 256 +
parseInt(octets[1]) * 65536 +
parseInt(octets[0]) * 16777216;
return fullNum;
}
/**
* Return the row number where the ip address is found.
* @param ipLookupTable IP look-up table.
* @param n IP address to number value.
*/
function findIP(ipLookupTable: number[][], n: number): number {
for (let i = 0; i < ipLookupTable.length; i++) {
if (ipLookupTable[i][0] <= n && ipLookupTable[i][1] >= n) {
return i;
}
}
return -1;
}

4. Rename the script to Analyze Web Downloads and save it.

Running the script

Navigate to any of the Week** worksheets and run the Analyze Web Downloads script.
The script will apply the conditional formatting and location labelling on the current
sheet. It will also update the Summary worksheet.

Before running the script

After running the script
Office Scripts sample scenario: Fetch
and graph water-level data from NOAA
Article • 11/17/2022

In this scenario, you need to plot the water level at the National Oceanic and
Atmospheric Administration's Seattle station . You'll use external data to populate a
spreadsheet and create a chart.

You'll develop a script that uses the fetch command to query the NOAA Tides and
Currents database . That will get the water level recorded across a given time span. The
information will be returned as JSON , so part of the script will translate that into range
values. Once the data is in the spreadsheet, it will be used to make a chart.

For more information about working with JSON, read Use JSON to pass data to and
from Office Scripts.

Scripting skills covered

External API calls ( fetch )
JSON parsing
Charts

Setup instructions
1. Open the workbook in Excel.

2. Under the Automate tab, select New Script and paste the following script into the
editor.

TypeScript

/**
* Gets data from the National Oceanic and Atmospheric Administration's
Tides and Currents database.
* That data is used to make a chart.
*/
async function main(workbook: ExcelScript.Workbook) {
// Get the current sheet.
let currentSheet = workbook.getActiveWorksheet();

// Create selection of parameters for the fetch URL.

// More information on the NOAA APIs is found here:
// https://api.tidesandcurrents.noaa.gov/api/prod/
const option = "water_level";
const startDate = "20201225"; /* yyyymmdd date format */
const endDate = "20201227";
const station = "9447130"; /* Seattle */

// Construct the URL for the fetch call.

const strQuery =
`https://api.tidesandcurrents.noaa.gov/api/prod/datagetter?
product=${option}&begin_date=${startDate}&end_date=${endDate}&datum=MLL
W&station=${station}&units=english&time_zone=gmt&application=NOS.COOPS.
TAC.WL&format=json`;

console.log(strQuery);

// Resolve the Promises returned by the fetch operation.

const response = await fetch(strQuery);
const rawJson: string = await response.json();

// Translate the raw JSON into a usable state.

const stringifiedJson = JSON.stringify(rawJson);

// Note that we're only taking the data part of the JSON and
excluding the metadata.
const noaaData: NOAAData[] = JSON.parse(stringifiedJson).data;

// Create table headers and format them to stand out.

let headers = [["Time", "Level"]];
let headerRange = currentSheet.getRange("A1:B1");
headerRange.setValues(headers);
headerRange.getFormat().getFill().setColor("#4472C4");
headerRange.getFormat().getFont().setColor("white");

// Insert all the data in rows from JSON.

let noaaDataCount = noaaData.length;
let dataToEnter = [[], []]
for (let i = 0; i < noaaDataCount; i++) {
let currentDataPiece = noaaData[i];
dataToEnter[i] = [currentDataPiece.t, currentDataPiece.v];
}

let dataRange = currentSheet.getRange("A2:B" + String(noaaDataCount +

1)); /* +1 to account for the title row */
dataRange.setValues(dataToEnter);

// Format the "Time" column for timestamps.

dataRange.getColumn(0).setNumberFormatLocal("[$-en-US]mm/dd/yyyy
hh:mm AM/PM;@");

// Create and format a chart with the level data.

let chart =
currentSheet.addChart(ExcelScript.ChartType.xyscatterSmooth,
dataRange);
chart.getTitle().setText("Water Level - Seattle");
chart.setTop(0);
chart.setLeft(300);
chart.setWidth(500);
chart.setHeight(300);
chart.getAxes().getValueAxis().setShowDisplayUnitLabel(false);
chart.getAxes().getCategoryAxis().setTextOrientation(60);
chart.getLegend().setVisible(false);

// Add a comment with the data attribution.

currentSheet.addComment(
"A1",
`This data was taken from the National Oceanic and Atmospheric
Administration's Tides and Currents database on ${new
Date(Date.now())}.`
);

/**
* An interface to wrap the parts of the JSON we need.
* These properties must match the names used in the JSON.
*/
interface NOAAData {
t: string; // Time
v: number; // Level
}
}

3. Rename the script to NOAA Water Level Chart and save it.

Running the script

On any worksheet, run the NOAA Water Level Chart script. The script fetches the water
level data from December 25, 2020 to December 27, 2020. The const variables at the
beginning of the script can be changed to use different dates or get different station
information. The CO-OPS API For Data Retrieval describes how to get all this data.

After running the script

Office Scripts sample scenario: Grade
calculator
Article • 10/25/2023

In this scenario, you're an instructor tallying every student's end-of-term grades. You've
been entering the scores for their assignments and tests as you go. Now, it is time to
determine the students' fates.

You'll develop a script that totals the grades for each point category. It will then assign a
letter grade to each student based on the total. To help ensure accuracy, you'll add a
couple checks to see if any individual scores are too low or high. If a student's score is
less than zero or more than the possible point value, the script will flag the cell with a
red fill and not total that student's points. This will be a clear indication of which records
you need to double-check. You'll also add some basic formatting to the grades so you
can quickly view the top and bottom of the class.

Scripting skills covered

Cell formatting
Error checking
Regular expressions
Conditional formatting

Setup instructions
1. Download sample workbook to your OneDrive.

Download the sample workbook

2. Open the workbook in Excel.

3. Under the Automate tab, select New Script and paste the following script into the
editor.

TypeScript

function main(workbook: ExcelScript.Workbook) {

// Get the worksheet and validate the data.
let studentsRange = workbook.getActiveWorksheet().getUsedRange();
if (studentsRange.getColumnCount() !== 6) {
throw new Error(`The required columns are not present. Expected
column headers: "Student ID | Assignment score | Mid-term | Final |
Total | Grade"`);
}

let studentData = studentsRange.getValues();

// Clear the total and grade columns.

studentsRange.getColumn(4).getCell(1,
0).getAbsoluteResizedRange(studentData.length - 1, 2).clear();

// Clear all conditional formatting.

workbook.getActiveWorksheet().getUsedRange().clearAllConditionalFormats
();

// Use regular expressions to read the max score from the assignment,
mid-term, and final scores columns.
let maxScores: string[] = [];
const assignmentMaxMatches = (studentData[0][1] as
string).match(/\d+/);
const midtermMaxMatches = (studentData[0][2] as string).match(/\d+/);
const finalMaxMatches = (studentData[0][3] as string).match(/\d+/);

// Check the matches happened before proceeding.

if (!(assignmentMaxMatches && midtermMaxMatches && finalMaxMatches))
{
throw new Error(`The scores are not present in the column headers.
Expected format: "Assignments (n)|Mid-term (n)|Final (n)"`);
}

// Use the first (and only) match from the regular expressions as the
max scores.
maxScores = [assignmentMaxMatches[0], midtermMaxMatches[0],
finalMaxMatches[0]];

// Set conditional formatting for each of the assignment, mid-term,

and final scores columns.
maxScores.forEach((score, i) => {
let range = studentsRange.getColumn(i + 1).getCell(0,
0).getRowsBelow(studentData.length - 1);
setCellValueConditionalFormatting(
score,
range,
"#9C0006",
"#FFC7CE",
ExcelScript.ConditionalCellValueOperator.greaterThan
)
});

// Store the current range information to avoid calling the workbook

in the loop.
let studentsRangeFormulas =
studentsRange.getColumn(4).getFormulasR1C1();
let studentsRangeValues = studentsRange.getColumn(5).getValues();
/* Iterate over each of the student rows and compute the total score
and letter grade.
* Note that iterator starts at index 1 to skip first (header) row.
*/
for (let i = 1; i < studentData.length; i++) {
// If any of the scores are invalid, skip processing it.
if (studentData[i][1] > maxScores[0] ||
studentData[i][2] > maxScores[1] ||
studentData[i][3] > maxScores[2]) {
continue;
}
const total = (studentData[i][1] as number) + (studentData[i][2] as
number) + (studentData[i][3] as number);
let grade: string;
switch (true) {
case total < 60:
grade = "F";
break;
case total < 70:
grade = "D";
break;
case total < 80:
grade = "C";
break;
case total < 90:
grade = "B";
break;
default:
grade = "A";
break;
}

// Set total score formula.

studentsRangeFormulas[i][0] = '=RC[-2]+RC[-1]';
// Set grade cell.
studentsRangeValues[i][0] = grade;
}

// Set the formulas and values outside the loop.

studentsRange.getColumn(4).setFormulasR1C1(studentsRangeFormulas);
studentsRange.getColumn(5).setValues(studentsRangeValues);

// Put a conditional formatting on the grade column.

let totalRange = studentsRange.getColumn(5).getCell(0,
0).getRowsBelow(studentData.length - 1);
setCellValueConditionalFormatting(
"A",
totalRange,
"#001600",
"#C6EFCE",
ExcelScript.ConditionalCellValueOperator.equalTo
);
["D", "F"].forEach((grade) => {
setCellValueConditionalFormatting(
grade,
totalRange,
"#443300",
"#FFEE22",
ExcelScript.ConditionalCellValueOperator.equalTo
);
})
// Center the grade column.

studentsRange.getColumn(5).getFormat().setHorizontalAlignment(ExcelScri
pt.HorizontalAlignment.center);
}

/**
* Helper function to apply conditional formatting.
* @param value Cell value to use in conditional formatting formula1.
* @param range Target range.
* @param fontColor Font color to use.
* @param fillColor Fill color to use.
* @param operator Operator to use in conditional formatting.
*/
function setCellValueConditionalFormatting(
value: string,
range: ExcelScript.Range,
fontColor: string,
fillColor: string,
operator: ExcelScript.ConditionalCellValueOperator) {
// Determine the formula1 based on the type of value parameter.
let formula1: string;
if (isNaN(Number(value))) {
// For cell value equalTo rule, use this format: formula1:
"=\"A\"",
formula1 = `=\"${value}\"`;
} else {
// For number input (greater-than or less-than rules), just append
'='.
formula1 = `=${value}`;
}

// Apply conditional formatting.

let conditionalFormatting: ExcelScript.ConditionalFormat;
conditionalFormatting =
range.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue)
;

conditionalFormatting.getCellValue().getFormat().getFont().setColor(fon
tColor);

conditionalFormatting.getCellValue().getFormat().getFill().setColor(fil
lColor);
conditionalFormatting.getCellValue().setRule({ formula1, operator });
}

4. Rename the script to Grade Calculator and save it.

Run the script
Run the Grade Calculator script on the only worksheet. The script totals the grades and
assigns each student a letter grade. If any individual grades have more points than the
assignment or test is worth, then the offending grade is marked red and the total is not
calculated. Also, any 'A' grades are highlighted in green, while 'D' and 'F' grades are
highlighted in yellow.

Before you run the script

After running the script

Office Scripts sample scenario: Schedule
interviews in Teams
Article • 12/05/2023

In this scenario, you're an HR recruiter scheduling interview meetings with candidates in

Teams. You manage the interview schedule of candidates in an Excel file. You'll need to
send the Teams meeting invite to both the candidate and interviewers. You then need to
update the Excel file with the confirmation that Teams meetings have been sent.

The solution has three steps that are combined in a single Power Automate flow.

1. A script extracts data from a table and returns an array of objects as JSON data.
2. The data is then sent to the Teams Create a Teams meeting action to send invites.
3. The same JSON data is sent to another script to update the status of the invitation.

For more information about working with JSON, read Use JSON to pass data to and
from Office Scripts.

Scripting skills covered

Power Automate flows
Teams integration
Table parsing

Setup instructions

Download the workbook

1. Download the sample workbook to your OneDrive.

Download the sample workbook

2. Open the workbook in Excel.

3. Change at least one of the email addresses to your own so that you receive an
invite.

Create the scripts

1. Under the Automate tab, select New Script and paste the following script into the
editor. This will extract table data to schedule invites.

TypeScript

function main(workbook: ExcelScript.Workbook): InterviewInvite[] {

const MEETING_DURATION =
workbook.getWorksheet("Constants").getRange("B1").getValue() as number;
const MESSAGE_TEMPLATE =
workbook.getWorksheet("Constants").getRange("B2").getValue() as string;

// Get the interview candidate information.

const sheet = workbook.getWorksheet("Interviews");
const table = sheet.getTables()[0];
const dataRows = table.getRangeBetweenHeaderAndTotal().getValues();

// Convert the table rows into InterviewInvite objects for the flow.
let invites: InterviewInvite[] = [];
dataRows.forEach((row) => {
const inviteSent = row[1] as boolean;
if (!inviteSent) {
const startTime = new Date(Math.round(((row[6] as number) - 25569) *
86400 * 1000));
const finishTime = new Date(startTime.getTime() + MEETING_DURATION *
60 * 1000);
const candidateName = row[2] as string;
const interviewerName = row[4] as string;

invites.push({
ID: row[0] as string,
Candidate: candidateName,
CandidateEmail: row[3] as string,
Interviewer: row[4] as string,
InterviewerEmail: row[5] as string,
StartTime: startTime.toISOString(),
FinishTime: finishTime.toISOString(),
Message: generateInviteMessage(MESSAGE_TEMPLATE, candidateName,
interviewerName)
});
}
});

console.log(JSON.stringify(invites));
return invites;
}

function generateInviteMessage(
messageTemplate: string,
candidate: string,
interviewer: string) : string {
return messageTemplate.replace("_Candidate_",
candidate).replace("_Interviewer_", interviewer);
}
// The interview invite information.
interface InterviewInvite {
ID: string
Candidate: string
CandidateEmail: string
Interviewer: string
InterviewerEmail: string
StartTime: string
FinishTime: string
Message: string
}

1. Name the script Schedule Interviews for the flow.

2. Create another new script with the following code. This will mark rows as invited.

TypeScript

function main(workbook: ExcelScript.Workbook, invites: InterviewInvite[]) {

const table = workbook.getWorksheet("Interviews").getTables()[0];

// Get the ID and Invite Sent columns from the table.

const idColumn = table.getColumnByName("ID");
const idRange = idColumn.getRangeBetweenHeaderAndTotal().getValues();
const inviteSentColumn = table.getColumnByName("Invite Sent?");

const dataRowCount = idRange.length;

// Find matching IDs to mark the correct row.

for (let row = 0; row < dataRowCount; row++){
let inviteSent = invites.find((invite) => {
return invite.ID == idRange[row][0] as string;
});

if (inviteSent) {
inviteSentColumn.getRangeBetweenHeaderAndTotal().getCell(row,
0).setValue(true);
console.log(`Invite for ${inviteSent.Candidate} has been sent.`);
}
}
}

// The interview invite information.

interface InterviewInvite {
ID: string
Candidate: string
CandidateEmail: string
Interviewer: string
InterviewerEmail: string
StartTime: string
FinishTime: string
Message: string
}

1. Name the second script Record Sent Invites for the flow.

Create the Power Automate flow

This flow run the interview scheduling scripts, send the Teams meetings, and record the
activity back in the workbook.

1. Create a new Instant cloud flow.

2. Choose Manually trigger a flow and select Create.

3. In the flow builder, select the + button and Add an action. Use the Excel Online
(Business) connector's Run script action. Complete the action with the following
values.

Location: OneDrive for Business

Document Library: OneDrive
File: hr-interviews.xlsx (Chosen through the file browser)
Script: Schedule Interviews

4. Add an action that uses the Microsoft Teams connector's Create a Teams meeting
action. As you select dynamic content from the Excel connector, a For each block
will be generated for your flow. Complete the connector with the following values.

Subject: Contoso Interview

Message: Message (dynamic content from Run script)
Time zone: Pacific Standard Time
Start time: StartTime (dynamic content from Run script)
End time: FinishTime (dynamic content from Run script)
Calendar id: Calendar
Required attendees: CandidateEmail ; InterviewerEmail (dynamic content
from Run script - note the ';' separating the values)

5. In the same For each block, add another Run script action. Use the following
values.

Location: OneDrive for Business

Document Library: OneDrive
File: hr-interviews.xlsx (Chosen through the file browser)
Script: Record Sent Invites
invites: result (dynamic content from Run script)
Press Switch input to entire array first.

6. Save the flow. The flow designer should look like the following image.
7. Use the Test button on the flow editor page or run the flow through your My flows
tab. Be sure to allow access when prompted.

Training video: Send a Teams meeting from

Excel data
Watch Sudhi Ramamurthy walk through a version of this sample on YouTube . His
version uses a more robust script that handles changing columns and obsolete meeting
times.
Office Scripts sample scenario:
Automated task reminders
Article • 02/12/2024

In this scenario you're managing a project. You use an Excel worksheet to track your
employees' status every month. You often need to remind people to fill out their status,
so you've decided to automate that reminder process.

You'll create a Power Automate flow to message people with missing status fields and
apply their responses to the spreadsheet. To do this, you'll develop a pair of scripts to
handle the working with the workbook. The first script gets a list of people with blank
statuses and the second script adds a status string to the right row. You'll also make use
of Teams Adaptive Cards to have employees enter their status directly from the
notification.

Scripting skills covered

Create flows in Power Automate
Pass data to scripts
Return data from scripts
Teams Adaptive Cards
Tables

Prerequisites
This scenario uses Power Automate and Microsoft Teams .

Setup instructions

Download the workbook

1. Download the sample workbook to your OneDrive.

Download the sample workbook

2. Open the workbook in Excel.

Create the scripts

1. First, we need a script to get all the employees with status reports that are missing
from the spreadsheet. Under the Automate tab, select New Script and paste the
following script into the editor.

TypeScript

/**
* This script looks for missing status reports in a project management
table.
*
* @returns An array of Employee objects (containing their names and
emails).
*/
function main(workbook: ExcelScript.Workbook): Employee[] {
// Get the first worksheet and the first table on that worksheet.
let sheet = workbook.getFirstWorksheet()
let table = sheet.getTables()[0];

// Give the column indices names matching their expected content.

const NAME_INDEX = 0;
const EMAIL_INDEX = 1;
const STATUS_REPORT_INDEX = 2;

// Get the data for the whole table.

let bodyRangeValues =
table.getRangeBetweenHeaderAndTotal().getValues();

// Create the array of Employee objects to return.

let people: Employee[] = [];

// Loop through the table and check each row for completion.
for (let i = 0; i < bodyRangeValues.length; i++) {
let row = bodyRangeValues[i];
if (row[STATUS_REPORT_INDEX] === "") {
// Save the email to return.
people.push({ name: row[NAME_INDEX].toString(), email:
row[EMAIL_INDEX].toString() });
}
}

// Log the array to verify we're getting the right rows.

console.log(people);

// Return the array of Employees.

return people;
}

/**
* An interface representing an employee.
* An array of Employees will be returned from the script
* for the Power Automate flow.
*/
interface Employee {
name: string;
email: string;
}

2. Save the script with the name Get People.

3. Next, we need a second script to process the status report cards and put the new
information in the spreadsheet. In the Code Editor task pane, select New Script
and paste the following script into the editor.

TypeScript

/**
* This script applies the results of a Teams Adaptive Card about
* a status update to a project management table.
*
* @param senderEmail - The email address of the employee updating
their status.
* @param statusReportResponse - The employee's status report.
*/
function main(workbook: ExcelScript.Workbook,
senderEmail: string,
statusReportResponse: string) {

// Get the first worksheet and the first table in that worksheet.
let sheet = workbook.getFirstWorksheet();
let table = sheet.getTables()[0];

// Give the column indices names matching their expected content.

const NAME_INDEX = 0;
const EMAIL_INDEX = 1;
const STATUS_REPORT_INDEX = 2;

// Get the range and data for the whole table.

let bodyRange = table.getRangeBetweenHeaderAndTotal();
let tableRowCount = bodyRange.getRowCount();
let bodyRangeValues = bodyRange.getValues();

// Create a flag to denote success.

let statusAdded = false;

// Loop through the table and check each row for a matching email
address.
for (let i = 0; i < tableRowCount && !statusAdded; i++) {
let row = bodyRangeValues[i];

// Check if the row's email address matches.

if (row[EMAIL_INDEX] === senderEmail) {
// Add the Teams Adaptive Card response to the table.
bodyRange.getCell(i, STATUS_REPORT_INDEX).setValues([
[statusReportResponse]
]);
statusAdded = true;
}
}

// If successful, log the status update.

if (statusAdded) {
console.log(
`Successfully added status report for ${senderEmail} containing:
${statusReportResponse}`
);
}
}

4. Save the script with the name Save Status.

Create the Power Automate flow

1. Open the Power Automate Create tab .

 Tip

If you haven't created a flow before, please check out our tutorial Start using
scripts with Power Automate to learn the basics.

2. Create a new Instant cloud flow.

3. Choose Manually trigger a flow from the options and select Create.

4. The flow needs to call the Get People script to get all the employees with empty
status fields. In the flow builder, select the + button and Add an action. Select the
Excel Online (Business) connector's Run script action. Provide the following entries
for the flow step:

Location: OneDrive for Business

Document Library: OneDrive
File: task-reminders.xlsx (Chosen through the file browser)
Script: Get People
5. Next, the flow needs to process each Employee in the array returned by the script.
Add the Microsoft Teams connector's Post adaptive card and wait for a response
action.

6. Sending an Adaptive Card requires the card's JSON to be provided as the

Message. You can use the Adaptive Card Designer to create custom cards. For
this sample, use the following JSON.

JSON

{
"$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
"type": "AdaptiveCard",
"version": "1.0",
"body": [
{
"type": "TextBlock",
"size": "Medium",
"weight": "Bolder",
"text": "Update your Status Report"
},
{
"type": "Image",
"altText": "",
"url": "https://i.imgur.com/f5RcuF3.png"
},
{
"type": "TextBlock",
"text": "This is a reminder to update your status report for this
month's review. You can do so right here in this card, or by adding it
directly to the spreadsheet.",
"wrap": true
},
{
"type": "Input.Text",
"placeholder": "My status report for this month is...",
"id": "response",
"isMultiline": true
}
],
"actions": [
{
"type": "Action.Submit",
"title": "Submit",
"id": "submit"
}
]
}

7. For the Recipient field, add email from the dynamic content (the selection will
have the Excel logo by it). Adding email causes the flow step to be surrounded by
a For each block. That means the array will be iterated over by Power Automate.

8. Fill out the remaining fields as follows:

Post as: Flow bot

Post in: Chat with Flow bot
Update message: Thank you for submitting your status report. Your response
has been successfully added to the spreadsheet.
9. In the For each block, following the Post adaptive card and wait for a response
action, add a new action. Select the Excel Online (Business) connector's Run script
action. Provide the following entries for the flow step:

Location: OneDrive for Business

Document Library: OneDrive
File: task-reminders.xlsx (Chosen through the file browser)
Script: Save Status
senderEmail: email (dynamic content from Excel)
statusReportResponse: response (dynamic content from Teams)

10. Save the flow. The flow designer should look like the following image.
Running the flow
To test the flow, make sure any table rows with blank status use an email address tied to
a Teams account (you should probably use your own email address while testing). Use
the Test button on the flow editor page or run the flow through your My flows tab. Be
sure to allow access when prompted.

You should receive an Adaptive Card from Power Automate through Teams. Once you fill
out the status field in the card, the flow will continue and update the spreadsheet with
the status you provide.
Before running the flow

Receiving the Adaptive Card

After running the flow

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Office Scripts sample scenario: Punch
clock button
Article • 02/07/2024

The scenario idea and script used in this sample was contributed by Office Scripts
community member Brian Gonzalez .

In this scenario, you'll create a time sheet for an employee that allows them to record
their start and end times with a button. Based on what's previously been recorded,
selecting the button will either start their day (clock in) or end their day (clock out).

Setup instructions
1. Download the sample workbook to your OneDrive.

Download the sample workbook

2. Open the workbook in Excel.

3. Under the Automate tab, select New Script and paste the following script into the
editor.

TypeScript

/**
* This script records either the start or end time of a shift,
* depending on what is filled out in the table.
* It is intended to be used with a Script Button.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the first table in the timesheet.
const timeSheet = workbook.getWorksheet("MyTimeSheet");
const timeTable = timeSheet.getTables()[0];

// Get the appropriate table columns.

const clockInColumn = timeTable.getColumnByName("Clock In");
const clockOutColumn = timeTable.getColumnByName("Clock Out");
const durationColumn = timeTable.getColumnByName("Duration");

// Get the last rows for the Clock In and Clock Out columns.
let clockInLastRow =
clockInColumn.getRangeBetweenHeaderAndTotal().getLastRow();
let clockOutLastRow =
clockOutColumn.getRangeBetweenHeaderAndTotal().getLastRow();

// Get the current date to use as the start or end time.

let date: Date = new Date();

// Add the current time to a column based on the state of the table.
if (clockInLastRow.getValue() as string === "") {
// If the Clock In column has an empty value in the table, add a
start time.
clockInLastRow.setValue(date.toLocaleString());
} else if (clockOutLastRow.getValue() as string === "") {
// If the Clock Out column has an empty value in the table,
// add an end time and calculate the shift duration.
clockOutLastRow.setValue(date.toLocaleString());
const clockInTime = new Date(clockInLastRow.getValue() as string);
const clockOutTime = new Date(clockOutLastRow.getValue() as
string);
const clockDuration = Math.abs((clockOutTime.getTime() -
clockInTime.getTime()));

let durationString = getDurationMessage(clockDuration);

durationColumn.getRangeBetweenHeaderAndTotal().getLastRow().setValue(du
rationString);
} else {
// If both columns are full, add a new row, then add a start time.
timeTable.addRow()
clockInLastRow.getOffsetRange(1,
0).setValue(date.toLocaleString());
}
}

/**
* A function to write a time duration as a string.
*/
function getDurationMessage(delta: number) {
// Adapted from here:
// https://stackoverflow.com/questions/13903897/javascript-return-
number-of-days-hours-minutes-seconds-between-two-dates

delta = delta / 1000;

let durationString = "";

let days = Math.floor(delta / 86400);

delta -= days * 86400;

let hours = Math.floor(delta / 3600) % 24;

delta -= hours * 3600;

let minutes = Math.floor(delta / 60) % 60;

if (days >= 1) {
durationString += days;
durationString += (days > 1 ? " days" : " day");

if (hours >= 1 && minutes >= 1) {

durationString += ", ";
}
else if (hours >= 1 || minutes > 1) {
durationString += " and ";
}
}

if (hours >= 1) {
durationString += hours;
durationString += (hours > 1 ? " hours" : " hour");
if (minutes >= 1) {
durationString += " and ";
}
}

if (minutes >= 1) {
durationString += minutes;
durationString += (minutes > 1 ? " minutes" : " minute");
}

return durationString;
}

4. Rename the script to "Punch clock".

5. Save the script.

6. In the workbook, select cell E2.

7. Add a script button. Go to the More options (…) menu in the Script details page
and select Add in workbook.

8. Save the workbook.

Run the script

Select the Punch clock button to run the script. It either logs the current time under
"Clock In" or "Clock Out", depending on what was previously entered.
７ Note

The duration is only recorded if it's longer than a minute. Manually edit the "Clock
In" time to test larger durations.

This script was contributed by Leslie Black in the spirit of the holiday season! It's a fun
script that shows a singing tree in Excel using Office Scripts.

Enjoy!

Watch the Seasons greetings script in action on the "Les's IT Blog" YouTube channel .

Setup: Sample Excel file

This workbook contains the data, objects, and formatting expected by the script.

Download the sample workbook

Sample code: Happy tree

Add the following script to the sample workbook and try the sample yourself!

TypeScript

/* Original version by Leslie Black. */

function main(workbook: ExcelScript.Workbook) {

let happyTree = workbook.getWorksheet('HappyTree');
happyTree.activate();

setOuterEdgeYellow(workbook) //yellow
setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
setFlashingStarAndSmileRed(workbook) //red
setFlashingStarAndSmileYellow(workbook) //yellow

setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow

setOuterEdgeRed(workbook) //red
setOuterEdgeYellow(workbook) //yellow
unblink(workbook)

console.log('Routine finished');

function blink(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getWorksheet('HappyTree');
// Set the eyes to brown.
selectedSheet.getRanges("N16:Q17, G16: J17")
.getFormat()
.getFill()
.setColor("C65911");
}

function unblink(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getWorksheet('HappyTree');
// Set the eyes back to white (except the pupils).
selectedSheet.getRanges("N16:N17, O16:Q16, G16:H17, I16:J16, P17:Q17,
J17")
.getFormat()
.getFill()
.setColor("FFFFFF");
}

function setFlashingStarAndSmileRed(workbook: ExcelScript.Workbook) {

// Set the star to red.
let selectedSheet = workbook.getWorksheet('HappyTree');
selectedSheet.getRanges("L2:L6, K3:K5, M3:M5, N4, J4")
.getFormat()
.getFill()
.setColor("FF0000");
// Set the smile points to black.
selectedSheet.getRanges("I26, O26")
.getFormat()
.getFill()
.setColor("000000");
}

function setFlashingStarAndSmileYellow(workbook: ExcelScript.Workbook) {

// Set the start to yellow.
let selectedSheet = workbook.getWorksheet('HappyTree');
selectedSheet.getRanges("L2:L6, K3:K5, M3:M5, N4, J4")
.getFormat()
.getFill()
.setColor("FFFF00");
// Clear the smile points.
selectedSheet.getRanges("O26, I26")
.getFormat()
.getFill().clear();
}
}

function setOuterEdgeYellow(workbook: ExcelScript.Workbook) {

let sheet = workbook.getWorksheet('HappyTree');
// Set the outer edge to yellow.
sheet.getRanges("Q11, G11, R12, F12, S13, E13, T14, D14, C15, U15,
T16:T17, D16:D17, C18, U18, T19, D19, L2:L6, C21, U21, C23, U23, C25, U25,
C27, U27, C29, U29, T30, D30, K3:K5, M3: M5, S31, E31, R32, F32, Q33, G33,
P34, H34, O35, I35, N36:N37, J36:J37, K37:M37, N4, J4, K7, M7, N8, J8, O9,
I9, P10, H10")
.getFormat()
.getFill()
.setColor("FFFF00");
}

function setOuterEdgeRed(workbook: ExcelScript.Workbook) {

let sheet = workbook.getWorksheet('HappyTree');
// Set the outer edge to red.
sheet.getRanges("Q11, G11, R12, F12, S13, E13, T14, D14, C15, U15,
T16:T17, D16:D17, C18, U18, T19, D19, L2:L6, C21, U21, C23, U23, C25, U25,
C27, U27, C29, U29, T30, D30, K3:K5, M3: M5, S31, E31, R32, F32, Q33, G33,
P34, H34, O35, I35, N36:N37, J36:J37, K37:M37, N4, J4, K7, M7, N8, J8, O9,
I9, P10, H10")
.getFormat()
.getFill()
.setColor("FF0000");
}
Differences between Office Scripts and
VBA macros
Article • 04/11/2023

Office Scripts and VBA macros have a lot in common. They both allow users to automate
solutions through an easy-to-use action recorder and allow edits of those recordings.
Both frameworks are designed to empower people who may not consider themselves
programmers to create small programs in Excel.

The fundamental difference is that VBA macros are developed for desktop solutions and
Office Scripts are designed for secure, cross-platform, cloud-based solutions.

This article describes the main differences between VBA macros (as well as VBA in
general) and Office Scripts. Since Office Scripts are only available for Excel, that is the
only host being discussed here.

Platform and ecosystem

The following table shows which features are supported by which platforms and
products.

ﾉ Expand table
Excel on Excel for Excel Excel Other Office Power
the web Windows for Mac for iOS products Automate

Office Scripts Yes Yes Yes No No Yes

Office Scripts Yes Yes Yes No No No

Action
Recorder

VBA macros No Yes Yes No Yes No

Office Add-ins Yes Yes Yes Yes Yes No

COM Add-ins No Yes Yes No Yes No

VBA is designed to be desktop-centric. VBA can interact with a user's desktop to

connect with similar technologies, such as COM and OLE. However, VBA has no
convenient way to call out to the internet. Office Scripts use a universal runtime for
JavaScript. This gives consistent behavior and accessibility, regardless of the machine
being used to run the script. They can also make calls to a limited set of web services.

Users require an enterprise or educational license to use or create Office Scripts. For the
full list of supported licenses, see Platform support. VBA is built-in to the desktop
versions of Excel and requires no special licensing.

Security
VBA macros have the same security clearance as Excel. This gives them full access to
your desktop. Office Scripts only have access to the workbook, not the machine hosting
the workbook. Additionally, no JavaScript authentication tokens can be shared with
scripts. This means the script has neither the tokens of the signed-in user nor are there
any API capabilities for signing in to an external service, so they are unable to use
existing tokens to make external calls on behalf of the user.

Admins have three options for VBA macros: allow all macros on the tenant, allow no
macros on the tenant, or allow only macros with signed certificates. This lack of
granularity makes it hard to isolate a single bad actor. Currently, Office Scripts can be off
for an entire tenant, on for an entire tenant, or on for a group of users in a tenant.
Admins also have control over who can share scripts with others and who can use
scripts in Power Automate.

Coverage
Currently, VBA offers a more complete coverage of Excel features, particularly those
available on the desktop client. Office Scripts cover nearly all of the scenarios for Excel
on the web. Additionally, as new features debut on the web, Office Scripts will support
them for both the Action Recorder and JavaScript APIs.

Office Scripts don't support Excel-level events. Scripts are only run when a user manually
starts them or when a Power Automate flow calls the script.

Power Automate
VBA doesn't have a Power Automate connector. All supported VBA scenarios involve a
user attending to the macro's execution.

Office Scripts can be run through Power Automate. Your workbook can be updated
through scheduled or event-driven flows, letting you automate workflows without even
opening Excel. Try Tutorial: Update a spreadsheet from a Power Automate flow to start
learning about Power Automate. You can also check out the Automated task reminders
sample to see Office Scripts connected to Teams through Power Automate in a real-
world scenario.

See also
Office Scripts in Excel
Run Office Scripts with Power Automate
Differences between Office Scripts and Office Add-ins
Troubleshooting Office Scripts
Excel VBA reference

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
Differences between Office Scripts and
Office Add-ins
Article • 03/30/2023

Understand the differences between Office Scripts and Office Add-ins to know when to
use each one. Office Scripts are designed to be quickly made by anyone looking to
improve their workflow. Office Add-ins integrate with the Office UI for a more interactive
experience through ribbon buttons and task panes. Office Add-ins can also expand
built-in Excel functions by providing custom functions.

Office Scripts run to completion with a manual button selection or as a step in Power
Automate , whereas Office Add-ins continue running depending on how they are
configured. For example, you can configure an Office Add-in to continue running even
when its task pane is closed. This means that Office Add-ins maintain state during a
session, whereas Office Scripts don't maintain an internal state between runs. If the
solution you're building requires a maintained state, you should visit the Office Add-ins
documentation to learn more about Office Add-ins.

The rest of this article describes on the main differences between Office Add-ins and
Office Scripts.

Platform support
The following table shows which features are supported by which platforms and
products.

ﾉ Expand table

Excel on Excel for Excel Excel Other Office Power

the web Windows for Mac for iOS products Automate

Office Scripts Yes Yes Yes No No Yes

Office Scripts Yes Yes Yes No No No

Action
Recorder

VBA macros No Yes Yes No Yes No

Office Add-ins Yes Yes Yes Yes Yes No

COM Add-ins No Yes Yes No Yes No

APIs
While the Office JavaScript APIs for Office Add-ins and the Office Scripts APIs share
some functionality, they are different platforms. The Office Scripts APIs are an optimized,
synchronous subset of the Excel JavaScript API model. The major difference is usage of
the load / sync paradigm with add-ins. Additionally, add-ins offer APIs for events and a
broader set of functionality outside of Excel, known as the Common APIs.

Office Add-ins offer greater connectivity to external web services and libraries. Office
Scripts are limited to specific external calls.

Events
Office Scripts do not support workbook-level events. Scripts are either triggered by
users selecting the Run button for a script or through Power Automate. Every script runs
the code in a single main function, then ends.

UX and UI controls
Office Scripts can only interact with the workbook, not the task pane. If you need
authentication, dialog windows, or additional UX and UI controls, you'll need to create
an Office Add-in instead of an Office Script.
See also
Office Scripts in Excel
Differences between Office Scripts and VBA macros
Troubleshooting Office Scripts
Build an Excel task pane add-in

６ Collaborate with us on
GitHub Office Scripts feedback
Office Scripts is an open source project.
The source for this content can
Select a link to provide feedback:
be found on GitHub, where you
can also create and review
 Open a documentation issue
issues and pull requests. For
more information, see our
 Provide product feedback
contributor guide.
When to use Power Query or Office
Scripts
Article • 03/30/2023

Power Query and Office Scripts are both powerful automation solutions for Excel.
Both solutions let Excel users clean and transform data in workbooks. A single Power
Query or Office Script can be refreshed and rerun on new data to produce consistent
results, which saves you time and lets you work with the resulting information faster.

This article provides a general overview of when you might favor one platform over the
other. In general, Power Query is good for pulling and transforming data from large,
external data sources and Office Scripts are good for quick, Excel-centric solutions and
Power Automate integrations.

Large data sources and data retrieval: Power

Query
We recommend Power Query when dealing with data sources from supported platforms.

Power Query has built-in data connections to hundreds of sources. Power Query is
specially designed for data retrieval, transformation, and combination tasks. When you
need data from one of those sources, Power Query gives you a no-code way of bringing
that data into Excel in the shape you need.

These Power Query connections are designed for large datasets. They do not have the
same transfer limits as Power Automate or Excel on the web.

Office Scripts offer a lightweight solution for smaller data sources or data sources not
covered by Power Query connectors. This includes using fetch or REST APIs or getting
information from ad-hoc data sources, such as a Teams adaptive card.

Formatting, visualizations, and programmatic

control: Office Scripts
We recommend Office Scripts when your needs go beyond data importing and
transformation.

Nearly everything you can do manually through the Excel UI is doable with Office
Scripts. They're great for applying consistent formatting to workbooks. Scripts create
charts, PivotTables, shapes, images, and other worksheet visualizations. Scripts also give
you precise control over the positions, sizes, colors, and other attributes of these
visualizations.

The inclusion of TypeScript code gives you a high degree of customization.

Programmatic control logic like if...else statements makes your script robust. This lets
you do things like conditionally read data without relying on complex Excel formulas, or
scan the workbook for unexpected changes before changing the workbook.

Formatting can be applied with Power Query through Excel templates . However,
templates are updated at the individual or organization level, whereas Office Scripts
offer more granular access control.

Power Automate integrations

Office Scripts offer more options for Power Automate integration. Scripts are tailored to
your solutions. You define the input and output of the script, so it works with any other
connector or data in the flow. The following screenshot shows an example Power
Automate flow that passes data from a Teams Adaptive Card to an Office Script.

Power Query is used in the SQL Server Power Automate connector. The Transform
data using Power Query action lets you build a query in Power Automate. While this is a
powerful tool for use with SQL Server, it does limit Power Query to that input source, as
shown in the following flow screenshot.

Platform dependencies
Office Scripts is currently available for Excel on the web, Excel for Windows, and Excel for
Mac. The full Power Query experience is currently only available for Excel for Windows.
Both can be used through Power Automate, which lets the flow work with Excel
workbooks stored in OneDrive.

See also
Power Query Portal
Power Query with Excel
Run Office Scripts with Power Automate

(Ebook PDF) Microsoft Office 365: in Practice, 2019 Editioninstant Download
100% (3)
(Ebook PDF) Microsoft Office 365: in Practice, 2019 Editioninstant Download
51 pages
Python For Excel Users
No ratings yet
Python For Excel Users
30 pages
Dokumen - Pub Excel 2024 From Beginners To Pro Simplify Your Work and Dominate Data With Smart Excel Strategies Secret Winning Formulas With Step by Step Tutorials To Stand Out From The Crowd
No ratings yet
Dokumen - Pub Excel 2024 From Beginners To Pro Simplify Your Work and Dominate Data With Smart Excel Strategies Secret Winning Formulas With Step by Step Tutorials To Stand Out From The Crowd
121 pages
Microsoft Access VBA
100% (1)
Microsoft Access VBA
18 pages
Pyxll-5 1 0
100% (1)
Pyxll-5 1 0
185 pages
Academic Staff Promotion Guide
No ratings yet
Academic Staff Promotion Guide
3 pages
Practical Guide Measuring Shrinkage
No ratings yet
Practical Guide Measuring Shrinkage
4 pages
Step by Step Tutorial Microsoft Excel 1
No ratings yet
Step by Step Tutorial Microsoft Excel 1
17 pages
Offer Letter
No ratings yet
Offer Letter
17 pages
Vlookup Training
No ratings yet
Vlookup Training
2 pages
XLookup - Exceljet
No ratings yet
XLookup - Exceljet
13 pages
Anil Nahar - Ready To Use 101 Powerful Excel VBA Code Just Copy - Paste - Run (For Functional Users) (2017) PDF
100% (1)
Anil Nahar - Ready To Use 101 Powerful Excel VBA Code Just Copy - Paste - Run (For Functional Users) (2017) PDF
123 pages
Solver Frontline Solvers Reference Guide
No ratings yet
Solver Frontline Solvers Reference Guide
305 pages
What Is Power Query
No ratings yet
What Is Power Query
17 pages
Excel 2016 Intermediate
0% (1)
Excel 2016 Intermediate
157 pages
Mon Excel Skills For Data Exploration PSO 11
No ratings yet
Mon Excel Skills For Data Exploration PSO 11
86 pages
Excel VBA Programming Guide
No ratings yet
Excel VBA Programming Guide
87 pages
Lesson 5 Analyzing Data With PivotTable
No ratings yet
Lesson 5 Analyzing Data With PivotTable
49 pages
VBA 9 Excel Events PDF
No ratings yet
VBA 9 Excel Events PDF
3 pages
SQL Commands 1
No ratings yet
SQL Commands 1
32 pages
Computer Lab Report (MS-EXCEL) (2081!07!13)
No ratings yet
Computer Lab Report (MS-EXCEL) (2081!07!13)
16 pages
Bill of Materials
100% (1)
Bill of Materials
24 pages
Employee Performance Dataset
No ratings yet
Employee Performance Dataset
8 pages
Exercise - Replacement Decision
No ratings yet
Exercise - Replacement Decision
1 page
How To Use Microsoft Excel
No ratings yet
How To Use Microsoft Excel
400 pages
Basics of VBA Macro
No ratings yet
Basics of VBA Macro
12 pages
Vba 00
No ratings yet
Vba 00
14 pages
Compensation Project Two
No ratings yet
Compensation Project Two
8 pages
Excel Student
100% (1)
Excel Student
6 pages
Microsoft Excel 365 - Basic & Advanced: Getting To Know Excel
No ratings yet
Microsoft Excel 365 - Basic & Advanced: Getting To Know Excel
6 pages
INTRODUCTION TO WHAT - If ANALYSIS
No ratings yet
INTRODUCTION TO WHAT - If ANALYSIS
15 pages
Introduction To MS Excel
No ratings yet
Introduction To MS Excel
1 page
Microsoft Access
No ratings yet
Microsoft Access
8 pages
Excel Basics for Beginners
No ratings yet
Excel Basics for Beginners
97 pages
Power BI DAX Functions Guide
No ratings yet
Power BI DAX Functions Guide
6 pages
Mo 200 1
No ratings yet
Mo 200 1
4 pages
AI in HR: Opportunities and Challenges
No ratings yet
AI in HR: Opportunities and Challenges
6 pages
Excel Lab Report for Grade XI
No ratings yet
Excel Lab Report for Grade XI
12 pages
Excel Xlookup
No ratings yet
Excel Xlookup
25 pages
Frontline Solvers User Guide
No ratings yet
Frontline Solvers User Guide
271 pages
40 Excel Tips For Becoming A Spreadsheet Pro - PCMag
No ratings yet
40 Excel Tips For Becoming A Spreadsheet Pro - PCMag
32 pages
Excel Skills
100% (1)
Excel Skills
1 page
Excel Basics2013
100% (1)
Excel Basics2013
20 pages
Excel Cheat Sheet
No ratings yet
Excel Cheat Sheet
74 pages
Excel Data Analysis For Dummies, 5th Edition McFedries
No ratings yet
Excel Data Analysis For Dummies, 5th Edition McFedries
10 pages
Microsoft Excel Basics Guide
100% (1)
Microsoft Excel Basics Guide
20 pages
DELL Certificat
No ratings yet
DELL Certificat
1 page
Excel 2003 Power Programming With Vba PDF Site Ru
No ratings yet
Excel 2003 Power Programming With Vba PDF Site Ru
2 pages
IDP Employee Central Core Hybrid - Organization Structure Design Consideration V2.0
No ratings yet
IDP Employee Central Core Hybrid - Organization Structure Design Consideration V2.0
45 pages
Excel 2016 VBA PPT Slide Deck
No ratings yet
Excel 2016 VBA PPT Slide Deck
111 pages
Excel VBA & Macro Guide
No ratings yet
Excel VBA & Macro Guide
4 pages
Power BI Building Blocks
No ratings yet
Power BI Building Blocks
16 pages
Microsoft Excel Tutorial
No ratings yet
Microsoft Excel Tutorial
12 pages
Excel For - Data Analytics
No ratings yet
Excel For - Data Analytics
40 pages
SQL Manual New
100% (2)
SQL Manual New
26 pages
Javascript API Office Scripts Office Scripts
No ratings yet
Javascript API Office Scripts Office Scripts
1,341 pages
Electronic Spreadsheet
No ratings yet
Electronic Spreadsheet
6 pages
Teaching Aid MS Excel
No ratings yet
Teaching Aid MS Excel
31 pages
Scripting QTP - CH14 - Working With Files
No ratings yet
Scripting QTP - CH14 - Working With Files
112 pages
MOS 2013 Microsoft® Excel® Expert: Harold Lamostre
No ratings yet
MOS 2013 Microsoft® Excel® Expert: Harold Lamostre
36 pages
Nicole's Court Verdict Unveiled
No ratings yet
Nicole's Court Verdict Unveiled
3 pages
Project Development M1 Chapter 2
No ratings yet
Project Development M1 Chapter 2
11 pages
Shaheed Baba Deep Singh Ji-Babu Rajab Ali
No ratings yet
Shaheed Baba Deep Singh Ji-Babu Rajab Ali
26 pages
Form XIV 4th QTR - Gr. 10
No ratings yet
Form XIV 4th QTR - Gr. 10
9 pages
Executive Summary: Operations Management Assignment Report Writing: Importance of Operations Management
No ratings yet
Executive Summary: Operations Management Assignment Report Writing: Importance of Operations Management
12 pages
2025 Campus Job Description - Ops - Consolidated
No ratings yet
2025 Campus Job Description - Ops - Consolidated
6 pages
Practical Research 2 Theoretical and Conceptual Framework Jerry V. Manlapaz, RN, PHD, Edd, LPT Theoretical and Conceptual Framework
No ratings yet
Practical Research 2 Theoretical and Conceptual Framework Jerry V. Manlapaz, RN, PHD, Edd, LPT Theoretical and Conceptual Framework
5 pages
Maintenance Fowa - Fuel - 50305
100% (1)
Maintenance Fowa - Fuel - 50305
20 pages
BS-5950-90 Example 001
No ratings yet
BS-5950-90 Example 001
7 pages
Types of Enterprises
No ratings yet
Types of Enterprises
21 pages
Forensic Accounting 1st Edition Rufus Fast Access
0% (1)
Forensic Accounting 1st Edition Rufus Fast Access
311 pages
Final Candlestick Patterns Guide
No ratings yet
Final Candlestick Patterns Guide
3 pages
Suggest and Offer
No ratings yet
Suggest and Offer
3 pages
Drawing - Design pp1 Form 4
No ratings yet
Drawing - Design pp1 Form 4
8 pages
Prayer to Renounce Pride & Embrace God's Truth
100% (2)
Prayer to Renounce Pride & Embrace God's Truth
5 pages
Bahan Berbahaya & Beracun (B3)
100% (1)
Bahan Berbahaya & Beracun (B3)
8 pages
Oliver P. de Guzman: Objective
No ratings yet
Oliver P. de Guzman: Objective
3 pages
Tonepad Bigmuff
No ratings yet
Tonepad Bigmuff
2 pages
Math Problem Set with Solutions
0% (1)
Math Problem Set with Solutions
24 pages
Ocean County Board of Elections Hagler Challenge
No ratings yet
Ocean County Board of Elections Hagler Challenge
6 pages
CMSL MAIN Book CS Vikas Vohra, YES Academy (Jan 24)
100% (1)
CMSL MAIN Book CS Vikas Vohra, YES Academy (Jan 24)
548 pages
Barefoot Counsellor
100% (1)
Barefoot Counsellor
18 pages
Generalized Periodontitis Treated With Periodontal, Orthodontic, and Prosthodontic Therapy: A Case Report
No ratings yet
Generalized Periodontitis Treated With Periodontal, Orthodontic, and Prosthodontic Therapy: A Case Report
16 pages
Bankruptcy Term Paper
100% (1)
Bankruptcy Term Paper
7 pages
The Lost Wallet
No ratings yet
The Lost Wallet
3 pages
Food Chain Self-Evaluation Rubric - Final
No ratings yet
Food Chain Self-Evaluation Rubric - Final
1 page
Lee 2025 Ai Critical Thinking Survey
No ratings yet
Lee 2025 Ai Critical Thinking Survey
23 pages
Guroian Flannery OConnor
No ratings yet
Guroian Flannery OConnor
10 pages
Statement of Cash Flow (Indirect Method)
No ratings yet
Statement of Cash Flow (Indirect Method)
7 pages
Understanding Power and Influence
100% (1)
Understanding Power and Influence
47 pages