Office 365 Excel Online

-->

This article will introduce you to the technical aspects of Office Scripts. You'll learn how the Excel objects work together and how the Code Editor synchronizes with a workbook.

An Excel add-in interacts with objects in Excel by using the Office JavaScript API, which includes two JavaScript object models: Excel JavaScript API: Introduced with Office 2016, the Excel JavaScript API provides strongly-typed Excel objects that you can use to access worksheets, ranges, tables, charts, and more. Learn more about using Office for the web to work together in Office 365. Sheet protection. When you share an Excel file with other users, you can protect a worksheet to help prevent it from being changed, and you can choose the actions that you allow the users of your worksheet to perform. Find training courses for Excel. Any other feedback? The more you tell us, the more we can help. How can we improve? With Microsoft 365 for the web (formally Office 365) you can edit and share Word, Excel, PowerPoint, and OneNote files on your devices using a web browser.

Note

Office Scripts in Excel on the web is in public preview. The functionality described here is subject to change as the feature develops. You can submit feedback on Office Scripts through the Help > Feedback button in Excel. You can submit feedback about the documentation to the OfficeDev/office-scripts-docs GitHub repository.

`main` function

Each Office Script must contain a main function with the ExcelScript.Workbook type as its first parameter. When the function is executed, the Excel application invokes this main function by providing the workbook as its first parameter. Hence, it is important to not modify the basic signature of the main function once you have either recorded the script or created a new script from the code editor.

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.

Caution

If your main function looks like async function main(context: Excel.RequestContext), your script is using the older async API model. For more information (including how to convert your script to the current API model), refer to Support older Office Scripts that use the Async APIs.

Object model

To write a script, you need to understand how the Office Script 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.

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.

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. Read the Working with ranges section of Using built-in JavaScript objects in Office Scripts for more information on handling those arrays in the Office Scripts framework.

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.

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

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.

Creating a table

Create tables by using.

Adding 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.

Note

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.

Office 365 Excel Online Save As Csv

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.

Removing 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.

Microsoft Word Online Excel

Cross-platform support: Excel add-ins run in Office on the web, Windows, Mac, and iPad.
Centralized deployment: Admins can quickly and easily deploy Excel add-ins to users throughout an organization.
Use of standard web technology: Create your Excel add-in using familiar web technologies such as HTML, CSS, and JavaScript.
Distribution via AppSource: Share your Excel add-in with a broad audience by publishing it to AppSource.

Note

Office 365 Excel Online Login

Excel add-ins are different from COM and VSTO add-ins, which are earlier Office integration solutions that run only in Office on Windows. Unlike COM add-ins, Excel add-ins do not require you to install any code on a user's device, or within Excel.

Components of an Excel add-in

An Excel add-in includes two basic components: a web application and a configuration file, called a manifest file.

The web application uses the Office JavaScript API to interact with objects in Excel, and can also facilitate interaction with online resources. For example, an add-in can perform any of the following tasks:

Create, read, update, and delete data in the workbook (worksheets, ranges, tables, charts, named items, and more).
Perform user authorization with an online service by using the standard OAuth 2.0 flow.
Issue API requests to Microsoft Graph or any other API.

The web application can be hosted on any web server, and can be built using client-side frameworks (such as Angular, React, jQuery) or server-side technologies (such as ASP.NET, Node.js, PHP).

The manifest is an XML configuration file that defines how the add-in integrates with Office clients by specifying settings and capabilities such as:

The URL of the add-in's web application.
The add-in's display name, description, ID, version, and default locale.
How the add-in integrates with Excel, including any custom UI that the add-in creates (ribbon buttons, context menus, and so on).
Permissions that the add-in requires, such as reading and writing to the document.

To enable end users to install and use an Excel add-in, you must publish its manifest either to AppSource or to an add-ins catalog. For details about publishing to AppSource, see Make your solutions available in AppSource and within Office.

Capabilities of an Excel add-in

In addition to interacting with the content in the workbook, Excel add-ins can add custom ribbon buttons or menu commands, insert task panes, add custom functions, open dialog boxes, and even embed rich, web-based objects such as charts or interactive visualizations within a worksheet.

Add-in commands

Add-in commands are UI elements that extend the Excel UI and start actions in your add-in. You can use add-in commands to add a button on the ribbon or an item to a context menu in Excel. When users select an add-in command, they initiate actions such as running JavaScript code, or showing a page of the add-in in a task pane.

Add-in commands

For more information about command capabilities, supported platforms, and best practices for developing add-in commands, see Add-in commands for Excel, Word, and PowerPoint.

Task panes

Task panes are interface surfaces that typically appear on the right side of the window within Excel. Task panes give users access to interface controls that run code to modify the Excel document or display data from a data source.

Task pane

For more information about task panes, see Task panes in Office Add-ins. For a sample that implements a task pane in Excel, see Excel Add-in JS WoodGrove Expense Trends.

Custom functions

Custom functions enable developers to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Users within Excel can access custom functions just as they would any native function in Excel, such as SUM().

Custom function

For more information about custom functions, see Create custom functions in Excel.

Dialog boxes

Dialog boxes are surfaces that float above the active Excel application window. You can use dialog boxes for tasks such as displaying sign-in pages that can't be opened directly in a task pane, requesting that the user confirm an action, or hosting videos that might be too small if confined to a task pane. To open dialog boxes in your Excel add-in, use the Dialog API.

Dialog box

Office 365 Excel Online Save As Csv

For more information about dialog boxes and the Dialog API, see Dialog boxes in Office Add-ins and Use the Dialog API in your Office Add-ins.

Content add-ins

Content add-ins are surfaces that you can embed directly into Excel documents. You can use content add-ins to embed rich, web-based objects such as charts, data visualizations, or media into a worksheet or to give users access to interface controls that run code to modify the Excel document or display data from a data source. Use content add-ins when you want to embed functionality directly into the document.

Content add-in

For more information about content add-ins, see Content Office Add-ins. For a sample that implements a content add-in in Excel, see Excel Content Add-in Humongous Insurance in GitHub.

JavaScript APIs to interact with workbook content

An Excel add-in interacts with objects in Excel by using the Office JavaScript API, which includes two JavaScript object models:

Excel JavaScript API: Introduced with Office 2016, the Excel JavaScript API provides strongly-typed Excel objects that you can use to access worksheets, ranges, tables, charts, and more.
Common API: Introduced with Office 2013, the Common API enables you to access features such as UI, dialogs, and client settings that are common across multiple types of Office applications. Because the Common API does provide limited functionality for Excel interaction, you can use it if your add-in needs to run on Excel 2013.

Next steps

Get started by creating your first Excel add-in. Then, learn about the core concepts of building Excel add-ins.

Loaddual927

Office 365 Excel Online

`main` function