N8N Tempest Weather Dashboard with Looker

While I’ve loved IFTTT and Zapier, their pricing structures have swung towards enterprises that can look the other way for $100 SaaS charges in order to avoid maintaining the code behind a large number of small integrations. The low-code tool space is thriving, so thankfully an open-source self-managed option is now maturing. I spent some time in the quieter moments of the 2023 holiday checking out N8N. Unlike it’s cloud-only competitors it has a healthy library of integrations towards cybersecurity tools, databases, and even a native home assistant app - all connections that lean heavily towards my DIY needs.

Hosting N8N took only a few moments as I have a stable home server on my Intel NUC.

A non-volatile place to put the data and the locally hosted SQL Lite database

docker volume create n8n_data

The docker run command with environment variables needed for callback URLs. I run in a subdomain behind my reverse proxy.

docker run -d \
 --name n8n \
 -p 5678:5678 \
 -e GENERIC_TIMEZONE="America/New_York" \
 -e TZ="America/New_York" \
 -e VUE_APP_URL_BASE_API=https://n8n.example.com/ \
 -e WEBHOOK_URL=https://n8n.example.com/ \
 -v n8n_data:/home/node/.n8n \
 --restart unless-stopped \
 docker.n8n.io/n8nio/n8n

And with that we are self-hosting.

Self Hosted

For a first integration, I rebuilt something I had working a while ago in pure python. Getting this together took much less time and, with the benefit experience, should be eaiser to maintain in the long run. My partner likes to garden, and one of the tasks she has every day is to figure out how much water to put in the garden based on the last three days of rainfall. I already have a local weather station in my back yard which can be queried with a REST call. The new automation will work like this:

N8N Workflow

1. N8N will trigger a daily timer and pull the REST call, which contains a field for the previous day’s rainfall in cm.
2. N8N will log previous day’s information in inches of rain to a google spreadsheet
3. A Looker Studio dashboard shared with my partner will query the spreadsheet and report the last 3 days rain fall.

Here’s what that workflow looks like in n8n.

N8N Workflow

The code block in the middle is a bit of python to do unit conversions and create a field for the previous day’s date.

from datetime import datetime, timedelta

for item in _input.all():
    del item.json.outdoor_keys, item.json.public_name, item.json.station_id, item.json.station_name, item.json.station_units, item.json.is_public, item.json.status
    c = item.json.obs[0].air_temperature
    cm = item.json.obs[0].precip_accum_local_yesterday_final
    item.json.air_temperature_f = (c * 9.0 / 5.0) + 32.0
    item.json.precip_accum_local_yesterday_final_in = cm / 2.54
    current_date = datetime.now()
    yesterday_date = current_date - timedelta(days=1)
    item.json.timestamp = yesterday_date.strftime("%m/%d/%Y")
return _input.all()

I write to a simple spreadsheet, but I’ve learned to always pre-process data in a SQL query view of the original data, which makes quick edits and data cleanup much easier and future proof in case I need a layer of indirection. In this case I only want the most recent 3 days of data … and I can do that with a SQL LIMIT more reliably than anything I found in Looker for some reason.

a simple view in Google Spreadsheets

The last step is creating a portrait formatted report in Looker Studio, which connects to spreadsheet data sources very easily without having to delegate data permissions. The portrait formatting will make things look reasonable on my partner’s mobile device.

A simple portrait report - home address redacted