CODEWITHSUNDEEP
microsoft-graph-api
Zak Burkhardt
Zak Burkhardt

Reputation: 21

Microsoft Graph API Bulk Edit of Noncontiguous Rows in Excel

I have a need to be able to edit multiple (10-20) noncontiguous rows in an Excel table via the Microsoft Graph API. My application receives a list of 10-20 strings as input. It then needs to be able to find the rows of data associated with those strings (they are all in the same column) and update each row (separate column) with different values. I am able to update the rows using individual PATCH requests that specify the specific row index to update, however, sending 10-20 separate HTTP requests is not sufficient due to performance reasons.

Here is what I have tried so far:

Is there any feature in the Microsoft Graph API I am not aware of that would enable me to do what I am proposing? Or any other idea/approach I am not thinking of? I would think that making bulk edits to noncontiguous rows in a range/table would be a common problem. I have searched through the API documentation/forums/etc. and cannot seem to find anything else that would help.

Any help/information in the right direction would be greatly appreciated!

Upvotes: 0

Views: 938

Answers (1)

Zak Burkhardt
Zak Burkhardt

Reputation: 21

After much trial and error I was able to solve my problem using filtering. I stumbled across this readme on filter apply: https://github.com/microsoftgraph/microsoft-graph-docs/blob/master/api-reference/v1.0/api/filter_apply.md which has an example request body of:

{
    "criteria": {
        "criterion1": "criterion1-value",
        "criterion2": "criterion2-value",
        "color": "color-value",
        "operator": {
        },
        "icon": {
          "set": "set-value",
          "index": 99
        },
        "dynamicCriteria": "dynamicCriteria-value",
        "values": {
        },
        "filterOn": "filterOn-value"
    }
}

Although this didn't help me immediately, it got me thinking in the right direction. I was unable to find any more documentation about how the request format works but I started playing with the request body until finally I got something working. I changed "values" to an array of String and "filterOn" to "values". Now rather than being limited to criterion1 and criterion2 I can filter on whatever values I pass in the "values" array.

{  
    "criteria": {
        "values": [
            "1",
            "2",
            "3",
            "4",
            "5"
        ],
        "filterOn": "values"
    }
}

After applying the filter I retrieve the visibleView range, which I discovered here: https://developer.microsoft.com/en-us/excel/blogs/additions-to-excel-rest-api-on-microsoft-graph/, like this:

/workbook/tables('tableName')/range/visibleView?$select=values

Lastly, I perform a bulk edit on the visibleView range with a PATCH request like this:

/workbook/tables('tableName')/range/visibleView

and a request body with a "values" array that matches the number of columns/rows I am updating.

Unfortunately this simple task was made difficult by a lack of Microsoft Graph API documentation, but hopefully this information here is able to help someone else.

Upvotes: 1

Related Questions