Get List of Rows from Excel Table using Graph API

Will Park 1 Reputation point
2021-09-29T23:40:27.463+00:00

Hi @Michael ,

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?

Microsoft Graph SDK
Microsoft Graph SDK
A Microsoft software developer kit designed to simplify building high-quality, efficient, and resilient applications that access Microsoft Graph.
733 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Shivam Dhiman-MSFT 4,406 Reputation points Microsoft Employee
    2022-03-10T18:28:47.197+00:00

    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
    181970-getworkbook1.png

    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.
    182004-worksheetsid1.png

    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

    181940-tableid1.png

    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.
    182021-listrows1.png

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

    No comments