Skip to content

Instantly share code, notes, and snippets.

@printminion
Last active November 16, 2020 08:14
Show Gist options
  • Star 9 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save printminion/3898429 to your computer and use it in GitHub Desktop.
Save printminion/3898429 to your computer and use it in GitHub Desktop.
Parse Google Drive spreadsheet data via php
<?php
/**
* @desc Parse Google Drive spreadsheet data via php
* @author Misha M.-Kupriyanov https://plus.google.com/104512463398531242371/
* @link https://gist.github.com/3898429
*/
//Spreadsheet https://docs.google.com/spreadsheet/pub?key=0Akgh73WhU1qHdFg4UmRhaThfUFNBaFR3N3BMVW9uZmc&output=html
//JSON Representation
$url = 'https://spreadsheets.google.com/feeds/list/0Akgh73WhU1qHdFg4UmRhaThfUFNBaFR3N3BMVW9uZmc/od6/public/basic?prettyprint=true&alt=json';
$formatArr = array('column1', 'column2', 'column3');
$rows = GData::getSpreadsheetData($url, $formatArr);
print_r($rows);
class GData {
public static function getSpreadsheetData($url, $rowFormatArr) {
$content = file_get_contents($url);
$contentArr = json_decode($content, true);
$rows = array();
foreach($contentArr['feed']['entry'] as $row) {
if ($row['title']['$t'] == '-') {
continue;
}
$rowItems = array();
foreach($rowFormatArr as $item) {
$rowItems[$item] = self::getRowValue($row['content']['$t'], $rowFormatArr, $item);
}
$rows[] = $rowItems;
}
return $rows;
}
static function getRowValue($row, $rowFormatArr, $column_name) {
echo "getRowValue[$column_name]:$row";
if (empty($column_name)) {
throw new Exception('column_name must not empty');
}
$begin = strpos($row, $column_name);
echo "begin:$begin";
if ($begin == -1) {
return '';
}
$begin = $begin + strlen($column_name) + 1;
$end = -1;
$found_begin = false;
foreach($rowFormatArr as $entity) {
echo "checking:$entity";
if ($found_begin && strpos($row, $entity) != -1) {
$end = strpos($row, $entity) - 2;
echo "end1:$end";
break;
}
if ($entity == $column_name) {
$found_begin = true;
}
#check if last element
if (substr($row, strlen($row) - 1) == $column_name) {
$end = strlen($row);
} else {
if ($end == -1) {
$end = strlen($row);
} else {
$end = $end + 2;
}
}
}
echo "end:$end";
echo "$column_name:$row";
$value = substr($row, $begin, $end - $begin);
$value = trim($value);
echo "${column_name}[${begin}-${end}]:[$value]";
return $value;
}
}
@alemicro
Copy link

alemicro commented Jul 1, 2020

$content = file_get_contents($url); give error

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment