[SOLVED] Back up your data every day

Ubidots API
1

I have tons of data now from my temperature sensors, I am not able to see my old data on ubidots because it gets refreshed every 2000 data points. I was wondering if there is a way I can send my data daily to an excel spreadsheet without having to download the csv file everyday. Any help would be appreciated, thanks.

2

Hey @npatel023 sure you can setup a Google Sheets script to pull the data every day at a specific time. Here’s an example that you can scale to more variables or a larger timeframe: https://www.hackster.io/AgustinP/get-ubidots-data-from-google-sheets-057040

3

@aguspg Thank you so much for this! I have tried his tutorial and just can’t seem to get it to work. I run the script and it executes but I don’t see any menu add ons on my google sheet. Also in the code, he DS_ID and TEMP_ID, I am not sure which one gets the id variable. Any help would be appreciated, thank you.

4

@aguspg I was able to figure out my problem. One last thing, how do I tell it to pull data every day at a specific time? Thank you.

5

Sure. Hang on - this feature one of the reasons I love google:

  1. In the script editor, click “Resources”–>“Current project triggers”

    670c64a645271473.png704Ă—209

  2. Click to add new one

    4d0381d6aa41fb22.png854Ă—185

  3. Select the function you want to run, set the trigger to “time-driven” then select the date it should run:

    a308561f8a6f8464.png850Ă—251

6

Thank you so much!!! I really appreciate all your help!!

7

One more thing I was wondering, when I import the data from Ubidots, is there a way to update only the new data and not have it import all of it again? @aguspg

8

Hey there, according to http://ubidots.com/docs/api/v1_6/data/get_variables_id_values.html you should specify a start and end time in millisecond timestamps. So instead of just passing the parameter “PAGE_SIZE” we add the timeframe. Here a full new code:

var TOKEN = "XXXXXXXXXXXXXXX";
var VAR_ID_1 = "56e865bd762542260e3e5b0b"

function onOpen() {

    var sheet = SpreadsheetApp.getActive();
    var menuItems = [
        {name: 'Get Data', functionName: 'get_variable_data'}
    ];
    sheet.addMenu('Ubidots', menuItems);
}

function get_variable_data(){

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Var Data");
    var options =
    {
        "contentType" : "application/json",
        "headers" : {"X-Auth-Token": TOKEN},
        "method": "get"
    };

    var d = new Date();
    var T_END = d.getTime();  // Get current time in ms
    var T_START = T_END - 24*3600*1000 // Current time in ms, minus 24 hours in ms
    var response = UrlFetchApp.fetch("http://things.ubidots.com/api/v1.6/variables/" + VAR_ID_1 + "/values/?page_size=1000&start=" + T_START + "&end=" + T_END, 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]);
    }
}

This example limits the response to 1000 results. If you think you’ll be having more than 1000 results per day, then you should increase that number. If you hit a limit (i.e. a time-out due to lots of data) then you’d have to use the pagination function of the API, that it, using several requests in a for loop specifying the “&page=” parameter. Let me know if that’d be the case.

9

@aguspg I really appreciate this. I tried to run the code in the script and get this error,
TypeError: Cannot call method “appendRow” of null. (line 30, file “Code”)
I can’t seem to figure out what’s wrong. Thank you for your help again.
We won’t have more than a 1000 results everyday so this will be great.

10

Make sure to put the right sheet name instead of “Var Data”.

1 Like
11

Thank you very much for that. I was wondering when I pull the data from Ubidots, the date and time are in the same cell in google sheets. Is there a way for me to get the date and time into google sheets into 2 different cells?
@ubidots @aguspg

12

I’m sure there are better ways to do it, but a quick google search showed me a function that works ([formatDate function][1]):

When you append the row:

    sheet.appendRow([Utilities.formatDate(date, "GMT-5", "yyyy-MM-dd"), Utilities.formatDate(date, "GMT-5", "HH:mm:ss"),  obj[i].value, obj[i].context]);

Make sure to use your timezone.
[1]: https://developers.google.com/apps-script/reference/utilities/utilities#formatDate(Date,String,String)

13

@aguspg I really really appreciate you helping me out with this! Works perfectly! Thank you so much, now I will be able to analyze my data.

14

Hello @aguspg could you explain me how can I use this example to get the data for 3 variables, please?
I’d like to put each variable data in a different sheet.
Please find attached my code, I’m getting each value but I don’t know how to put it in a different sheet,

Thank you so much

Cristian M.
Bogotá, Colombia.Ubidots_GoogleSheets.txt (1.8 KB)

15

Hello npatel,

did you figure out your problem? could you help me with it, please?
I have a project with 3 variables and I’d like to save data for them. I see the example but ir is for 1 variable and a can’t yet to scale it for 3 variables.

Thank you so much,