After successful authentication with the Pay as you Track API data is returned from the API in a format that is based on (but does not fully follow) the Google Visualization JSON format. This is described here:

https://developers.google.com/chart/interactive/docs/dev/implementing_data_source#json-response-format

A typical successful call to the Pay as you Track API will result in a status of “ok” and a “dataset” field. This is where the payload of the Pay as you Track API deviates a little from a Google DataSource.

{
   "version":"0.6",
   "reqId":"0",
   "status":"ok",
   "sig":"-42971447",
   "dataset":{........}

A Google DataSource would normally contain a field ‘table’, the Pay as you Track API contains a “dataset” field. The Pay as you Track “dataset” field is a simple object that allows for an array of tables.

{
    "dataset":{
        "datasetname":"GroupDataSet",
        "tables":[....

Each table in the ‘tables’ array is formatted as a self describing Google DataSource table. This allows for multiple data fragments to be returned from the server in the same call. If desired the data in these tables can be used to create a Google Visualization DataTable (new DataTable(…)) for use in Google Visualizations.

An Example

Lets look at an example. Here is a truncated response from the Group-getByTypeID call where the groupTypeID query parameter is set to 3 (vehicles).

{
   "version":"0.6",
   "reqId":"",
   "status":"ok",
   "sig":"1064354350",
   "dataset":{
      "datasetname":"GroupDataSet",
      "tables":[
         {
            "cols":[
               {
                  "id":"0",
                  "label":"GroupID",
                  "type":"number",
                  "p":{
                     "autoincrement":true
                  }
               },
               {
                  "id":"1",
                  "label":"Description",
                  "type":"string",
                  "p":{}
               },
               {
                  "id":"2",
                  "label":"ParentID",
                  "type":"number",
                  "p":{}
               },
               {
                  "id":"3",
                  "label":"GroupTypeID",
                  "type":"number",
                  "p":{}
               },
               {
                  "id":"4",
                  "label":"TimeStamp",
                  "type":"string",
                  "p":{}
               }
            ],

We can see that the dataset is called GroupDataSet, and contains one table called ‘Folders’. Here is a flattened view of the table. Important properties include “cols” (table column descriptors) and “rows” (the actual data).

{"cols":[
   {"id":"0","label":"GroupID","type":"number","p":{"autoincrement":true}},
   {"id":"1","label":"Description","type":"string","p":{}},
   {"id":"2","label":"ParentID","type":"number","p":{}},
   {"id":"3","label":"GroupTypeIDC","type":"number","p":{}},
   {"id":"4","label":"TimeStamp","type":"string","p":{}}],
"rows":[
   {"c":[{"v":850},{"v":"Vehicles"},{"v":848},{"v":3},{"v":"AAAAAACiVlg="}],"p":{"rowstate":"unchanged"}}],
"p":{"tablename":"Folders","primarykey":[0]}}]
}

DateTimes

The section details how datetimes are returned in the JSON response. The JSON specification doesn’t have a standard for returning datetimes.

By default, the Pay as you Track API returns datetimes in UTC in the format ‘new Date(******)’. Here is a JSON fragment showing the default way that dates are returned.

            "cols":[
               {
                  "id":"0",
                  "label":"CurrentUTC",
                  "type":"datetime",
                  "p":{}
               }
            ],
            "rows":[
               {
                  "c":[
                     {
                        "v":new Date(1614936565690)
                     }
                  ],
                  "p":{}
               }
            ]...

This is non-standard BUT for convenience allows for the returned data with dates to be loaded into a javascript object very easily. This function will quickly load returned json without having to explicitly format dates.

function(json) {
  return Function('"use strict";return (' + json + ')')();
};

If this isn’t to your liking a developer can pass a ‘dtm’ query parameter and set a datetime format string. To return dates in ISO format set this as dtm=o. For example, “2021-03-05T09:46:31.4570000”

Back to: Contents