如何在PHP中使用Google Sheets API在一系列单元格上设置数据验证


How do you set data validation on a range of cells with the Google Sheets API in PHP?

我有以下代码:

$requests = '{
  "requests": [
    {
      "setDataValidation": {
        "range": {
          "sheetId": mySheetID,
          "startRowIndex": 0,
          "endRowIndex": 10,
          "startColumnIndex": 0,
          "endColumnIndex": 6
        },
        "rule": {
          "condition": {
            "type": "NUMBER_GREATER",
            "values": [
              {
                "userEnteredValue": "5"
              }
            ]
          },
          "inputMessage": "Value must be > 5",
          "strict": true
        }
      }
    }
  ]
}';
// $client is a confirmed, working authorized Google_Client object.
$serviceSheets      = new 'Google_Service_Sheets($client);
// NOTE This is not the same object as in 'App'GoogleSheets
$batchUpdateRequest = new 'Google_Service_Sheets_BatchUpdateSpreadsheetRequest;
$batchUpdateRequest->setRequests($requests);
$serviceSheets->spreadsheets->batchUpdate(
    // $id is correct.
    $id,
    $batchUpdateRequest
);
,我得到以下错误:
{
  "error": {
    "code": 400,
    "message": "Invalid value at 'requests' (type.googleapis.com/google.apps.sheets.v4.BatchUpdateSpreadsheetRequest.Request), '"{'n '"requests'": ['n {'n '"setDataValidation'": {'n '"range'": {'n '"sheetId'": '"1100288254'",'n '"startRowIndex'": 0,'n '"endRowIndex'": 10,'n '"startColumnIndex'": 0,'n '"endColumnIndex'": 6'n },'n '"rule'": {'n '"condition'": {'n '"type'": '"NUMBER_GREATER'",'n '"values'": ['n {'n '"userEnteredValue'": '"5'"'n }'n ]'n },'n '"inputMessage'": '"Value must be > 5'",'n '"strict'": true'n }'n }'n }'n ]'n}'"",
    "errors": [
      {
        "message": "Invalid value at 'requests' (type.googleapis.com/google.apps.sheets.v4.BatchUpdateSpreadsheetRequest.Request), '"{'n '"requests'": ['n {'n '"setDataValidation'": {'n '"range'": {'n '"sheetId'": '"1100288254'",'n '"startRowIndex'": 0,'n '"endRowIndex'": 10,'n '"startColumnIndex'": 0,'n '"endColumnIndex'": 6'n },'n '"rule'": {'n '"condition'": {'n '"type'": '"NUMBER_GREATER'",'n '"values'": ['n {'n '"userEnteredValue'": '"5'"'n }'n ]'n },'n '"inputMessage'": '"Value must be > 5'",'n '"strict'": true'n }'n }'n }'n ]'n}'"",
        "domain": "global",
        "reason": "badRequest"
      }
    ],
    "status": "INVALID_ARGUMENT"
  }
}

我试着传递$requests作为一个数组,但它说它需要JSON,所以这不是问题。

我也试过传递requests键(对象数组)和单个setDataValidation请求(对象)的值。

请求本身是从Google的文档中复制过来的

无效JSON:

     "sheetId": mySheetIdConfirmedCorrect,
                              ^---- unquoted string

不要构建自己的JSON。构建一个普通的PHP数组结构,然后json_encode()它。

JSON也不能包含"代码",所以如果mySheet...实际上是一个变量,那又是无效的JSON。你不能传递除数据以外的任何东西——表达式、代码等等。