unable to get json from google sheet using curl in php

Question

i tried to get json from my google sheet using curl in php. however data is not displayed.

this is my code:

$feed = "https://docs.google.com/spreadsheets/d/1h2fIi74s-1fypmZWkOGSwJh1ih9pfcOwQ81KTTiAfIU/edit#gid=2053646480";

$curl = curl_init($feed);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0);
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);

$curl_response = curl_exec($curl);

curl_close($curl);

$curl_json = json_decode($curl_response, true);
print_r($curl_json);

before i had done it with fopen (got code frm some forum) now it has been disabled though url_fopen is enabled in php.ini file and looking for an alternative as i want the same result as before.

  <?php
 //header('Content-type: application/json');

// Set your CSV feed
$feed = 'https://docs.google.com/spreadsheets/d/1sIq64_3lg9mNxidpClqdjZCZgOPbh8etCRml2cYHXeg/export?format=csv&id=1sIq64_3lg9mNxidpClqdjZCZgOPbh8etCRml2cYHXeg&gid=1790778854';

 // Arrays we'll use later
 $keys = array();
 $newArray = array();

 // Function to convert CSV into associative array
 function csvToArray($file, $delimiter) { 
 if (($handle = fopen($file, 'r')) !== FALSE) { 
 $i = 0; 
 while (($lineArray = fgetcsv($handle, 4000, $delimiter, '"')) !== FALSE) { 
  for ($j = 0; $j < count($lineArray); $j++) { 
    $arr[$i][$j] = $lineArray[$j]; 
  } 
  $i++; 
} 
fclose($handle); 
} 
return $arr; 
} 

// Do it
$data = csvToArray($feed, ',');

// Set number of elements (minus 1 because we shift off the first row)
$count = count($data) - 1;

//Use first row for names  
$labels = array_shift($data);  

foreach ($labels as $label) {
$keys[] = $label;
}

// Add Ids, just in case we want them later
/*$keys[] = 'id';

 for ($i = 0; $i < $count; $i++) {
 $data[$i][] = $i;
 }
 */
 // Bring it all together
 for ($j = 0; $j < $count; $j++) {
 $d = array_combine($keys, $data[$j]);
 $newArray[$j] = $d;
 }

 // Print it out as JSON
 echo json_encode($newArray);

 ?>

First print this $curl_response and check if the response is in json. — Ravinder Reddy, Jun 30 '17 at 14:24
this url does not return json, but some html with embedded js. — YvesLeBorg, Jun 30 '17 at 14:40

Pascal Meunier · Answer 1 · 2017-07-01T05:17:04.337

0

You can use this URL to get JSON data from a Google Spreadsheet.

https://spreadsheets.google.com/feeds/list/1h2fIi74s-1fypmZWkOGSwJh1ih9pfcOwQ81KTTiAfIU/od6/public/full?alt=json

Please note that this API is deprecated, you should use the new Google Data API. Here is more information: https://developers.google.com/gdata/samples/spreadsheet_sample

edited Jul 01 '17 at 05:17

answered Jun 30 '17 at 20:12

Pascal Meunier

685
6
15

sorry this doesn't work, i want to implement this in php using curl. any help? – Sabin Pdel Jul 01 '17 at 02:53
You can follow this to get the correct worksheet, it works for me. https://stackoverflow.com/questions/24531351/retrieve-google-spreadsheet-worksheet-json – Pascal Meunier Jul 01 '17 at 05:16
i was using following code before to get the desired result. – Sabin Pdel Jul 01 '17 at 05:20

unable to get json from google sheet using curl in php

1 Answers1