How to update a row in an excel table with Graph API
- Matthias Schmitz
- Apr 22
- 2 min read
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!
Comments