[Account state issue] Fetch worksheets in a workbook - FileOpenUserUnauthorized - Could not obtain a WAC access token #3014

MetatronR · 2024-02-21T17:47:54Z

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 Excel section
Click on the worksheets in a workbook endpoint
Consent to the Files.ReadWrite permission
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 beta endpoint. -> 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

 {
   "@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"
   }
}

The text was updated successfully, but these errors were encountered:

MetatronR · 2024-02-21T22:50:13Z

Email address for reference: [email protected]

Please let me know if you need more info or access to this (demo) account.

Avisoul · 2024-02-22T06:30:56Z

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.

ElinorW · 2024-02-27T14:52:09Z

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.

vova-visme · 2024-03-04T12:04:57Z

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

ElinorW · 2024-03-05T12:34:06Z

Hi @vova-visme,
alright... Let me see what I can do

xxyziggy · 2024-03-05T15:33:28Z

I'm having the same issue with my account, please do investingte, we have hundreds of clients depending on this functionality

ilyamokka · 2024-04-15T09:18:24Z

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

ElinorW · 2024-05-16T12:03:45Z

hey @MetatronR,

Are you able to access the file on the web? without the use of Graph API's?

vova-visme · 2024-05-16T20:42:46Z

@ElinorW the files are accessible and editable using OneDrive interface if that what you mean.

MetatronR · 2024-05-17T10:09:40Z

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.

MetatronR · 2024-05-17T10:12:54Z

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.

musale · 2024-05-27T08:52:59Z

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

microsoft-github-policy-service bot added the ToTriage label Feb 21, 2024

ElinorW added the Needs: Author Feedback label Feb 27, 2024

ElinorW self-assigned this Feb 27, 2024

microsoft-github-policy-service bot added ToTriage and removed ToTriage labels Feb 27, 2024

ElinorW removed the ToTriage label Feb 27, 2024

microsoft-github-policy-service bot added the Status: No Recent Activity label Mar 2, 2024

ElinorW added Status: Needs Investigation and removed Status: No Recent Activity Needs: Author Feedback labels Mar 5, 2024

ElinorW added Needs: Author Feedback labels May 16, 2024

microsoft-github-policy-service bot added Needs: Attention 👋 Author has responded to the issue and removed Needs: Author Feedback labels May 17, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Account state issue] Fetch worksheets in a workbook - FileOpenUserUnauthorized - Could not obtain a WAC access token #3014

[Account state issue] Fetch worksheets in a workbook - FileOpenUserUnauthorized - Could not obtain a WAC access token #3014

MetatronR commented Feb 21, 2024 •

edited

MetatronR commented Feb 21, 2024

Avisoul commented Feb 22, 2024 •

edited

ElinorW commented Feb 27, 2024 •

edited

vova-visme commented Mar 4, 2024 •

edited

ElinorW commented Mar 5, 2024 •

edited

xxyziggy commented Mar 5, 2024 •

edited

ilyamokka commented Apr 15, 2024

ElinorW commented May 16, 2024

vova-visme commented May 16, 2024

MetatronR commented May 17, 2024

MetatronR commented May 17, 2024

musale commented May 27, 2024

[Account state issue] Fetch worksheets in a workbook - FileOpenUserUnauthorized - Could not obtain a WAC access token #3014

[Account state issue] Fetch worksheets in a workbook - FileOpenUserUnauthorized - Could not obtain a WAC access token #3014

Comments

MetatronR commented Feb 21, 2024 • edited

MetatronR commented Feb 21, 2024

Avisoul commented Feb 22, 2024 • edited

ElinorW commented Feb 27, 2024 • edited

vova-visme commented Mar 4, 2024 • edited

ElinorW commented Mar 5, 2024 • edited

xxyziggy commented Mar 5, 2024 • edited

ilyamokka commented Apr 15, 2024

ElinorW commented May 16, 2024

vova-visme commented May 16, 2024

MetatronR commented May 17, 2024

MetatronR commented May 17, 2024

musale commented May 27, 2024

MetatronR commented Feb 21, 2024 •

edited

Avisoul commented Feb 22, 2024 •

edited

ElinorW commented Feb 27, 2024 •

edited

vova-visme commented Mar 4, 2024 •

edited

ElinorW commented Mar 5, 2024 •

edited

xxyziggy commented Mar 5, 2024 •

edited