I'm having a heck of a time getting the PHP library for Google Sheets to let me create a Spreadsheet with multiple tabs (sheets). Snippet truncated for simplicity
use Google\Service\Exception as ServiceException;
use Google\Service\Sheets;
use Google\Service\Sheets\AddSheetRequest;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
use Google\Service\Sheets\BatchUpdateValuesRequest;
use Google\Service\Sheets\SheetProperties;
use Google\Service\Sheets\Spreadsheet;
function addTabs($sheetId, array $tabNames) {
$requests = [];
foreach ($tabNames as $index => $tabName) {
$request = new AddSheetRequest();
$props = new SheetProperties();
$props->setTitle($tabName);
$props->setIndex($index);
$request->setProperties($props);
$requests[] = $request;
}
$batchRequest = new BatchUpdateSpreadsheetRequest();
$batchRequest->setRequests($requests);
try {
$this->service->spreadsheets->batchUpdate($sheetId, $batchRequest);
} catch (ServiceException $e) {
dd($e);
}
}
addTabs($id, ['A', 'B', 'C']);
Getting these errors.
Invalid JSON payload received. Unknown name "properties" at 'requests[0]': Cannot find field.
Invalid JSON payload received. Unknown name "properties" at 'requests[1]': Cannot find field.
Invalid JSON payload received. Unknown name "properties" at 'requests[2]': Cannot find field.
I can't tell what's wrong here, as this seems to match the spec defined in the documentation https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#AddSheetRequest
Another tack I've tried is generating all tabs at once in the initial Spreadsheet creation request:
function createSheet()
{
$sheetProps = [
'properties' => [
'title' => 'Test',
],
'sheets' => [],
];
$sheetRequest = new Spreadsheet($sheetProps);
$sheet1 = [
'properties' => [
'title' => 'Tab 1',
],
'data' => [
'rowData' => [
['values' => [['userEnteredValue' => ['numberValue' => 1]], ['userEnteredValue' => ['numberValue' => 2]], ['userEnteredValue' => ['numberValue' => 3]]]],
['values' => [['userEnteredValue' => ['numberValue' => 4]], ['userEnteredValue' => ['numberValue' => 5]], ['userEnteredValue' => ['numberValue' => 6]]]],
],
],
];
$sheet2 = [
'properties' => [
'title' => 'Tab 2',
],
'data' => [
'rowData' => [
['values' => [['userEnteredValue' => ['numberValue' => 1]], ['userEnteredValue' => ['numberValue' => 2]], ['userEnteredValue' => ['numberValue' => 3]]]],
['values' => [['userEnteredValue' => ['numberValue' => 4]], ['userEnteredValue' => ['numberValue' => 5]], ['userEnteredValue' => ['numberValue' => 6]]]],
],
],
];
$sheetRequest = new Spreadsheet([
'properties' => [
'title' => 'Testing',
],
'sheets' => [$sheet1, $sheet2],
]);
return $this->service->spreadsheets->create($sheetRequest);
}
Error:
Invalid JSON payload received. Unknown name \"0\" at 'spreadsheet.sheets[0].data.row_data': Cannot find field.
If I instead change the above line to just the following instead of an array it works.
'sheets' => $sheet1
But the spec clearly shows Spreadsheet.sheets as an array: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#Spreadsheet
What am I missing here? Does anyone have a good working example of creating a multi-tab spreadsheet using the PHP Sheets API?
When I saw your showing script (top script), the property of addSheet
is not included. I thought that this might be the reason for your current issue. When this is reflected in your 1st script, how about the following modification?
function addTabs($sheetId, array $tabNames) {
$requests = [];
foreach ($tabNames as $index => $tabName) {
$addSheet = new \Google\Service\Sheets\Request(); // Added
$request = new AddSheetRequest();
$props = new SheetProperties();
$props->setTitle($tabName);
$props->setIndex($index);
$request->setProperties($props);
$addSheet->setAddSheet($request); // Added
array_push($requests, $addSheet); // Added
}
$batchRequest = new BatchUpdateSpreadsheetRequest();
$batchRequest->setRequests($requests);
try {
$this->service->spreadsheets->batchUpdate($sheetId, $batchRequest);
} catch (ServiceException $e) {
dd($e);
}
}
By this modification, requests
is as follows. And, 3 sheets of "A", "B" and "C" are inserted to the Spreadsheet.
[
{"addSheet":{"properties":{"hidden":null,"index":0,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"A"}}},
{"addSheet":{"properties":{"hidden":null,"index":1,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"B"}}},
{"addSheet":{"properties":{"hidden":null,"index":2,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"C"}}}]
]