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;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';
$keyFile = 'service-account.json';
$excelFile = 'orders.xlsx';
$tab = 'Sheet1';

$client = new Client();
$client->setApplicationName('Import Excel Into Google Sheets');
$client->setAuthConfig($keyFile);
$client->addScope(Sheets::SPREADSHEETS);

$service = new Sheets($client);

/**
 * Read the workbook into rows of display strings.
 *
 * The '' is not decoration: a blank cell would otherwise come back as null,
 * and the client drops nulls, which turns the row into a JSON object and
 * fails the whole request.
 */
function readWorkbook(string $file): array
{
    $reader = IOFactory::createReader('Xlsx');
    $sheet = $reader->load($file)->getActiveSheet();

    return $sheet->toArray('', true, true, false);
}

/**
 * Put an apostrophe in front of values Google would otherwise reinterpret.
 */
function keepAsText(array $rows, array $columns): array
{
    foreach ($rows as $r => $row) {
        if ($r === 0) {
            continue;   // leave the header alone
        }
        foreach ($columns as $c) {
            if (isset($row[$c]) && $row[$c] !== '') {
                $rows[$r][$c] = "'" . $row[$c];
            }
        }
    }

    return $rows;
}

/**
 * Empty the tab, formatting included.
 *
 * values->clear() only removes values, so number formats from a previous
 * import survive and quietly restyle whatever lands next.
 */
function emptyTab(Sheets $service, string $spreadsheetId, string $tab): void
{
    $sheetId = null;
    foreach ($service->spreadsheets->get($spreadsheetId)->getSheets() as $sheet) {
        if ($sheet->getProperties()->getTitle() === $tab) {
            $sheetId = $sheet->getProperties()->getSheetId();
        }
    }

    $service->spreadsheets->batchUpdate($spreadsheetId, new BatchUpdateSpreadsheetRequest([
        'requests' => [
            new Request(['updateCells' => [
                'range'  => ['sheetId' => $sheetId],
                'fields' => 'userEnteredValue,userEnteredFormat',
            ]]),
        ],
    ]));
}

/**
 * Send the rows and report what the API says it wrote.
 */
function push(Sheets $service, string $spreadsheetId, string $tab, array $rows): void
{
    $response = $service->spreadsheets_values->update(
        $spreadsheetId,
        $tab . '!A1',
        new ValueRange(['values' => $rows]),
        ['valueInputOption' => 'USER_ENTERED']
    );

    printf("  wrote %d cells over %d row(s) into %s\n",
        $response->getUpdatedCells(), $response->getUpdatedRows(), $response->getUpdatedRange());
}

/**
 * Show how Google actually stored a few cells, next to how it displays them.
 */
function inspect(Sheets $service, string $spreadsheetId, string $tab, array $cells): void
{
    foreach ($cells as $label => $ref) {
        $stored = $service->spreadsheets_values
            ->get($spreadsheetId, "$tab!$ref", ['valueRenderOption' => 'UNFORMATTED_VALUE'])
            ->getValues();
        $shown = $service->spreadsheets_values
            ->get($spreadsheetId, "$tab!$ref", ['valueRenderOption' => 'FORMATTED_VALUE'])
            ->getValues();

        printf("    %-9s stored %-12s as %-7s shown as %s\n",
            $label,
            var_export($stored[0][0] ?? null, true),
            gettype($stored[0][0] ?? null),
            var_export($shown[0][0] ?? null, true));
    }
}

$watch = ['Ref' => 'A2', 'Ordered' => 'C2', 'Qty' => 'D2', 'Discount' => 'G2'];

$rows = readWorkbook($excelFile);
echo "Read " . count($rows) . " rows from $excelFile.\n";
echo "Row 2 as PhpSpreadsheet hands it over:\n  " . json_encode($rows[1]) . "\n\n";

echo "First attempt, pushing those rows straight in:\n";
emptyTab($service, $spreadsheetId, $tab);
push($service, $spreadsheetId, $tab, $rows);
inspect($service, $spreadsheetId, $tab, $watch);

// Column A holds reference codes like 007. Left alone, Google reads them as
// numbers and the leading zeros are gone for good.
$protected = keepAsText($rows, [0]);

echo "\nSecond attempt, with column A protected:\n";
echo "  Row 2 now starts with " . json_encode($protected[1][0]) . "\n";
emptyTab($service, $spreadsheetId, $tab);
push($service, $spreadsheetId, $tab, $protected);
inspect($service, $spreadsheetId, $tab, $watch);