Device groups is a useful feature that allows you to simplify your IoT fleet. When grouping devices, you can do things like:
Scenario | Example |
In a dynamic dashboard dropdown, display only the devices of a given group. |  |
In a map widget or devices table widget, display only the devices of a given group. |  |
|
This article explains how to setup a Google Sheets project to:
Get all devices of a given group and print them in a Sheet
Get all devices of the current Sheet, and assign them to a Group
Requirements
An active Google Apps account, with access to Google Drive and Google Sheets
An active Ubidots account with access to the Device Groups feature
At least two device groups created in your Ubidots account
Create a Google Sheets
Create a Google Sheets with the following sheets and columns:
Google Sheets: One sheet per group. Name the sheet using the Device Group label, NOT the name. To retrieve the label, go to "Devices" --> "Groups" and check the column "Label" in the Devices Group table:
Google Sheet columns: In each Sheet, add these columns:
Name | Label | ID | Link |
As an example, I've created a Google Spreadsheet with two Sheets, each corresponding to two of the groups in my account: "weather" and "group_abcd":
Create a Google Script
In the tab "Extensions" of your Google Spreadsheet, go to "Apps Script":
Then give a name to the Project and click "Save":
Copy the following code, replacing the "TOKEN" variable with your own Ubidots account token.
Google Script Code
This code has two main functions:
getDevicesOfCurrentGroup: Uses the sheet's name as device group label, and makes a request to this API endpoint (/api/v2.0/device_groups/<device_group_key>/devices/) in order to retrieve all devices in the group.setDevicesOfCurrentGroup: Reads the column "label" of the current sheet to create an array of device labels, then uses the sheet's name as device group label to sends that array to this API endpoint (/api/v2.0/device_groups/<device_group_key>/_/assign_devices/).
NOTE: The function setDevicesOfCurrentGroup doesn't erase devices from a group. If you erase a sheet's row, it won't eliminate the device from the group.
TOKEN = '<YOUR-UBIDOTS-TOKEN>'
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Ubidots')
.addItem('Get devices of current group', 'getDevicesOfCurrentGroup')
.addItem('Add devices to current group', 'setDevicesOfCurrentGroup')
.addToUi();
}
function getDevicesOfCurrentGroup() {
var sheet = SpreadsheetApp.getActiveSheet();
var deviceGroupLabel = sheet.getName();
if (deviceGroupLabel) {
var devices = getDevicesInGroup(deviceGroupLabel);
var data = devices.map(function(device) {
return [
device.name,
device.label,
device.id,
device.url
];
});
var range = sheet.getDataRange();
range.offset(1, 0, range.getNumRows() - 1).clearContent();
range.offset(1, 0, data.length, data[0].length).setValues(data);
SpreadsheetApp.getUi().alert("Retrieved " + devices.length + " devices from group " + deviceGroupLabel);
} else {
SpreadsheetApp.getUi().alert("Could not find device group " + deviceGroupLabel);
}
}
function setDevicesOfCurrentGroup() {
var sheet = SpreadsheetApp.getActiveSheet();
var deviceGroupLabel = sheet.getName();
if (deviceGroupLabel) {
var deviceLabels = getDeviceLabels(sheet);
console.log(deviceLabels);
assignDevicesToGroup(deviceGroupLabel, deviceLabels);
SpreadsheetApp.getUi().alert("Synced " + deviceLabels.length + " devices to group " + deviceGroupLabel);
} else {
SpreadsheetApp.getUi().alert("Could not set devices in group " + deviceGroupLabel);
}
}
function getDevicesInGroup(deviceGroupLabel) {
var url = "https://industrial.api.ubidots.com/api/v2.0/device_groups/~" + deviceGroupLabel + "/devices/";
var response = UrlFetchApp.fetch(url, {
"headers": {
"X-Auth-Token": TOKEN
}
});
var data = JSON.parse(response.getContentText());
if (data.results.length > 0) {
return data.results;
} else {
return [];
}
}
function getDeviceLabels(sheet) {
var data = sheet.getDataRange().getValues();
var deviceLabels = [];
for (var i = 1; i < data.length; i++) {
if (data[i][1]) {
deviceLabels.push('~' + data[i][1]);
}
}
return deviceLabels;
}
function assignDevicesToGroup(deviceGroupLabel, deviceLabels) {
var url = "https://industrial.api.ubidots.com/api/v2.0/device_groups/~" + deviceGroupLabel + "/_/assign_devices/";
var payload = JSON.stringify(deviceLabels);
var response = UrlFetchApp.fetch(url, {
"method": "post",
"headers": {
"X-Auth-Token": TOKEN,
"Content-Type": "application/json"
},
"payload": payload
});
}
Run the project!
Back to your spreadsheets file, refresh the page to see the newly-created menu:
Now click on each menu option to see the results:
"Get devices of current group" function
In my example, the 7 devices of the group are retrieved:
"Add devices to current group" function
Now let's copy the device "Menlo Park" to the group "group_abcd", and run the "Add devices to current group" function:
Now I can see the device "Menlo Park" in the group with label "group_abdc"!
Improvements
Depending on your use case and fleet size, you might want to add the following improvements to the code:
Include a
page_sizeparameter in the "Get devices of current group" API call, in order to request more devices than the default page size. See API docs - Pagination.If the request takes too long, consider adding a
forloop in order to brake it into multiple requests. For example, if retrieving 1,000 devices, then make 10 requests with page_size=100.
Add a logic to detect blank rows, and erase the missing device from the device group.
Periodically run the "
Get devices of current group" using App Script's time-driven triggers, to ensure your sheet is always up to date.
Other found useful...