How to edit your devices using Google Spreadsheet with the Domotz Public API

January 20,2020 in  Engineering - Developer Posts - Domotz MSP Blog
by Alessandro Pagiaro

In just a matter of minutes, by following these simple steps Domotz Public API allows […]

Google Sheets and Domotz API integration

In just a matter of minutes, by following these simple steps Domotz Public API allows you to easily connect Domotz with Google Sheets.

Thanks to the integration, we can quickly create a spreadsheet that fetches, modifies, and updates the device data from a Domotz Agent with just one click – directly from Google Sheets.

In case you’re not familiar….Google Sheets is a tool very similar to Microsoft Excel that is available to every Google Account. Need a Google Account? You can sign up for one here (FREE).

STEP 1: Create a New Google Sheet

First of all, we need to open a new Google Spreadsheet. To do that, we can easily go to https://spreadsheet.new and automatically create a new Spreadsheet file using our Google Account.

STEP 2: Create an API Key in Domotz

In order to fetch data using Domotz Public API, we need to create an API Key. The idea is to store the generated API Key in the spreadsheet itself so as to reference it whenever we need… using the cell name (e.g. A1).


To generate a new API Key, go to Domotz Portal (http://portal.domotz.com) > Settings (Top Right Corner) > API Keys > Create an API Key. Enter your password and write a name to remember why you created the API Key (i.e. Google Spreadsheet).

Copy the API Key and paste it in the Spreadsheet wherever you prefer (for this tutorial I’m going to use the cell `C1`).

You’ll do the same for the endpoint… copy and paste it into another cell (in this tutorial I’m going to use the cell `E1`).

Spreadsheet with the API Key

Spreadsheet with the API Key

STEP 3: Let’s code a Google Apps Script!

Now that we have all the elements to play with, we can start the Google Apps Scripts! We have to write a script to fetch the data and render it in Google Sheets.

Open the Script Editor (Tool > Script Editor) and rename the default file in `utils.gs`. In this file, we are going to write some essential functions needed:
– A function to customize our menu
– A function to fetch the data from Domotz
– A function to write the data down into the Spreadsheet file

Customize the menu

Google Sheets permits to add a custom field in the main menu. To do that we need to insert the following lines into the script file:

function onOpen(){ // Invoked everytime the Spreadsheet is open
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Domotz')
  .addItem('GetAgents', 'getAgents')
  .addItem('GetDeviceList', 'getDeviceList')
  .addItem('Update Selected Device', 'updatedevice')
  .addToUi();
}

Custom Menu List

Add function to fetch the data

Now we need a function that helps us to invoke the Domotz API. I defined the function below that automatically adds the API Key in the request reading it from the spreadsheet. This function can be extended as you want but keep it simple for this tutorial.

var API_KEY = SpreadsheetApp.getActiveSheet().getRange('C1').getValue(); // Edit the cell to fetch the correct value from the spreadsheet
function domotzFetch(url){
  return UrlFetchApp.fetch(url, {
    headers: {
    'X-Api-Key': API_KEY,
    'Accept': 'application/json'
    }
  })
}

Add function to write the JSON into the file

Now we need to find a way to render our JSON into the Spreadsheet table. This can be tricky stuff since JSON has, potentially, a huge amount of nesting levels instead of the table… is just a table.

To convert a JSON into a table, I defined a function that has two input parameters: the JSON with the data (it is an array of object in our case) and a map used to convert the JSON (maybe nested) field into a flattened array. The result of the function is an array of arrays containing all our fields defined in the map.

function createTableFromJSON(jsonObject, keys){
  var result = [];
  var header = [];
  for(var key in keys){
    header.push(keys[key]);
  }
  result.push(header); 
  jsonObject.map(function(agent){
    var agentArray = [];
    for(key in keys){
      var prop = key.split('.').reduce(function(o, i){ return o ? o[i] : null }, agent)
      agentArray.push(prop);
    }
    result.push(agentArray);
  });
  return result;
}


The functions can be used in this way:

var object = JSON.parse(fetchedDataFromDomotz);
  
var keys = {
 'id': 'DeviceId',
 'display_name': 'Name',
 'importance': 'Importance',
 'ip_addresses': 'IP Addresses',
 'hw_address': 'MAC Address',
 'user_data.vendor': 'Make',
 'user_data.model': 'Model',
 'details.zone': 'Location',
 'details.room': 'Room'
};
  
var result = createTableFromJSON(object, keys);

Write data into the table

These two functions are pretty easy. The `writeTable` gets a cell coordinates and an Array of Array as input (the result of the function above) and start to write down the values into the file.

function writeTable(startingRow, startingColumn, values) {
  values.map(function(value, index) {
    writeRow(startingRow+index, startingColumn, value);
  });
}

function writeRow(row, startingColumn, values){
  const fn = function (v) {
    return [v];
  }
  
  SpreadsheetApp.getActiveSheet().getRange(row, startingColumn, 1, values.length).setValues([values.map(fn)]);
}

STEP 4: Fetch the agents

If all the functions above are clear, we can now proceed in writing the function to fetch data from Domotz. Let’s start with fetching the Agent List.

var BASE_URL = SpreadsheetApp.getActiveSheet().getRange('E1').getValue();

function getAgents(){
  var fetchUrl = BASE_URL+'agent?page_size=4000'; // API Resource to retrieve the Agent List
  var jsondata = domotzFetch(fetchUrl);
  
  var response   = JSON.parse(jsondata.getContentText());
  var keys = {
    'display_name': 'Name',
    'creation_time': 'Creation Time',
    'id': 'Id',
    'access_right.api_enabled': 'Can use API', // The Agent specific API can be used only by the premium agents
    'status.value': 'Status'
  };
  
  var result = createTableFromJSON(response, keys);

  var cell = SpreadsheetApp.getActiveSheet().getCurrentCell(); // Get the selected cell
  
  writeTable(cell.getRow(), cell.getColumn(), result);
}

STEP 5: Run it and test the Domotz Public API + Google Sheet integration

This is a good point to try to execute your code. Go to the main Spreadsheet tab, reload it (press Command + R on your keyboard). Then select the first empty cell, wait until the Domotz label appears in the menu, select GetAgents and voilà!… your agents are automatically fetched from your Domotz Account.

STEP 6: Fetch the devices

Let’s continue to implement the functionalities required to fetch and modify our agents.
We have to define a `getAgents`-like function that fetch the device list from a specific agent and render the result into our spreadsheet.

In this case, the implementation is a bit more tricky since we need to specify which agent we want to query. I’ve found this solution: when you request a list of devices, a popup is shown to request the agent ID.

To implement this solution we need, first of all, create a new file called `selectAgentIdPopup.html` and define in it the template of the popup

  
  Agent Id:
    <input id="agentId" type="number" placeholder="1234" />
     <input type="button" value="Close" />


And then, create the function to fetch the devices. If the agentId is not specified (as it is when you invoke it clicking on the menu item), this function opens the popup, otherwise, if you pass a deviceId (as happens when it is invoked by the popup itself), it fetches the data. Even in this case the login to fetch and render the data is similar to the previous one, except the mapping object between the devices fields and the table values.

function getDeviceList(agentId, cell){
  if(!agentId){
    var html = HtmlService.createHtmlOutputFromFile('selectAgentIdPopup');
    SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showModalDialog(html, 'Please, specify the agent ID');
    return;
  }
  
  var fetchUrl = BASE_URL+'agent/'+agentId+'/device';
  var jsondata = domotzFetch(fetchUrl);
  
  var object = JSON.parse(jsondata.getContentText());
  
  var keys = {
    'id': 'DeviceId',
    'display_name': 'Name',
    'importance': 'Importance',
    'ip_addresses': 'IP Addresses',
    'hw_address': 'MAC Address',
    'user_data.vendor': 'Make',
    'user_data.model': 'Model',
    'details.zone': 'Location',
    'details.room': 'Room'
  };
  
  var result = createTableFromJSON(object, keys);
  /* Prepend agentId */
  result.map(function(line, index){
    if(index == 0) line.unshift('AgentId');
    else line.unshift(agentId);
  });
  
  if(!cell){
    cell = SpreadsheetApp.getActiveSheet().getCurrentCell();
  }
  writeTable(cell.getRow(), cell.getColumn(), result);
}

Notice, as in the previous case, the function starts to fill the spreadsheet from the cell you are on, so be sure to don’t select an agent cell otherwise the data will be overwritten.

 

STEP 7: Edit device field

The final step! After you edit some device information, you want to update the Domotz Device. To help you in this operation I wrote the following functions: the usual handler (as seen before) and a function that, starting from a deviceId cell, read the values (you can extend them as you prefer) and create the JSON object to sent back to Domotz.

function updateDeviceField_(deviceId, agentId, field, newValue){
  var url = BASE_URL+['agent/', agentId, '/device/', deviceId, '/', field].join('');
  Logger.log(url);
  
  UrlFetchApp.fetch(url, {
    method: 'PUT',
    payload: JSON.stringify(newValue),
    headers: {
      'X-Api-Key': API_KEY,
      'Content-Type': 'application/json'
    }
  });
    
}

function updatedevice(){
  
  var deviceIdCell = SpreadsheetApp.getActiveSheet().getCurrentCell();
  
  var agentId = SpreadsheetApp.getActiveSheet().getRange(deviceIdCell.getRow(), deviceIdCell.getColumn()-1).getValue();
  var make = SpreadsheetApp.getActiveSheet().getRange(deviceIdCell.getRow(), deviceIdCell.getColumn()+5).getValue();
  var model = SpreadsheetApp.getActiveSheet().getRange(deviceIdCell.getRow(), deviceIdCell.getColumn()+6).getValue();
  var zone = SpreadsheetApp.getActiveSheet().getRange(deviceIdCell.getRow(), deviceIdCell.getColumn()+7).getValue();
  var room = SpreadsheetApp.getActiveSheet().getRange(deviceIdCell.getRow(), deviceIdCell.getColumn()+8).getValue();
  
  // Update every property, one each API call
  updateDeviceField_(deviceIdCell.getValue(), 11822, 'user_data/vendor', make);
  updateDeviceField_(deviceIdCell.getValue(), 11822, 'details/zone', zone);
  updateDeviceField_(deviceIdCell.getValue(), 11822, 'user_data/model', model);
  updateDeviceField_(deviceIdCell.getValue(), 11822, 'details/room', room);
}


Of course, this file is just a small example of what you can do combining Domotz API with Google Spreadsheet and Google App Script. There are tons of other great Domotz Integrations and Features to test. Try testing some on your own and we’d love to hear what you think and learned in our forum!