Office 365 Excel Graph API — Use the power of excel engine in your web applications
Introduction
There are several use cases where there is a need for complex real-time calculations in applications. Traditional approach has been to write complex code logic on the server side using node JS, ASP.net web API or any other server-side web services framework and the API call is made from the client UI to get the results. This approach requires cost and effort to develop, test and maintain the code logic.
Microsoft Excel has been a popular standalone application among business users to perform complex calculations . What if a web application can use Excel’s engine to perform complex computations? This could eliminate the many hours of development effort and allow the business analyst or the product owner to implement the logic in excel spreadsheets.
Office 365 Graph API
Office 365 has risen with popularity with more than $150 million users and growing as we speak. Office 365 provides graph APIs which is a unified programming models to access Office 365 applications and features.
Excel graph API within Office 365 allows third party applications to access excel workbook and charts functionality using rest APIs. In this case, excel books needs to be stored in SharePoint document libraries or one drive. Third party application outside of Office 365 with appropriate privileges can make use of Excel APIs to read, write and compute formula’s. Excel API’s provide wide range of operations that can be performed at the level of workbook, sheets, range, table and even charts.
Architecture
This section illustrates how various types applications can interact with excel graph API. Also, details like security and privileges, configuration, etc will be discussed in detail. The diagram below describes overall architecture for any application using excel graph API.
1. Excel repository
Excel sheet needs to be stored in some repository. Office 365(O365) provides couple of options. 1. SharePoint online/O365 groups document library 2. User’s one drive location. Depending on the needs, one of these repositories may be chosen.
In some cases, the business requirements may mandate having separate excel file for each item or a record of the business entity. In such cases, excel template can be maintained in SharePoint document library and instance of the excel workbook can be copied from the template. While designing this, it is important to decide on an appropriate naming convention and directory structure.
2. Data Input
Excel workbook needs to have input data for the calculations to run. There are multiple ways to push the data. As part of Excel Graph API, there are various APIs available to write date. For instance, data can be written to a specific sheet, range etc. Using these API’s features, third party application can push data to the excel sheet.
Alternatively, data can be pumped into the excel workbook asynchronously using SSIS packages, Azure Data factory, Microsoft Flow, etc. depending on the business requirement. For manageability, it is recommended to maintain input data in a separate sheet of the excel workbook.
3. Formula Calculations
When interacting with excel workbook programmatically, one may assume that formula calculation may happen automatically. That may not the case. Third party application may have to specifically call the API to refresh the workbook.
It is also recommended to maintain the same session if a sequence of API calls is made one after the other. For example, in the same session, excel workbook can be created, input data can be pushed, and formulas can be refreshed at the end of the session.
4. Reading Output
Excel graph API provides various options to read data from excel sheet. Data can be read at various levels — cell, range, table, worksheets etc.
The results can be shown in the third-party application in the real-time when the API call is returned or the data can be stored or cached in the application database for future use.
5. Charts and Dashboards
Excel can be used to create professional looking reports and dashboards using the existing data. These charts and dashboards can be extracted from the excel sheet using graph APIs and displayed in a third-party application’s user interface.
6. Security and Privileges
To use the Excel graph API, Azure AD registration with appropriate privileges needs to be created before a third-party application can call the API. There are two ways to register the app.
1. Register to get access without a user: In this case, tenant administrator would have to give consent before using the app registration ID.
2. Register to get access on behalf of a user: In this case, third-party application will access the excel document on behalf of the logged in user. This would require the logged in user to allow the third-party application to use logged-in user credentials to access the application.
After the Azure AD registration is complete, third-party apps can make the API call using the client ID from the app registration and generated tokens.
7. Configuration
This process can be enhanced one step further if the third-party applications use multiple variations of the excel worksheets for various use cases. In this case, for each use case, third-party application can maintain configurations like location of the excel sheet, input data range, output data range, charts configuration, etc. A new variation of excel within the application can be introduced by adding a new configuration without writing much code.
Use Case at a US Federal Agency
In this section, we will look at how power of excel is used by a mission critical grants management system at a federal agency to perform calculations. This approach has been used at Heath resources and services administration (HRSA) which is a United States federal agency. HRSA gives out about $10 billion in grants every year which results in quality and affordable health care for tens of millions of people.
This excel approach helps in computing grant money allocation for each grant applicant for each program based on total appropriation money and other criteria. This excel spreadsheet contains complex logic which would be time consuming to implement using custom code. The data is pumped to excel sheet from its mission critical grants management system and pulls the computed results from the excel sheet.
Points to note
- Azure AD registration step with appropriate permissions are required to call the Graph API.
- Real-time API calls are not recommended for high volume scenarios with heavy loads.
- Office 365 throttles API requests when there is an overwhelming number of requests. It is recommended to reduce the number of operations per request or avoid immediate retries.
- If your organization does not have Office 365, this can be implemented using third party excel libraries like Aspose cells, etc.
