Alternatively, you can use a household budget template in Excel. This template for a budget indicates estimated versus actual costs for individual items and automatically calculates the difference. Event budgeting templates itemize expenses, including costs for the venue, refreshments, entertainment and more, so you know where each penny goes. Microsoft Excel is the industry leading spreadsheet software program, a powerful data visualization and analysis tool. Take your analytics to the next level with Excel.
-->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.
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.
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 Office Excel online, free
Further reading on the object model
The Office Scripts API reference documentation is a comprehensive listing of the objects used in Office Scripts. There, you can use the table of contents to navigate to any class you'd like to learn more about. The following are several commonly viewed pages.
Office Excel online, free
See also