英文:
Google Sheets PHP API - Trouble with adding multiple tabs
问题
我遇到了一个问题,无法让Google Sheets的PHP库允许我创建具有多个标签(工作表)的电子表格。以下是一些相关的代码部分:
在尝试将多个标签(工作表)添加到电子表格时,我收到了以下错误消息:
- 收到无效的JSON有效负载。在'requests[0]'处找不到字段“properties”。
 - 收到无效的JSON有效负载。在'requests[1]'处找不到字段“properties”。
 - 收到无效的JSON有效负载。在'requests[2]'处找不到字段“properties”。
 
我很难确定问题出在哪里,因为这似乎与文档中定义的规范相匹配。我还尝试了在初始电子表格创建请求中一次生成所有标签(工作表):
但是,我收到以下错误消息:
收到无效的JSON有效负载。在'spreadsheet.sheets[0].data.row_data'处找不到字段“0”。
如果我改为只使用以下行而不是数组,它可以正常工作:
但是规范清楚地显示了Spreadsheet.sheets是一个数组。
在这里我是否遗漏了什么?是否有人有一个使用PHP Sheets API创建多标签电子表格的有效示例?
请注意,我只翻译了您的问题内容,没有提供回答。
英文:
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?
答案1
得分: 1
当我看到你的展示脚本(顶部脚本)时,addSheet 属性没有包含在内。我认为这可能是你当前问题的原因。当这个修改反映在你的第一个脚本中时,以下修改如何?
修改后的脚本:
function addTabs($sheetId, array $tabNames) {
    $requests = [];
    foreach ($tabNames as $index => $tabName) {
        $addSheet = new \Google\Service\Sheets\Request(); // 添加
        $request = new AddSheetRequest();
        $props = new SheetProperties();
        $props->setTitle($tabName);
        $props->setIndex($index);
        $request->setProperties($props);
        $addSheet->setAddSheet($request); // 添加
        array_push($requests, $addSheet); // 添加
    }
    $batchRequest = new BatchUpdateSpreadsheetRequest();
    $batchRequest->setRequests($requests);
    try {
        $this->service->spreadsheets->batchUpdate($sheetId, $batchRequest);
    } catch (ServiceException $e) {
        dd($e);
    }
}
- 
通过这个修改,
requests如下所示。并且,将 "A"、"B" 和 "C" 这三个工作表插入到电子表格中。[ {"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"}}} ] 
参考链接:
英文:
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?
Modified script:
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,
requestsis 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"}}}] ] 
Reference:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论