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
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 and the Variable(s) you wish to retrieve.
var TOKEN = "PUT YOUR TOKEN HERE"
var URL = "http://industrial.api.ubidots.com/api/v1.6/"
var DEVICE_ID = "DEVICE-ID"
var VARIABLE_ID = "VARIABLE-ID"
var NUMBER_OF_VALUES = "100" // Number of values to retieve from the variable.
var VARIABLE_SHEET = "Variables" // Name of the sheet where the variables values will be store.
var VALUES_SHEET = "Values" // Name of the sheet where the values from the variable will be store.
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(VARIABLE_SHEET);
if (sheet != null) {
SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheet);
}
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
sheet.setName(VARIABLE_SHEET);
var options =
{
"contentType" : "application/json",
"headers" : {"X-Auth-Token": TOKEN},
"method": "get"
};
var response = UrlFetchApp.fetch(URL + "datasources/"+ DEVICE_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_SHEET);
if (sheet != null) {
SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheet);
}
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
sheet.setName(VALUES_SHEET);
var options =
{
"contentType" : "application/json",
"headers" : {"X-Auth-Token": TOKEN},
"method": "get"
};
var response = UrlFetchApp.fetch( URL + "variables/"+ VARIABLE_ID +"/values/?page_size="+ NUMBER_OF_VALUES, 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:
