The short instructions to create end-to-end IoT monitoring system.
The central place to store time-series data (sensor data = timestamp, temperature, humidity, uv index). Details worksheet will be filled up with the sensor data.
onChange script will be triggered whenever new row is created. Temperature,Humidity and UV Index worksheet will be updated based on new sensor data. 3 charts are also re-published whenever onChange is triggered.
onChange Trigger
function onChange(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Details");
var sheetT = ss.getSheetByName("Temperature"); // sheetT, sheetH, sheetU are used by glide tab
...
// 1. Sort descending by column A. New entry will be moved to the top (Row2). 3 charts will be re-generated with Row2-Row9.
var range = sheet.getRange("A2:E");
range.sort({column: 1, ascending:false});
// 2. extract the latest values
// getRange(row, column, optNumRows, optNumColumns)
var rowRange = sheet.getRange(2, 1, 1, 5).getValues()[0];
var lastUpdated = new Date(rowRange[0]).toLocaleString(); // timestamp -> date/time
var currTemperature = rowRange[2];
var currHumidity = rowRange[3];
var currUvIndex = rowRange[4];
// 3. ranndom string for images
var randomId = new Date(rowRange[0]).toISOString(); // use timestamp instead. convert it to ISO format.
// 4. update 'Temperature', 'Humidity` and 'UV index' worksheets
// 4.1 Temperature Sheet
// example : Single cell editing
/*
var cell = sheet.getRange("C2");
cell.setValue(currTemperature+5);
*/
var valuesT = [[
lastUpdated,
currTemperature,
// URL : <<chart-path>>&format=image&update=<<randomId>>
"https://docs.google.com/spreadsheets/d/e/<<Your-Published-Chart>>&format=image&update="+randomId
]];
var range = sheetT.getRange("A2:C2");
range.setValues(valuesT);
// 4.2 Humidity Sheet
...
// 4.3 UV Index Sheet
...
// TODO : High-Water Mark Treatment
var colA = sheet.getRange("A1:A").getValues();
var colAlast = colA.filter(String).length; // non-empty row
Logger.log(colAlast);
// if ( colAlast >= highwaterMark ) delete oldest X rows
}
From Script Editor, select Edit menu and go to Current project's triggers to add onChange trigger.
- Create a Zappier Zap with a Catch Hook (WebHook).
- Add one action using the data retrieved from the webhook.
- Set Create Spreadsheet Row to store the sensor data.
- Edit Template to map Google Spreadsheet/Worksheet with the sensor data (JSON Keys).
NOTE : Zapier(Live Update) is Disabled
Please watch tutorials to learn more about Glide.
Only Temperature, Humidiy & UV Index worksheets are appeared. Details worksheet are for time-series data storage.
URL for HTTP request node can be found from Zapier Catch Hook > View WebHook. Simulate (every 5 mins) the sensor data with Node-RED for this example. Alternatively create MQTT subscriber (+ rules) and post meaningful data over Zapier Webhook.
