Ubiquiti Networks mFi Power Socket Mini Data to Google Sheets via Apps Script Web App

2023-02-07

With the Russian aggression in Ukraine and other factors, the energy crisis is emerging today. Measuring and analysing the energy consumption of household appliances is crucial. Knowing which appliances consume the most energy, when and how to avoid using them or even replace them is the key to saving energy. For example, take a look at the readings of some of our household appliances:

Appliance typeModel numberConsumptionNotes
Fridge with freezerHisense RB34D4DDE0.3699 kWh per day20°C constant ambient temperature
Washing machineMiele SOFTTRONIC W 643 F0.6440 kWh per cycleDefault cotton cycle at 60°C
KettleWMF Lono Glas0.1795 kWh per cycle1.7 liter of water from tap to 100°C

Here are the values on charts using the tools detailed below with some Google Sheets QUERY function magic with an assumptions of:

  • fridge running 24 hours a day
  • one tea a day
  • one washing cycle a day

Average consumption / Device / Day in Wh Average consumption / Device / Day in Wh Average consumption / Device / Day in Wh

Consumption distribution in Wh / Daytime Consumption distribution in Wh / Daytime Consumption distribution in Wh / Daytime

Prerequisites

  • Target Google Spreadsheet locale has to be set to United States for decimal place & date format compatibility
  • Tested on mFi mPower Mini the EU version only but should be similar to US model

Installation steps

Prepare Google Spreadsheet & Web App

  1. Create new Google Spreadsheet under your private & permanent Google Account

  2. Go to Apps Script editor via Extensions > Apps Script

  3. Add external libraries by Bruce McPherson:

    • in the left tab section Libraries click the rounded plus button

    • fill in Script ID field with the cDbAbstraction library ID:

      1Ddsb4Y-QDUqcw9Fa-rJKM3EhG2caosS9Nhch7vnQWXP7qkaMmb1wjmTl

    • add another library repeating the step above with cDriverSheet library ID:

      18fvqHqSs2YwU2ZMUcx6-9GE30u6i663rZTz7K0xNsStHoiJcs487JvN-

  4. Copy & paste whole the Apps Script code below to the Code.gs tab editor

  5. Adjust the following variables in the GVAR object to match your environment / settings:

    SPREADSHEET_ID
SHEET_NAME
DATE_FORMAT
TIME_FORMAT
NON_ZERO_VALUES_ONLY

  6. Deploy the script as a Web app with the following options:

    • Execute as : Me (your@email.address)
    • Who has access : Anyone

  7. Run the testPost function and authorize the script on the first run

    • Run > testPost

Prepare the mFi device

  1. Copy the Web App Deployment URL and update it in:

    1. the step 9. below, in the line starting with cron.1.job.1.cmd in the variable name {YOUR_WEBSERVICE_URL}
    2. the step 13. below in the wget push command in the variable name {YOUR_WEBSERVICE_URL}

  2. Adjust schedule in the line below starting with cron.1.job.1.schedule

  3. Reset the mFi to default settings holding the reset button for ~5 seconds

  4. Wait approximately 30 seconds

  5. Connect to the mFi ###### newly discovered WiFi network

  6. SSH to the mFi IP address:

    • typically on a default mFi the IP address is 192.168.2.20

    • you can find devices on your local subnet via nmap -sP {YOUR_SUBNET}/24

    • replacing the {YOUR_MFI_IP}variable with your real mFi IP address

    • using the legacy cypher, oKexAlgorithms & oHostKeyAlgorithms

      ssh -c aes128-cbc -oKexAlgorithms=+diffie-hellman-group1-sha1 -oHostKeyAlgorithms=+ssh-dss ubnt@{YOUR_MFI_IP}

    • where default credentials are as follows:

      • Username: ubnt
      • Password: ubnt

  7. Successful login is indicated by prompt:

    BusyBox v1.11.2 (2015-04-06 14:24:36 PDT) built-in shell (ash)
Enter 'help' for a list of built-in commands.

MF.v2.1.11#

  8. Edit the /tmp/system.cfg file with vi editor:

    vi /tmp/system.cfg

  9. Add following lines at the end of the system.cfg cron file:

    • using i command to insert text

    • and wq command to write & quit editor

      cron.1.job.1.schedule=* * * * *
cron.1.job.1.status=enabled
cron.1.job.1.cmd=M=`ifconfig ath0 | grep -o -E '([[:xdigit:]]{1,2}:){5}[[:xdigit:]]{1,2}'`;P=`cat /proc/power/pf1`;R=`cat /proc/power/relay1`;V=`cat /proc/power/v_rms1`;I=`cat /proc/power/i_rms1`;(/usr/bin/wget -b --no-cookies -4 --tries=1 -o /dev/null -O /dev/null --no-check-certificate --secure-protocol=auto --post-data="mac=$M&pf1=$P&relay1=$R&v_rms1=$V&i_rms1=$I" {YOUR_WEBSERVICE_URL}) >/dev/null 2>&1
cron.1.status=enabled
cron.status=enabled

  10. Run following command to update the mFi flash:

    cfgmtd -f /tmp/system.cfg -w

  11. Reboot the mFi device with:

    reboot

  12. Connect to the mFi now via browser and configure it to connect to your internet ready local WiFi network:

    1. you will need to use Mozilla Firefox browser
    2. you will need to update your browser settings / flags to accept TLS version 1 as mFi uses such TLS version
    3. go to about:config tab
    4. search for security.tls.version.min variable and set it to 1
    5. typically on a default mFi the IP address is 192.168.2.20
    6. than you can type in the mFi IP address and select that you accept the weak TLS security risk

  13. You can try to post some data to the Apps Script Web App with the following command:

    M=`ifconfig ath0 | grep -o -E '([[:xdigit:]]{1,2}:){5}[[:xdigit:]]{1,2}'`;P=`cat /proc/power/pf1`;R=`cat /proc/power/relay1`;V=`cat /proc/power/v_rms1`;I=`cat /proc/power/i_rms1`
    • followed by wget push command:
    (/usr/bin/wget -b --no-cookies -4 --tries=1 -o /dev/null -O /dev/null --no-check-certificate --secure-protocol=auto --post-data="mac=$M&pf1=$P&relay1=$R&v_rms1=$V&i_rms1=$I" {YOUR_WEBSERVICE_URL}) >/dev/null 2>&1

