atwork.blog

news and infos about microsoft, technology, cloud and more

How to access files stored in OneDrive or in a SharePoint site with Microsoft Graph and the Excel API

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.

image

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.

image

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:

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".

image

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

image

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

SNAGHTMLe3a17ab

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

SNAGHTMLe3dc216

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 

SNAGHTMLe445281

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

SNAGHTMLe482767

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

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"] ]

}

image

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

SNAGHTMLe4ff515

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".

image

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!

SNAGHTMLe5ff3b0

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

SNAGHTMLe6883c4

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

SNAGHTMLe6bb7c5

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

image

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

SNAGHTMLe800e22

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  

image

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

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')

{
    "values": [ ["3000.0"], ["580.0"] ],
    "valueTypes": [ ["Double"], ["Double"] ]
}

image

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

image

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.

Loading