Last August, we announced the availability of the Excel REST API on Microsoft Graph. We continue to explore opportunities for new scenarios and functionality that allows for deeper and richer integration with Excel workbooks. We are happy to announce new additions to the Excel REST APIs. In this blog post, we’ll introduce the highlights of the new APIs. As always, we are eager to hear your feedback. Try them out and let us know what you think!
The new APIs are available via the Microsoft Graph v1.0 endpoint. You can access the new APIs for workbooks located in OneDrive for Business or in any Office 365 group.
https://graph.microsoft.com/v1.0/me/drive/root:/book.xlsx:/workbookFor an overview of the Excel REST APIs, see Working with Excel in Microsoft Graph.
What’s new
Pivot Table resource and refresh action
The pivot table collection is now available on the worksheet resource. To begin with, this will enable developers to know about the pivot tables are available on a given workbook. This will also enable applications to refresh an individual pivot table or all pivot tables associated with a worksheet from the source data. Applications can take advantage of this feature by refreshing the pivot table remotely based on timing or other business criteria.
- Get pivotTable: Read properties of pivotTable object. Example,
GET /worksheets/{id or name}/pivotTables/{id} refresh: Refreshes a given pivotTable. Example,GET /worksheets/{id or name}/pivotTables/{id}/refreshrefreshAll: Refresh all tables within a given worksheet. Note that this action is available only on the pivot table collection. Example,GET /worksheets/{id or name}/pivotTables/refreshAll
In the future, we’ll look to add more pivot table functionality using the same resources.
Visible rows on a filtered range
We’ve added a new resource called visibleView that represents visible rows of a given range. When a filter is applied on a range, it is often useful to know what values are visible (or selected from filter criteria). Previously, this required going through the underlying range to determine whether a row is visible or not. Such a procedure is cumbersome when the range is fairly large.
With the new feature being added, applications can now apply a filter on a large table based on desired criteria and get easy access to filtered data. This feature will reduce the complexity involved in determining visible rows and take advantage of Excel’s filtering capability.
In order to get the visible view object, get the object on the underlying range, as shown.
GET /{range-object}/visibleView
The visible view resource comes with useful properties that represent the range such as cell addresses, column and row count, index, formulas, number format, values/text, and value types.
If the visible range happens to be large, getting the entire resource might not be performant. In such a case, iterating over the rows provides a better user experience. The rows relationship on visibleView allows just that – iterating over the visible range rows.
Access the rows collection using the following code.
GET /{range-object}/visibleView/rows
New range manipulation functions
The following new range functions have been added. These functions reduce the complexity involved in determining the target range that the applications wishes to operate on.
columnsAfter, columnsBefore, rowsAbove, rowsBelow: Gets a certain number of rows or columns based on relative position of a given range.resizedRange: Gets a range object similar to the current range object, but with its bottom-right corner expanded or contracted by some number of rows and columns.
Access these new range APIs by using the syntax:
GET /{range-object}/{function-name}
For example: GET /workbook/worksheets/sheet1/usedRange/columnsAfter(count=2)
New table resource properties
Gain insights into the structure of the Excel table by using the following new properties: highlightFirstColumn, highlightLastColumn, showBandedColumns, showBandedRows, showFilterButton
Want 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.
After you jump in, tell us what you think. Post questions or feedback about the APIs on StackOverflow, make suggestions for the docs on GitHub, or suggest new features on UserVoice.
