In Singapore, the utilities company sends you an email with your consumption and bill every month. I wanted to integrate this data into Home-Assistant. I achieved using Apps Script and MQTT. (See Code in GitHub).
This same technology can actually be used to create any email-based sensors.
Requirements
- Using gMail. Apps Script is a Google Drive solution that allows you to run some Google APIs and execute them on a schedule in a very easy way.
- Having a MQTT component configure.
How It Works
Apps Script
- Create a new Standalone Script.
/**
* Fetches utility consumption details from the Utilities email and sends it to Hassio MQTT.
*
* Apps Script on drive.google.com
*/
/* Configuration. */
/** @const {string} URL for MQTT Server API. */
var MQTT_URL = 'https://your-ha-domain:port/api/services/mqtt/publish';
/** @const {string} (Long-Lived) Access token for API. */
var API_TOKEN = 'your-api-token'
/** @const {string} MQTT Topic for Utility Bill. */
var EXPENSE_TOPIC = '/utilities/bill';
/** @const {string} MQTT Topic for Electricity Consumption. */
var ELECTRICITY_TOPIC = '/utilities/electricity';
/** @const {string} MQTT Topic for Water Consumption. */
var WATER_TOPIC = '/utilities/water';
/** @const {string} MQTT Topic for Gas Consumption. */
var GAS_TOPIC = '/utilities/gas';
/** @const {string} Query search to fetch utility emails. */
var UTILITY_QUERY = 'subject:"Utility Bill Summary"';
/** @const {!RegExp} RegExp to find monetary values in SGD. */
var SGD_REGEXP = new RegExp(/S\$[0-9]+\.[0-9]{2}/g);
/** @const {!RegExp} RegExp to find kWh energy values. */
var KWH_REGEXP = new RegExp(/[0-9]+ kWh/g);
/** @const {!RegExp} RegExp to find Cu M water values. */
var CUM_REGEXP = new RegExp(/[0-9]+\.[0-9]+ Cu M/);
/* Main workflow. */
/**
* Parses content of utilities email and send data to Hassio.
* Main function. Schedule on a periodic basis (e.g. weekly).
*/
function main() {
var utilityEmail = fetchLastUtilityEmail();
if (!utilityEmail) return;
var utilityContent = getEmailContent(utilityEmail);
if (!utilityContent) return;
var billExpense = getBillExpense(utilityContent);
var electricityConsumption = getElectricityConsumption(utilityContent);
var waterConsumption = getWaterConsumption(utilityContent);
var gasConsumption = getGasConsumption(utilityContent);
sendToMqttServer(EXPENSE_TOPIC, billExpense);
sendToMqttServer(ELECTRICITY_TOPIC, electricityConsumption);
sendToMqttServer(WATER_TOPIC, waterConsumption);
sendToMqttServer(GAS_TOPIC, gasConsumption);
}
/* Url fetching utilities. */
/**
* Send data to MQTT server.
* @param {string} topic MQTT topic to which to send data.
* @param {*} data Data to send to MQTT.
*/
function sendToMqttServer(topic, data) {
var payload = {
'topic': topic,
'payload': data,
'retain': true,
};
var headers = {
'Authorization': 'Bearer ' + API_TOKEN,
'Content-Type': 'application/json',
};
var fetchOptions = {
'method': 'post',
'headers': headers,
'payload': JSON.stringify(payload),
'escaping': false,
};
UrlFetchApp.fetch(MQTT_URL, fetchOptions);
}
/* Email parsing. */
/**
* Fetches the last utility email.
* @return {GmailMessage} Message for the utility bill.
* Returns null if the thread is not found.
*/
function fetchLastUtilityEmail() {
var threads = GmailApp.search(UTILITY_QUERY);
if (threads.length === 0) return null;
var messages = threads[0].getMessages();
return messages[0];
}
/**
* Get email contents.
* @param {!GmailMessage} emailMessage Email message from which to get body.
* @return {string} Contents of the message.
*/
function getEmailContent(emailMessage) {
return emailMessage.getBody();
}
/**
* Extracts expense from utility email.
* @param {string} emailBody Contents of the utility email.
* @return {?number} Total expense of the month for utility email.
* Returns null if not found or unparseable as float.
*/
function getBillExpense(emailBody) {
return parseContentVariable(emailBody, SGD_REGEXP, 0, 'S$', parseFloatOrNull);
}
/**
* Extracts electricity consumption from utility email.
* @param {string} emailBody Contents of the utility email.
* @return {?number} Electricity consumption of the month for utility email.
* Returns null if not found or unparseable as float.
*/
function getElectricityConsumption(emailBody) {
return parseContentVariable(emailBody, KWH_REGEXP, 0, ' kWh', parseIntOrNull);
}
/**
* Extracts water consumption from utility email.
* @param {string} emailBody Contents of the utility email.
* @return {?number} Water consumption of the month for utility email.
* Returns null if not found or unparseable as float.
*/
function getWaterConsumption(emailBody) {
return parseContentVariable(emailBody, CUM_REGEXP, 0, ' Cu M', parseFloatOrNull);
}
/**
* Extracts gas usage from utility email.
* @param {string} emailBody Contents of the utility email.
* @return {?number} Gas consumption of the month for utility email.
* Returns null if not found or unparseable as float.
*/
function getGasConsumption(emailBody) {
return parseContentVariable(emailBody, KWH_REGEXP, 3, ' kWh', parseIntOrNull);
}
/* Email parsing utilities. */
/**
* Extracts a clean content from a text.
* @param {string} content Content from which to extract text.
* @param {!RegExp} pattern Pattern to extract content.
* @param {number=} index Index for matched pattern.
* @param {string=} replacementText Text to remove before proceeding.
* @param {function(string):(?string|?number)=} modifierFunction Function to manipulate value.
* @return {?number|?string} Parsed content.
* Null if not found, or unparseable.
*/
function parseContentVariable(content, pattern, index, replacementText, modifierFunction) {
if (index === undefined) index = 0;
if (replacementText === undefined) replacementText = '';
var matches = content.match(pattern);
if (!matches || matches.length < index + 1) return null;
var positionedMatch = matches[index];
if (replacementText) positionedMatch = positionedMatch.replace(replacementText, '');
if (modifierFunction) return modifierFunction(positionedMatch);
return positionedMatch;
}
/**
* Parses to int or null.
* @param {string} string String to parse to int.
* @return {?number} Parsed int, or null if not parseable.
*/
function parseIntOrNull(string) {
var int = parseInt(string, 10);
if (isNaN(int)) return null;
return int;
}
/**
* Parses to float or null.
* @param {string} string String to parse to int.
* @return {?number} Parsed float, or null if not parseable.
*/
function parseFloatOrNull(string) {
var float = parseFloat(string, 10);
if (isNaN(float)) return null;
return float;
}
This script fetches an email with a given subject. Then, uses RegExp to parse the content (document.querySelector is not available in Apps Script). Finally, it sends the data to MQTT via REST API (HTTP Post request).
If you are also in Singapore, you can just adjust your MQTT_URL and API_TOKEN with your Home-Assistant URL for MQTT and your API Token respectively.
- Create a Time-Driven Trigger a daily, weekly or monthly trigger the script and update the sensor data.
Home-Assistant
- Create sensors to read the post data.
sensor:
- platform: mqtt
name: "Utilities Bill"
unit_of_measurement: "SGD$"
state_topic: "/utilities/bill"
- platform: mqtt
name: "Electricity Consumption"
unit_of_measurement: "kWh"
state_topic: "/utilities/electricity"
- platform: mqtt
name: "Water Consumption"
unit_of_measurement: "Cu M"
state_topic: "/utilities/water"
- platform: mqtt
name: "Gas Consumption"
unit_of_measurement: "kWh"
state_topic: "/utilities/gas"
- (Optional) Add the sensors to your groups or views.