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

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

/** Write the fixture back, so every demo starts from the same grid. */
function seed(Sheets $service, string $spreadsheetId, string $tabName): void
{
    $service->spreadsheets_values->update(
        $spreadsheetId,
        $tabName . '!A1',
        new ValueRange(['values' => [
            ['Client', 'Status', 'Owner', 'Fee'],
            ['Acme Ltd', 'pending', 'ada', 1200],
            ['Acme Holdings', 'PENDING', 'grace', 800],
            ['Bravo Ltd', 'paid', 'ada', 450],
            ['Pending Motors', 'pending', 'alan', 990],
            ['Total', '', '', '=SUM(D2:D5)'],
        ]]),
        ['valueInputOption' => 'USER_ENTERED']
    );
}

/** Print the grid, and the Total row's formula alongside its value. */
function dump(Sheets $service, string $spreadsheetId, string $tabName, string $label): void
{
    $rows = $service->spreadsheets_values->get($spreadsheetId, $tabName . '!A1:D6')->getValues() ?? [];
    $formula = $service->spreadsheets_values
        ->get($spreadsheetId, $tabName . '!D6', ['valueRenderOption' => 'FORMULA'])
        ->getValues()[0][0] ?? '';

    printf("%s\n", $label);
    foreach ($rows as $index => $row) {
        printf("  %-15s %-9s %-6s %s\n", $row[0] ?? '', $row[1] ?? '', $row[2] ?? '', $row[3] ?? '');
    }
    printf("  (D6 holds %s)\n", $formula);
}

/**
 * Run one findReplace and report what it claims to have changed.
 *
 * Every counter in the reply is separate, and a null means "none" rather
 * than zero, so they are printed exactly as they arrive.
 */
function findReplace(Sheets $service, string $spreadsheetId, array $spec, string $label): void
{
    try {
        $response = $service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
            'requests' => [new Request(['findReplace' => $spec])],
        ]));

        $result = $response->getReplies()[0]->getFindReplace();

        printf("%s\n", $label);
        printf("    occurrences=%-5s rows=%-5s values=%-5s formulas=%s\n",
            var_export($result->getOccurrencesChanged(), true),
            var_export($result->getRowsChanged(), true),
            var_export($result->getValuesChanged(), true),
            var_export($result->getFormulasChanged(), true)
        );
    } catch (Google\Service\Exception $e) {
        $error = json_decode($e->getMessage(), true)['error'] ?? [];

        printf("%s\n", $label);
        printf("    HTTP %d: %s\n", $e->getCode(), $error['message'] ?? $e->getMessage());
    }
}

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

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

// ------------------------------------------------------------ scope is required
echo "\n";
findReplace($service, $spreadsheetId, [
    'find'        => 'pending',
    'replacement' => 'awaiting',
], 'no scope at all');

findReplace($service, $spreadsheetId, [
    'find'        => 'pending',
    'replacement' => 'awaiting',
    'allSheets'   => true,
    'sheetId'     => $sheetId,
], 'allSheets and sheetId together');

// ------------------------------------------------------- the default is greedy
findReplace($service, $spreadsheetId, [
    'find'        => 'pending',
    'replacement' => 'awaiting',
    'sheetId'     => $sheetId,
], 'defaults');

echo "\n";
dump($service, $spreadsheetId, $tabName, "After the default replace:");

// -------------------------------------------------- matching the whole cell only
seed($service, $spreadsheetId, $tabName);

echo "\n";
findReplace($service, $spreadsheetId, [
    'find'            => 'pending',
    'replacement'     => 'awaiting',
    'sheetId'         => $sheetId,
    'matchCase'       => true,
    'matchEntireCell' => true,
], 'matchCase + matchEntireCell');

echo "\n";
dump($service, $spreadsheetId, $tabName, "After the careful replace:");

// ------------------------------------------------------------------- formulas
seed($service, $spreadsheetId, $tabName);

echo "\n";
findReplace($service, $spreadsheetId, [
    'find'            => 'D2:D5',
    'replacement'     => 'D2:D4',
    'sheetId'         => $sheetId,
    'includeFormulas' => false,
], 'D2:D5 -> D2:D4, formulas off');

findReplace($service, $spreadsheetId, [
    'find'            => 'D2:D5',
    'replacement'     => 'D2:D4',
    'sheetId'         => $sheetId,
    'includeFormulas' => true,
], 'D2:D5 -> D2:D4, formulas on');

echo "\n";
dump($service, $spreadsheetId, $tabName, "After touching the formula:");