var TOKEN = "BBJT-fasndfldsjbfgbruu74n34lk50gdf8";

var DS_ID = "5e7ce6700ff4cgdf7gshfg7fd"

var NH3_ID = "5e7f9b2a476378dgs9h78fd7"

var OXI_ID = "5e7cfce40ff4gf8ss9dg7fg9"

var RED_ID = "5e7cfce64763e7488354u45h"

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("NH3");

    var options =

    {

        "contentType" : "application/json",

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

        "method": "get"

    };

    var response = UrlFetchApp.fetch("http://things.ubidots.com/api/v1.6/variables/"+ NH3_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]);

    }

}