Google Sheets PHP API – 添加多个选项卡时出现问题

huangapple
go评论127阅读模式
英文:

Google Sheets PHP API - Trouble with adding multiple tabs

问题

我遇到了一个问题，无法让Google Sheets的PHP库允许我创建具有多个标签（工作表）的电子表格。以下是一些相关的代码部分：

在尝试将多个标签（工作表）添加到电子表格时，我收到了以下错误消息：

  1. 收到无效的JSON有效负载。在'requests[0]'处找不到字段“properties”。
  2. 收到无效的JSON有效负载。在'requests[1]'处找不到字段“properties”。
  3. 收到无效的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

  1. use Google\Service\Exception as ServiceException;
  2. use Google\Service\Sheets;
  3. use Google\Service\Sheets\AddSheetRequest;
  4. use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
  5. use Google\Service\Sheets\BatchUpdateValuesRequest;
  6. use Google\Service\Sheets\SheetProperties;
  7. use Google\Service\Sheets\Spreadsheet;
  8.     
  9. function addTabs($sheetId, array $tabNames) {
  10.     $requests = [];
  12.     foreach ($tabNames as $index => $tabName) {
  13.         $request = new AddSheetRequest();
  14.         $props = new SheetProperties();
  15.         $props->setTitle($tabName);
  16.         $props->setIndex($index);
  17.         $request->setProperties($props);
  18.         $requests[] = $request;
  19.     }
  21.     $batchRequest = new BatchUpdateSpreadsheetRequest();
  22.     $batchRequest->setRequests($requests);
  24.     try {
  25.         $this->service->spreadsheets->batchUpdate($sheetId, $batchRequest);
  26.     } catch (ServiceException $e) {
  27.         dd($e);
  28.     }
  29. }
  31. addTabs($id, ['A', 'B', 'C']);

Getting these errors.

  1.     Invalid JSON payload received. Unknown name "properties" at 'requests[0]': Cannot find field.
  2.     Invalid JSON payload received. Unknown name "properties" at 'requests[1]': Cannot find field.
  3.     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:

  1. function createSheet()
  2. {
  3.     $sheetProps = [
  4.         'properties' => [
  5.             'title' => 'Test',
  6.         ],
  7.         'sheets' => [],
  8.     ];
  9.     $sheetRequest = new Spreadsheet($sheetProps);
  10.     $sheet1 = [
  11.         'properties' => [
  12.             'title' => 'Tab 1',
  13.         ],
  14.         'data' => [
  15.             'rowData' => [
  16.                 ['values' => [['userEnteredValue' => ['numberValue' => 1]], ['userEnteredValue' => ['numberValue' => 2]], ['userEnteredValue' => ['numberValue' => 3]]]],
  17.                 ['values' => [['userEnteredValue' => ['numberValue' => 4]], ['userEnteredValue' => ['numberValue' => 5]], ['userEnteredValue' => ['numberValue' => 6]]]],
  18.             ],
  19.         ],
  20.     ];
  21.     $sheet2 = [
  22.         'properties' => [
  23.             'title' => 'Tab 2',
  24.         ],
  25.         'data' => [
  26.             'rowData' => [
  27.                 ['values' => [['userEnteredValue' => ['numberValue' => 1]], ['userEnteredValue' => ['numberValue' => 2]], ['userEnteredValue' => ['numberValue' => 3]]]],
  28.                 ['values' => [['userEnteredValue' => ['numberValue' => 4]], ['userEnteredValue' => ['numberValue' => 5]], ['userEnteredValue' => ['numberValue' => 6]]]],
  29.             ],
  30.         ],
  31.     ];
  32.     $sheetRequest = new Spreadsheet([
  33.         'properties' => [
  34.             'title' => 'Testing',
  35.         ],
  36.         'sheets' => [$sheet1, $sheet2],
  37.     ]);
  39.     return $this->service->spreadsheets->create($sheetRequest);
  40. }

Error:

  1. 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.

  1.  '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 属性没有包含在内。我认为这可能是你当前问题的原因。当这个修改反映在你的第一个脚本中时，以下修改如何？

修改后的脚本：

  1. function addTabs($sheetId, array $tabNames) {
  2.     $requests = [];
  3.     foreach ($tabNames as $index => $tabName) {
  4.         $addSheet = new \Google\Service\Sheets\Request(); // 添加
  5.         $request = new AddSheetRequest();
  6.         $props = new SheetProperties();
  7.         $props->setTitle($tabName);
  8.         $props->setIndex($index);
  9.         $request->setProperties($props);
  10.         $addSheet->setAddSheet($request); // 添加
  11.         array_push($requests, $addSheet); // 添加
  12.     }
  13.     $batchRequest = new BatchUpdateSpreadsheetRequest();
  14.     $batchRequest->setRequests($requests);
  15.     try {
  16.         $this->service->spreadsheets->batchUpdate($sheetId, $batchRequest);
  17.     } catch (ServiceException $e) {
  18.         dd($e);
  19.     }
  20. }

  • 通过这个修改，requests 如下所示。并且，将 "A"、"B" 和 "C" 这三个工作表插入到电子表格中。

    1. [
    2.   {"addSheet":{"properties":{"hidden":null,"index":0,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"A"}}},
    3.   {"addSheet":{"properties":{"hidden":null,"index":1,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"B"}}},
    4.   {"addSheet":{"properties":{"hidden":null,"index":2,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"C"}}}
    5. ]

参考链接：

英文:

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:

  1. function addTabs($sheetId, array $tabNames) {
  2.     $requests = [];
  3.     foreach ($tabNames as $index => $tabName) {
  4.         $addSheet = new \Google\Service\Sheets\Request(); // Added
  5.         $request = new AddSheetRequest();
  6.         $props = new SheetProperties();
  7.         $props->setTitle($tabName);
  8.         $props->setIndex($index);
  9.         $request->setProperties($props);
  10.         $addSheet->setAddSheet($request); // Added
  11.         array_push($requests, $addSheet); // Added
  12.     }
  13.     $batchRequest = new BatchUpdateSpreadsheetRequest();
  14.     $batchRequest->setRequests($requests);
  15.     try {
  16.         $this->service->spreadsheets->batchUpdate($sheetId, $batchRequest);
  17.     } catch (ServiceException $e) {
  18.         dd($e);
  19.     }
  20. }

  • By this modification, requests is as follows. And, 3 sheets of "A", "B" and "C" are inserted to the Spreadsheet.

    1. [
    2.   {"addSheet":{"properties":{"hidden":null,"index":0,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"A"}}},
    3.   {"addSheet":{"properties":{"hidden":null,"index":1,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"B"}}},
    4.   {"addSheet":{"properties":{"hidden":null,"index":2,"rightToLeft":null,"sheetId":null,"sheetType":null,"title":"C"}}}]
    5. ]

Reference:

huangapple
  • 本文由 发表于 2023年7月7日 06:53:42
  • 转载请务必保留本文链接：https://go.coder-hub.com/76632972.html
  • google-sheets
  • google-sheets-api
  • php
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定