Spreadsheets, driven by code.

<?php

require 'vendor/autoload.php';

use Google\Client;
use Google\Service\Sheets;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
use Google\Service\Sheets\Request;
use Google\Service\Sheets\ValueRange;

$spreadsheetId = 'YOUR_SPREADSHEET_ID';
$keyFile = 'service-account.json';
$tabName = 'Stock';

$client = new Client();
$client->setApplicationName('Conditional Formatting');
$client->setAuthConfig($keyFile);
$client->addScope(Sheets::SPREADSHEETS);

$service = new Sheets($client);

function sheetIdFor(Sheets $service, string $spreadsheetId, string $title): int
{
    foreach ($service->spreadsheets->get($spreadsheetId)->getSheets() as $sheet) {
        if ($sheet->getProperties()->getTitle() === $title) {
            return $sheet->getProperties()->getSheetId();
        }
    }

    $service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
        'requests' => [
            new Request(['addSheet' => ['properties' => ['title' => $title]]]),
        ],
    ]));

    return sheetIdFor($service, $spreadsheetId, $title);
}

$sheetId = sheetIdFor($service, $spreadsheetId, $tabName);

// Start from a clean tab so the rule list is not appended to on a re-run.
while (true) {
    try {
        $service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
            'requests' => [
                new Request(['deleteConditionalFormatRule' => [
                    'sheetId' => $sheetId,
                    'index'   => 0,
                ]]),
            ],
        ]));
    } catch (Google\Service\Exception $e) {
        break;
    }
}

$service->spreadsheets_values->update(
    $spreadsheetId,
    $tabName . '!A1',
    new ValueRange(['values' => [
        ['Item', 'In stock', 'Price'],
        ['Widget', 120, 9.99],
        ['Gadget', 45, 24.50],
        ['Doohick', 300, 1.75],
        ['Gizmo', 12, 99.00],
    ]]),
    ['valueInputOption' => 'USER_ENTERED']
);

$dataRange = [
    'sheetId'          => $sheetId,
    'startRowIndex'    => 1,
    'endRowIndex'      => 5,
    'startColumnIndex' => 1,
    'endColumnIndex'   => 2,
];

$priceRange = [
    'sheetId'          => $sheetId,
    'startRowIndex'    => 1,
    'endRowIndex'      => 5,
    'startColumnIndex' => 2,
    'endColumnIndex'   => 3,
];

// A boolean rule: one condition, one format, applied when it is true.
$lowStock = new Request(['addConditionalFormatRule' => [
    'index' => 0,
    'rule'  => [
        'ranges'      => [$dataRange],
        'booleanRule' => [
            'condition' => [
                'type'   => 'NUMBER_LESS',
                'values' => [['userEnteredValue' => '50']],
            ],
            'format' => [
                'backgroundColor' => ['red' => 0.96, 'green' => 0.80, 'blue' => 0.80],
                'textFormat'      => ['bold' => true],
            ],
        ],
    ],
]]);

// A gradient rule: no condition, a colour scale across the range.
$priceScale = new Request(['addConditionalFormatRule' => [
    'index' => 1,
    'rule'  => [
        'ranges'       => [$priceRange],
        'gradientRule' => [
            'minpoint' => ['type' => 'MIN', 'color' => ['red' => 1.0, 'green' => 1.0, 'blue' => 1.0]],
            'maxpoint' => ['type' => 'MAX', 'color' => ['red' => 0.20, 'green' => 0.67, 'blue' => 0.28]],
        ],
    ],
]]);

$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [$lowStock, $priceScale],
]));

echo "Rules applied.\n\n";

// Read the rules back. They live on the SHEET, not on the cells.
$meta = $service->spreadsheets->get($spreadsheetId, [
    'fields' => 'sheets(properties(sheetId),conditionalFormats)',
]);

foreach ($meta->getSheets() as $sheet) {
    if ($sheet->getProperties()->getSheetId() !== $sheetId) {
        continue;
    }

    $rules = $sheet->getConditionalFormats() ?? [];
    printf("Rules stored on the tab: %d\n", count($rules));

    foreach ($rules as $index => $rule) {
        if ($rule->getBooleanRule()) {
            $condition = $rule->getBooleanRule()->getCondition();
            $values = array_map(
                fn($value) => $value->getUserEnteredValue(),
                $condition->getValues() ?? []
            );
            printf("  [%d] boolean  %s %s\n", $index, $condition->getType(), implode(', ', $values));
        }

        if ($rule->getGradientRule()) {
            printf("  [%d] gradient %s -> %s\n", $index,
                $rule->getGradientRule()->getMinpoint()->getType(),
                $rule->getGradientRule()->getMaxpoint()->getType());
        }
    }
}

// Gizmo has 12 in stock, so the rule matches and B5 is pink on screen.
$cell = $service->spreadsheets->get($spreadsheetId, [
    'ranges'          => [$tabName . '!B5'],
    'includeGridData' => true,
    'fields'          => 'sheets(data(rowData(values(userEnteredFormat,effectiveFormat(backgroundColor)))))',
]);

$values = $cell->getSheets()[0]->getData()[0]->getRowData()[0]->getValues()[0];

echo "\nB5 holds 12, so the rule matches. What does the cell say?\n";
printf("  userEnteredFormat : %s\n", $values->getUserEnteredFormat() ? 'set' : 'none');

$effective = $values->getEffectiveFormat();
printf("  effectiveFormat   : %s\n", $effective ? sprintf('%.2f / %.2f / %.2f',
    $effective->getBackgroundColor()->getRed() ?? 0,
    $effective->getBackgroundColor()->getGreen() ?? 0,
    $effective->getBackgroundColor()->getBlue() ?? 0) : 'none');