How to update a row in an excel table with Graph API

In a recent project I wanted to update a row in excel and I had the feeling that the standard Excel online connector was kind of slow.

That's why I tried to recreate this with the Graph API and it was more complex than expected.

Prerequisites:

• Your excel workbook needs to have the data stored in a table formatted way. Otherwise it will be difficult to loop through the data

• Use the HTTP preauthorized request to call the Graph API

  

Final HTTP Call

What you need to do in the end is a PATCH request with the following parameters:

Url:

https://graph.microsoft.com/v1.0/sites/{site ID}/drive/items/{item ID}/workbook/worksheets/{name of worksheet}/range(address='{range of row]')

Headers:

Content-type: application/json

Body:

{

"values":

[ [ {the values you want to patch per column separated by comma} ] ]

}

How to get the needed parameters

Body

Getting the parameters for the body is probably the easiest part. You just need to add the values you want to add to the table in the correct order of the columns.

Example:

Order number	Product name	Amount
A1	Apple	1

Let's say you want to add the order O2, with the product Oranges and the amount 2 to the table your body needed to look like this:

{

"values":

[ [ "O2","Oranges","2" ] ]

}

Site ID

To get the site ID you need to add _api/site/id to the end of your SharePoint site. This should look like this: https://companyname.sharepoint.com/sites/sitename/_api/site/id

As the outcome you will see some parameters. The last parameter is the GUID that you need.

Item ID

To geht the item ID you need to do another HTTP call. This time a GET request.

Url:

https://graph.microsoft.com/v1.0/sites/{site ID}/drive/root/children/{name and ending of your file}

Afterwards you need to parse the body of the outcome and look for the field id:

You will see a lot of "id" fields but the one directly in the body of the request is the correct one.

Name of worksheet and range of row

To get the range of the row, we first need to get all rows of the table. Afterwards we can loop through each row and patch the new information.

HTTP Call to get all the rows:

GET request with the following Url: https://graph.microsoft.com/v1.0/sites/{site ID}/drive/root/children/{name and ending of your file}/workbook/tables/{name of your table}/rows

Of course you need to parse the body of the response.

Now in the loop we can use another HTTP call to get the range of the row we want to look at:

GET request with the following Url: https://graph.microsoft.com/v1.0/sites/{site ID}/drive/root/children/{name and ending of your file}/workbook/tables/{name of your table}/rows/itemAt(index={index of the current iteration}/range

This will give you all the rest of the needed information.

The needed range will look something like this: "address": "Sheet1!A2:C2",

With the split function you can now retrieve name of the sheet (Sheet1) and the range of the current record in the table (A2:C2).

Summary

Now you saw how to replace the Update a row excel online action with different Graph API calls. I am not hundert percent sure if it is really faster than the normal call but it was interesting to figure out this approach.

I hope you learned something. I learned a lot!