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 = 'Orders';

$client = new Client();
$client->setApplicationName('Add A Dropdown');
$client->setAuthConfig($keyFile);
$client->addScope(Sheets::SPREADSHEETS);

$service = new Sheets($client);

/** Return a brand-new tab with this name, deleting any previous one. */
function freshSheetId(Sheets $service, string $spreadsheetId, string $title): int
{
    $requests = [];

    foreach ($service->spreadsheets->get($spreadsheetId)->getSheets() as $sheet) {
        if ($sheet->getProperties()->getTitle() === $title) {
            $requests[] = new Request([
                'deleteSheet' => ['sheetId' => $sheet->getProperties()->getSheetId()],
            ]);
        }
    }

    $requests[] = new Request(['addSheet' => ['properties' => ['title' => $title]]]);

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

    $replies = $response->getReplies();

    return end($replies)->getAddSheet()->getProperties()->getSheetId();
}

/** Report the validation rule attached to one cell. */
function validationOf(Sheets $service, string $spreadsheetId, string $tabName, string $cell): string
{
    $meta = $service->spreadsheets->get($spreadsheetId, [
        'ranges'          => [$tabName . '!' . $cell],
        'includeGridData' => true,
        'fields'          => 'sheets(data(rowData(values(dataValidation))))',
    ]);

    $rowData = $meta->getSheets()[0]->getData()[0]->getRowData();

    if (!$rowData || !$rowData[0]->getValues()) {
        return 'none';
    }

    $rule = $rowData[0]->getValues()[0]->getDataValidation();

    if (!$rule) {
        return 'none';
    }

    $values = array_map(
        fn($value) => $value->getUserEnteredValue(),
        $rule->getCondition()->getValues() ?? []
    );

    return sprintf('%s [%s] strict=%s',
        $rule->getCondition()->getType(),
        implode(', ', $values),
        $rule->getStrict() ? 'true' : 'false');
}

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

$service->spreadsheets_values->update(
    $spreadsheetId,
    $tabName . '!A1',
    new ValueRange(['values' => [
        ['Order', 'Region'],
        ['SO-1001', 'North'],
        ['SO-1002', 'South'],
        ['SO-1003', ''],
    ]]),
    ['valueInputOption' => 'USER_ENTERED']
);

// The dropdown itself: a list of allowed values on the Region column.
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['setDataValidation' => [
            'range' => [
                'sheetId'          => $sheetId,
                'startRowIndex'    => 1,
                'endRowIndex'      => 4,
                'startColumnIndex' => 1,
                'endColumnIndex'   => 2,
            ],
            'rule' => [
                'condition' => [
                    'type'   => 'ONE_OF_LIST',
                    'values' => [
                        ['userEnteredValue' => 'North'],
                        ['userEnteredValue' => 'South'],
                        ['userEnteredValue' => 'East'],
                        ['userEnteredValue' => 'West'],
                    ],
                ],
                'inputMessage' => 'Pick a sales region',
                'strict'       => true,
                'showCustomUi' => true,
            ],
        ]]),
    ],
]));

printf("B2 validation: %s\n", validationOf($service, $spreadsheetId, $tabName, 'B2'));

// Now the question that decides how much you can trust it.
echo "\nWriting an invalid value through the API:\n";

$service->spreadsheets_values->update(
    $spreadsheetId,
    $tabName . '!B4',
    new ValueRange(['values' => [['Atlantis']]]),
    ['valueInputOption' => 'USER_ENTERED']
);

$back = $service->spreadsheets_values->get($spreadsheetId, $tabName . '!B4')->getValues();
printf("  B4 now holds: %s\n", $back[0][0] ?? '(empty)');

// A dropdown fed from a range instead of a literal list.
$service->spreadsheets_values->update(
    $spreadsheetId,
    $tabName . '!E1',
    new ValueRange(['values' => [['Standard'], ['Express'], ['Courier']]]),
    ['valueInputOption' => 'RAW']
);

$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['setDataValidation' => [
            'range' => [
                'sheetId'          => $sheetId,
                'startRowIndex'    => 1,
                'endRowIndex'      => 4,
                'startColumnIndex' => 2,
                'endColumnIndex'   => 3,
            ],
            'rule' => [
                'condition' => [
                    'type'   => 'ONE_OF_RANGE',
                    'values' => [['userEnteredValue' => '=' . $tabName . '!E1:E3']],
                ],
                'strict'       => true,
                'showCustomUi' => true,
            ],
        ]]),
    ],
]));

printf("\nC2 validation: %s\n", validationOf($service, $spreadsheetId, $tabName, 'C2'));

// Removing a rule: the same request, with no rule at all.
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['setDataValidation' => [
            'range' => [
                'sheetId'          => $sheetId,
                'startRowIndex'    => 1,
                'endRowIndex'      => 2,
                'startColumnIndex' => 1,
                'endColumnIndex'   => 2,
            ],
        ]]),
    ],
]));

printf("B2 validation after clearing: %s\n", validationOf($service, $spreadsheetId, $tabName, 'B2'));