Skip to main content
All CollectionsIoT Projects Tutorials
Add Devices to Groups using Google Sheets
Add Devices to Groups using Google Sheets

Simplify device group management using Google Sheets

Sergio M avatar
Written by Sergio M
Updated over 5 months ago

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.

Use Global Events to create a single event that can be triggered by any device of the group, saving the need to create thousands of events.

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()).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_size parameter 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 for loop 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...

Did this answer your question?