Update Values in Excel Named Ranges using Microsoft Graph API

Question

I have an Excel file loaded into Office 365 that is accessible via the Microsoft Graph API with many named ranges, some are individual values, some are blocks of cells.

I can successfully update individual values using the API, but when I try to update multiple cells at the same time, I run into problems.

For this example, consider a single 3-cell array from F10:F12

I would like to populate as follows:

F10 = A

F11 = B

F12 = C

So, I create list of strings that ends up looking like this... [ ["A"], ["B"], ["C"] ]

and I pass it to the Graph API using the following code...

public static async Task<WorkbookRange> UpdateRangeArray(string strItemId, string strSheetName, string strRangeName, List<string> strRangeValues, string strSessionId)
{
    string[][] strValueArray = new string[strRangeValues.Count][];
    try
    {
        int i = 0;
        foreach (var val in strRangeValues)
        {
            strValueArray[i] = new string[1] { val };
            i++;
        }
    }

    var jsonValueArray = JsonConvert.SerializeObject(strValueArray);

    var rangeUpdate = new Microsoft.Graph.WorkbookRange();
    rangeUpdate.Values = jsonValueArray;

    var result = await graphClient.Users[_strUserId].Drive.Items[strItemId].Workbook.Worksheets[strSheetName]
        .Range(strRangeName)
        .Request()
        .Header("workbook-session-id", strSessionId)
        .PatchAsync(rangeUpdate).ConfigureAwait(false);

    return result;

 }

So I am able to update the values in the range, EXCEPT instead of the expected values, what I get is this...

F10 = [ ["A"],["B"],["C"] ]

F11 = [ ["A"],["B"],["C"] ]

F12 = [ ["A"],["B"],["C"] ]

Instead of the Graph API putting the first value in the first cell, second value in the second cell, and so forth... It puts the entire array of data in each cell.

I assume this is some sort of formatting error, maybe my JSON is malformed or perhaps I'm submitting it using the wrong Graph API endpoint or something.

Any help would be greatly appreciated...

UPDATE 1:

I also tried doing this with the RANGE (ie - .Range("F10:F12")) instead of using the NAME and I get the same result.

UPDATE 2:

Cross-posted on GitHub in case this is a bug not just a user error. https://github.com/microsoftgraph/msgraph-sdk-dotnet/issues/695

UPDATE 3:

I can successfully PATCH via the Graph API Explorer to the following URL... https://graph.microsoft.com/v1.0/me/drive/items/{item-id}/workbook/worksheets/INPUTS/range(address='F10:F12')

with this body... {"values":[["Hello"],["How"],["Are You?"]]}

...and it works.

but still can't get it to work via the MSGraph-SDK-dotnet

UPDATE 4:

I AM able to get it to work correctly using Postman and the resulting RestSharp code that works looks like this...

    public static async Task TestUpdatePostman()
    {
        var client = new RestClient("https://graph.microsoft.com/v1.0/users/{USER-ID}/drive/items/{ITEM-ID}/workbook/worksheets/INPUTS/range(address='F10:F12')");
        client.Timeout = -1;
        var request = new RestRequest(Method.PATCH);
        request.AddHeader("Authorization", "Bearer {INSERT-TOKEN}");
        request.AddHeader("Content-Type", "application/json");
        request.AddParameter("application/json", "{\"values\":[[\"Hello\"],[\"How\"],[\"Are You?\"]]}", ParameterType.RequestBody);
        IRestResponse response = client.Execute(request);
        Console.WriteLine(response.Content);
    }

This again makes me believe there's an issue with the way the SDK wrapper implements the API call.

Answer 1

It might not be a SDK issue. When updating the range, rangeUpdate.Values should be a JToken type instead of a string. Try using:

JArray jsonValueArray = JArray.FromObject(strValueArray);