Excel is probably Microsoft’s most popular developer tool. With a built-in functional programming language that supports lambdas and variables, Excel has become a tool for people to build their businesses, using it to manage and explore large amounts of data. It is no exaggeration to say that most of the world economy depends on Excel for the spreadsheet-based predictive models run by major banks.
However, there is one major problem with Excel applications. It’s hard to use and certainly hard to incorporate into modern applications. Complex simulations built over months may be ideal for line-of-business applications, but they are standalone applications and are not designed for use as a service that can be used in other code. Instead of automating forecasting and analysis, spreadsheets continue to be part of the manual workflow.
Excel extension with Microsoft Graph
Working with Excel using the Graph API is relatively easy. This is a set of REST APIs that have a structure common to all calls. This allows you to quickly create URLs to access all the OneDrive locations you need to work with spreadsheets and books. It’s important to remember that your personal OneDrive account isn’t available. You need a SharePoint-based business account. This is controlled by Azure Active Directory.
If you want to get started right away, you can get 90 days free (renewable as many times as you need). Developer tenant.. This will give you all the tools you need to get started building your Microsoft Graph application.
Make Excel work with REST
First, use the Azure Active Directory API to grant the appropriate permissions to your application.These can Generate the correct access token This should be passed as part of a REST call to Microsoft Graph. Depending on how your application uses Excel, your application may need read-only or read-and-write access. Read-only access is ideal for applications that extract data from workbooks updated by task workers. Read and write access, on the other hand, is suitable for workbooks that apply functions to incoming data and work with external data sources.
Another question when using the Excel API is how to use the workbook you are accessing. Is it a persistent data store that can access writes from another session, such as a database, or is it a non-persistent data store that loses changes at the end of the session? Both approaches are worth it. Non-persistent options are ideal for working with analytical applications, functions, or Excel graphing tools. There is also a sessionless option. This option requires the code to wait for the workbook to load each time it is needed. This is slow and inefficient, but it means that the calls are completely independent of each other and there is no possibility of data leakage from call to call.
Tools such as Microsoft’s Graph Explorer and Postman are important parts of working with these APIs. You can use them to inspect the header and body associated with the API URL to create and test REST calls. We recommend that you first use them to explore OneDrive and make sure that you can access your Excel spreadsheet and that you have the correct URL and body structure for use in your code via the REST API of your choice.
Get started with Excel chart queries
Accessing a spreadsheet begins with the file name, the name of the workbook you are accessing, and the name of the worksheet. As with all Microsoft Graph APIs, you need to create the complete URL of the worksheet before adding the action to perform and its associated parameters.
For example, the following REST URL opens a spreadsheet in the OneDrive spreadsheet folder and Then get the values stored in a set of rows in a table on a particular worksheet..
Use such a call to get data from an Excel application. Other calls can use the cloud-hosted Excel calculation engine to update tables and run worksheets. In a normal application flow, you create an Excel session, update the source data in the table, recalculate the sheet, and then read the results from the table. This is similar to working with Excel as a stand-alone application, but because you’re working with files in cloud storage, you have to explicitly call the calculation operation to see the results. Finally, once you have the data you need, you can close the session.
Generate chart from Excel
One of the useful features of the Excel API is: Use Excel charts from any application.. One collection holds all the Chart objects available in the workbook. You can then use the returned information to get a graph image of the result for a particular graph name. Use the name as an identifier and the height, width, and fitting of the chart as parameters in the JSON body of the request. The response to the request retains the base-64 encoded image for display by the application. That way, you don’t have to use additional charting components in your application. All you need is an Excel spreadsheet somewhere in OneDrive.
Using Excel in this way may seem like a deception, but spreadsheets have decades of work and deep knowledge of the enterprise is stored in these functions and calculations. Would you like to treat them as a service and use them in your latest applications? Save days or months of development time. For a few REST calls, that’s a pretty win.
Copyright © 2021 IDG Communications, Inc.
https://www.infoworld.com/article/3643701/working-with-the-excel-microsoft-graph-apis.html#tk.rss_all Build in Excel using Microsoft Graph API