5

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

Improve this question
4
  • 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. Commented Dec 16, 2017 at 17:19
  • I'm having this exact same issue, documentation on updating/deleting isn't clear in comparison to reading. @wescpy might know Commented Jun 9, 2018 at 0:49
  • 1
    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?) Commented Jun 9, 2018 at 1:14
  • 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. Commented Oct 22, 2018 at 23:50

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@Xavier Shay I'm sorry I couldn't help. For example, how about trying to delete conditional formats using "Try this API" of https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate? You can do that by importing spreadsheetId and Request body.
Batch API doesn't necessarily help, it just groups together other API calls and this case there only appears to be developers.google.com/sheets/api/reference/rest/v4/spreadsheets/…
if you have a bunch of rules this won't work because gsheets resets the 0 based index as you delete so at some point your code will error out.
@grantr Thank you for your comment. About if you have a bunch of rules this won't work because gsheets resets the 0 based index as you delete so at some point your code will error out., in order to correctly understand your current issue, can you provide a detailed flow for correctly replicating it? Because I cannot understand some point. I deeply apologize for my poor English skill. I would like to confirm some point.
@grantr Although I'm not sure about your actual situation, if you can use Google Apps Script, in the current stage, when the method clearConditionalFormatRules of Class Sheet released on April 11, 2018 is used, all conditional format rules can be removed. Ref This method was not existing on Dec 17, 2017 posted this answer. The sample script is SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => sheet.clearConditionalFormatRules()). But, if this was not useful, I apologize.
1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.