Finding the correct index for conditional formatting rules across multiple worksheets in Google Sheets API

Other Questions Google Sheets
1

I’m trying to update conditional formatting rules using the Google Sheets API but I’m having trouble with the indexing across different worksheets.

My Understanding

From what I’ve learned, here’s what I need to do:

  1. Retrieve all the existing conditional formatting rules
  2. Identify the correct index for the rule I want to modify
  3. Send an update request that includes this index

The Issue

The conditional formatting rules I get back are organized by separate worksheets. Each worksheet has its own set of rules with unique indexes (0, 1, 2, etc.). However, when I attempt to update a rule, it appears that the API requires a global index that spans the entire spreadsheet.

My Current Approach

I retrieve the rules using the following request:

GET https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}?fields=sheets(properties(title,sheetId),conditionalFormats)

The response I receive is similar to this:

{
  "worksheets": [
    {
      "properties": {
        "worksheetId": 123,
        "name": "FirstSheet"
      },
      "conditionalFormats": [
        {
          "ranges": [{
            "startRow": 1,
            "endRow": 100,
            "startCol": 1,
            "endCol": 5,
            "worksheetId": 123
          }],
          "booleanRule": {
            "condition": {
              "type": "TEXT_CONTAINS",
              "values": [{
                "userEnteredValue": "PRIORITY"
              }]
            },
            "format": {
              "backgroundColor": {
                "red": 0.8,
                "green": 0.2,
                "blue": 0.2
              }
            }
          }
        }
      ]
    },
    {
      "properties": {
        "worksheetId": 456,
        "name": "SecondSheet"
      },
      "conditionalFormats": [
        {
          "ranges": [{
            "startRow": 2,
            "endRow": 200,
            "startCol": 3,
            "endCol": 8,
            "worksheetId": 456
          }],
          "booleanRule": {
            "condition": {
              "type": "TEXT_EQ",
              "values": [{
                "userEnteredValue": "COMPLETED"
              }]
            },
            "format": {
              "backgroundColor": {
                "red": 0.2,
                "green": 0.8,
                "blue": 0.2
              }
            }
          }
        }
      ]
    }
  ]
}

When I try to make an update for the rule on SecondSheet (which is index 0 within that specific sheet) with the following request:

{
  "requests": [{
    "updateConditionalFormatRule": {
      "index": 0,
      "worksheetId": 456,
      "rule": {
        "ranges": [{
          "startRow": 2,
          "endRow": 200,
          "startCol": 3,
          "endCol": 8,
          "worksheetId": 456
        }],
        "booleanRule": {
          "condition": {
            "type": "TEXT_EQ",
            "values": [{
              "userEnteredValue": "FINISHED"
            }]
          },
          "format": {
            "backgroundColor": {
              "red": 0.3,
              "green": 0.7,
              "blue": 0.9
            }
          }
        }
      }
    }
  }]
}

It updates a different rule instead. This suggests that the index is perhaps treated as a global index across all worksheets, not a local index specific to each one.

My Question

Is there a method to obtain a complete list of all conditional formatting rules showing their correct global indexes? Or am I overlooking how indexing functions when updating rules in various worksheets? My spreadsheet contains about 100 conditional formatting rules distributed across multiple worksheets, making accurate indexing essential.

2

I had this exact problem with a complex spreadsheet that had tons of conditional formatting rules. Google Sheets API doesn’t give you a direct way to get rules with their indexes already calculated. Here’s what worked for me: I built a mapping function that processes the entire spreadsheet response and assigns sequential indexes to each rule in its worksheet. The tricky part is that indexes are worksheet-specific, but rules can shift around when you add or delete other rules - that’s probably why you’re updating the wrong one. I’d grab all the rules right before your update instead of using cached indexes, since other operations might’ve moved things around. Also double-check you’re using the right sheetId in your update request - I’ve screwed that up before and got the same weird behavior.

3

I’ve hit this same issue. The index is per-worksheet, not global, and timing screws everything up. If you’re doing batch updates between fetching and updating rules, the indexes shift on you. I grab a fresh fetch right before every update call now. Also check for hidden conditional formats - deleted rules sometimes stick around and mess with indexing. Clear out any unused rules first.

4

The API docs can be misleading. The index parameter in updateConditionalFormatRule refers to the position within that specific worksheet, rather than across the entire spreadsheet. The challenge you’re facing is due to the fact that conditional formatting rules aren’t listed in the order you might expect; they’re returned in the order they were created, not in a logical sequence. When retrieving the rules, ensure you’re iterating through them correctly and taking note of their actual positions in each worksheet’s conditionalFormats array. I recommend adding logging to track rule details along with their calculated indexes for each worksheet before making update requests. This practice can save considerable time and effort while troubleshooting indexing issues. Remember, each worksheet’s conditional formatting rules function as separate arrays; use the array position as the index when updating.