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

$client = new Client();
$client->setApplicationName('Insert Rows And Columns');
$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();
}

/** Print the grid as it currently stands. */
function dump(Sheets $service, string $spreadsheetId, string $tabName, string $label): void
{
    $rows = $service->spreadsheets_values->get($spreadsheetId, $tabName . '!A1:E8')->getValues() ?? [];

    printf("%s\n", $label);
    foreach ($rows as $index => $row) {
        printf("  row %-2d %s\n", $index + 1, implode(' | ', $row));
    }
}

/** Report whether a cell carries a background colour. */
function fillOf(Sheets $service, string $spreadsheetId, string $tabName, string $cell): string
{
    $meta = $service->spreadsheets->get($spreadsheetId, [
        'ranges'          => [$tabName . '!' . $cell],
        'includeGridData' => true,
        'fields'          => 'sheets(data(rowData(values(userEnteredFormat(backgroundColor)))))',
    ]);

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

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

    $format = $rowData[0]->getValues()[0]->getUserEnteredFormat();

    if (!$format || !$format->getBackgroundColor()) {
        return 'no formatting';
    }

    $colour = $format->getBackgroundColor();

    return sprintf('%.2f / %.2f / %.2f',
        $colour->getRed() ?? 0, $colour->getGreen() ?? 0, $colour->getBlue() ?? 0);
}

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

$service->spreadsheets_values->update(
    $spreadsheetId,
    $tabName . '!A1',
    new ValueRange(['values' => [
        ['Name', 'Team', 'Start date'],
        ['Ada', 'Platform', '2026-01-06'],
        ['Grace', 'Platform', '2026-02-17'],
        ['Alan', 'Data', '2026-03-02'],
    ]]),
    ['valueInputOption' => 'USER_ENTERED']
);

// Shade row 2 so the inherited formatting is visible later.
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['repeatCell' => [
            'range' => [
                'sheetId'          => $sheetId,
                'startRowIndex'    => 1,
                'endRowIndex'      => 2,
                'startColumnIndex' => 0,
                'endColumnIndex'   => 3,
            ],
            'cell' => ['userEnteredFormat' => [
                'backgroundColor' => ['red' => 1.0, 'green' => 0.95, 'blue' => 0.70],
            ]],
            'fields' => 'userEnteredFormat.backgroundColor',
        ]]),
    ],
]));

dump($service, $spreadsheetId, $tabName, "Before:");

// Insert one row at position 3, inheriting row 2's formatting.
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['insertDimension' => [
            'range' => [
                'sheetId'    => $sheetId,
                'dimension'  => 'ROWS',
                'startIndex' => 2,
                'endIndex'   => 3,
            ],
            'inheritFromBefore' => true,
        ]]),
    ],
]));

echo "\n";
dump($service, $spreadsheetId, $tabName, "After inserting a row at position 3:");

printf("\n  A3 (inherited from row 2) : %s\n", fillOf($service, $spreadsheetId, $tabName, 'A3'));

// The same insert without inheritance, so the new row starts plain.
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['insertDimension' => [
            'range' => [
                'sheetId'    => $sheetId,
                'dimension'  => 'ROWS',
                'startIndex' => 4,
                'endIndex'   => 5,
            ],
            'inheritFromBefore' => false,
        ]]),
    ],
]));

printf("  A5 (inheritFromBefore off): %s\n", fillOf($service, $spreadsheetId, $tabName, 'A5'));

// Inserting at the very top is the one case that must not inherit.
echo "\nInserting at the top with inheritFromBefore => true:\n";

try {
    $service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
        'requests' => [
            new Request(['insertDimension' => [
                'range' => [
                    'sheetId'    => $sheetId,
                    'dimension'  => 'ROWS',
                    'startIndex' => 0,
                    'endIndex'   => 1,
                ],
                'inheritFromBefore' => true,
            ]]),
        ],
    ]));

    echo "  accepted\n";
} catch (Google\Service\Exception $e) {
    $error = json_decode($e->getMessage(), true)['error'] ?? [];
    printf("  HTTP %d: %s\n", $e->getCode(), $error['message'] ?? $e->getMessage());
}

// Columns work the same way, and can be sized to fit afterwards.
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['insertDimension' => [
            'range' => [
                'sheetId'    => $sheetId,
                'dimension'  => 'COLUMNS',
                'startIndex' => 1,
                'endIndex'   => 2,
            ],
            'inheritFromBefore' => true,
        ]]),
        new Request(['autoResizeDimensions' => [
            'dimensions' => [
                'sheetId'    => $sheetId,
                'dimension'  => 'COLUMNS',
                'startIndex' => 0,
                'endIndex'   => 4,
            ],
        ]]),
    ],
]));

$meta = $service->spreadsheets->get($spreadsheetId, [
    'fields' => 'sheets(properties(sheetId),data(columnMetadata(pixelSize)))',
]);

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

    $widths = array_slice($sheet->getData()[0]->getColumnMetadata(), 0, 4);

    echo "\nColumn widths after inserting a column and auto-resizing:\n";
    foreach ($widths as $index => $column) {
        printf("  column %s : %d px\n", chr(65 + $index), $column->getPixelSize());
    }
}