A typical inquiry from our users is how to extract the data stored in the Ubidots cloud for further analysis, and our answer has always been to use our API to get either JSON or CSV formatted data.

We know this is quite a generic answer, so we wanted to take a step further and see what options are there to make the process a bit friendlier.

After doing some research, I fell in love with Google Apps Script. It is a free services that allows you to automate certain data extraction/pushing tasks using Google's existing products. The language is similar to Javascript, and they provide enough examples to easily get started. After playing with it for a while, the following guides were created to help you:

The possibilities are enormous; for instance, you could create an automatic report generator and send it to your customers every week.

Another application would be device provisioning; if you have thousands of devices to deploy, and their information is in a Google Sheet, you could create a script to read the sheet and create a Ubidots data source for every line on the file.

Creating a Google Apps Script

Create a Google Sheet and add two sheets to it with these names:

  • "Variables"
  • "Values"

From your Google Sheet, click on "Tools" then "Script Editor...", then "Blank Project":

Open the Script Editor:

Add the code below (in the code section) to the script.

//Replace the following constants with your Ubidots token, the datasource you wish to import, and the Variable(s) you wish to retrieve. 

var TOKEN = "HlXGrAq4Ah5ZtBtOzZPBXTZ7vKftOX";

var DS_ID = "554e5f557625423aa924180e"

var TEMP_ID = "554ea8f0762542156af95321"

var PAGE_SIZE = "100"

function onOpen() {

    var sheet = SpreadsheetApp.getActive();

    var menuItems = [

        {name: 'Get Ubidots Variables', functionName: 'get_ds_variables'},

        {name: 'Get Data', functionName: 'get_variable_data'}

    ];

    sheet.addMenu('Ubidots', menuItems);

}

function get_ds_variables(){

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Variables");

    var options =

    {

        "contentType" : "application/json",

        "headers" : {"X-Auth-Token": TOKEN},

        "method": "get"

    };

    var response = UrlFetchApp.fetch("http://things.ubidots.com/api/v1.6/datasources/"+ DS_ID +"/variables/", options);

    var obj = JSON.parse(response).results

    for (var i = 0, l = obj.length; i < l; i++) {

        var date = new Date(obj[i].last_activity)

        sheet.appendRow([obj[i].name, date, obj[i].last_value.value, obj[i].unit]);

    }

}

function get_variable_data(){

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Values");

    var options =

    {

        "contentType" : "application/json",

        "headers" : {"X-Auth-Token": TOKEN},

        "method": "get"

    };

    var response = UrlFetchApp.fetch("http://things.ubidots.com/api/v1.6/variables/"+ TEMP_ID +"/values/?page_size="+ PAGE_SIZE, options);

    var obj = JSON.parse(response).results

    for (var i = 0, l = obj.length; i < l; i++) {

        var date = new Date(obj[i].timestamp)

        sheet.appendRow([date, obj[i].value, obj[i].context]);

    }

}

Done! now open your Google Sheet again and you'll see a new menu to trigger the functions:

Finally, run the functions to extract your data. The first "Get Ubidots Variables" will retrieve the variables of the specified data source. The second "Get Data" will retrieve the values of the specified variable. From then on you can create pivot tables, get data from other sources, etc. I.e. How about using Piktochart's Google Sheets integration to create a beautiful report for your customers?

Results

 A Google Chart created from the extracted data from Ubidots:

Did this answer your question?