Jump to content
apagiaro

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

Recommended Posts

apagiaro

Google Spreadsheet is the tool Excel-like that offered to every Google Account. In this tutorial, I'll show you how to use it to fetch data from a Domotz Agent and how to modify them.

 

Set the environment

First of all, we need to open a new Google Spreadsheet. To do that, we can easily go https://spreadsheet.new

To fetch data using Domotz Public API, we need to create an API Key. The idea is to store it in the spreadsheet itself so to reference it whenever we need. 
You can generate a new API Key going 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). 

1569185111_Screenshot2019-12-1316_52_49.thumb.png.2e993af09fcca5696452436060cbf2e6.png

Copy the API Key and paste it on the Spreadsheet wherever you prefer (in this tutorial I'm going to use the cell `C1`). Do the same for the endpoint, so copy and paste it in another cell (in this tutorial I'm going to use the cell `E1`).

 

154150743_Screenshot2019-12-1316_56_00.thumb.png.031c6e89bcd4273a8df57630cac916a7.png

 

Let's code!
Now we have to write a script to fetch the data and render it. Are you ready? Let's start to code!

Open the Script Editor (Tool > Script Editor) and rename the default file in `utils.gs`. We are going to write there some essential function 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 Spreadsheet permits to add custom field in the main menu. To do that let's insert the following script into the 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();
}


The result will be

788338516_Screenshot2019-12-1317_02_01.png.e8f72cdfd31581b74f6a5d3b12817bac.png

 

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

 

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

 

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

Run it
This is a good point to try to execute your code.
Let's go to the main Spreadsheet tab, reload it. 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.

 

(In progress...)

  • Like 1

Share this post


Link to post
Share on other sites
apagiaro

Fetch the devices

Let's continue in implementing 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 pass 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

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
  Agent Id:
    <input type="number" placeholder="1234" id="agentId"/>
     <input type="button" value="Close"
        onclick="google.script.run.getDeviceList(document.getElementById('agentId').value)" />
  </body>
</html>

1065171268_Screenshot2019-12-1317_34_16.thumb.png.71d627f48f28f8b65482376625590696.png

 

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.

 

Edit device field
And then the last part! 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);
}

 

  • Like 2

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...