Setting PUT request body with PHP

问题:

I'm trying to use the Google Sheets API's spreadsheets.values.update (https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/update) method to insert data into a spreadsheet.

I've already acquired a correctly scoped access token, and now I'm trying to send a PUT request via cURL. I'm using the PHP curl wrapper (https://github.com/php-curl-class/php-curl-class) but plain PHP would also be great.

Here's what I have so far:

$curl = new Curl();
    $curl->setHeader("Authorization","Bearer ".json_decode($a4e->user->google_token)->access_token);
    $curl->setOpt(CURLOPT_POSTFIELDS, '{"values": [["Elizabeth", "2", "0.5", "60"]]}');
    $curl->put('https://sheets.googleapis.com/v4/spreadsheets/11I1xNv8cHzBGE7uuZtB9fQzbgrz4z7lIaEADfta60nc/values/Sheet1!A1', array(
        'valueInputOption' => 'USER_ENTERED'    
    ));

    if ($curl->error) {
        echo 'Error: ' . $curl->errorCode . ': ' . $curl->errorMessage;
    }
    else {
        echo json_encode($curl->response);
    }

The cURL request executes successfully, and returns the following:

{"spreadsheetId":"11I1xNv8cHzBGE7uuZtB9fQzbgrz4z7lIaEADfta60nc","updatedRange":"Sheet1!A1"}

However, it does not update the data in the sheet.

Does anyone know what I am doing wrong? Many thanks in advance.

回答1:

I take it you are successfully authenticated using OAuth? I would look into your put URL. You are saying "Sheet1!A1" yet you are entering data that looks like it would span across multiple cells. So I would try adjusting "Sheet1!A1" to include an actual rage of cells since you have many pieces of data you are importing. Normally the response should look similar to this. Because it doesn't have any of that last information in your response it looks like the cells you are specifying to write to aren't correct.

回答2:

At first:

• you've called $curl->setOpt(CURLOPT_POSTFIELDS, '{"values": [["Elizabeth", "2", "0.5", "60"]]}'); passing in a regular string.
  But curl CURLOPT_POSTFIELDS option accepts it's value to be urlencoded string like 'para1=val1&para2=val2&...' or an associative array of data(key/value pairs)

Secondly:

• the put function from that library(php-curl-class) function put($url, $data = array()) ... considers its second argument as post data.
  As you passed in an array of data when calling put method - you've overwrote previous post data set by $curl->setOpt(CURLOPT_POSTFIELDS, ....

回答3:

OK, so I ran the parameters through Postman (https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop?hl=en) and exported the following code:

$curl = curl_init();

curl_setopt_array($curl, array(
  CURLOPT_URL => "https://sheets.googleapis.com/v4/spreadsheets/11I1xNv8cHzBGE7uuZtB9fQzbgrz4z7lIaEADfta60nc/values/Sheet1!A1?valueInputOption=USER_ENTERED",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "PUT",
  CURLOPT_POSTFIELDS => "{\"range\":\"Sheet1!A1\",\"majorDimension\":\"ROWS\",\"values\":[[\"x\",\"x\",\"x\",\"x\",\"x\"]]}",
  CURLOPT_HTTPHEADER => array(
    "authorization: Bearer {token}",
    "cache-control: no-cache",
    "content-type: application/json",
  ),
));

$response = curl_exec($curl);
$err = curl_error($curl);

curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

And it finally works! The content-type: application/json is absolutely essential, as per RomanPerekhrest's suggestion. The request doesn't update anything without it.