Get List of Rows from Excel Table using Graph API

Question

Hi @MichaelHan-MSFT ,

I'm trying to retrieve table data from a sheet in a .xlsx file in OneDrive and have a few questions:

1) There seem to be at least two packages officially maintained by Microsoft for acquiring an access token for Graph API access - @azure/msal-node (currently works for me), and @microsoft/microsoft-graph-client (not working for me). Which do you recommend for my current task? For the former, how do you recommend making requests to the Graph API after receiving the token? Just using an http client like Axios or something similar?

2) Second, how do I identify the id of the table? Based on the official docs, if I want to get the whole table as a data structure, it looks like the best way is to retrieve it as a list of rows, but it looks like I need the id of the table from which I want to get the rows, as seen in this example. The current approach that I had in mind was to maintain only one table in the worksheet, get the list of tables belonging to the worksheet as seen in this example, then use the id of the single table in the array that I get back, but then how do I get the id of the worksheet so that I can use it in this request? If it involves getting the workbook object first, then how do I get the id of the workbook? I've tried getting the id of the table via Graph Explorer with the GET query to endpoint "https://graph.microsoft.com/v1.0/me/drive/root/search(q='.xlsx')?select=name,id,webUrl" as suggested in this old post, but it returns 'Not Found - 404 - 189ms'.

3) Lastly, is it only possible to retrieve Excel table data from a .xlsx file in OneDrive? What if the file is in a Sharepoint Site?

Answer 1

Hi @Will Park

MSAL authentication is most commonly used because MSAL gives you many ways to get token, with a consistent API for a number of platforms.

To get the Tables you can follow below steps

Step1: Locate the File in your drive use this Graph API endpoint https://graph.microsoft.com/v1.0/me/drive/root/children

Step2: To get the worksheets id use this Graph API endpoint
https://graph.microsoft.com/v1.0/me/drive/items/{item-id}/workbook/worksheets
Put the item ID which captured from Step 1.

Step3: To get the Table id use this Graph API endpoint,
https://graph.microsoft.com/v1.0/me/drive/items/{items-id}/workbook/worksheets/{Sheet-id}/tables

Note: You can get multiple table here if they are present in your sheets you can locate them by their id’s.
Put the Sheets-ID which captured from step 2.

Step4: To List the rows of that table use this Graph API endpoint:
https://graph.microsoft.com/v1.0/me/drive/items/{items-id}/workbook/worksheets/{Sheets-id}/tables/{Table-id}/rows
Put the Table-ID which captured from step 3.

To get the Excel table data from Sharepoint use the below Graph Api endpoints in this sequence

https://graph.microsoft.com/v1.0/sites/root  
https://graph.microsoft.com/v1.0/sites/{sites-id}/drives  
https://graph.microsoft.com/v1.0/sites/{sites-id}/drives/{drives-id}/root/children  
https://graph.microsoft.com/v1.0/sites/{sites-id}/drives/{drives-id}/items/{items-id}/workbook/worksheets  
https://graph.microsoft.com/v1.0/sites/{sites-id}/drives/{drives-id}/items/{items-id}/workbook/worksheets/{Sheet-id}  
https://graph.microsoft.com/v1.0/sites/{sites-id}/drives/{drives-id}/items/{items-id}/workbook/worksheets/{Sheet-id}/tables  
https://graph.microsoft.com/v1.0/sites/{sites-id}/drives/{drives-id}/items/{items-id}/workbook/worksheets/{Sheet-id}/tables/{tables-id}  
https://graph.microsoft.com/v1.0/sites/{sites-id}/drives/{drives-id}/items/{items-id}/workbook/worksheets/{Sheet-id}/tables/{tables-id}/rows  

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

Answer 2

i have this problem but i have right app permission for user,site,files to read,write and execute.