Ubidots Community

Export data from Ubidots to google sheets for more than 1 variable

Hello,

I would like to export data from ubidots to a google sheets file. My project has 3 variables but example from ubidots community is for 1 variable and I can’t yet scale ir for 3 variables.

I would apreciate your kind help with this.

Thank you so much,

Cristian M.

You just need to add an array with the variables’ ids that you need to retrieve values from and iterate it. The example below may serve you as reference

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

var TOKEN = "PUT_YOUR_TOKEN_HERE";

var DS_ID = "PUT_YOUR_DEVICE_ID_HERE"

var VARIABLES_TO_RETRIEVE_DATA_FROM = ["VARIABLE_ID_1", "VARIABLE_ID_2"]

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"

    };

  for (var i = 0, l = VARIABLES_TO_RETRIEVE_DATA_FROM.length; i < l; i++) {
    var url = "http://things.ubidots.com/api/v1.6/variables/"+ VARIABLES_TO_RETRIEVE_DATA_FROM[i] +"/values/?page_size="+ PAGE_SIZE
    var response = UrlFetchApp.fetch(url, options);
         
    var obj = JSON.parse(response).results
    
    for (var j = 0, m = obj.length; j < m; j++) {
      var date = new Date(obj[j].timestamp)
      sheet.appendRow([date, obj[j].value, obj[j].context]);
      }
  }
}