blog.atwork.at

The Microsoft Graph API is the new unified endpoint for developers accessing data from an Office 365 tenant. Amongst many other features, you can work with OneDrive and SharePoint files with the Graph REST API. Additionally, you can directly work with Excel files through the Graph Excel API. Since all samples I found just use the personal OneDrive for Business (OFB) file storage, I wanted to document how to access files that are stored in a SharePoint Online site (SPO) as well. So, here we go!

The scenario

Microsoft Excel is very useful for data in tables, charts and extensive calculations, but can work as small and simple data source (for example in PowerApps) as well. So, in many use cases, having data stored in an Excel file often is a very pragmatic and a very quick way to access data easily. There are endless use cases and reasons to use data with Excel. In my scenario I wanted to write data from another cloud service through an Azure function into an existing Excel file stored in a SharePoint site. The Azure function and the code is not part of this story, this covers how to access the files stored in Office 365 with Microsoft Graph.

First, I had to solve, how to access data stored in a SPO site and how to use that file with the Excel REST API. The goal was, to add data programmatically from other data sources with the Graph Excel API. My demo Excel file looked as followed.

This file was stored to a SPO site "https://<tenant-name>.sharepoint.com/sites/apps" in a document library named "Documents" in a folder "Accounting". We come to that later.

My issue

I already used Excel-files stored in OneDrive for my demos - that works great (see the How-To below). Unfortunately, I did not find documented samples how to access Excel-files from a SPO site. So, I needed to find out how this works. Graph Explorer did a great job helping me figuring that out. This article is my resulting documentation.

Useful links

The official documentation really helped, so here's some sources for this topic:

• https://dev.office.com/blogs/power-your-apps-with-the-new-excel-rest-api
• https://dev.office.com/code-samples#?filters=excel,office%20365%20app
• https://docs.microsoft.com/en-us/onedrive/developer/rest-api/
• https://developer.microsoft.com/en-us/graph/docs/api-reference/v1.0/api/drive_get

Accessing files stored in OneDrive for Business

First, let's have a look at the well documented way with OFB: I uploaded the InvoiceList.xlsx file to my OFB storage in a folder named "Documents".

So, the virtual path is  my personal <OneDrive>/Documents/InvoiceList.xlsx.

