Build on Excel using the Microsoft Graph API

Excel is probably Microsoft’s most popular developer tool. With a built-in functional programming language that now supports lambdas and variables, Excel has become a tool that people build businesses around, using it to manage and explore large amounts of data. With spreadsheet-based prediction models running in major banks, it’s not a stretch to say that large chunks of the global economy depend on Excel.

But there’s one big problem with Excel applications: They’re hard to use and certainly hard to build into a modern application. That complex simulation you spent months building might be ideal in a line-of-business application, but it’s a stand-alone application, not designed for use as a service that can be consumed by other code. Instead of automating predictions or analysis, your spreadsheet is still part of a manual workflow.

Extending Excel with the Microsoft Graph

Microsoft has shifted to a web-based approach to working with its Office applications. This has opened up new opportunities for working with Excel, either using its JavaScript APIs to connect custom Excel add-ins to your line-of-business systems or using its Microsoft Graph endpoints to call Excel functionality from approved applications. The last option is the most interesting, as it gives you the necessary tools to open Excel spreadsheets that are stored in OneDrive for Business as headless applications.

Using the Graph API to work with Excel is relatively easy; it’s a set of REST APIs with a common structure for all calls. This lets you quickly build URLs that access OneDrive locations, which are all you need to work with a spreadsheet or workbook. It’s important to remember that you can’t use a personal OneDrive account. You need a SharePoint-based business account, which is controlled by Azure Active Directory.

If you want to get started quickly, you can take advantage of a free 90-day (renewable as many times as you need) developer tenant. This gives you all the tools you need to start building Microsoft Graph applications.

Putting Excel to work through REST

First, give your application the appropriate permissions, using the Azure Active Directory APIs. These can generate the correct access token that needs to be passed as part of any REST call to the Microsoft Graph. Your application will need either read-only or read and write access, depending on how you’re intending to use Excel in it. Read-only access is best for applications that are extracting data from workbooks that are updated by task workers, while read and write access works well for workbooks that apply functions to incoming data and work with external data sources.

Another question when working with the Excel APIs is how you’re working with the workbook you’re accessing. Is it a persistent data store, much like a database, where any writes are accessible from another session, or is it non-persistent, where any changes are lost when your session ends? Both approaches have their value; the non-persistent option is ideal for working with analytical applications, functions, or Excel’s charting tools. There is a sessionless option too, where your code needs to wait for the workbook to be loaded every time you need it. This can be slow and inefficient, but it does mean that calls are completely independent of each other, and there is no chance of data leaking from call to call.

Tools such as Microsoft’s Graph Explorer or Postman are an important part of working with these APIs. You can use them to build and test REST calls, examining the headers and bodies associated with API URLs. It’s worth first using them to explore your OneDrive to be sure you can access your Excel spreadsheets and that you have the correct URL and body structure for use in your code via your choice of REST APIs.

Getting started with Excel Graph queries

Accessing a spreadsheet starts with its filename, the name of the workbook you want to access, and then the worksheet name. Like all Microsoft Graph APIs, you need to construct the full URL for your worksheet before you add the action you want to take along with its associate parameters.

For example, the following REST URL opens a spreadsheet in a spreadsheet folder in OneDrive and then gets the values stored in a set of rows in a table in a specific worksheet.

https://graph.microsoft.com/v1.0/me/drive/root/sheetfolder/sheetname.xlsx/workbook/worksheets/tablename/Range(address=’Sheet1!A1:D24’)

You’d use a call like this to get data from an Excel application. Other calls let you update tables and run your worksheet using a cloud-hosted Excel calculation engine. A typical application flow would then create an Excel session, update the source data in a table, recalculate your sheet, and then read the results from the table. It’s much the way you’d work with Excel as a stand-alone application, though as you’re working with a file in cloud storage, you won’t see results without explicitly calling a calculation operation. Finally, once you have the data you need, you can close the session.

It’s best to treat your Excel calls as asynchronous functions, much like working with any serverless application. Your code will need to allow for the time to spin up the calculation engine if you’re making calculations, as well as any latency between OneDrive and your location. JavaScript calls can work with promises, while C# can use async/await with the Microsoft Graph SDK.

Generating charts from Excel

One useful feature of the Excel APIs is the ability to use Excel charts from any application. A single collection holds all the Chart objects available in a workbook. You can then use the information returned to get the resulting chart image for a specific chart name using the name as an identifier, with the chart height, width, and fitting as parameters in the JSON body of your request. The response to the request holds a base-64 encoded image, ready for display in your application. That way you don’t have to use additional charting components in your application—all you need is an Excel spreadsheet in OneDrive somewhere.

Using Excel this way may seem a little like cheating, but we’ve got decades of work in our spreadsheets, and a lot of deep corporate knowledge is stored in those functions and calculations. Why not treat them as a service and use them in our modern applications? It could save days or even months of development time. For a handful of REST calls, that’s quite a win.