Microsoft Graph API returning no tables for shared worksheet

Question

My goal is to read and write content on an Online Excel spreadsheet that was shared with me. The operation must be performed with Microsoft Graph API in a WPF C# application. The problem is I cannot access the individual rows, only the information up to the worksheet names.

I can get the correct worksheet with the following call:

GET https://graph.microsoft.com/v1.0/drives(DRIVE_ID)/items(ITEM_ID)/workbook/worksheets(WORKSHEET_ID)/

for which I get the following response:

 {
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#drives(DRIVE_ID)/items(ITEM_ID)/workbook/worksheets/$entity",
"@odata.id": "/drives(DRIVE_ID)/items(ITEM_ID)/workbook/worksheets(%27%7B00000000-0001-0000-0500-000000000000%7D%27)",
"id": "{00000000-0001-0000-0500-000000000000}",
"name": "Daniel",
"position": 5,
"visibility": "Visible"
}

which is correct. However, if I append a /tables, I get the following response

{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#drives(DRIVE_ID)/items(ITEM_ID)/workbook/worksheets('%7B00000000-0001-0000-0500-000000000000%7D')/tables",
"value": []
}

which is not correct, since the worksheet has content and, without a table reference, I cannot access the tables' rows with a /tables('0')/Rows query.

The user I am using has read/write access to the worksheet (I can change the worksheet using the browser). On the application, I request all security scopes, even though I've read that only "files.read" and "files.readwrite" are necessary. The token is correctly granted and I can read a lot of information from the files, drives and workbooks, except the tables and therefore the rows.

The URL that I can edit using the browser is based on SharePoint. https://COMPANY_NAME.sharepoint.com/...

Any advice?

Update 2017-10-08

So you can check if the request I am doing is correct, when requesting

GET https://graph.microsoft.com/v1.0/drives/DRIVE_ID/items/ITEM_ID/workbook/worksheets

I get

{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets",
"value": [{
    "@odata.id": "/drives('DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0000-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0000-000000000000}",
    "name": "N0",
    "position": 0,
    "visibility": "Hidden"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0100-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0100-000000000000}",
    "name": "N1",
    "position": 1,
    "visibility": "Hidden"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0200-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0200-000000000000}",
    "name": "N2",
    "position": 2,
    "visibility": "Visible"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0300-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0300-000000000000}",
    "name": "N3",
    "position": 3,
    "visibility": "Visible"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0400-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0400-000000000000}",
    "name": "N4",
    "position": 4,
    "visibility": "Visible"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0500-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0500-000000000000}",
    "name": "Daniel",
    "position": 5,
    "visibility": "Visible"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0600-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0600-000000000000}",
    "name": "N6",
    "position": 6,
    "visibility": "Visible"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0700-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0700-000000000000}",
    "name": "N7",
    "position": 7,
    "visibility": "Visible"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0800-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0800-000000000000}",
    "name": "N8",
    "position": 8,
    "visibility": "Visible"
},
{
    "@odata.id": "/drives('b%DRIVE_ID')/items('ITEM_ID')/workbook/worksheets(%27%7B00000000-0001-0000-0900-000000000000%7D%27)",
    "id": "{00000000-0001-0000-0900-000000000000}",
    "name": "N9",
    "position": 9,
    "visibility": "Visible"
}]
}

Also, when trying

GET https://graph.microsoft.com/v1.0/drives/DRIVE_ID/items/ITEM_ID/workbook/worksheets/00000000-0001-0000-0500-000000000000

then the response is:

{
  "error": {
    "code": "ItemNotFound",
    "message": "The requested resource doesn't exist.",
    "innerError": {
      "request-id": "GUID",
      "date": "2017-10-08T07:00:0"
    }
  }
}

Finally, I requesting the following scope when creating the authentication token: "user.read", "files.read.all", "files.read", "files.read.selected","files.readwrite","files.readwrite.all", "files.readwrite.selected", "sites.read.all", "sites.readwrite.all"

Answer 1

I found the same issue. The problem appeared to be caused by the fact that I created the tables in the desktop Excel app - not in the browser.

To fix it, I opened the desktop Excel app and removed all the tables. Then I returned to Excel online and created the tables. These are named Table1, Table2 and so on, and there is no way to rename them in Excel online - it's a known, documented, deficiency.

Graph Explorer was then able to find and list the tables.

For fun, I then reopened the worksheet in the desktop app, renamed Table1, Table2... to my liking, and Graph explorer could still find them with their new names.

Fixed.

Answer 2

In addition to dpim's response, I'd say first check if the file has any table at all. If there is no table involved, you can directly interact with the data using range object.

For range write operation, check range update API. This requires that you know the address you are working on.

For reading, you can use GET /workbook/worksheets/{id|name}/range(address='<address>') or if you know of name (named-item) of the range, you can use GET /workbook/names/{name}/range. If you don't know of the address or name, you can use used range to get all the cells that have data in it to begin.

Answer 3

Can you confirm that the worksheet has a table? A table is a specific element of the worksheet and is not synonymous with data ranges.

To get cell values for a worksheet, you can fetch the used range. This will give you a reference to the range containing any modified cells (cells with a format and/or value) on the sheet. You can then manipulate the range as needed.

To address your second issue, getting an ItemNotFound error when querying for a worksheet: I believe your query is not using the correct worksheet id. The ids are of the format {<guid>} and you are querying on /<guid>, not on /{<guid>}

Try: GET https://graph.microsoft.com/v1.0/drives/DRIVE_ID/items/ITEM_ID/workbook/worksheets/{00000000-0001-0000-0500-000000000000}

In place of: GET https://graph.microsoft.com/v1.0/drives/DRIVE_ID/items/ITEM_ID/workbook/worksheets/00000000-0001-0000-0500-000000000000