Setting PUT request body with PHP

Question

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.

score 1 · Answer 1 · answered Jun 25 '16 at 15:46

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.

answered Jun 25 '16 at 15:46

Charlie Fish

18,491
19
86
179

Hi there, thanks for the comment. Yes, authentication is successful, and I am able to read data with no problems from the same spreadsheet. The migration docs here (https://developers.google.com/sheets/guides/migration#add_a_new_row_of_data) say "The range specified need only refer to the first cell in the row; the API infers the cells to update based on the values provided with the request." so I don't think that should be a problem.. – praine Jun 25 '16 at 16:02

score 1 · Answer 2 · answered Jun 25 '16 at 16:03

1

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

answered Jun 25 '16 at 16:03

RomanPerekhrest

88,541
4
65
105

Hi, thanks for your help. The API requires a single query parameter to be set, "valueInputOption", which I am trying to set using the second argument in the php-curl-class function. However, it also seems to require a JSON encoded string in the BODY of the PUT request.. I'm not sure how to include both of these elements in my request.. – praine Jun 25 '16 at 16:27
if it "require a JSON encoded string in the BODY of the PUT request." have you tried to set the array `array('valueInputOption' => '{"values": [["Elizabeth", "2", "0.5", "60"]]}' ))` as a second argument to `put` function? – RomanPerekhrest Jun 25 '16 at 16:42
"valueInputOption" has to be one of the following: "RAW" or "USER_ENTERED" (as per: https://developers.google.com/sheets/reference/rest/v4/ValueInputOption) – praine Jun 25 '16 at 16:46
I've changed my PUT request to the following: `$curl->put('https://sheets.googleapis.com/v4/spreadsheets/11I1xNv8cHzBGE7uuZtB9fQzbgrz4z7lIaEADfta60nc/values/Sheet1!A1?valueInputOption=USER_ENTERED','{"values": [["Elizabeth", "2", "0.5", "60"]]}');` - the cURL request executes successfully, but the spreadsheet data still isn't updating :-/ – praine Jun 25 '16 at 16:47
1

also, try to set additional header `CURLOPT_HTTPHEADER` specifying content-type as `json` – RomanPerekhrest Jun 25 '16 at 17:11
That did the trick! Thank you very much for all your help and suggestions! – praine Jun 25 '16 at 17:36

score 0 · Accepted Answer · answered Jun 25 '16 at 17:35

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.

Setting PUT request body with PHP

3 Answers3

Linked