Error Creating Session for Workbook : 503 CoauthSessionTerminated. The workbook needs to be updated.

Question

Using latest Node MS Graph Client

Our app uploads files to OneDrive and creates/updates Excel workbooks.
(app id : 4d42f407-c4ef-49bb-ac28-e14c32f843ff)

We create/update multiple workbooks under multiple tenants.

While testing Excel file update, we are intermittently running into extended (6+ hours) 503 Errors on SessionCreate operation only. There seems to be no documentation on this particular error.

All other operations like file upload, workbook create, sheet range insert/update work without issues.

We need sessions to ensure that our clients can view/edit excel files while they are being updated by our app, without causing "file locked for editing".

Request:

session_options = {"persistChanges": true};
wb_session = await client.api("/users/{ID}/drive/items/{EXCEL-FILE-ID}/workbook/createSession").post(session_options);

Response:

{
  statusCode: 503,
  code: 'CoauthSessionTerminated',
  requestId: 'd2d63efc-0e40-422c-b195-55ee9398d49a',
  date: 2021-03-19T23:41:54.000Z,
  body: '{"code":"CoauthSessionTerminated","message":"The workbook needs to be updated. Click OK to continue.","innerError":{"code":"transientFailure","message":"The request failed due to a transient error. Please try your request again.","innerError":{"code":"CoauthSessionTerminated","message":"The workbook needs to be updated. Click OK to continue."},"date":"2021-03-19T23:41:54","request-id":"d2d63efc-0e40-422c-b195-55ee9398d49a","client-request-id":"2b75dee0-f070-b591-724d-1ab50b6f9681"}}'
}

Answer 1

@Danstan - we manage thousands of workbooks and since sessions are per-workbook-ID we have to open as many as the number of unique workbooks we have to update. If you can trace requests by app ID - 4d42f407-c4ef-49bb-ac28-e14c32f843ff (or I can send you a log of about 500 most recent requests half of which fail with this error).

The error seems incredibly random and is still happening now.

Simplified flow (tenant authorizes offline access)
All requests are in try/catch

1. Get client access token from refresh token if needed
2. Get Drive info
3. Get or create folders (app stores data in specific folders)
4. Upload pdf/png/jpg file(s) (app always uploads several files) and get uploaded file IDs
5. Check if Excel file exists (tenants can have multiple files that need to be updated)
6. IF file exists -> Get {File ID} ELSE Upload a new Excel file -> get {File ID} in response
7. Create a workbook session for {File ID}
8. IF session created -> add {Session ID} to headers for subsequent requests ELSE proceed without Session ID
9. Update Excel file : Add a row to a sheet
10. IF session created -> close session {Session ID}

Note that this error does not prevent us from actually updating the workbook, UNLESS the workbook is opened in a browser by a user, in which case we will get a "workbook is locked" error in step #9 (and we then retry at a later time). This however, poses a significant problem since many of our clients want to see "real time" updates in their Excel spreadsheets, which is ONLY possible if the session is created properly.

Transient underlying/inner error would signify workload not being available, however it ONLY affects the sessions "endpoint" - file upload, folder create, excel update - all of those operations perform flawlessly. But in the course of a time period, we can have successful session create followed by session create errors completely randomly: e.g. we can have 20 successful sessions followed by 5 errors, or a sequence of alternating success-errors. It does not seem to be affected by number of requests: e.g. 5 requests per minute have pretty much the same % of failures as 5 requests per second. All fail with this specific error.

Another interesting point (apart from a weird message for REST) is that this error seems to happen randomly on existing and newly created files , if we had just uploaded a blank file (step #6) there could not be any other session on it except for the one we're trying to create, and we explicitly close open sessions. Most files are small (and new ones are "empty" - no rows). We tried "pausing" up to 5 seconds after uploading a new file before creating a session on it, but that made no difference - some would error, and some would succeed.

Thanks for looking into this.