Excel, the beloved productivity tool, now has a powerful REST API as part of the Microsoft Graph that enables developers to build rich and deep integrations with Excel workbooks in Office 365.
Do you need to calculate, analyze, automate, report on or manage data in your app? Well, look no further at building these capabilities yourself – using the new Excel REST API, you have the power and simplicity of Excel at your fingertips (or a few simple HTTP calls) for all of your app’s calculation needs.
This addition to the Microsoft Graph is another step in our journey to enable modern programmability scenarios built on the Office platform. Last year, we announced a powerful Excel JavaScript API that enables developers to build interactive Office Add-ins. Today, we’re announcing the general availability of the Excel REST API which provides those same capabilities, and opens up a new world for Excel integrations – you can access, aggregate, analyze, model and generate data in Excel workbooks on Office 365 using simple HTTP calls.
Let’s take a look at the API – using the Microsoft Graph, you can address Excel workbooks sitting in OneDrive for Business or in any Office 365 group, for example:
https://graph.microsoft.com/v1.0/me/drive/root:/book.xlsx:/
From there you can access the workbook, simply by adding the /workbook segment:
https://graph.microsoft.com/beta/me/drive/root:/book.xlsx:/workbook
As simple as that, you can then interact with the worksheets, ranges, tables, and charts in that workbook, and do filtering, sorting, call functions, apply formatting and much more. For example, getting your ‘forecast’ chart is as simple as calling:
GET .../workbook/worksheets('Sheet1')/charts('forecast')/image()
To help spark ideas for how you can use the Excel API in your own applications, here are some of the powerful scenarios possible today:
Use Excel as a calculation service
Users have long loved the ease of creating complex calculations within Excel. With Excel REST API, it’s no longer necessary to recode your Excel formulas in custom app code. With more than 300 Excel worksheet functions available, you have full access to the powerful Excel calculation engine. For example, apps calculating monthly payment could simply take advantage of the PMT function from Excel. With a simple API call including principle, rate and number of payments, Excel will do all the heavy lifting for you and return the monthly payment instantly.
POST .../workbook/functions/pmt { "rate": 0.035, "nper": 20, "pv": -2000 }
This makes it extremely easy to leverage the hundreds of Excel functions in your app!
Excel as a reporting service
Creating professional looking reports and dashboards from complex data sets in Excel is a common task. With the Excel REST API, you can use Excel’s charting capabilities and extract the resulting tables/charts to present as part of your own application’s experience.
For example, you can change formatting, create charts and get the chart back as an image in your app – changing the background fill color of range A1:A6 is as simple as:
PATCH .../workbook/worksheets('Sheet1')/ range(address='A1:A6')/format/fill
{ color: "#FF0000" }
Create a column clustered chart with data from A1:C6:
POST .../workbook/worksheets('Sheet1')/charts/add
{ "type": "ColumnClustered", "sourcedata": "A1:C6", "seriesby": "Auto" }
Get the chart created as an image:
GET .../workbook/worksheets('Sheet1')/charts('Chart1')/image(width=0,height=0,fittingMode='fit')
Excel as a data service
Users across all type of industries and business use Excel to store critical business data. For example, a sales tracking app enables sales professionals to easily save and access all of their sales data. Using the Excel REST API, you can sort and filter this data just like those user would via Excel’s UI, which means that all of the data in the Excel spreadsheets can then become a native part of your application. Here are a couple of samples for sorting and filtering data in a range or a table:
Sort data in range A1:A6:
POST .../workbook/worksheets('Sheet1')/range(address='A1:A6')/sort/apply
{ fields: [{ key: 0, ascending: true }] }
Filter the Table1 to get the top 3 items based on the 1st column:
POST .../workbook/worksheets('Sheet1')/tables('Table1')/columns/itemAt(index=0)/filter/apply
{ criteria: { filterOn: "topPercent", criterion1: "3" } }
Partners
We always look forward to working with developers and partners to increase the number of places where Excel can continue to enable people to be more productive across all types of scenarios. Just recently we worked with two great partners who are now leveraging the Excel REST API in their applications:
Zapier lets users build easy automations to tedious tasks. Zapier recently announced a new Excel integration powered by Excel REST API, with many cool use cases like greatly simplifying a data collection process, users can now build zaps where data is automatically added into Excel from other services like emails, surveys or any other of their connected services.
Sage is working on integrating Sage 50 accounting software with Office 365, leveraging Excel via the new Excel REST API to access and combine business data in Sage 50 with the productivity benefits of Office. With powerful interactive Microsoft Excel reporting and business performance dashboards, Sage has simplified how to interact with the data and enabled small and medium businesses to make sense of data quickly and make faster, better decisions.
What to learn more?
Excellent! Visit https://dev.office.com/excel/rest, where you’ll find documentation and code samples to help you get started. It only takes a few lines of code to set up a basic integration using our to-do list sample.
Once you jump in, tell us what you think. Let us know your feedback on the API and documentation through GitHub and Stack Overflow, and make new feature suggestions on UserVoice.
Note: Any request that modifies the workbook should be performed in a persisted session. Find more details on how to create a persisted session in our documentation.
create a persisted session
POST .../workbook/CreateSession
content-type: Application/Json
authorization: Bearer {access-token}
{ "persistChanges": true }
Response
HTTP code: 201, Created
content-type: application/json;odata.metadata
{ "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#microsoft.graph.sessionInfo", "id": "{session-id}", "persistChanges": true}Usage
The session ID returned from the CreateSession call is then passed as a header on subsequent API requests using the workbook-session-id HTTP header.
GET .../workbook/Worksheets
authorization: Bearer {access-token}
workbook-session-id: {session-id}
