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 type | Model number | Consumption | Notes |
---|---|---|---|
Fridge with freezer | Hisense RB34D4DDE | 0.3699 kWh per day | 20°C constant ambient temperature |
Washing machine | Miele SOFTTRONIC W 643 F | 0.6440 kWh per cycle | Default cotton cycle at 60°C |
Kettle | WMF Lono Glas | 0.1795 kWh per cycle | 1.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
Prerequisites
- Target Google Spreadsheet locale has to be set to
United States
for decimal place & date format compatibility - Tested on
mFi mPower Mini
theEU
version only but should be similar toUS
model
Installation steps
Prepare Google Spreadsheet & Web App
Create new Google Spreadsheet under your private & permanent Google Account
Go to
Apps Script
editor viaExtensions
>Apps Script
Add external libraries by Bruce McPherson:
in the left tab section
Libraries
click the roundedplus
buttonfill in
Script ID
field with thecDbAbstraction
library ID:1Ddsb4Y-QDUqcw9Fa-rJKM3EhG2caosS9Nhch7vnQWXP7qkaMmb1wjmTl
add another library repeating the step above with
cDriverSheet
library ID:18fvqHqSs2YwU2ZMUcx6-9GE30u6i663rZTz7K0xNsStHoiJcs487JvN-
Copy & paste whole the Apps Script code below to the
Code.gs
tab editorAdjust the following variables in the
GVAR
object to match your environment / settings:SPREADSHEET_ID SHEET_NAME DATE_FORMAT TIME_FORMAT NON_ZERO_VALUES_ONLY
Deploy
the script as a Web app with the following options:Execute as
:Me (your@email.address)
Who has access
:Anyone
Run the
testPost
function and authorize the script on the first runRun
>testPost
Prepare the mFi device
Copy the Web App
Deployment URL
and update it in:- the step
9.
below, in the line starting withcron.1.job.1.cmd
in the variable name{YOUR_WEBSERVICE_URL}
- the step
13.
below in thewget
push command in the variable name{YOUR_WEBSERVICE_URL}
- the step
Adjust schedule in the line below starting with
cron.1.job.1.schedule
- see crontab man
Reset the
mFi
to default settings holding thereset
button for ~5
secondsWait approximately 30 seconds
Connect to the
mFi ######
newly discovered WiFi networkSSH
to the mFiIP 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 realmFi
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
- Username:
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#
Edit the
/tmp/system.cfg
file with vi editor:vi /tmp/system.cfg
Add following lines at the end of the
system.cfg
cron
file:using
i
command to insert textand
wq
command to write & quit editorcron.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
Run following command to update the mFi flash:
cfgmtd -f /tmp/system.cfg -w
Reboot the
mFi
device with:reboot
Connect to the mFi now via browser and configure it to connect to your internet ready local WiFi network:
- you will need to use Mozilla Firefox browser
- you will need to update your browser settings / flags to accept
TLS
version1
as mFi uses such TLS version - go to
about:config
tab - search for
security.tls.version.min
variable and set it to1
- typically on a default mFi the IP address is 192.168.2.20
- than you can type in the mFi
IP address
and select that you accept the weakTLS
security risk
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
- followed by
Interesting links for mFi
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;
}
*/