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

Question

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

Answer 1

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 :

• sheets.spreadsheets.get
• sheets.spreadsheets.batchUpdate

If I misunderstand your question, I'm sorry.

Answer 2

To elaborate on the previous answers and comments and hopefully save someone else some time, this is what I did to remove all conditional formats in a given sheet.

def delete_conditional_format_rules(spread_sheet_id, sheet_id)
  reqs = []
  sheet_info = JSON.parse(google.get_spreadsheet(spread_sheet_id).to_json)
  sheet_info['sheets'].each do |info|
    if info['properties']['sheetId'] == sheet_id && info['conditionalFormats']
      info['conditionalFormats'].each do |i| 
        reqs.push(
          delete_conditional_format_rule: {
            index: 0,
            sheet_id: sheet_id
          }
        )
      end
    end
  end
  body = { requests: reqs }
  sheets.spreadsheets.batch_update_spreadsheet(spread_sheet_id, body)
end

After getting the spreadsheet information from sheets.spreadsheets.get, I looped through each sheet. After finding the sheet that I want to edit by comparing it to what I passed through the method, I check to see if it has any conditional formats (to null proof it). Then for each array element of conditionalFormats I add a request object to the final array I pass in the body of the call and delete at index 0.

It's probably a little clunky, but I got it to do what I needed it to. Hopefully this helps.