Then I opened Graph Explorer (did I mention, that's the best tool ever?). The basis URL for accessing the unified endpoint is https://graph.microsoft.com/v1.0. To access the files, we can use the final version 1, it's all GA since a while.

I used the /me/drive/root/children method to see all items in the OFB root folder of the signed-in user and selected just the relevant data as the ID and the name:

/me/drive/root/children

https://graph.microsoft.com/v1.0/me/drive/root/children?$select=id,name

My target file is stored in the Documents folder with ID 01MU67ZIGB2TLWB4WJFZF23RXATCSKKBWN... to access all objects in a folder, we can use the drive/items method followed by the container ID. To see the content we add then children method:

/me/drive/items/<folder-id>/children

https://graph.microsoft.com/v1.0/me/drive/items/01MU67ZIGB2TLWB4WJFZF23RXATCSKKBWN/children?$select=id,name

If your desired document is stored in a deeper folder structure, repeat this step above by entering each folder with it's ID until you find the document.

So, we see there's one item in that folder. Now we get the Document ID of InvoicesList.xlsx which is 01MU67ZIF5XAO6DHM4HFDLYTMTRZJMBWQO.To see that specific file let's use the document ID as here:

/me/drive/items/<document-id>

https://graph.microsoft.com/v1.0/me/drive/items/01MU67ZIF5XAO6DHM4HFDLYTMTRZJMBWQO

So we can access that file and it's properties. If you already know the document ID, you can skip searching through the containers...

Accessing the Excel file content

Ok, now it's simple. We can use the Excel REST API to read the content. You can step through each method and see the result (f.e., the collections of worksheets), but here I directly access some cells of the first data sheet named "Invoices":

/me/drive/items/<document-id>/workbook/worksheets('<sheet-name')/range(address='<cell-range>')

https://graph.microsoft.com/v1.0/me/drive/items/01MU67ZIF5XAO6DHM4HFDLYTMTRZJMBWQO/workbook/worksheets('Invoices')/range(address='a2:d4')?$select=values 

We see the values of the cell range A2 to D4.

You find more info about the Microsoft Graph Excel REST API at

• Power your Apps with the new Excel REST API on the Microsoft Graph 
• New additions to the Excel REST APIs on the Microsoft Graph endpoint

Update the Excel file content

We can use a HTTP PATCH operation to modify the content. In this sample, I update two customer names in cell D2 and D3:

PATCH: /me/drive/items/<document-id>/workbook/worksheets('<sheet-name')/range(address='<cell-range>')

https://graph.microsoft.com/v1.0/me/drive/items/01MU67ZIF5XAO6DHM4HFDLYTMTRZJMBWQO/workbook/worksheets('Invoices')/range(address='d2:d3')

In the HTTP body we need to have the new values and types. This goes in JSON format as here:

{
     "values": [ ["Company ABC"], ["Company DEF"] ],
     "valueTypes": [ ["String"], ["String"] ]
}

For numbers, use the type ["Double"] etc. The operation delivers the result instantly as shown above. Also, we see the new values in Excel:

That's cool, or?

Now, let's see how to do the same with a file stored in a SharePoint Online document library.

Accessing files stored in a SharePoint Online site in a document library

To be honest: This was my personal pain point, since I did not find the correct syntax for a while. So, here's my solution. The same file was uploaded to an existing SPO site "https://<tenant-name>.sharepoint.com/sites/apps" in a document library named "Documents" in a folder "Accounting".

So, the virtual path is <SPO-Site>/Documents/Accounting/InvoiceList.xlsx.

In Graph Explorer, we can see the logical containers of a SPO site as follows:

/sites/<tenant-name>.sharepoint.com:/<sites/teams>/<sitename>:/drives

https://graph.microsoft.com/v1.0/sites/m365x145152.sharepoint.com:/sites/apps:/drives?$select=name,id

The SPO URL to the document library is https://m365x145152.sharepoint.com/sites/apps/Documents/Forms/AllItems.aspx - take care of the the exact syntax with the two : characters - this took a while to figure that out. This stackoverflow post with answers by Alx and Ravi helped - Thx!

So, now we have the document library ID of "Documents" which is b!VJHh7rQ7yUarSPyLLEFxV-QOLwKPY1xHt9zH_sGv7Ivfj2k4iLqEQqExki8MFHPC. Just for completeness, to access the document library properties, we can use:

/sites/<tenant-name>.sharepoint.com/drives/<document-library-id>

https://graph.microsoft.com/v1.0/sites/m365x145152.sharepoint.com/drives/b!VJHh7rQ7yUarSPyLLEFxV-QOLwKPY1xHt9zH_sGv7Ivfj2k4iLqEQqExki8MFHPC

Anyway, we want to get the folder and then the document. We can access the content of the document library as above:

/sites/<tenant-name>.sharepoint.com/drives/<document-library-id>/root/children

https://graph.microsoft.com/v1.0/sites/m365x145152.sharepoint.com/drives/b!VJHh7rQ7yUarSPyLLEFxV-QOLwKPY1xHt9zH_sGv7Ivfj2k4iLqEQqExki8MFHPC/root/children?$select=id,name

With the folder ID of "Accounting" 016ZKWMAKWINLPGMQ3ZBG3JDSUTGGNSJVR, we can access that content with the children method to get our file ID later:

/sites/<tenant-name>.sharepoint.com/drives/<document-library-id>/items/<folder-id>/children

https://graph.microsoft.com/v1.0/sites/m365x145152.sharepoint.com/drives/b!VJHh7rQ7yUarSPyLLEFxV-QOLwKPY1xHt9zH_sGv7Ivfj2k4iLqEQqExki8MFHPC/items/016ZKWMAKWINLPGMQ3ZBG3JDSUTGGNSJVR/children?$select=id,name

Now, we are in business. Finally, we have the document ID 016ZKWMALAS7H4MSB5QBEJD6DPCESWXHDO. We can access that file with the items method and the ID:

/sites/<tenant-name>.sharepoint.com/drives/<document-library-id>/items/<document-id>

https://graph.microsoft.com/v1.0/sites/m365x145152.sharepoint.com/drives/b!VJHh7rQ7yUarSPyLLEFxV-QOLwKPY1xHt9zH_sGv7Ivfj2k4iLqEQqExki8MFHPC/items/016ZKWMALAS7H4MSB5QBEJD6DPCESWXHDO

Cool, we get all the file properties. This way was exactly what I needed for my scenario.

Use Excel REST API against the file stored in SPO

Again, now it's easy. We can simply add the workbook & more methods:

/sites/<tenant-name>.sharepoint.com/drives/<document-library-id>/items/<document-id>/workbook/worksheets('<sheet-name')/range(address='<cell-range>')

https://graph.microsoft.com/v1.0/sites/m365x145152.sharepoint.com/drives/b!VJHh7rQ7yUarSPyLLEFxV-QOLwKPY1xHt9zH_sGv7Ivfj2k4iLqEQqExki8MFHPC/items/016ZKWMALAS7H4MSB5QBEJD6DPCESWXHDO/workbook/worksheets('Invoices')/range(address='a2:d4')?$select=values  

We now are traversing through the data with a long URI. Isn't that awesome?

Patch it again

Just to complete this story, I also update the file content with a HTTP PATCH operation to change the numbers in H2 and H3:

PATCH: /sites/<tenant-name>.sharepoint.com/drives/<document-library-id>/items/<document-id>/workbook/worksheets('<sheet-name')/range(address='<cell-range>')

https://graph.microsoft.com/v1.0/sites/m365x145152.sharepoint.com/drives/b!VJHh7rQ7yUarSPyLLEFxV-QOLwKPY1xHt9zH_sGv7Ivfj2k4iLqEQqExki8MFHPC/items/016ZKWMALAS7H4MSB5QBEJD6DPCESWXHDO/workbook/worksheets('Invoices')/range(address='h2:h3')

As you can see, the Tax and the Total column got recalculated automatically.

Summary

Working with Excel REST API in Microsoft Graph is really powerful and can be used in many scenarios. You can work with Excel files stored in OneDrive or in SharePoint Online as described above.

I hope this article clarifies how to access files with Microsoft Graph.