Alright — time for the slightly nerdy, very satisfying bit.
This is where the sensors stop being lonely hardware and start sending real data to the cloud.
No smoke. No drama. Just packets, spreadsheets, and the occasional garbage value that makes you laugh and cry.
The simple goal — in one line
Read water level. Send it. Get an alert if things go wrong.
What I stitch together here
- Arduino MKR WiFi 1010 — the brain and Wi-Fi radio.
- TDK Eval board + USSM sensors — they do distance sensing.
- Google Sheets — the datastore and quick dashboard.
- Google Apps Script — the tiny web API that accepts posts and emails alerts.
Think: sensor → MKR → HTTPS POST → Apps Script → Sheet → emails.
That chain is the whole system.
Why Google Sheets? (Spoiler: Because it’s fast)
Sheets is free, easy, and everyone knows it.
You don’t need a server or a database yet.
For a prototype and a competition demo, it’s perfect.
Later we can swap it for a proper backend. But right now — speed wins.
Server-side: Apps Script doPost
On the Google side, doPost(e) checks:
- JSON parse ok?
- api_key matches?
- value is numeric?
- alert_requested true AND value compared vs THRESHOLD_VALUE in the sheet?
If conditions match, it sends email(s) and writes Alert Sent @ TIMESTAMP into the DataLog row.
Create a google sheet "TDK-MKR-Logger" with 3 tabs.
tab1 named "ConfigDetails"
Tab 2 "Email_list"
Tab 3 "DataLog"
Click on Extensions and select Apps Script
Copy and Paste my Code into it.
/**
* Google Apps Script for "TDK-MKR-Logger" Google Sheet
*
* Sheets expected:
* 1) "ConfigDetails" - columns: key | Value | Description
* - keys expected: THRESHOLD_VALUE, POST_INTERVAL_MS
* 2) "Email_list" - column A header "Email Addresses" followed by emails
* 3) "DataLog" - columns: Timestamp | Sensor Name | Value | Alert Sent
*
* doPost(e): Accepts JSON payload and appends row to DataLog
* Payload example:
* { "sensor_name": "MKR_UltraSim_01", "value": 12.3, "alert_requested": true }
*
* doGet(e): Returns JSON config: { "THRESHOLD_VALUE": 15, "POST_INTERVAL_MS": 10000 }
*
* Notes:
* - This script uses MailApp to send emails (requires permission).
* - Deploy as Web App and set "Execute as: Me" so MailApp runs under your account.
*/
const SHEET_NAME = 'TDK-MKR-Logger';
const TAB_CONFIG = 'ConfigDetails';
const TAB_EMAILS = 'Email_list';
const TAB_DATALOG = 'DataLog';
// MAIN - POST handler
function doPost(e) {
try {
if (!e || !e.postData || !e.postData.contents) {
return jsonResponse({ error: 'No POST data received' }, 400);
}
// parse JSON body
let payload;
try {
payload = JSON.parse(e.postData.contents);
} catch (err) {
return jsonResponse({ error: 'Invalid JSON', details: err.toString() }, 400);
}
const sensorName = (payload.sensor_name || payload.sensor || 'UnknownSensor').toString();
// coerce numeric
const value = parseFloat(payload.value);
const alertRequested = Boolean(payload.alert_requested || payload.alert || false);
if (isNaN(value)) {
return jsonResponse({ error: 'Value must be a number' }, 400);
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
if (!ss) {
return jsonResponse({ error: 'Spreadsheet not found or not opened' }, 500);
}
const configSheet = ss.getSheetByName(TAB_CONFIG);
const emailSheet = ss.getSheetByName(TAB_EMAILS);
const dataSheet = ss.getSheetByName(TAB_DATALOG);
if (!configSheet || !emailSheet || !dataSheet) {
return jsonResponse({ error: 'One or more expected tabs not found', requiredTabs: [TAB_CONFIG, TAB_EMAILS, TAB_DATALOG] }, 500);
}
// read config values
const config = readConfig(configSheet);
const threshold = Number(config.THRESHOLD_VALUE);
// fallback if config missing or invalid
const THRESHOLD_VALUE = isFinite(threshold) ? threshold : 15;
const nowIso = (new Date()).toISOString();
// prepare row: Timestamp | Sensor Name | Value | Alert Sent
let alertSentText = '';
const newRow = [ nowIso, sensorName, value, '' ];
dataSheet.appendRow(newRow);
const lastRow = dataSheet.getLastRow();
// If alert requested AND value is below threshold (per your spec "lesser than threshold triggers alert")
if (alertRequested && value < THRESHOLD_VALUE) {
// read emails
const emails = readEmails(emailSheet);
if (emails.length > 0) {
const subject = `ALERT: ${sensorName} reading ${value} < ${THRESHOLD_VALUE}`;
const body = [
`Sensor: ${sensorName}`,
`Value: ${value}`,
`Threshold: ${THRESHOLD_VALUE}`,
`Timestamp: ${nowIso}`,
'',
'This alert was generated by the TDK-MKR-Logger Google Apps Script.'
].join('\n');
// send to each email (MailApp.sendEmail handles comma-separated list too)
try {
MailApp.sendEmail(emails.join(','), subject, body);
alertSentText = `Alert Sent @ ${nowIso}`;
// write alert info into the Alert Sent column (4th column)
dataSheet.getRange(lastRow, 4).setValue(alertSentText);
} catch (mailErr) {
// email failed - log error into sheet in Alert Sent column
const errText = `Alert FAILED @ ${nowIso} -- ${mailErr.toString()}`;
dataSheet.getRange(lastRow, 4).setValue(errText);
// return success with warning about email failure
return jsonResponse({ status: 'ok', message: 'row appended, email failed', error: mailErr.toString() }, 200);
}
} else {
// no emails found - put note in Alert Sent
const note = `Alert requested but no email addresses found @ ${nowIso}`;
dataSheet.getRange(lastRow, 4).setValue(note);
}
}
// Successful append (and maybe alert)
return jsonResponse({ status: 'ok', row: lastRow, alert: alertSentText || null, config: config }, 200);
} catch (err) {
return jsonResponse({ error: 'Unhandled exception', details: err.toString() }, 500);
}
}
// GET handler - returns config as JSON
function doGet(e) {
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const configSheet = ss.getSheetByName(TAB_CONFIG);
if (!configSheet) {
return jsonResponse({ error: 'ConfigDetails sheet not found' }, 500);
}
const config = readConfig(configSheet);
// ensure numeric where appropriate
if (config.POST_INTERVAL_MS) config.POST_INTERVAL_MS = Number(config.POST_INTERVAL_MS);
if (config.THRESHOLD_VALUE) config.THRESHOLD_VALUE = Number(config.THRESHOLD_VALUE);
return jsonResponse(config, 200);
} catch (err) {
return jsonResponse({ error: 'Unhandled exception', details: err.toString() }, 500);
}
}
/* ================= Helper functions ================= */
function readConfig(sheet) {
// Expecting two columns: key (A) | Value (B)
// Read all data and build a simple key->value object
const values = sheet.getDataRange().getValues();
const config = {};
for (let i = 1; i < values.length; i++) { // skip header row assumed at row 1
const row = values[i];
const key = (row[0] || '').toString().trim();
const val = (row[1] !== undefined && row[1] !== null) ? row[1] : '';
if (key) config[key] = val;
}
return config;
}
function readEmails(sheet) {
// Read column A (skip header); filter empty and validate basic email format
const col = sheet.getRange(2, 1, Math.max(0, sheet.getLastRow() - 1), 1).getValues();
const emails = [];
for (let i = 0; i < col.length; i++) {
const v = (col[i][0] || '').toString().trim();
if (v) {
// Allow comma-separated addresses in single cell too; split and trim
const parts = v.split(',');
parts.forEach(p => {
const e = p.trim();
if (e && e.indexOf('@') > -1) emails.push(e);
});
}
}
// dedupe simple
return Array.from(new Set(emails));
}
function jsonResponse(obj, statusCode) {
// Apps Script ContentService doesn't allow setting HTTP status codes directly to callers,
// but when deployed as Web App it will return 200 unless an exception is thrown.
// We'll return a JSON wrapper with a code field so the device can inspect success/failure.
const wrapper = Object.assign({}, obj, { _code: statusCode || 200 });
const json = JSON.stringify(wrapper);
return ContentService.createTextOutput(json).setMimeType(ContentService.MimeType.JSON);
}
Click on Deploy
Select New Deployment> in description type any thing you want >in Who has access select anyone >Click Deploy > from there a web URL will be created. Copy it and save for next part of arduino mkr 1010 Programming.
In next Blog we will discuss the hardware part.