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

Ubidots API
1

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.

2

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]);
      }
  }
}