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.

Copyright © 2021 IDG Communications, Inc.

Source link

Leave a Comment

Your email address will not be published. Required fields are marked *