When is Microsoft Graph API finished calculating in Excel?

Question

I can see how to trigger the calculation of a spreadsheet using Microsoft Graph API here... https://learn.microsoft.com/en-us/graph/api/workbookapplication-calculate?view=graph-rest-1.0&tabs=http

But, when I pull the results from the calculations they don't seem to be updated. However, I pull a second or third time, it is usually updated.

I assume this means that calculation hasn't finished b/c of the size of the file or complexity of the calcs.

However, being asynchronous, I'm not finding any way to check to see when the calculations have finished.

Any idea how to do this?

UPDATE 1:

This is the code I'm (now) using to create the session (per @UJJAVAL123-MSFT)

var persistChanges = false;

var wrkbk = await graphClient.Me.Drive.Items[strItemId].Workbook
    .CreateSession(persistChanges)
    .Request()
    .PostAsync();

This will give me a value for 'id' like this...

cluster=US5&session=15.SN3PEPF000074ED1.A82.1.V24.7737Nwad4oPuVafaO%2fpAkiay14.5.en-US5.en-US26.10037ffe965d2cf2-Unlimited1.A1.N16.16.0.12904.3505114.5.en-US5.en-US1.V1.N0.1.A&usid=1b230e8f-3bd0-cdaa-2da6-47db74154075

I'm not exactly sure how/where to use this, or whether I use the entire thing (it looks like a query string) or if I'm supposed to parse and pull out one of the values...

cluster=US5
session=15.SN3PEPF000074ED1.A82.1.V24.xxxxxxxxxxxxxxxxx%2fpAkiay14.5.en-US5.en-US26.10037ffe965d2cf2-Unlimited1.A1.N16.16.0.12904.3505114.5.en-US5.en-US1.V1.N0.1.A
usid=1b230e8f-3bd0-cdaa-2da6-xxxxxxxxxxxx

and this is the code i'm using to trigger the calculation, but not sure how to connect the two...

var calculationType = "FullRebuild";

await graphClient.Me.Drive.Items[strItemId].Workbook.Application
    .Calculate(calculationType)
    .Request()
    .PostAsync();

Also, I'm seeing that it is possible to create, refresh and close a session, but not entirely sure how to check on a specific async process inside that session.

Here is the code I'm using to check a specific range for a value, not sure where we pass the session-id here either...

var result = await graphClient.Me.Drive.Items[strItemId].Workbook.Worksheets[strSheetName]
    .Range(strRangeName)
    .Request()
    .GetAsync();

UPDATE 2:

I can run an API call (presumably) in the same session successfully by passing the workbook-session-id (which is the ENTIRE string shown above) and I get the expected 204 No Content response. However, it is not clear from the c# Code Snippet in the Microsoft Graph Explorer how to pass the workbook-session-id in the request.

Here is the code it provides...

GraphServiceClient graphClient = new GraphServiceClient( authProvider );

await graphClient.Me.Drive.Items["{item-id}"].Workbook.Application
    .Calculate(null)
    .Request()
    .PostAsync();

So the question remains, how can I do a PostAsync or GetAsync and reference the workbook-session-id?

This code does NOT give me an error...

await graphClient.Me.Drive.Items[strItemId].Workbook.Application
    .Calculate(calculationType)
    .Request()
    .Header("workbook-session-id",wrkbk.Id)
    .PostAsync();

So now, the question is WHEN do I get the workbook-session-id? Do I get it when I initially open the workbook and then pass it to every call?

Did you managed to make it working? All of my requests are fine, Im using `persistChanges: true`, but when I download the file, nothing changes (cells are not evaluated) — Nickon, Jul 20 '22 at 20:50

score 1 · Answer 1 · answered May 06 '20 at 09:26

1

You should create a session and pass the session Id with each request. The presence of a session Id in the requests ensures that you are using the Excel API in the most efficient way possible.

Check here for API call to get a session

answered May 06 '20 at 09:26

ah, okay, starting to make sense. but how do i pass a session id with the calculate function (in c#)? seems like the session is at the `workbook` level and not seeing any documentation on how to use session id at the `workbook.application` level. – gotmike May 06 '20 at 11:57
@gotmike ,Appreciate your response. why don't you try this is Graph explorer once and pass session-id in Request header and check if its working. It could be an issue with api. And this Async shall work here. – May 06 '20 at 14:00
ok i tried in graph explorer and got a session id (updated above in question) but not sure how to use it in the step for triggering calculation. and also, then i'm not sure how to know when it's complete. – gotmike May 06 '20 at 14:16

score 0 · Accepted Answer · answered May 19 '20 at 12:52

So after a decent amount of testing, i figured it out.

The answer is that you use the CreateSession method (https://learn.microsoft.com/en-us/graph/api/workbook-createsession?view=graph-rest-1.0&tabs=http) to get the workbook info, and you set the persistChanges setting, then you get back info about the workbook session.

Like this...

using Microsoft.Graph;

// strItemId = the id from the microsoft graph api of the item
// strUserId = the id of the user from the microsoft graph api (note: must have permissions set correctly)
public static async Task<WorkbookSessionInfo> GetWorkbookSessionId(string strItemId, string strUserId)
{

    // true = you can see changes in the workbook
    // false = don't update the workbook, just do calculations
    var persistChanges = true;

    try
    {

        var wrkbk = await graphClient.Users[strUserId].Drive.Items[strItemId].Workbook
        .CreateSession(persistChanges)
        .Request()
        .PostAsync();

        var result = wrkbk;

        return result;
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error getting items: {ex.Message}");
        return null;
    }

}

And you are returned a WorkbookSessionInfo object, which includes the SessionId for use in subsequent calls. This way, it keeps all your calls in the same session!

https://learn.microsoft.com/en-us/graph/api/resources/workbooksessioninfo?view=graph-rest-1.0

When is Microsoft Graph API finished calculating in Excel?

2 Answers2

Linked