Excel API Essential Guide

What type of API does Excel provide?

Excel does not have a typical web API like REST, GraphQL, or SOAP. Instead, Excel provides different types of APIs for programmatic interaction:

Excel Object Model

The primary API for Excel is its Object Model, which allows developers to interact with Excel programmatically. This is not a web API, but rather a set of objects, properties, and methods that can be accessed through various programming languages and environments. Key points include:

• It allows direct manipulation of Excel workbooks, worksheets, cells, charts, etc.
• Can be used with VBA (Visual Basic for Applications) within Excel itself
• Can be accessed from external applications using COM (Component Object Model) on Windows

Office JavaScript API

For web and mobile applications, Microsoft provides the Office JavaScript API:

• Allows interaction with Excel workbooks in Office Online or desktop Excel
• Used for building Office Add-ins
• Based on JavaScript and works in web browsers

Microsoft Graph API

For cloud-based access to Excel files stored in OneDrive or SharePoint, Microsoft offers the Microsoft Graph API:

• RESTful API for accessing various Microsoft 365 services, including Excel files
• Allows reading and writing Excel workbook data stored in the cloud
• Not specific to Excel, but provides Excel-related functionality as part of a broader API

Open XML SDK

For working with Excel file formats directly:

• Allows creation and manipulation of Excel files (.xlsx) without requiring Excel to be installed
• Used for server-side processing of Excel files

In summary, Excel does not have a single, unified web API like REST or GraphQL. Instead, it offers different APIs and SDKs depending on the specific use case and development environment. The choice of API depends on factors like whether you're working with desktop Excel, web-based Excel, or Excel files in the cloud.

Does the Excel API have webhooks?

The official Excel API does not currently have webhooks. However, Excel does support events through its JavaScript API for Office Add-ins. The Excel JavaScript API supports various events, including onActivated, onAdded, onCalculated, onChanged, onSelectionChanged, and onDataChanged, among others. You can register event handlers for these events using the Excel JavaScript API. The events system in Excel also supports coauthoring scenarios, where multiple users can work on the same workbook simultaneously. It's worth noting that Office Scripts (which use TypeScript/ExcelScript) for Excel on the web do not currently support event listeners.

Rate Limits and other limitations

Here are the key points about the API Rate Limits for the Excel API:

General Excel API Limits

• The Excel service has a general limit of 5000 requests per 10 seconds per app for all tenants.
• There is also a limit of 1500 requests per 10 seconds per app per tenant.

Specific Limits for Excel Resources

• These limits apply to many Excel-related resources like workbook, workbookApplication, workbookChart, workbookRange, etc.

Considerations

• Despite the documented limits, some users have reported getting throttled (429 errors) at much lower rates, like 10 requests per second. This suggests the actual limits may be lower in practice.

• The limits are applied independently for different report APIs. For example, requests to Teams reports and Outlook reports count separately against their respective limits.

Best Practices

• Implement retry logic with exponential backoff to handle 429 errors.
• Spread out requests over time rather than sending in bursts.
• Monitor the x-ms-throttle-limit-percentage header to proactively adjust request rates.

Other Relevant Limits

• There is a global limit of 130,000 requests per 10 seconds per app across all tenants for any request type.

• Microsoft 365 reports have lower limits:

  • 14 requests per 10 minutes for CSV requests
  • 100 requests per 10 minutes for JSON beta requests

In summary, while the documented limits are fairly high, actual throttling may occur at lower rates. It's important to implement proper error handling and adjust request rates based on observed behavior.

Latest API Version

The most recent version of the Excel API is ExcelApi 1.17. Here are the key points about the Excel API versions:

Latest Version

• ExcelApi 1.17 is the latest stable version of the Excel API.

Key Points

• ExcelApi 1.17 is supported in:

  • Office on the web
  • Office on Windows (Microsoft 365 subscription) version 2302 (Build 16130.20332) and later
  • Office on Mac version 16.70 and later
  • Office on iPad version 16.70 and later

• There is also an ExcelApiOnline version that is supported only in Office on the web.

• Microsoft releases new API versions periodically to add new features and capabilities.

• Older API versions are still supported for backwards compatibility.

Best Practices