Apps Script Code

/**
* global variable object contains all global variables
* @type {Object}
* @const
*/
var GVAR = {
  // spreadsheet id for data
  //"SPREADSHEET_ID" : SpreadsheetApp.getActiveSpreadsheet().getId(),
  "SPREADSHEET_ID" : "{YOUR_SPREADSHEET_ID}",
  // sheet name
  "SHEET_NAME" : "{YOUR_SHEET_ID}", // gid param in spreadsheet url
  // datestamp date format
  //"DATE_FORMAT" : "dd.MM.yyyy",
  "DATE_FORMAT" : "MM/dd/yyyy",
  // timestamp time format
  "TIME_FORMAT" : "HH:mm:ss",
  // write only non zero values
  "NON_ZERO_VALUES_ONLY" : true,
  // log file id
  //"LOG_FILE_ID" : "{YOUR_LOG_FILE_ID}"
}

/**
* webservice HTTP POST endpoint
*
* @param {Object} HTTP POST request parameters 
* @returns {String} result 
*/
function doPost(e) {
  /*
  e = (e || new Object());
  e.parameters = (e.parameters || new Object());
  e.parameters.mac = (e.parameters.mac || "00:AA:11:BB:22:CC");
  e.parameters.pf1 = (e.parameters.pf1 || "0.0000");
  e.parameters.relay1 = (e.parameters.relay1 || "1.0000");
  e.parameters.v_rms1 = (e.parameters.v_rms1 || "11.0000");
  e.parameters.i_rms1 = (e.parameters.i_rms1 || "0.1010");
  */
  try {
    var dbHandler = new cDbAbstraction.DbAbstraction(cDriverSheet, {
      siloid: GVAR.SHEET_NAME,
      dbid: GVAR.SPREADSHEET_ID
    });
    var handlerResult = null;
    var aTz = Session.getScriptTimeZone();
    var aDate = new Date();
    e.parameters["date"] = Utilities.formatDate(aDate, aTz, GVAR.DATE_FORMAT);
    e.parameters["time"] = Utilities.formatDate(aDate, aTz, GVAR.TIME_FORMAT);
    if (GVAR.NON_ZERO_VALUES_ONLY === false || (GVAR.NON_ZERO_VALUES_ONLY === true && e.parameters["relay1"][0] === "1" && e.parameters["i_rms1"][0] != "0.0")) {
      //Flog(JSON.stringify(e.parameters));
      handlerResult = dbHandler.save(e.parameters);
    } else {
      return false
    };
    return ContentService.createTextOutput(
      "(" + JSON.stringify(handlerResult) + ")"
    ).setMimeType(ContentService.MimeType.JAVASCRIPT);
  } catch(e) {
    //Flog("ERROR: " + e);
    Logger.log(e);
  }
}

/**
* get sheet by id
*
* @param {string} spreadsheet id
* @param {string} sheet id
* @return {object} sheet object
*/
function getSheetById(spreadsheetId, sheetId) {
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var sheets = ss.getSheets();
  for (i in sheets)
    if (sheetId == sheets[i].getSheetId())
    return sheets[i];
  return null;
}

/**
* tests webservice
*/
function testPost() {
  var options = {
    'method'  : 'POST',
    'followRedirects' : true,
    'muteHttpExceptions': true
  };
  var url = ScriptApp.getService().getUrl();
  // mac=$M&pf1=$P&relay1=$R&v_rms1=$V&i_rms1=$I
  var result = UrlFetchApp.fetch(encodeURI(url + '?mac=00:AA:11:BB:22:CC&pf1=0.0000&relay1=1.0000&v_rms1=11.0000&i_rms1=0.1010'), options);
  if (result.getResponseCode() == 200) {
    var params = JSON.stringify(result);
    Logger.log(params);
  } else {
    throw "Error: " + result.getResponseCode();
  }
}

/**
* logs message to log file
* @returns {Bool} success
*/
/*
function Flog(logMessage, initFile){
  logMessage = (logMessage || new Date());
  var logFile = null, logFileTxt = null;
  logFile = DriveApp.getFileById(GVAR.LOG_FILE_ID);
  if (initFile) {
    logFile.setContent("Begin log file.");
    return true;
  }
  logFileTxt = logFile.getBlob().getDataAsString();
  logFileTxt = Utilities.formatDate((new Date()), "Europe/Prague", "yyyy-MM-dd'T'HH:mm:ss.SSSXXX") + " : " + logMessage + "\n" + logFileTxt;
  logFile.setContent(logFileTxt);
  return true;
}
*/

Ubiquiti Networks mFi Power Socket Mini Ubiquiti Networks mFi Power Socket Mini Ubiquiti Networks mFi Power Socket Mini

EnglishProfessionalmFiLinuxEnergySavings

↩︎ Předvolební chvilka veselé poezie