Google Apps Scripts are a powerful tool to extend the usability of Google products like Docs, Sheets, and Slides, among others. Google Apps Scripts have a wide range of JavaScript classes and libraries that enable users to create add-ons and automate many tasks when using Google products.
Google Apps Script provides more than 30 built-in services for interacting with user data, other Google systems, and external systems. These services are provided as global objects akin to JavaScript's standard Math object. For example, just as Math offers methods like random() and constants like PI, Apps Script's Spreadsheet service offers methods like openById(id), classes (child objects) like Range, and enums like DataValidationCriteria.
Following this guide, you will be able to import the data from a Google Sheet, adapt the payload to match Ubidots HTTP API, and post the data to Ubidots where it can be utilized in many different ways.
Common uses: This particular uses of importing data is vast, but it can come in handy especially when importing historical data to fill gaps, analyze historical data with recent data, or simulated data-sets to further develop the end-user IoT App being assembled in Ubidots.
Requirements
An active Ubidots account
Data in a Google Sheet (formatted to code)
Step-by-Step
Google Sheets Data Setup
Google Script Editor Setup
Send Data to Ubidots
1. Google Sheet data setup
Step 1: Open a Google Sheet of a preexisting dataset or copy/paste a dataset into a new sheet. It is required the data to be organized into columns following the below structure:
Column A - Timestamp (YYYY-MM-DDTHH:mm:ss)
Column B - “Variable Label 1”
Column C - “Variable Label 2”
Column D - "Variable Label 3"
Column E - "Variable Label 4"
Column F - "Variable Label 5"
Column G - Sent
Note that the first column corresponds to each data point’s (dots as we call them at Ubidots) timestamp and formatted to a readable timestamp as YYYY-MM-DDTHH:mm:dd:ss . The following columns of the sheet are the variables to be uploaded to Ubidots with their respective timestamp values.
The final column should be label as “Sent” and will be updated automatically by the script upon a successful reception of dots to Ubidots.
NOTE: If any cells are left empty, the script used to import data will bypass that cell and continue with the import.
NOTE 2: The sheet must be named as "Data".
2. Google Script Editor setup
From the Google Sheet formatted as Step 1, select “Tools” from the mainbar dropdown of Sheets and then “Script editor” option:
Step 1: Create a script Extensions → Apps Script
A new tab will open with the Google Script Editor.
Step 2: Edit the project’s title and the script’s name. We named our example project title and script name “SendToUbidots” accordingly.
Step 3: Erase any existing code, then Copy and Paste the below code into the editor. Be sure to update where indicate your Ubidots account TOKEN in line 1 and then assign DEVICE_LABEL in line 2 of the Device in Ubidots that will house the variables' data.
var TOKEN = "PUT-YOUR-TOKEN-HERE";
var DEVICE_LABEL = "Google-sheet"; // Change device label as desired
function onOpen() {
var sheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Send Data', functionName: 'sendData'}
];
sheet.addMenu('Ubidots', menuItems);
}
function sendData() {
var sheetApp = SpreadsheetApp.getActive();
var sheet = sheetApp.getSheetByName("Data");
var data = sheet.getDataRange().getValues();
var payload = {};
for (var i = 1; i < data.length; i++) {
var date = new Date(data[i][0]);
var timestamp = date.getTime();
for (var j = 1; j < data[0].length - 1; j++){
payload[data[0][j]] = {"value": data[i][j], "timestamp":
timestamp};
}
var status = send_to_ubidots(DEVICE_LABEL, payload);
if (status == null){
sheet.activate().getDataRange().getCell(i +
1,data[0].length).setValue("Couldn't send data");
}
else{
sheet.activate().getDataRange().getCell(i +
1,data[0].length).setValue("Sent");
}
Utilities.sleep(500);
}
}
function send_to_ubidots(device_label, payload){
var options = {
"contentType" : "application/json",
"headers" : {"X-Auth-Token": TOKEN},
"payload": JSON.stringify(payload),
"method": "post"
};
// Send HTTP POST request to Ubidots
try{
var response =
UrlFetchApp.fetch("https://industrial.api.ubidots.com/api/v1.6/devices/" + device_label + "/?force=true", options);
if(response.getResponseCode() == 200){
return JSON.parse(response.getContentText());
}
} catch(e) {
// If not successful
SpreadsheetApp.getUi().alert(e.message);
return null;
}
}
Step 4: Save the code by clicking on the “Save” icon and then you’re ready to import data to Ubidots.
3. Send Data to Ubidots
After saving the script in the previous instruction, a new dropdown menu will appear in your Spreadsheet's mainbar, as displayed below. There you can find your “Ubidots” script and once clicked, the option "Send Data" becomes available.
Select "Send Data" to run script and import data.
After clicking "Send Data," the script will run automatically reading the formatted table to send every dot (cell) to the designated device in Ubidots according to the assigned timestamp.
4. Results
Now with data simply available in Ubidots, you can continue to assemble and perfect your IoT App as needed.
Other users also found helpful: