Google Spreadsheets API - Conditional Formatting based on range and adjacent values

Question

I have the following very streamlined version of a spreadsheet:

Sect   | Lbl    | A      | B       | C       | D       | E
==========================================================
Sec1   | Lbl1   | 1      | 8       | 6       | 10      |
----------------------------------------------------------
Sec2   | Lbl2   | 2      | 1       | 1       | >100    |
----------------------------------------------------------
etc...

I want to apply a rule/rules across all values to say:

Bg Color = Green if: - the cell to the right is not blank and higher than this value

Bg Color = Red if: - the cell to the right is not blank and less than this value

Bg Color = white (no action) if: - the cell to the right has the same value

In addition, if the value is set to the non-numeric ">100" I need to convert it to 100 as part of this formatting.

I'm using C# to do this with the Spreadsheets v4 API. So far I have the code below but I'm unsure as to how I could apply multiple conditions to a formatting rule.

Update

Please note updated table example above before reading below

Thanks to TheMaster I've got something up and running but it's not quite right yet. I've got an additional factor of:

• the first row should be excluded from the conditional formatting
• the first two columns are labels and should also be ignored/excluded
• all other columns and rows of data need to be compared to the column to their right (if available) and coloured accordingly.

Here is my code so far for the Red rule (where cell value > cell value to the right). In addition to this I have a Green rule (where cell value < cell value to the right) and a White rule (where cell value = cell value to the right)

They are indexed as below in a batchupdate request: 0 = red 1 = green 2 = white

The Code for the Red rule:

formatRequest.Requests.Add(new Google.Apis.Sheets.v4.Data.Request()
          {
              AddConditionalFormatRule = new AddConditionalFormatRuleRequest()
              {
                  Rule = new ConditionalFormatRule()
                  {
                      BooleanRule = new BooleanRule()
                      {
                          Condition = new BooleanCondition()
                          {
                              Type = "CUSTOM_FORMULA",
                              Values = new List<ConditionValue>() {
                                  new ConditionValue()
                                  {
                                      UserEnteredValue = "=AND(NOT(ISBLANK(A2)),(1*REGEXEXTRACT(A2,\"\\d+\"))>(1*REGEXEXTRACT(B2,\"\\d+\")))"
                                  }
                              }
                          },
                           Format = new CellFormat()
                           {
                               BackgroundColor = new Color()
                               {
                                   Red = 0.8f,
                                   Green = 0f,
                                   Blue = 0f,
                                   Alpha = 1f
                               }
                           }
                      },
                      Ranges =  new List<GridRange>()
                      {
                          new GridRange()
                          {
                              SheetId = Convert.ToInt32(sheetId)
                              ,StartRowIndex = 1
                          },
                      }
                   },
                  Index = 0
              }
          });

The problem is it doesn't apply the conditional formatting to the whole sheet...only the first column of data.

Answer 1

• You should use CUSTOM_FORMULA as Boolean condition Type
• You'll need to add two conditional formatting rules with index 0 and 1
• Range will be open-ended and cover the full sheet.

Snippet(for A1:Z;Bg:red):

• Boolean Condition JSON:

{ 
  "type": "CUSTOM_FORMULA",
  "values": [
    {
      userEnteredValue: "=AND(NOT(ISBLANK(A1)),A1>(IF(ISNUMBER(B1),B1,1*REGEXEXTRACT(B1,\"\d+\"))))"
    }
  ]
}

• BooleanRule/Format/BackgroundColor JSON:

{
  "red": 1,
  "green": 0,
  "blue": 0,
  "alpha": 1
}