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
  });
}