MS Graph WorkbookApplication/Calculate API call permissions work unexpectedly.

Question

MS Graph WorkbookApplication/Calculate API call permissions work unexpectedly.

Zachary Faltersack 11

Hello,

I'm interested in using the API call described here: https://learn.microsoft.com/en-us/graph/api/workbookapplication-calculate

My requirements are that I use Application permissions so I was disheartened to see that it quite clearly indicates you must use the Files.ReadWrite delegated permission and that no application permissions are supported. Being the optimist I am, I tried it anyways and was surprised to see that it worked.

I used the Application Sites.Selected permission on a Sharepoint drive, which mimics the production scenario I'm interested in building. Obviously I was delighted it worked but I'm afraid of relying on it in my product given the documentation specs.

Is it a bug that this works?
Is this an undocumented but supported scenario?

I know the Sites.Selected permission is relatively new and could see this being a bug that once resolved removes this "feature" from working. If that's the case I don't want to rely on it in my product.

Any insight here would be appreciated.

Thanks!

CarlZhao-MSFT 45,996 Reputation points

2021-09-22T07:28:23.93+00:00

I'm not sure, I need to check your token, can you use https://jwt.ms/ to parse your token and provide a screenshot?
Zachary Faltersack 11 Reputation points

2021-09-22T15:52:15.627+00:00
Sure thing. I'm also using the GraphServiceClient and during the process of gathering that jwt for you I also noticed that the URL the client is constructing doesn't exactly match my expectations. I'm including that outgoing URL as I see it based on the GraphServiceClient, as well as the code and the decoded jwt.

URL: https://graph.microsoft.com:443/v1.0/drives/<omitted drive id>/items/<omitted item id>/workbook/application/microsoft.graph.calculate

Sample Code:
private static async Task CalculateProofOfConcept(IConfidentialClientApplication app, string sitePath, string itemId)
{
var authProvider = new ClientCredentialProvider(app);
var graphServiceClient = new GraphServiceClient(authProvider);

var site = await graphServiceClient .Sites .Root .SiteWithPath(sitePath) .Request() .GetAsync().ConfigureAwait(false); var drive = await graphServiceClient.Sites[site.Id] .Drive .Request() .GetAsync() .ConfigureAwait(false); await graphServiceClient.Drives[drive.Id] .Items[itemId] .Workbook .Application .Calculate("Full") .Request() .PostAsync() .ConfigureAwait(false); }

Decoded JWT:
Zachary Faltersack 11 Reputation points

2021-09-22T16:09:13.14+00:00

I also just realized it may be relevant that the client app I'm using is currently only scoped to my tenant but in the final version it would be a multitenant app doing this work in Sharepoint drives within my customers' tenants.

1 answer

Your answer

CarlZhao-MSFT 45,996 Reputation points

2021-09-22T07:28:23.93+00:00

I'm not sure, I need to check your token, can you use https://jwt.ms/ to parse your token and provide a screenshot?
Zachary Faltersack 11 Reputation points

2021-09-22T15:52:15.627+00:00

Sure thing. I'm also using the GraphServiceClient and during the process of gathering that jwt for you I also noticed that the URL the client is constructing doesn't exactly match my expectations. I'm including that outgoing URL as I see it based on the GraphServiceClient, as well as the code and the decoded jwt.

URL: https://graph.microsoft.com:443/v1.0/drives/<omitted drive id>/items/<omitted item id>/workbook/application/microsoft.graph.calculate

Sample Code:
private static async Task CalculateProofOfConcept(IConfidentialClientApplication app, string sitePath, string itemId)
{
var authProvider = new ClientCredentialProvider(app);
var graphServiceClient = new GraphServiceClient(authProvider);

var site = await graphServiceClient .Sites .Root .SiteWithPath(sitePath) .Request() .GetAsync().ConfigureAwait(false); var drive = await graphServiceClient.Sites[site.Id] .Drive .Request() .GetAsync() .ConfigureAwait(false); await graphServiceClient.Drives[drive.Id] .Items[itemId] .Workbook .Application .Calculate("Full") .Request() .PostAsync() .ConfigureAwait(false); }

Decoded JWT:
Zachary Faltersack 11 Reputation points

2021-09-22T16:09:13.14+00:00

