Implementing Server-Side Excel Automation with Microsoft Graph

This article is a reprint of a post originally published on Hatena Blog on September 30, 2019.

Introduction

Excel is a tool that many of you use frequently, and server-side automation for Excel has been achieved through various methods over the years. Although it previously relied on unsupported methods or third-party products, it is now possible to create Excel files on the server side using Microsoft Graph.

The requirements can be summarized as follows:

• Place an Excel template in a desired location on a SharePoint site
• Copy the Excel template
• Write values into the copied file
• Download the updated file (or save it to Azure Blob Storage, etc.)

Execution Steps

Let's take a look at the implementation methods by examining the APIs.

Place an Excel template in a desired location on a SharePoint site

Since this task does not need to be automated, please perform it manually. If you wish to automate template updates, file uploading is available.

https://docs.microsoft.com/en-us/graph/api/driveitem-put-content?WT.mc_id=M365-MVP-5002941

https://docs.microsoft.com/en-us/graph/api/driveitem-createuploadsession?WT.mc_id=M365-MVP-5002941

Copy the Excel template

Use the copy method to copy files. Since you can specify the driveId, copying between SharePoint sites or from SharePoint to OneDrive for Business is also possible.

https://docs.microsoft.com/en-us/graph/api/driveitem-copy?WT.mc_id=M365-MVP-5002941

Write values into the copied file

To write values into cells, send a PATCH request to a range. In addition to setting values, you can also set formulas and formatting. Values are specified as a 2D array.

https://docs.microsoft.com/en-us/graph/api/range-update?WT.mc_id=M365-MVP-5002941

Also, when writing a large amount of data, using a session is recommended for better performance.

https://docs.microsoft.com/en-us/graph/api/workbook-createsession?WT.mc_id=M365-MVP-5002941

Download the updated file

You can download the created file using the content property. Sending a GET request will redirect you to a temporary URL from which the file can be downloaded.

https://docs.microsoft.com/en-us/graph/api/driveitem-get-content?WT.mc_id=M365-MVP-5002941

Conclusion

In this article, I introduced file manipulation and Excel operations. There are many variations possible, such as using the manager API to automatically set a supervisor's name in an application form, or emailing the URL of the created file instead of downloading it. I encourage you to leverage Microsoft Graph and enjoy the freedom of programming.