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('Filter Rows');
$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();
}

/** Ask the API which rows the filter is currently hiding. */
function hiddenRows(Sheets $service, string $spreadsheetId, string $range): array
{
    $meta = $service->spreadsheets->get($spreadsheetId, [
        'ranges'          => [$range],
        'includeGridData' => true,
        'fields'          => 'sheets(data(rowMetadata(hiddenByFilter)))',
    ]);

    $hidden = [];

    foreach ($meta->getSheets()[0]->getData()[0]->getRowMetadata() as $index => $row) {
        if ($row->getHiddenByFilter()) {
            $hidden[] = $index + 1;
        }
    }

    return $hidden;
}

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

$service->spreadsheets_values->update(
    $spreadsheetId,
    $tabName . '!A1',
    new ValueRange(['values' => [
        ['Client', 'Region', 'Status', 'Total'],
        ['Acme', 'North', 'paid', 1200],
        ['Bravo', 'South', 'pending', 340],
        ['Cortex', 'North', 'paid', 980],
        ['Delta', 'East', 'pending', 2150],
        ['Echo', 'South', 'paid', 275],
        ['Foxtrot', 'North', 'cancelled', 640],
    ]]),
    ['valueInputOption' => 'USER_ENTERED']
);

$dataRange = $tabName . '!A1:D7';
$gridRange = [
    'sheetId'          => $sheetId,
    'startRowIndex'    => 0,
    'endRowIndex'      => 7,
    'startColumnIndex' => 0,
    'endColumnIndex'   => 4,
];

// ----------------------------------------------------------- basic filter
// Column index 3 is Total. Criteria are keyed by column index, and the key is
// absolute - the fourth column of the sheet, not the fourth column of the range.
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['setBasicFilter' => ['filter' => [
            'range'    => $gridRange,
            'criteria' => [
                3 => ['condition' => [
                    'type'   => 'NUMBER_GREATER',
                    'values' => [['userEnteredValue' => '500']],
                ]],
            ],
        ]]]),
    ],
]));

echo "Basic filter set: Total > 500\n\n";

$rows = $service->spreadsheets_values->get($spreadsheetId, $dataRange)->getValues() ?? [];

printf("values.get still returns %d rows:\n", count($rows));
foreach ($rows as $index => $row) {
    printf("  row %-2d %-9s %-7s %-10s %s\n", $index + 1, $row[0], $row[1], $row[2], $row[3]);
}

$hidden = hiddenRows($service, $spreadsheetId, $dataRange);

printf("\nRows the filter is hiding in the browser: %s\n", implode(', ', $hidden) ?: 'none');

// -------------------------------------------------- hiddenValues criteria
// The other way to write criteria: name the values to hide, not a condition.
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['setBasicFilter' => ['filter' => [
            'range'    => $gridRange,
            'criteria' => [
                2 => ['hiddenValues' => ['cancelled', 'pending']],
            ],
        ]]]),
    ],
]));

$hidden = hiddenRows($service, $spreadsheetId, $dataRange);

printf("\nAfter replacing it with hiddenValues on Status: rows %s hidden\n", implode(', ', $hidden) ?: 'none');

// ------------------------------------------------------------ filter view
$response = $service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['addFilterView' => ['filter' => [
            'title'     => 'Big northern orders',
            'range'     => $gridRange,
            'sortSpecs' => [
                ['dimensionIndex' => 3, 'sortOrder' => 'DESCENDING'],
            ],
            'criteria'  => [
                1 => ['condition' => [
                    'type'   => 'TEXT_EQ',
                    'values' => [['userEnteredValue' => 'North']],
                ]],
            ],
        ]]]),
    ],
]));

$view = $response->getReplies()[0]->getAddFilterView()->getFilter();

printf("\nFilter view created: '%s'\n", $view->getTitle());
printf("  filterViewId : %d\n", $view->getFilterViewId());
printf("  open it at   : .../edit#gid=%d&fvid=%d\n", $sheetId, $view->getFilterViewId());

// The view sorts. The stored rows do not move.
$rows = $service->spreadsheets_values->get($spreadsheetId, $dataRange)->getValues() ?? [];

printf("\nStored order after the filter view sorted by Total descending:\n");
foreach ($rows as $index => $row) {
    printf("  row %-2d %-9s %-7s %-10s %s\n", $index + 1, $row[0], $row[1], $row[2], $row[3]);
}

// ------------------------------------------------- one basic filter, one sheet
$meta = $service->spreadsheets->get($spreadsheetId, [
    'fields' => 'sheets(properties(sheetId),basicFilter,filterViews(filterViewId,title))',
]);

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

    printf("\nbasicFilter on this sheet : %s\n", $sheet->getBasicFilter() ? 'one' : 'none');
    printf("filterViews on this sheet : %d\n", count($sheet->getFilterViews() ?? []));

    foreach ($sheet->getFilterViews() ?? [] as $filterView) {
        printf("  %d  %s\n", $filterView->getFilterViewId(), $filterView->getTitle());
    }
}

// ------------------------------------------------------------ clear it again
$service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
    'requests' => [
        new Request(['clearBasicFilter' => ['sheetId' => $sheetId]]),
    ],
]));

$hidden = hiddenRows($service, $spreadsheetId, $dataRange);

printf("\nAfter clearBasicFilter: rows hidden = %s\n", implode(', ', $hidden) ?: 'none');