DOCSEARCHbyAlgolia
Submit Docs

Office Scripts Documentation

repository·main·Indexed 42 days ago

https://github.com/officedev/office-scripts-docs

Tutorials and how-to guides for automating tasks in Excel and other Office applications using Office Scripts. Includes best practices for error handling, working with two-dimensional arrays, and configuring asynchronous external API calls using fetch.

Tokens
94.6K
Snippets
174
Records
261
Agent score
53%

What's inside Office Scripts

  1. Understand the Office Scripts coding environment

    main

    Office Scripts are written in TypeScript or JavaScript and use the Office Scripts JavaScript APIs to interact with Excel workbooks. The code editor is based on Visual Studio Code, providing a familiar environment for developers.

    Key characteristics:

    • Language: While the action recorder and documentation use TypeScript, any valid JavaScript will work because TypeScript is a superset of JavaScript.
    • Self-contained: The editor manages installation, compilation, and execution automatically.
    • API Scope: Scripts use a specialized version of the Office JavaScript APIs. Do not assume code written for Office Add-ins is directly portable to Office Scripts.

  2. Explore Office Scripts samples and scenarios

    main

    Office Scripts provides a variety of pre-built solutions to automate daily tasks. Samples are categorized by complexity and use case:

    • Basics: Fundamental examples focused on specific Excel features like Range objects, charts, conditional formatting, data validation, dates, images, row/column visibility, and tables.
    • Quick scenarios: Focused scripts that solve specific problems, such as combining tables, creating a table of contents, managing calculation modes, or using fetch for external data.
    • Cross-application scenarios: Advanced workflows that integrate Office Scripts with Power Automate to connect Excel with other applications (e.g., converting CSVs to Excel, emailing chart images, or running scripts on all files in a OneDrive folder).
    • Real-world scenarios: End-to-end solutions for complex tasks like analyzing web downloads, fetching NOAA water-level data, or managing interview scheduling in Teams.
    • Community contributions: Fun and creative scripts contributed by the community, such as the 'Game of Life' or seasonal animations.

    Note: Before running samples, ensure your Microsoft 365 subscription and account meet the Platform support requirements.

  4. Compare feature coverage and execution models

    main

    Feature Coverage

    • VBA: Offers more complete coverage of Excel features, particularly those specific to the desktop client.
    • Office Scripts: Covers nearly all scenarios for Excel on the web. Support for new web features is added to both the Action Recorder and JavaScript APIs as they debut.

    Execution and Events

    • VBA: Can utilize Excel-level events (e.g., worksheet or object events).
    • Office Scripts: Do not support Excel-level events. Scripts must be triggered manually by a user or via a Power Automate flow.

  5. Create stacked line charts

    main

    Stacked line charts allow you to show cumulative totals. Use the following ExcelScript.ChartType options:

    • ExcelScript.ChartType.lineStacked: Standard stacked line chart.
    • ExcelScript.ChartType.lineMarkersStacked: Stacked line chart with markers.
    • ExcelScript.ChartType.lineStacked100: 100% stacked line chart (shows percentage distribution).
    • ExcelScript.ChartType.lineMarkersStacked100: 100% stacked line chart with markers.

    You can also position the legend using chart.getLegend().setPosition() with ExcelScript.ChartLegendPosition values.

  6. Compare Office Scripts and VBA macros

    main

    When deciding between Office Scripts and VBA, consider the target environment and use case.

    Office Scripts are designed for secure, cross-platform, cloud-based solutions. They run on a universal JavaScript runtime, providing consistent behavior across different machines. They are ideal for web-based workflows and automation via Power Automate.

    VBA macros are designed for desktop-centric solutions. They can interact with desktop technologies like COM and OLE but lack a convenient way to make web calls. They are best suited for complex desktop-only automation that requires deep integration with the local operating system.

  7. Avoid unsupported union types involving ExcelScript types

    main

    Because Office Scripts converts synchronous code to asynchronous code at runtime, you cannot use union types that combine an ExcelScript type with a custom user-defined interface. Doing so causes the internal Promise to be returned to the script in a way the compiler cannot handle, resulting in unexpected behavior (like receiving {} instead of the actual object).

    Unsupported pattern:

    interface MyTable {
  getName(): string
}

// This union is not supported
const tableOrMyTable: ExcelScript.Table | MyTable = selectedSheet.getTables()[0];
    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
}

  8. When to use a `try...catch` statement

    main

    Use try...catch to detect API failures (such as exceeding data limits) and allow the script to continue running instead of crashing with a runtime error.

    Common patterns include:

    1. Custom Error Messaging: Catching an error to provide a more helpful message to the user.
    2. Fallback Behavior: Catching an error to attempt an alternative logic (e.g., breaking a large update into smaller batches).
    WARNING

    Using try...catch inside or around a loop can significantly decrease script performance.

    // Pattern 1: Custom error messaging
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; 
}

// Pattern 2: Fallback behavior
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);
}

  10. Understand limitations of external calls via Power Automate

    main

    External calls made through the Power Automate Excel Online connector will fail by design to uphold existing data loss prevention (DLP) policies.

    Because scripts run through Power Automate execute outside of your organization's firewalls, administrators have two primary ways to control security:

    1. Disable the Excel Online connector in Power Automate.
    2. Turn off Office Scripts for Excel via the Office Scripts administrator controls.

  11. Understand security differences between Office Scripts and VBA

    main

    The security models for Office Scripts and VBA differ significantly in scope and granularity:

    • Scope of Access: VBA macros have the same security clearance as Excel, giving them full access to the user's desktop. Office Scripts are sandboxed; they only have access to the workbook and cannot access the machine hosting it.
    • Authentication: Office Scripts cannot access JavaScript authentication tokens. They cannot use the signed-in user's tokens or sign in to external services to make calls on behalf of the user.
    • Administrative Control:
      • VBA: Admins can allow all, allow none, or allow only signed certificates. This lacks granularity for isolating specific users.
      • Office Scripts: Admins can enable/disable scripts for an entire tenant or specific groups of users. Admins also control script sharing permissions and usage within Power Automate.

  12. Choose between Power Query and Office Scripts

    main

    Both Power Query and Office Scripts automate data cleaning and transformation in Excel, but they serve different primary purposes:

    • Use Power Query when your primary goal is retrieving, transforming, and combining data from large, external data sources. It is a no-code solution designed for high-volume data retrieval without the transfer limits found in Power Automate or Excel on the web.
    • Use Office Scripts when you need Excel-centric automation, programmatic control, or integration with Power Automate. It is ideal for tasks involving formatting, creating visualizations (charts, PivotTables, shapes), and handling smaller or ad-hoc data sources (like REST APIs via fetch).