I also just realized it may be relevant that the client app I'm using is currently only scoped to my tenant but in the final version it would be a multitenant app doing this work in Sharepoint drives within my customers' tenants.

Answer 1

CarlZhao-MSFT 45,996

So, I think your doubts have been resolved, because you are not calling this URL endpoint https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/application/calculate. According to my test, this endpoint does only support Files.ReadWrite delegated permissions.

And based on our experience, the /me endpoint will not support application permissions at all. So if you want to call this endpoint, you can modify the code:

GraphServiceClient graphClient = new GraphServiceClient( authProvider );  
  
var calculationType = "calculationType-value";  
  
await graphClient.Me.Drive.Items["{driveItem-id}"].Workbook.Application  
	.Calculate(calculationType)  
	.Request()  
	.PostAsync();

If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Zachary Faltersack 11 Reputation points

2021-09-23T15:05:33.157+00:00

Frankly I'm even more confused at this point because I can't find any documentation at all for the endpoint I'm hitting, as if it isn't supposed to exist. I knew that I was using a mildly different path because I was obviously going to a SharePoint drive as opposed to a user's personal drive, but since the only docs around for 'calculate' were that one endpoint I just assumed (shame on me) that that was an inconsistency in the docs (not an indication that I was using an altogether different API).

So my main question still remains: can I trust that this endpoint will continue to exist, function, and where can I find good documentation for it?
CarlZhao-MSFT 45,996 Reputation points

2021-09-24T09:12:09.957+00:00

I noticed that you call three api endpoints in your code. What is the reason?
Zachary Faltersack 11 Reputation points

2021-09-24T15:42:00.453+00:00

I just provided a complete example of how I got the IDs I used.

In practice I will first get the name of a SharePoint site so I need to the ID for its drive and then store those for reuse later when making calls to operate on DriveItems in that drive.

The reason I did it this way for my proof-of-concept was because it allowed me to build a call that most approximates the one that is documented (eg, calculating a workbook from a drive).
CarlZhao-MSFT 45,996 Reputation points

2021-09-27T10:08:48.073+00:00

I cannot reproduce your problem locally. I tried your endpoint, but I kept getting a 403 error. And I can't find this endpoint in the official doc. So, can you use the token to call this endpoint in postman? https://graph.microsoft.com/v1.0/drives/<omitted drive id>/items/<omitted item id>/workbook/application/calculate. . Then you can take a screenshot to tell me the result.
Zachary Faltersack 11 Reputation points

2021-09-27T17:05:51.923+00:00

Here you go (I use a different application than Postman, but same basic concept). I tried this with both /calculate and /microsoft.graph.calculate and got equivalent results:

Here are the headers in the response /calculate:

Here are the headers in the response /microsoft.graph.calculate:

Additionally, I've executed these requests successfully both with the Sites.Selected permission (after having granted access for the site to the service principal) as well as the Sites.FullControll.All application permissions.
CarlZhao-MSFT 45,996 Reputation points

2021-09-28T09:59:55.413+00:00

This is indeed an unknown problem, I will check it.
Zachary Faltersack 11 Reputation points

2021-10-01T20:33:51.987+00:00

FWIW my preference would be the docs are updated to say this is allowed :)
Zachary Faltersack 11 Reputation points

2021-12-02T20:50:49.777+00:00

Hello, it's been a couple of months and I'm wondering if there's any sort of update on this issue. Thanks!
Tommy He 25 Reputation points

2025-01-08T22:18:57.38+00:00

edited, see below
Tommy He 25 Reputation points

2025-01-08T22:41:13.23+00:00
We have the same use case in mind (recalculating a workbook in Full using Application permissions). The endpoint also worked for us (though with code 204 similar to Zachary's screenshots rather than the documented code 200); however, there was delay between when we received a response in the API and when it actually seemed to persist for the next GET request on the same file (a few secs).

It would be great if there are answers for:

Is there a way to "sync" the persisted file with the edits or only have the edits to the file return once the changes have fully persisted?

Is this supposed to be supported or could it be terminated at any point? FWIW this is an important use case for us and would definitely prefer it to be officially in the docs : )

Share via

MS Graph WorkbookApplication/Calculate API call permissions work unexpectedly.

1 answer

Your answer