88431

Setting PUT request body with PHP

Question:

I'm trying to use the Google Sheets API's spreadsheets.values.update (<a href="https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/update" rel="nofollow">https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/update</a>) 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 (<a href="https://github.com/php-curl-class/php-curl-class" rel="nofollow">https://github.com/php-curl-class/php-curl-class</a>) 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:

<blockquote>

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

</blockquote>

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

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

Answer1:

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 <a href="https://developers.google.com/sheets/reference/rest/v4/UpdateValuesResponse" rel="nofollow">this</a>. 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.

Answer2:

At first:

<ul><li>you've called $curl->setOpt(CURLOPT_POSTFIELDS, '{"values": [["Elizabeth", "2", "0.5", "60"]]}'); passing in a regular string.<br /> 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)</li> </ul>

Secondly:

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

Answer3:

OK, so I ran the parameters through Postman (<a href="https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop?hl=en" rel="nofollow">https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop?hl=en</a>) 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.

Recommend

  • AS3 error #1009
  • Error Generating Certificate SHA-1
  • CCSpriteFrame warning..AnchorPoint won't work as expected.Regenerate the .plist?
  • Not getting each error email alert from logstash 1.5.4
  • Access Google spreadsheet API without Oauth token
  • True coding convention about usings/try/catches
  • Using a Pipe to Write and Read Array of Int
  • How do i verify form input before displaying record [duplicate]
  • Android : Draw TextEdit(s) or TextView(s) when user enter number of them.[exp: 6 then draw 6 TextVie
  • How to sort list items by their priority in C?
  • Google Sheet API append to a specific sheet
  • How to avoid subset out of range when running a delete rows with variable
  • Excel now() shouldn't update existing timestamp
  • Office.js Add-in: Insert image/picture in Excel 2016
  • On Error Goto Line1 not working in VBA
  • Excel macro paste just values
  • Visual Basic, VBA array loop
  • What is Subqueries syntax for Microsoft Query?
  • How can I rewrite my PHP & MySQL to group my HTML list by equal column values?
  • Loop paste formula until next cell in range is empty
  • Renaming worksheets in Excel via export from Visual Studio 2010
  • How to update Sql table from excel directly?
  • Node.js decipher not works for other ciphers' ciphertext
  • Excel Marcro - delete a row based on row number [closed]
  • Pop up charts in VBA Excel
  • How to run a query between dates and times?
  • Tips for creating scalable WPF user control
  • How to calculate total Fridays between two dates in excel on weekly, biweekly and monthly basis?
  • How do I pass worksheet and ranges as variables?
  • OleDbDataAdapter not filling all of the rows
  • Excel distinct count with conditions
  • several dataProvider per one Test in TestNG
  • Initializer list vs. initialization method
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Date difference with leap year
  • VBA Convert delimiter text file to Excel
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Benchmarking RAM performance - UWP and C#
  • Angular 2 constructor injection vs direct access
  • IndexOutOfRangeException on multidimensional array despite using GetLength check