CODEWITHSUNDEEP
excelrestgraph
Dale M
Dale M

Reputation: 2473

Is it possible to delete all rows in an Excel table with the Graph Rest Api?

I know that I can delete a single row using this method:

DELETE https://graph.microsoft.com/v1.0/drives/{drive-id}/items/{item-id}/workbook/tables/AccountRegister/rows/$/ItemAt(index={n})

(For the benefit of others this documentation is wrong).

Is there an endpoint to delete all rows (or a range of rows)?

Upvotes: 1

Views: 2087

Answers (3)

jp.mrqs
jp.mrqs

Reputation: 31

You can use the DataBodyRange to delete all rows.

HTTP

POST /me/drive/items/{id}/workbook/tables/{id|name}/DataBodyRange/delete
Content-type: application/json

{
  "shift": "Up"
}

C#

GraphServiceClient graphClient = new GraphServiceClient(authProvider);

await graphClient.Drives["{drive-id}"]
        .Items["{driveItem-id}"]
        .Workbook
        .Tables["{table-id}"]
        .DataBodyRange()
        .Delete("Up")
        .Request()
        .PostAsync();

Upvotes: 3

patella
patella

Reputation: 13

I was struggling with the same challenge and here is the approach I am now using to do this:

  1. Get table properties: https://learn.microsoft.com/en-us/graph/api/table-get - the showHeaders and showTotals properties in the response body will be needed in the next steps.
  2. Get table range: https://learn.microsoft.com/en-us/graph/api/table-range - the address property in the response body will provide the worksheet name and the cell range of the table. The range will include the header and the total row(s) if they are shown (see previous step).
  3. Delete the table range: https://learn.microsoft.com/en-us/graph/api/range-delete - I always use {"shift": "up"} in the request body.
  4. ATTENTION: if the showHeaders and showTotals properties are both false then the delete operation will remove the table altogether, so make sure at least one of them is shown and exclude the corresponding row(s) from the delete range. As a precaution I always do an 'update table' (https://learn.microsoft.com/en-us/graph/api/table-update) before the delete operation and force showHeaders=true + showTotals=false. Then I restore the original values from step 1 after the delete operation is completed.

Upvotes: 1

George R
George R

Reputation: 21

You can delete them in a batch using a POST call. Reference: https://learn.microsoft.com/en-us/graph/json-batching

Example json body to post

{
  "requests": [
    {
      "id": "1",
      "method": "DELETE",
      "url": "/drives/{drive-id}/items/{item-id}/workbook/tables/AccountRegister/rows/$/ItemAt(index={0}"
    },
    {
      "id": "2",
      "method": "DELETE",
      "url": "/drives/{drive-id}/items/{item-id}/workbook/tables/AccountRegister/rows/$/ItemAt(index={1}"
    }
}

This would delete rows 1 and 2 of the table. Headers are excluded from the index.

FYI. There are limitations: Currently no more than 20 calls in a batch. https://learn.microsoft.com/en-us/graph/known-issues#json-batching

Upvotes: 2

Related Questions