microsoftgraph
[Account state issue] Fetch worksheets in a workbook - FileOpenUserUnauthorized - Could not obtain a WAC access token
Describe the bug Unable to access the worksheets of a workbook freshly created in my own drive.
DISCLAIMER: Once the bug happens, that particular account can't be used with the Excel endpoints anymore...
To Reproduce Steps to reproduce the behavior:
- Go to https://developer.microsoft.com/en-us/graph/graph-explorer
- Connect account (Oauth2)
- Go to the
Excelsection - Click on the
worksheets in a workbookendpoint - Consent to the
Files.ReadWritepermission - Replace the
{drive-item-id}URL path placeholder with the ID of any newly created (or older) Excel file.
Expected behavior Should receive the list of worksheets in the workbook.
Actual behavior Receiving API error responses on bugged accounts (new accounts are not affected initially).
Device: any device - This is an internal API exception
Error response:
// Calling: GET https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}/workbook/worksheets
{
"error": {
"code": "FileOpenUserUnauthorized",
"message": "You do not have permissions to open this file in the browser.",
"innerError": {
"code": "unauthorizedUncategorized",
"message": "Required authentication information for the resource is either missing or invalid.",
"innerError": {
"code": "FileOpenUserUnauthorized",
"message": "You do not have permissions to open this file in the browser."
},
"date": "2024-02-21T17:41:21",
"request-id": "5ea61f3a-4fa5-4eee-a688-c59580e5f38d",
"client-request-id": "da72c07c-b20d-750e-b949-52e7f16ca7df"
}
}
}
Additional context This behavior is pretty old. These issues would suddenly render an account unusable. Once these issues happen, there is no way to use the same account with the workbook API endpoints.
Frequency: Had over 900 errors (on many accounts - Oauth2) in the last 12+ months. Over 200 exceptions in the last 14 days.
Previously the API response was:
// Calling: GET https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}/workbook/worksheets
error: {
code: AccessDenied,
innerError: {
client-request-id: 7211bb30-a46d-472a-aacb-0e57371bf94e,
date: 2024-02-21T16:18:02,
request-id: 7211bb30-a46d-472a-aacb-0e57371bf94e
},
message: Could not obtain a WAC access token.
}
This bug renders any integration with the Microsoft Excel impossible as there no way control or prevent the API errors received while fetching the worksheets of a workbook.
What was tried (in the Graph Explorer):
- Call
GET https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}/workbook/worksheets->error - Call
GET https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}/workbook->same error - Call
POST https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}/workbook/createSession->same error - Call
POST https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}/workbook/worksheets->same error - To use the
betaendpoint. ->no change - To delete all files ->
no change - To wait a few months... ->
no change - To give all Files related permissions ->
no change - To create/use new accounts ->
only a matter of time until they get bugged - To share that file (anyone with the link) ->
no change - To revoke the permission from https://microsoft.com/consent ->
no change - To get the details using the
https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}endpoint ->works- able to download the actual file ->
works
- able to download the actual file ->
{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('redacted%40outlook.com')/drive/items/$entity",
"@microsoft.graph.downloadUrl": "https://my.microsoftpersonalcontent.com/personal/redacted/_layouts/15/download.aspx?redacted&ApiVersion=2.0",
"@microsoft.graph.tips": "Use $select to choose only the properties your app needs, as this can lead to performance improvements. For example: GET me/drive/items('<key>')?$select=audio,bundle",
"createdDateTime": "2024-02-21T17:08:46Z",
"eTag": "\"{BFAB8571-F031-44A3-B24E-AB2225F3784E},4\"",
"id": "42C71CBB2C347AB2!sbfab8571f03144a3b24eab2225f3784e",
"lastModifiedDateTime": "2024-02-21T17:08:49Z",
"name": "Book1.xlsx",
"webUrl": "https://onedrive.live.com?cid=redacted",
"cTag": "\"c:{BFAB8571-F031-44A3-B24E-AB2225F3784E},3\"",
"size": 8169,
"createdBy": {
"application": {
"id": "00000000-0000-0000-0000-0000481710a4",
"displayName": "i:0i.t|ms.sp.ext|00000000-0000-0000-0000-0000481710a4@9188040d-6c67-4c5b-b112-36a304b66dad"
},
"user": {
"email": "[email protected]",
"id": "42c71cbb2c347ab2",
"displayName": "redacted"
}
},
"lastModifiedBy": {
"user": {
"email": [email protected]",
"id": "42c71cbb2c347ab2",
"displayName": "redacted"
}
},
"parentReference": {
"driveType": "personal",
"driveId": "redacted",
"id": "redacted",
"name": "Documents",
"path": "/drive/root:",
"siteId": "redacted"
},
"file": {
"mimeType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"hashes": {
"quickXorHash": "ELlta8FIrAaiuKfF4g6FIsgAq8k="
}
},
"fileSystemInfo": {
"createdDateTime": "2024-02-21T17:08:46Z",
"lastModifiedDateTime": "2024-02-21T17:08:49Z"
},
"shared": {
"scope": "users"
}
}
Email address for reference: [email protected]
Please let me know if you need more info or access to this (demo) account.
Once these issues happen, there is no way to use the same account with the workbook API endpoints
I suppose, it also would be nice to have the damaged accounts revived in the end.
Hi @MetatronR, This seems to be an issue with the Excel API's... could you please post this on the graph Q&A forum at https://aka.ms/askgraph where someone from the appropriate team will be able to help out.
This issue has been automatically marked as stale because it has been marked as requiring author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment.
![microsoft-github-policy-service[bot] avatar](https://avatars.githubusercontent.com/in/95686?v=4 "Published by a pub.dev verified publisher"){kind=small}
@ElinorW thank you for coming to this ticket. We were doing zoom with MS support team in India half year ago and it didn't bring any results, although we were promised that it will be investigated. With all respect, above mentioned forum looks like another place to bury the issue for several years or forever. The issue is reproducible in Graph Explorer as well, so maybe this repo is pretty relevant for investigating that, although it is not a source of the issue. If you have any ways for direct contact with api devs team in mind, feel free to share, I will appreciate that.
I'm having the same issue with my account, please do investingte, we have hundreds of clients depending on this functionality
I've encountered this issue with one of my accounts. It's a shame, I'd like to keep relying on Excel in my work
hey @MetatronR,
Are you able to access the file on the web? without the use of Graph API's?
@ElinorW the files are accessible and editable using OneDrive interface if that what you mean.
Hi, yes, the files are accessible both from OneDrive and from the API (list my items in drive and by ID).
Example (truncated) from search:
"createdDateTime": "2023-07-03T17:25:14Z",
"eTag": "\"{97267835-B5D7-4213-8F90-683AF56B12A7},8\"",
"id": "42C71CBB2C347AB2!s97267835b5d742138f90683af56b12a7",
"lastModifiedDateTime": "2023-07-03T17:27:42Z",
"name": "TestWAC1.xlsx",
"webUrl": "https://onedrive.live.com/personal/42c71cbb2c347ab2/_layouts/15/Doc.aspx?resid=42C71CBB2C347AB2!s97267835b5d742138f90683af56b12a7&cid=42c71cbb2c347ab2",
"cTag": "\"c:{97267835-B5D7-4213-8F90-683AF56B12A7},6\"",
"size": 9807,
"file": {
"mimeType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"hashes": {
"quickXorHash": "KmNoZn5d4fWeToIDr0TU1NmbO3A="
}
},
"fileSystemInfo": {
"createdDateTime": "2023-07-03T17:25:14Z",
"lastModifiedDateTime": "2023-07-03T17:27:42Z"
},
"shared": {
"scope": "users"
}
Also results got by calling https://graph.microsoft.com/v1.0/me/drive/items/42C71CBB2C347AB2!s97267835b5d742138f90683af56b12a7 :
"createdDateTime": "2023-07-03T17:25:14Z",
"eTag": "\"{97267835-B5D7-4213-8F90-683AF56B12A7},8\"",
"id": "42C71CBB2C347AB2!s97267835b5d742138f90683af56b12a7",
"lastModifiedDateTime": "2023-07-03T17:27:42Z",
"name": "TestWAC1.xlsx",
"webUrl": "https://onedrive.live.com/personal/42c71cbb2c347ab2/_layouts/15/Doc.aspx?resid=42C71CBB2C347AB2!s97267835b5d742138f90683af56b12a7&cid=42c71cbb2c347ab2",
"cTag": "\"c:{97267835-B5D7-4213-8F90-683AF56B12A7},6\"",
"size": 9807,
"file": {
"mimeType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"hashes": {
"quickXorHash": "KmNoZn5d4fWeToIDr0TU1NmbO3A="
}
},
"fileSystemInfo": {
"createdDateTime": "2023-07-03T17:25:14Z",
"lastModifiedDateTime": "2023-07-03T17:27:42Z"
},
"shared": {
"scope": "users"
}
I get by calling https://graph.microsoft.com/v1.0/me/drive/items/42C71CBB2C347AB2!s97267835b5d742138f90683af56b12a7/workbook/worksheets (fresh error IDs) :
{
"error": {
"code": "FileOpenUserUnauthorized",
"message": "You do not have permissions to open this file in the browser.",
"innerError": {
"code": "unauthorizedUncategorized",
"message": "Required authentication information for the resource is either missing or invalid.",
"innerError": {
"code": "FileOpenUserUnauthorized",
"message": "You do not have permissions to open this file in the browser."
},
"date": "2024-05-17T10:04:48",
"request-id": "c3ff4e0d-a1d7-4b2b-9043-ee0fe9b9bc12",
"client-request-id": "c30403e0-0ad5-2c22-05bc-9e2de1b26921"
}
}
}
All these calls have been made from the Graph Explorer using these scopes: Files.Read, Files.Read.All, Files.ReadWrite and Files.ReadWrite.All.
Observation: In the past (months) the error used as described in the initial message:
error: {
code: AccessDenied,
innerError: {
client-request-id: 7211bb30-a46d-472a-aacb-0e57371bf94e,
date: 2024-02-21T16:18:02,
request-id: 7211bb30-a46d-472a-aacb-0e57371bf94e
},
message: Could not obtain a WAC access token.
}
After some update it got replaced with:
{
"error": {
"code": "FileOpenUserUnauthorized",
"message": "You do not have permissions to open this file in the browser.",
"innerError": {
"code": "unauthorizedUncategorized",
"message": "Required authentication information for the resource is either missing or invalid.",
"innerError": {
"code": "FileOpenUserUnauthorized",
"message": "You do not have permissions to open this file in the browser."
},
"date": "2024-05-17T10:04:48",
"request-id": "c3ff4e0d-a1d7-4b2b-9043-ee0fe9b9bc12",
"client-request-id": "c30403e0-0ad5-2c22-05bc-9e2de1b26921"
}
}
}
Yet the API is still not functional. That issues does not happen automatically on all accounts. But once it happens once, that account will not be usable anymore with the Excel API.
@MetatronR where is your excel file stored? According to the excel API docs https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0, the API is only used for excel workbooks stored in OneDrive for Business, Sharepoint site or Group drive.
hi @xxyziggy, Sorry for the delay on this issue... I've been trying to get the team that handles this API to take a look at this, seems to be something with the OneDrive/SharePoint API... but I am not sure why they haven't followed up here. I'll keep nudging them
Hey @MetatronR and @xxyziggy Could either of you share a snippet of a recent call (shouldn't be older than 30 days) that shows the error message again, with the request ID and timestamp? I need a recent log to share with the team.
GET https://graph.microsoft.com/v1.0/me/drive/items/42C71CBB2C347AB2!s97267835b5d742138f90683af56b12a7/workbook/worksheets -> 401
Response body:
{
"error": {
"code": "FileOpenUserUnauthorized",
"message": "You do not have permissions to open this file in the browser.",
"innerError": {
"code": "unauthorizedUncategorized",
"message": "Required authentication information for the resource is either missing or invalid.",
"innerError": {
"code": "FileOpenUserUnauthorized",
"message": "You do not have permissions to open this file in the browser."
},
"date": "2024-07-11T19:14:47",
"request-id": "c142c67d-fd8a-47fd-a3ae-79518d3ce9f2",
"client-request-id": "0292446b-074e-5948-067c-c69b02f36e3c"
}
}
}
Response headers:
{
"cache-control": "no-cache",
"client-request-id": "0292446b-074e-5948-067c-c69b02f36e3c",
"content-type": "application/json",
"request-id": "c142c67d-fd8a-47fd-a3ae-79518d3ce9f2"
}
At the same time, GET https://graph.microsoft.com/v1.0/me/drive/items/42C71CBB2C347AB2!s97267835b5d742138f90683af56b12a7 works just fine.
A few more IDs just in case:
{
"cache-control": "no-cache",
"client-request-id": "82e264be-7c1a-9219-d6b5-62fd84711ff1",
"content-type": "application/json",
"request-id": "03aa5c3c-74f4-4ca9-9098-3e5fcc6a4ca1"
}
or
{
"cache-control": "no-cache",
"client-request-id": "241f883d-8116-7235-b4d6-f9856a6d666c",
"content-type": "application/json",
"request-id": "a76971f8-c669-45e5-9ea8-fb54831533a7"
}
Hey @MetatronR, Happy to share that the team responsible is in the process of rolling out the fixes for this issue. They shared that this should be resolved by the end of the month and the damaged accounts will be revived as well.
Hi @ElinorW Could you please share if there are any updates? You were mentioning the end of previous month as a time frame for a probable resolution. We will much appreciate any information you have as it is very anticipated topic by us and our clients.