Assigned
Status Update
No update yet.
Comments
jp...@google.com
Generated Metadata
- Summary: The Sheets API does not provide a way to determine the global index of conditional formatting rules across multiple sheets, making it difficult to update or delete specific rules. A feature request is being made to allow updating conditional format rules by sheet ID and index.
- Tags:
Additional resources:
https://developers.google.com/sheets/api/samples/conditional-formatting https://developers.google.com/sheets/api/reference/rest/.../spreadsheets/requ... https://developers.google.com/sheets/api/guides/conditional-format https://stackoverflow.com/.../how-do-you-determine-the-index-of-an-existi... https://developers.google.com/.../api.../sheets/.../google/api/.../sheets/.../Upd...
You can
bl...@google.com
Automated by Blunderbuss job workspace-devrel-public-issue-tracker-blunderbuss-autoassigner for config assign for component 191608.
ch...@gmail.com
After further investigation, it seems the issue isn't a global indexing one, but rather that the Google API is updating some random rule from a different sheet entirely. A sheet that isn't even on my spreadsheet.
Here's the exact data of the request I'm sending:
Array
(
[requests] => Array
(
[updateConditionalFormatRule] => Array
(
[index] => 77
[sheetId] => 661531580
[rule] => Array
(
[ranges] => Array
(
[0] => Array
(
[sheetId] => 661531580
[startRowIndex] => 0
[endRowIndex] => 579
[startColumnIndex] => 2
[endColumnIndex] => 3
)
)
[booleanRule] => Array
(
[condition] => Array
(
[type] => TEXT_EQ
[values] => Array
(
[0] => Array
(
[userEnteredValue] => RK
)
)
)
[format] => Array
(
[backgroundColor] => Array
(
[red] => 0
[green] => 0.5
[blue] => 0.5
)
)
)
)
)
)
)
And exactly what Google returns (with the spreadsheetId hidden by me, but it is correct in my request/result):
Array
(
[spreadsheetId] => {my-spreadsheet-id}
[replies] => Array
(
[0] => Array
(
[updateConditionalFormatRule] => Array
(
[newIndex] => 77
[oldIndex] =>
[oldRule] => Array
(
[ranges] => Array
(
[0] => Array
(
[endColumnIndex] => 10
[endRowIndex] => 159
[sheetId] => 1236872813
[startColumnIndex] => 9
[startRowIndex] => 157
)
)
[booleanRule] => Array
(
[condition] => Array
(
[type] => TEXT_CONTAINS
[values] => Array
(
[0] => Array
(
[relativeDate] =>
[userEnteredValue] => DOV
)
)
)
[format] => Array
(
[horizontalAlignment] =>
[hyperlinkDisplayType] =>
[textDirection] =>
[verticalAlignment] =>
[wrapStrategy] =>
[backgroundColor] => Array
(
[alpha] =>
[blue] => 0.7882353
[green] => 0.76862746
[red] => 0.63529414
)
[backgroundColorStyle] => Array
(
[themeColor] =>
[rgbColor] => Array
(
[alpha] =>
[blue] => 0.7882353
[green] => 0.76862746
[red] => 0.63529414
)
)
)
)
)
[newRule] => Array
(
[ranges] => Array
(
[0] => Array
(
[endColumnIndex] => 3
[endRowIndex] => 579
[sheetId] => 661531580
[startColumnIndex] => 2
[startRowIndex] => 0
)
)
[booleanRule] => Array
(
[condition] => Array
(
[type] => TEXT_EQ
[values] => Array
(
[0] => Array
(
[relativeDate] =>
[userEnteredValue] => RK
)
)
)
[format] => Array
(
[horizontalAlignment] =>
[hyperlinkDisplayType] =>
[textDirection] =>
[verticalAlignment] =>
[wrapStrategy] =>
[backgroundColor] => Array
(
[alpha] =>
[blue] => 0.49803922
[green] => 0.49803922
[red] =>
)
[backgroundColorStyle] => Array
(
[themeColor] =>
[rgbColor] => Array
(
[alpha] =>
[blue] => 0.49803922
[green] => 0.49803922
[red] =>
)
)
)
)
)
)
)
)
)
As you can see, I'm sending the request to update index 77 on sheetId 661531580. But the oldRule Google updates is on sheetId 1236872813. My spreadsheet doesn't have any sheet with that id. The new, updated rule is on my 661531580 spreadsheet now, but in index 23 rather than the desired 77.
It seems particular to the spreadsheet I'm working on (which has 20 or so sheets and several hundred rules) as I tried the same code on a fresh, super simple spreadsheet and things were fine. The spreadsheet was copied from an old one at some point, maybe there's an issue there?
Let me know if you need the specific spreadsheet id or any other information.
Here's the exact data of the request I'm sending:
Array
(
[requests] => Array
(
[updateConditionalFormatRule] => Array
(
[index] => 77
[sheetId] => 661531580
[rule] => Array
(
[ranges] => Array
(
[0] => Array
(
[sheetId] => 661531580
[startRowIndex] => 0
[endRowIndex] => 579
[startColumnIndex] => 2
[endColumnIndex] => 3
)
)
[booleanRule] => Array
(
[condition] => Array
(
[type] => TEXT_EQ
[values] => Array
(
[0] => Array
(
[userEnteredValue] => RK
)
)
)
[format] => Array
(
[backgroundColor] => Array
(
[red] => 0
[green] => 0.5
[blue] => 0.5
)
)
)
)
)
)
)
And exactly what Google returns (with the spreadsheetId hidden by me, but it is correct in my request/result):
Array
(
[spreadsheetId] => {my-spreadsheet-id}
[replies] => Array
(
[0] => Array
(
[updateConditionalFormatRule] => Array
(
[newIndex] => 77
[oldIndex] =>
[oldRule] => Array
(
[ranges] => Array
(
[0] => Array
(
[endColumnIndex] => 10
[endRowIndex] => 159
[sheetId] => 1236872813
[startColumnIndex] => 9
[startRowIndex] => 157
)
)
[booleanRule] => Array
(
[condition] => Array
(
[type] => TEXT_CONTAINS
[values] => Array
(
[0] => Array
(
[relativeDate] =>
[userEnteredValue] => DOV
)
)
)
[format] => Array
(
[horizontalAlignment] =>
[hyperlinkDisplayType] =>
[textDirection] =>
[verticalAlignment] =>
[wrapStrategy] =>
[backgroundColor] => Array
(
[alpha] =>
[blue] => 0.7882353
[green] => 0.76862746
[red] => 0.63529414
)
[backgroundColorStyle] => Array
(
[themeColor] =>
[rgbColor] => Array
(
[alpha] =>
[blue] => 0.7882353
[green] => 0.76862746
[red] => 0.63529414
)
)
)
)
)
[newRule] => Array
(
[ranges] => Array
(
[0] => Array
(
[endColumnIndex] => 3
[endRowIndex] => 579
[sheetId] => 661531580
[startColumnIndex] => 2
[startRowIndex] => 0
)
)
[booleanRule] => Array
(
[condition] => Array
(
[type] => TEXT_EQ
[values] => Array
(
[0] => Array
(
[relativeDate] =>
[userEnteredValue] => RK
)
)
)
[format] => Array
(
[horizontalAlignment] =>
[hyperlinkDisplayType] =>
[textDirection] =>
[verticalAlignment] =>
[wrapStrategy] =>
[backgroundColor] => Array
(
[alpha] =>
[blue] => 0.49803922
[green] => 0.49803922
[red] =>
)
[backgroundColorStyle] => Array
(
[themeColor] =>
[rgbColor] => Array
(
[alpha] =>
[blue] => 0.49803922
[green] => 0.49803922
[red] =>
)
)
)
)
)
)
)
)
)
As you can see, I'm sending the request to update index 77 on sheetId 661531580. But the oldRule Google updates is on sheetId 1236872813. My spreadsheet doesn't have any sheet with that id. The new, updated rule is on my 661531580 spreadsheet now, but in index 23 rather than the desired 77.
It seems particular to the spreadsheet I'm working on (which has 20 or so sheets and several hundred rules) as I tried the same code on a fresh, super simple spreadsheet and things were fine. The spreadsheet was copied from an old one at some point, maybe there's an issue there?
Let me know if you need the specific spreadsheet id or any other information.
Description
Description
If you have multiple sheets with conditional formatting rules, the only way to retrieve them via the API seems to be:
The docs seem to indicate that the index for an existing conditional format is the key within the array of returned rules. However, the rules are all grouped underneath specific 'sheets' meaning under 'Sheet1' you might have a conditionalFormat with index 0 (the key within the array). You might also have a rule within 'Sheet2' that has an index of 0 (the first value in the array).
If you then try to update the Sheet2 (giving it sheetId = 222 in my example below) rule with index 0 with a request like:
It sometimes updates a different rule. I believe it's updating the rule that's globally (across the entire spreadsheet) index = 0. When I test this the data returned after the request tells me the oldRule was a different one than intended. In our example it might be Sheet1's index = 0 rule.
However, there's no way to determine that global index #. There's no way to fetch conditional formats across the entire spreadsheet (aka, not grouped by sheet) and there's no field on the list of existing rules that defines what the global index # is.
So either we need a way to determine the global index # or the update needs to respect the passed sheetId in the request and base its index count off that.