Monday, April 7, 2014

Parsing Google Visualization data with PHP

A project of mine queried another service that returned the data to me as a "Google Visualization" data. This is a data table, and while native to many languages, PHP isn't one of them.

function parseGV($gv) {
 // Return array
 $data = array();
 // Buh-Bye extra BS.
 $gv = preg_replace('/google.visualization.Query.setResponse\(/', '', $gv);
 $gv = preg_replace('/\)\;/', '', $gv);
 // Get table Headings
 preg_match('/"cols":\[{(.*)}\],"rows"/', $gv, $tables);
 $columns = array();
 $cols = explode('},{', $tables[1]);
 foreach ($cols as $col) {
  $items = explode(",", $col);
  foreach ($items as $item) {
   $item = preg_replace('/\"/', '', $item);
   $arr = explode(':', $item);
   if ($arr[0] == 'id') {
    array_push($columns, $arr[1]);
   }
  }   
 }
 // Get the rows
 preg_match('/"rows":\[{"c":(.*)\]/', $gv, $r);
 if (count($r)) {
  preg_match_all('/\[{(.*?)}\]}/', $r[1], $rows);
  for ($i = 0; $i < count($rows[1]); $i++) {
   $pairs = explode('},{', $rows[1][$i]);
   for ($p = 0; $p < count($pairs); $p++) {
    $pair = preg_replace('/\"/', '', $pairs[$p]);
    $s = preg_replace('/v:/', '', $pair);
    $data[$i][$columns[$p]] = $s;
   }
  }
 }
 return $data;
}

You use it like so:

$companies = parseGV(file_get_contents("https://rvacore-test.appspot.com/d/company/".$my_co_id."/companies?auth=".$auth_token, false, $context));

And you get back an array like:

(
    [0] => Array
        (
            [id] => 77db8797-7c01-4b87-84a9-XXXXXXXXXXX
            [name] => My Sub-Company
            [street] => 1313 Mockingbird Ln.
            [unit] => 
            [city] => Chester
            [province] => VA
            [country] => 
            [postalCode] => 23836
            [address] => 1313 Mockingbird Ln., Chester, VA, 23836
            [timeZone] => (GMT  00:00) Dublin, Edinburgh, Lisbon, London
            [telephone] => 
            [fax] => 
            [parentId] => 7ba8917f-b1d2-42f1-96fe-XXXXXXXXXXXXXXX
            [networkOperatorStatus] => 0.0
            [networkOperatorStatusChangeDate] => new Date(2014,1,7,20,52,55)
            [companyStatus] => 1.0
            [companyStatusChangeDate] => new Date(2014,1,7,20,52,55)
            [notificationEmails] => 
            [changeDate] => new Date(2014,1,7,20,52,57)
            [changedBy] => System
        )
    [1] => Array
        (
            [id] => 11a9e443-a1b9-45b8-bee2-XXXXXXXXXXXX
            [name] => Another Sub-Company
         ...
        )
    [2] => Array
        (
         ...
        )
    [3] => Array
        (
         ...
        )
)

Didn't see this elsewhere on the web, so here it is.