CODEWITHSUNDEEP
c#excelmicrosoft-graph-api
Ravi Anand
Ravi Anand

Reputation: 5534

how to Add a formula in excel cell using Microsoft graph API?

I am trying to add a content to a cell in Excel using Microsoft Graph's Excel API. I am able to add content using PATCH method but when I try to add a formula, it does not behave like a formula. If I pass something like 'formulas': '=sum(2+2)', it does not behave as it should.

Result i am getting is in snip: enter image description here

Is this doable?

here is my code:

//Set up workbook and worksheet endpoints
var workbookEndpoint = "https://graph.microsoft.com/v1.0/me/drive/items/" +
    fileId + "/workbook";

var worksheetsEndpoint = workbookEndpoint + "/worksheets";

var patchMethod = new HttpMethod("PATCH");

var summaryTableRowJson = "{" +
    "'formulas': '=sum(2+2)'" +
    "}";

var colNamePatchBody = new StringContent(summaryTableRowJson);
colNamePatchBody.Headers.Clear();
colNamePatchBody.Headers.Add("Content-Type", "application/json");

var colNameRequestMessage = new HttpRequestMessage(patchMethod, worksheetsEndpoint +
    "('" + worksheetName + "')/range(address='Sheet1!B2')")
{
    Content = colNamePatchBody
};

var colNameResponseMessage = await client.SendAsync(colNameRequestMessage);

Upvotes: 1

Views: 904

Answers (1)

Marc LaFleur
Marc LaFleur

Reputation: 33124

You need to pass this in via the formulas rather than the values property:

{
  "formulas" : "=sum(2+2)"
}

You should also consider using the Microsoft Graph Client Library for .NET instead of rolling your own raw HTTP calls. It will save you a ton of headaches over time. It also results in much cleaner code:

await graphClient.Me
    .Drive
    .Items["id"]
    .Workbook
    .Worksheets["Sheet1"]
    .Range("C12")
    .Request()
    .PatchAsync(new WorkbookRange()
    {
        Formulas = JArray.Parse(@"[['=2.2']]")
    });

Upvotes: 2

Related Questions