MS Graph API/Workbooks (Excel API): Updating a cell format in a UseRange

Question

What's I'm doing well

I'm currently creating an dotnet core app to consume and process data from an Excel sheet stored in personal OneDrive. I'm using MSAL to create a session token and the data consumption is working great. Here's my working code:

// Get the range for data to process
var dataRangeRequest = myGraphServiceClient // an instance of GraphServiceClient
    .Me.Drive.Items[fileItemId]
    .Workbook
    .Sheets[sheetId]
    .UsedRange(valuesOnly: false)
    .Request();

var dataRange = await dataRangeRequest.GetAsync(ct)

// Extract column names (headers) from the data range
var columnNames = dataRange.Text.First.ToObject<string[]>();
// Extract data cells from the data range
var lines = dataRange.Text.Skip(1).Select(line=>line.ToObject<string[]>).ToArray();
[...] // Here I process the lines using the columnNames.

// --EVERYTHING WORKS UNTIL HERE--

What I'm not doing well

Now, I want to turn red a faulty data cell in the original Excel document

var faultyCell = (row: 34, column: 5); // the row/column offset of the faulty cell in dataRange

// ---------------------------------
// --FOLLOWING CODE IS NOT WORKING--
// ---------------------------------
var changeRange = new WorkbookRange
    {
        RowIndex = faultyCell.row,
        ColumnIndex = faultyCell.column,
        RowCount = 1,
        ColumnCount = 1,
        Format = new WorkbookRangeFormat {Fill = new WorkbookRangeFill {Color = "red"}}
    };
await dataRangeRequest.PatchAsync(changeRange, ct); // Throwing a Microsoft.Graph.ServiceException

I intercepted the HTTP request & response and it's the following:

REQUEST

PATCH https://graph.microsoft.com/v1.0/me/drive/items/<file id>/workbook/worksheets/{sheetId}/microsoft.graph.usedRange(valuesOnly=true) HTTP/1.1

{
    "columnIndex": 5,
    "rowIndex": 34,
    "columnCount": 1,
    "rowCount": 1,
    "format": {
        "fill": {
            "color": "Red",
            "@odata.type": "microsoft.graph.workbookRangeFill"
        },
        "@odata.type": "microsoft.graph.workbookRangeFormat"
    },
    "@odata.type": "microsoft.graph.workbookRange"
}

RESPONSE

400 Bad Request

{
  "error": {
    "code": "BadRequest",
    "message": "Bad Request - Error in query syntax.",
    "innerError": {
      "date": "<the date>",
      "request-id": "<a guid>",
     "client-request-id": "<another guid>"
   }
 }
}

Success with a manual HTTP request

I succeed to update manually the cell using a HTTP request by following the documentation.

WORKING REQUEST:

PATCH https://graph.microsoft.com/v1.0/me/drive/items/<file id>/workbook/worksheets/{sheetId}/range(address='F35:F35')/format/fill

{"color": "red"}

Problems

1. I don't know how to generate this HTTP request from C# by using the Microsoft.Graph api. (the documentation is obsoleted, there's no .Format on IWorkbookWorksheetRangeRequestBuilder. This error seems documented on GitHub. Is there an easy way to use the graph SDK to send an arbritary http request?
2. More importantly: for this to work, I need to translate the cell offset to a range address. Is there an utility somewhere to do that? In my example I manually translated the offset 5,34 in the range to address F35.

Specifications

Packages:

• Microsoft.Graph: v3.15.0 (latest release version)
• Microsoft.Identity.Client: (MSAL) v4.15.0 (not the latest version, but shoudn't be a problem here)

Answer 1

I can confirm, that this issue with no .Format on IWorkbookWorksheetRangeRequestBuilder is frustrating for me as well ;) I've reported this issue on GitHub:

https://github.com/microsoftgraph/msgraph-sdk-dotnet/issues/233

For me the workaround was to do it like so (i was basing it on this SOF: REST call to Microsoft Graph

        var requestUrl = $@"https://graph.microsoft.com/v1.0/users/{user id}/drive/items/{Consts.DriveId}/workbook/worksheets/{Consts.SheetId}/range(address='{range}')/{resource}";

        string workbookRangeFill = GraphServiceClient.HttpProvider.Serializer.SerializeObject(workbookRange);

        // Create the request message and add the content.
        HttpRequestMessage hrm = new HttpRequestMessage(new HttpMethod(httpMethod), requestUrl);
        hrm.Content = new StringContent(workbookRangeFill, System.Text.Encoding.UTF8, "application/json");

        // Authenticate (add access token) our HttpRequestMessage
        await GraphServiceClient.AuthenticationProvider.AuthenticateRequestAsync(hrm);

        // Send the request and get the response.
        HttpResponseMessage response = await GraphServiceClient.HttpProvider.SendAsync(hrm);

Whereas workbookRange variable is of either of type: WorkbookRangeFill or WorkbookRangeFont (Depending on the need) I assume you will be interested in WorkbookRangeFill (to change the color in range/cell)

range variable is range in spreadsheet in the format: "A1:B3"

resource variable is format/fill for WorkbookRangeFill and format/font for WorkbookRangeFont

and of course {user id} is the user which owns the document (i am using client credentials flow with with application permisions For other scenarios i assume you can just change one thing in the code above. So that, instead of:

https://graph.microsoft.com/v1.0/users/{user id}/drive

to use

https://graph.microsoft.com/v1.0/me/drive