PHPSpreadsheet · Google Sheets API · Excel

Spreadsheets, driven by code.

Hands-on PHP tutorials for working with Excel and Google Sheets — read and write .xlsx, convert files to JSON, stream downloads in the browser, and insert images, formulas, and styling. Every guide ships with code you can copy, run, and adapt.

dropdown.php View article
<?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'));

The full script from Add A Dropdown To A Google Sheet Using Google Sheets API PHP Client — copy, run, adapt.

IOFactory::load() PhpSpreadsheet
Open any spreadsheet file
getActiveSheet() PhpSpreadsheet
Select the worksheet to fill
fromArray() PhpSpreadsheet
Write many rows at once
getCalculatedValue() PhpSpreadsheet
Read a formula result
save('php://output') PhpSpreadsheet
Stream the file as a download
spreadsheets_values->get() Google Sheets
Read a range of cells
spreadsheets_values->update() Google Sheets
Write a range of cells
spreadsheets->create() Google Sheets
Create a new spreadsheet
json_encode() PHP
Serialize rows to JSON
header() PHP
Send the download headers