• When developing Excel add-ins, it's recommended to:

  1. Target the minimum API version required for your functionality.
  2. Use runtime checks (like isSetSupported) to enable additional features on newer hosts that support higher API versions.
  3. Keep up to date with the latest API versions to leverage new features.

• When submitting add-ins to the Office Store, reference the latest API version your add-in is compatible with, while ensuring it still works on the minimum supported version.

Additional Information

• Microsoft no longer releases discrete versions of Excel. Instead, features are introduced automatically over time using Windows Update.

• The current stable version number for Excel remains at 16.0 for both Windows and Mac.

How to get a Excel developer account and API Keys?

Here's how you can get a developer account for Excel to create an API integration:

Key Steps

1. Sign up for a Microsoft 365 developer account:

   • Go to the Microsoft 365 Developer Program website
   • Create a free developer account, which gives you access to Microsoft 365 apps and services

2. Register your application in Microsoft Entra ID:

   • Sign in to the Azure portal using your Microsoft 365 developer account
   • Navigate to "Microsoft Entra ID" > "App registrations" > "New registration"
   • Provide a name for your app and configure other settings as needed

3. Configure API permissions:

   • In your app registration, go to "API permissions"
   • Add permissions for the Excel API (part of Microsoft Graph)
   • Select the specific permissions your app needs (e.g., Files.Read, Files.ReadWrite)

4. Obtain authentication credentials:

   • In your app registration, get the client ID and tenant ID
   • Create a client secret or certificate for authentication

5. Implement authentication in your app:

   • Use the Microsoft Authentication Library (MSAL) to handle token acquisition
   • Use the obtained tokens to make API calls to Excel

Key Points to Consider

• You'll need to decide whether your app will use delegated permissions (acting on behalf of a user) or application permissions (acting as itself)
• Familiarize yourself with the Microsoft Graph API documentation for Excel-related endpoints
• Consider using the Microsoft Graph SDKs for easier integration in your preferred programming language

Best Practices

• Securely store your application's credentials (client secret or certificate)
• Implement proper error handling and token refresh mechanisms
• Use incremental authorization to request only the permissions your app needs
• Test your integration thoroughly in a non-production environment before deploying to production

By following these steps, you'll be able to set up a developer account and create an API integration with Excel using Microsoft Graph. Remember to review the documentation and sample code provided by Microsoft for detailed implementation guidance.

What can you do with the Excel API?

Here's the markdown text with the trailing list of URLs and citation references removed, and any URLs inside the content formatted correctly for the markdown file format:

Workbook

• Access and modify workbook properties
• Create, open, and save workbooks
• Manage workbook-level settings and options

Worksheet

• Create, delete, and rename worksheets
• Access and modify worksheet properties
• Hide or unhide worksheets
• Manage worksheet-level settings and options

Range

• Read and write cell values
• Format cells (font, color, borders, etc.)
• Apply formulas to cells
• Merge and unmerge cells
• Insert and delete rows and columns
• Copy and paste cell contents
• Find and replace cell values

Table

• Create and delete tables
• Add and remove rows and columns
• Apply table styles
• Sort and filter table data
• Access table properties and metadata

Chart

• Create and delete charts
• Modify chart types and styles
• Update chart data sources
• Customize chart elements (title, legend, axes, etc.)
• Export charts as images

PivotTable

• Create and delete PivotTables
• Add and remove fields
• Apply PivotTable styles
• Refresh PivotTable data
• Modify PivotTable layout and options

Shape

• Add and remove shapes
• Modify shape properties (size, position, color, etc.)
• Group and ungroup shapes
• Apply text to shapes

Comment

• Add and delete comments
• Read and modify comment content
• Manage comment replies
• Resolve and reopen comments

Conditional Formatting

• Apply and remove conditional formatting rules
• Modify existing conditional formatting
• Access conditional formatting properties

Data Validation

• Add and remove data validation rules
• Modify existing data validation
• Access data validation properties

Custom Functions

• Create custom functions for use in Excel formulas
• Define function parameters and return values
• Implement asynchronous functions

Events

• Subscribe to and handle various Excel events
• Respond to user actions and data changes

Application-level APIs

• Access and modify Excel application settings
• Manage Excel add-ins
• Control calculation modes and options

This list covers the main data models and capabilities of the Excel API. Each of these areas offers extensive functionality for interacting with and manipulating Excel workbooks programmatically.