How to programmatically delete all conditional formatting on a Google Sheet?

5
votes

I tried combining two examples from https://developers.google.com/sheets/api/samples/conditional-formatting

  1. Read all conditional formats.
  2. Delete them.

Deletion requires the index to delete, but this is not returned in the read API response. I tried assuming that the index of the returned formats in the array was appropriate, but this encountered an error "no conditional format at index" in the middle of the operation, before they were all deleted.

Here is a copy of the sheet I am trying to clear: https://docs.google.com/spreadsheets/d/1Y0tsEcka-1gziimesE74IhPFqGkUO985eZNoVQ9y0BU/edit#gid=0

1
google-sheetsgoogle-apigoogle-sheets-apigoogle-sheets-conditionalformatting
I was able to mostly work around this by trying to delete each rule individually and ignoring errors, but that still left some formats in the sheet. – Xavier Shay
I'm having this exact same issue, documentation on updating/deleting isn't clear in comparison to reading. @wescpy might know – calumbrodie
To quickly update in case anyone else reads this, I ended up doing the same as above. Deleting each rule 1 at a time (using the index of 0) seems to work (as each update updates the overall index and there is always a 0 indexed conditional.... maybe?) – calumbrodie
I believe this to be a bug in the Google Sheets API. If you have N conditional format rules, the batchUpdate request only lets you delete (N-1)/2, rounded down, of the rules. I've created an issue here. – Brian Amadio

1 Answers

3
votes

How about this solution? In this solution, you problem can be solved by 2 times of API requests.

1. Retrieve all conditional formats from a sheet on Spreadsheet.

sheets.spreadsheets.get is used for this situation.

Request :

GET https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###?ranges=### sheet name ###&fields=sheets%2FconditionalFormats%2Franges%2FsheetId

Please input ### spreadsheet ID ### and ### sheet name ###.

Response :

This response retrieves the number of conditional formats. This is used for deleting conditional formats.

{"sheets": [{"conditionalFormats": [
  {"ranges": [{"sheetId": #####}]},
  {"ranges": [{"sheetId": #####}]}
]}]}

2. Delete all conditional formats.

sheets.spreadsheets.batchUpdate is used for this situation.

Request :

POST https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###:batchUpdate

Request body :

Here, index means the number of conditional formats retrieved by above GET method. For example, when there are 2 conditional formats in the sheet, the length of requests is 2. The following requests[0] means sheets.conditionalFormats[0] as shown above.

{"requests": [
  {"deleteConditionalFormatRule": {"sheetId": #####, "index": 0}},
  {"deleteConditionalFormatRule": {"sheetId": #####, "index": 1}}
]}

Please input ### spreadsheet ID ### and sheetId.

Note :

  • In order to use above APIs, you can retrieve access token.
  • Because to delete all conditional formats on a sheet is the aim, the information which is retrieved from spreadsheet is the necessity minimum.

References :

If I misunderstand your question, I'm sorry.