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.

6 comments:

  1. Great post, very usefull. Can I share this for my blog?

    ReplyDelete
  2. Thank you I am looking for this code for last 3 days :)

    ReplyDelete
  3. Initially this code returned an empty array in my case. After removing the double quotes around the strings cols, rows and c, results where returned by the parseGV function. Furthermore, I replaced 'id' with 'label' to get back meaningful labels.

    ReplyDelete