Microsoft Graph API - Access Excel content

Question

Hello everyone,

I am a beginner learning to use API, and I would like some help accessing the content of an Excel file stocked within a OneDrive.

I managed to access a specific cell's content using this query :
https://graph.microsoft.com/v1.0/me/drive/items/{item-id}/workbook/worksheets/{worksheet-id}/cell(row=3,column=2)

But I am struggling to find information about the following queries :

• obtaining the content of the cells of a specific column
• find a specific value within a column (let's say C1), identifying its row (for instance R12), and then getting the content of a cell from that row and another specific column (R12-C3 for instance)

I would be thankful if anyone could give me any pointers/tips or documentation on how to achieve that.

Thank you for your time.

Answer 1

Hi @SJ

Please follow the below steps-

Step1: Get the range of used values of your sheet using this https://graph.microsoft.com/v1.0/me/drives/{drive-id}/items/{item-id}/workbook/worksheets/{sheet-id}/usedRange Graph API reference. In response you will find "address": "Sheet1!A1:F14" property which indicates smallest range that encompasses any cells that have a value or formatting assigned to them. Use this to find last value of your column and put that in address in next step.

Step2: To get values from worksheet of particular column use
GET https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/{sheet-id}/range(address='A1:A14')?$select=values Graph API. Please refer to this documentation for more details.

If you want to get contents of column in excel for particular table you can use this GET https://graph.microsoft.com/v1.0/me/drives/{drive-id}/items/{item-id}/workbook/worksheets/{sheet-id}/tables/{table-id}/columns/{column-id} Graph API for more details please refer to this documentation.

In a response you will get all the content of column use that(or usedRange) to identify row number, after that you can use this
GET https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/{sheet-id}/range(address='A7:F7') Graph API to get the result.

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.