使用Google Sheets API写入工作表

Question

我想写数据到我的工作表：

$spreadsheetId = 'xxx';
$range = 'Test!A1:D5';

$values = [
  'range' => "A1",
  'majorDimension' => 'DIMENSION_UNSPECIFIED',
  'values' => [
    ["Item", "Cost", "Stocked", "Ship Date"],
    ["Wheel", "$20.50", "4", "3/1/2016"],
    ["Door", "$15", "2", "3/15/2016"],
    ["Engine", "$100", "1", "30/20/2016"],
  ],
];

$body = new Google_Service_Sheets_ValueRange(array(
  'values' => $values
));
$params = array(
  'valueInputOption' => 'RAW'
);
$result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);

但在日志中我看到：

PHP Fatal error:  Uncaught exception 'Google_Service_Exception' with message '{
  "error": {
    "code": 400,
    "message": "Invalid JSON payload received. Unknown name \"range\" at 'data.values': Cannot find field.\nInvalid JSON payload received. Unknown name \"major_dimension\" at 'data.values': Cannot find field.",
    "errors": [
      {
        "message": "Invalid JSON payload received. Unknown name \"range\" at 'data.values': Cannot find field.\nInvalid JSON payload received. Unknown name \"major_dimension\" at 'data.values': Cannot find field.",
        "domain": "global",
        "reason": "badRequest"
      }
    ],
    "status": "INVALID_ARGUMENT"
  }
}

我做错了什么？我将非常感谢为您提供的示例代码：）

在例子中没有足够的信息（https://developers.google.com/sheets/api/guides/values#writing_to_a_single_range）

Answer 1

这里的情况并非如此，但我通过从values数组中的项中删除NULL值，解决了类似的问题。

谷歌的PHP库似乎很难解析这些值，最终生成了一个无效的JSON有效载荷。

Answer 2

试试这个：

$spreadsheetId = 'xxx';
$range = 'Test!A1:D5';

$values = [
    ["Item", "Cost", "Stocked", "Ship Date"],
    ["Wheel", "$20.50", "4", "3/1/2016"],
    ["Door", "$15", "2", "3/15/2016"],
    ["Engine", "$100", "1", "30/20/2016"],
];

$updateBody = new Google_Service_Sheets_ValueRange([
    'range' => $range,
    'majorDimension' => 'ROWS',
    'values' => $values,
]);

$valueInputOption = 'USER_ENTERED'; // Or RAW       

$params = [
  'valueInputOption' => $valueInputOption
];

$result = $this->sheetsService->spreadsheets_values->update(
    $spreadsheetID,
    $updateRange,
    $updateBody,
    $params
); 

Answer 3

你快到了。不要忘记包括图纸名称

 "range": "Sheet1!A1:D5"

查看基本写作指南以了解更多示例。