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.
Latest complete 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()); } }
The full script from Insert Rows And Columns In A Google Sheet Using Google Sheets API PHP Client — copy, run, adapt.
Key methods
IOFactory::load()
PhpSpreadsheet
getActiveSheet()
PhpSpreadsheet
fromArray()
PhpSpreadsheet
getCalculatedValue()
PhpSpreadsheet
save('php://output')
PhpSpreadsheet
spreadsheets_values->get()
Google Sheets
spreadsheets_values->update()
Google Sheets
spreadsheets->create()
Google Sheets
json_encode()
PHP
header()
PHP
Latest articles
Insert Rows And Columns In A Google Sheet Using Google Sheets API PHP Client
August 17, 2026
Add Conditional Formatting In A Google Sheet Using Google Sheets API PHP Client
August 16, 2026
Freeze Rows And Columns In A Google Sheet Using Google Sheets API PHP Client
August 16, 2026
Translate Excel Formulas Into Another Language In PHP Using PHPSpreadSheet
August 16, 2026
Format Google Sheets Cells Using Google Sheets API PHP Client
August 15, 2026
Write Reusable Formulas With The LAMBDA Function And LET
August 15, 2026
Replace Fill Down With The ARRAYFORMULA Function In Google Sheets
August 15, 2026
Pull Live Data Into Google Sheets With The IMPORT Functions
August 14, 2026
Replace VLOOKUP With The XLOOKUP Function In Google Sheets
August 14, 2026
Browse by technique