New additions to the Excel REST APIs on the Microsoft Graph endpoint

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.

For 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}/refresh
• refreshAll: 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.