ESP32 Episode 10 : Data Visualization and Data Logging in ESP32
Hello, hi, ciao, bonjour to the 10th episode of Esther’s ESP32 Journey. This might be the last episode of my blog related to ESP32. Can’t believe that we’ve done so many things on this semester. Never would i have imagined myself spending more or less 3 hours every week compiling and uploading codes in Arduino IDE. I hope my blogs are helpful for those who are studying embedded system especially using ESP32.
Back to topic, today’s topic will be data visualization and data logging in ESP32. Basically, it’s kinda similar to the previous project, the circuit is the same as the previous one. But, of course we’re going to bring the previous project to the next level 📈
Overview
This is the project overview :
Well, I modified a little bit for the deep sleep. On this project, I changed it to 5 minutes so it can displayed the readings every 5 minutes to the Google Sheets. Why? Most of the times, my laptop suddenly doesn’t recognize the USB to UART port.
Hands-on🧑🏻🔧
For this week’s hands on, I’ll be dividing into 2 different parts which is data visualization and data logging 📈📉
Equipments for this week’s episode are :
- Breadboard
- ESP32
- Male to male jumper
- BME280 sensor
- Previous reading of previous episodes (especially Eps 9)
Early Notes : This blog contains a lot of screenshots so ENJOY😚
Data Visualization
Continuing the previous project, we will make a new file on online file manager. I hope you already read my previous episode so I’m assuming all of you have tried hosting a website, creating database, setting up the circuit and so on. On this project, we will make a file, I named it as ‘esp32_chart’. Then, all you need to do is paste this code :
<!--
Rui Santos
Complete project details at https://RandomNerdTutorials.com
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files.
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
-->
<?php
$servername = "localhost";
// REPLACE with your Database name
$dbname = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, value1, value2, value3, reading_time FROM Sensor order by reading_time desc limit 40";
$result = $conn->query($sql);
while ($data = $result->fetch_assoc()){
$sensor_data[] = $data;
}
$readings_time = array_column($sensor_data, 'reading_time');
// ******* Uncomment to convert readings time array to your timezone ********
/*$i = 0;
foreach ($readings_time as $reading){
// Uncomment to set timezone to - 1 hour (you can change 1 to any number)
$readings_time[$i] = date("Y-m-d H:i:s", strtotime("$reading - 1 hours"));
// Uncomment to set timezone to + 4 hours (you can change 4 to any number)
//$readings_time[$i] = date("Y-m-d H:i:s", strtotime("$reading + 4 hours"));
$i += 1;
}*/
$value1 = json_encode(array_reverse(array_column($sensor_data, 'value1')), JSON_NUMERIC_CHECK);
$value2 = json_encode(array_reverse(array_column($sensor_data, 'value2')), JSON_NUMERIC_CHECK);
$value3 = json_encode(array_reverse(array_column($sensor_data, 'value3')), JSON_NUMERIC_CHECK);
$reading_time = json_encode(array_reverse($readings_time), JSON_NUMERIC_CHECK);
/*echo $value1;
echo $value2;
echo $value3;
echo $reading_time;*/
$result->free();
$conn->close();
?>
<!DOCTYPE html>
<html>
<meta name="viewport" content="width=device-width, initial-scale=1">
<script src="https://code.highcharts.com/highcharts.js"></script>
<style>
body {
min-width: 310px;
max-width: 1280px;
height: 500px;
margin: 0 auto;
}
h2 {
font-family: Arial;
font-size: 2.5rem;
text-align: center;
}
</style>
<body>
<h2>ESP Weather Station</h2>
<div id="chart-temperature" class="container"></div>
<div id="chart-humidity" class="container"></div>
<div id="chart-pressure" class="container"></div>
<script>
var value1 = <?php echo $value1; ?>;
var value2 = <?php echo $value2; ?>;
var value3 = <?php echo $value3; ?>;
var reading_time = <?php echo $reading_time; ?>;
var chartT = new Highcharts.Chart({
chart:{ renderTo : 'chart-temperature' },
title: { text: 'BME280 Temperature' },
series: [{
showInLegend: false,
data: value1
}],
plotOptions: {
line: { animation: false,
dataLabels: { enabled: true }
},
series: { color: '#059e8a' }
},
xAxis: {
type: 'datetime',
categories: reading_time
},
yAxis: {
title: { text: 'Temperature (Celsius)' }
//title: { text: 'Temperature (Fahrenheit)' }
},
credits: { enabled: false }
});
var chartH = new Highcharts.Chart({
chart:{ renderTo:'chart-humidity' },
title: { text: 'BME280 Humidity' },
series: [{
showInLegend: false,
data: value2
}],
plotOptions: {
line: { animation: false,
dataLabels: { enabled: true }
}
},
xAxis: {
type: 'datetime',
//dateTimeLabelFormats: { second: '%H:%M:%S' },
categories: reading_time
},
yAxis: {
title: { text: 'Humidity (%)' }
},
credits: { enabled: false }
});
var chartP = new Highcharts.Chart({
chart:{ renderTo:'chart-pressure' },
title: { text: 'BME280 Pressure' },
series: [{
showInLegend: false,
data: value3
}],
plotOptions: {
line: { animation: false,
dataLabels: { enabled: true }
},
series: { color: '#18009c' }
},
xAxis: {
type: 'datetime',
categories: reading_time
},
yAxis: {
title: { text: 'Pressure (hPa)' }
},
credits: { enabled: false }
});
</script>
</body>
</html>
I won’t stop reminding u guys for billions of times, don’t forget to change dbname, username, and password.
Don’t know what’s wrong, but it when I tried to access https://estheresp32.000webhostapp.com/esp32_chart.php . It shows something like this.
It says there was an error on line 34. I tried to check the code and it turns out that I haven’t changed the name of table that I’m using hehe. I forgot to change the code I copied from randomnerdtutorial. On randomnerdtutorial, they create a table named Sensor. Meanwhile, I created a table named SensorData.
$sql = "SELECT id, value1, value2, value3, reading_time FROM SensorData order by reading_time desc limit 40";After that, I tried accessing https://estheresp32.000webhostapp.com/esp32_chart.php again. And it shows 3 different charts based on my BME280 sensor readings.
Well, it’s pretty cool! By displaying the readings into chart, I think it’s more pleasing for eye and easier to analyze the data.
Data Logging
On this part, we will learn how to publish sensor readings to Google Sheets using ESP32. How can we do that? Basically, we’re going to use a third party service (IFTTT) to integrate ESP32 and Google sheets.
The first thing I’m going to do is to create an account on IFTTT. Don’t worry, it’s free😊
The next thing we need to do is to create an applet. Go to my applets, then click the create your own button
Click the add button beside ‘if this’ text
After that you’ll be directed to a page where you will choose a service. Search for webhooks on the searchbar and click the webhooks icon.
Then, choose the receive a web request icon
Press the connect button
Then type your event name and press the create trigger button
After that, click the add button beside “then that” text
Then, search for Google Sheets service on the search bar and click the Google Sheets icon
Afterwards, click the add row to spreadsheet icon
Click connect to link with the Google Sheets
Choose the account you want to connect
If you want, you can change your google sheets account, spreadsheet name, drive folder path. If you’re done, click create action
Click continue
Click Finish
The applet should be created. To check it, you can go to https://ifttt.com/maker_webhooks and click Documentation. On that page, you will see your unique API Key.
Don’t forget to change the event name before you click the test it icon
Afterwards, go to Google Drive. A new folder should be created and inside that folder there will be a Google Sheets
For the circuit, please follow this schematics (the same one as the one in eps 9)
Then compile and upload this code on Arduino IDE
/*
* Rui Santos
* Complete Project Details http://randomnerdtutorials.com
*/
#ifdef ESP32
#include <WiFi.h>
#else
#include <ESP8266WiFi.h>
#endif
#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.h>
// Replace with your SSID and Password
const char* ssid = "REPLACE_WITH_YOUR_SSID";
const char* password = "REPLACE_WITH_YOUR_PASSWORD";
// Replace with your unique IFTTT URL resource
const char* resource = "REPLACE_WITH_YOUR_IFTTT_URL_RESOURCE";
// How your resource variable should look like, but with your own API KEY (that API KEY below is just an example):
//const char* resource = "/trigger/bme280_readings/with/key/nAZjOphL3d-ZO4N3k64-1A7gTlNSrxMJdmqy3";
// Maker Webhooks IFTTT
const char* server = "maker.ifttt.com";
// Time to sleep
uint64_t uS_TO_S_FACTOR = 1000000; // Conversion factor for micro seconds to seconds
// sleep for 30 minutes = 1800 seconds
uint64_t TIME_TO_SLEEP = 1800;
// Uncomment to use BME280 SPI
/*#include <SPI.h>
#define BME_SCK 13
#define BME_MISO 12
#define BME_MOSI 11
#define BME_CS 10*/
#define SEALEVELPRESSURE_HPA (1013.25)
Adafruit_BME280 bme; // I2C
//Adafruit_BME280 bme(BME_CS); // hardware SPI
//Adafruit_BME280 bme(BME_CS, BME_MOSI, BME_MISO, BME_SCK); // software SPI
void setup() {
Serial.begin(115200);
delay(2000);
// initialize BME280 sensor
bool status;
status = bme.begin(0x76);
if (!status) {
Serial.println("Could not find a valid BME280 sensor, check wiring!");
while (1);
}
initWifi();
makeIFTTTRequest();
#ifdef ESP32
// enable timer deep sleep
esp_sleep_enable_timer_wakeup(TIME_TO_SLEEP * uS_TO_S_FACTOR);
Serial.println("Going to sleep now");
// start deep sleep for 3600 seconds (60 minutes)
esp_deep_sleep_start();
#else
// Deep sleep mode for 3600 seconds (60 minutes)
Serial.println("Going to sleep now");
ESP.deepSleep(TIME_TO_SLEEP * uS_TO_S_FACTOR);
#endif
}
void loop() {
// sleeping so wont get here
}
// Establish a Wi-Fi connection with your router
void initWifi() {
Serial.print("Connecting to: ");
Serial.print(ssid);
WiFi.begin(ssid, password);
int timeout = 10 * 4; // 10 seconds
while(WiFi.status() != WL_CONNECTED && (timeout-- > 0)) {
delay(250);
Serial.print(".");
}
Serial.println("");
if(WiFi.status() != WL_CONNECTED) {
Serial.println("Failed to connect, going back to sleep");
}
Serial.print("WiFi connected in: ");
Serial.print(millis());
Serial.print(", IP address: ");
Serial.println(WiFi.localIP());
}
// Make an HTTP request to the IFTTT web service
void makeIFTTTRequest() {
Serial.print("Connecting to ");
Serial.print(server);
WiFiClient client;
int retries = 5;
while(!!!client.connect(server, 80) && (retries-- > 0)) {
Serial.print(".");
}
Serial.println();
if(!!!client.connected()) {
Serial.println("Failed to connect...");
}
Serial.print("Request resource: ");
Serial.println(resource);
// Temperature in Celsius
String jsonObject = String("{\"value1\":\"") + bme.readTemperature() + "\",\"value2\":\"" + (bme.readPressure()/100.0F)
+ "\",\"value3\":\"" + bme.readHumidity() + "\"}";
// Comment the previous line and uncomment the next line to publish temperature readings in Fahrenheit
/*String jsonObject = String("{\"value1\":\"") + (1.8 * bme.readTemperature() + 32) + "\",\"value2\":\""
+ (bme.readPressure()/100.0F) + "\",\"value3\":\"" + bme.readHumidity() + "\"}";*/
client.println(String("POST ") + resource + " HTTP/1.1");
client.println(String("Host: ") + server);
client.println("Connection: close\r\nContent-Type: application/json");
client.print("Content-Length: ");
client.println(jsonObject.length());
client.println();
client.println(jsonObject);
int timeout = 5 * 10; // 5 seconds
while(!!!client.available() && (timeout-- > 0)){
delay(100);
}
if(!!!client.available()) {
Serial.println("No response...");
}
while(client.available()){
Serial.write(client.read());
}
Serial.println("\nclosing connection");
client.stop();
}I compiled and uploaded the code. the serial monitor shows no sign of error. But, when I checked my Google Sheets, it displays nothing. I was confused why that kind of thing happened. Then, I realized that I was pasting the wrong resource🥲
I pasted /trigger/esther_bme280_readings/json/with/key/{apikey} while it must be /trigger/esther_bme280_readings/with/key/{apikey}
Waiting for 30 minutes is quite long and I’m doing this at 2 a.m in the morning so I’m kinda sleepy right now🥱.. So, i changed the time to sleep to 5 minutes (change the code to 300)… Change this part of the code👇🏻 (the TIME_TO_SLEEP variable)
Then, I check my google sheets. After putting the correct IFTTT URL Resource, the Google Sheets display my bme280 sensor readings :
So so so sorry for the first 11 error rows 😢
Well, that’s it for today’s blog. As this might be my last ESP32 related blog, I want to express my gratitude for those who have been reading this amateur writings. I’m so so so sorry for the low quality pictures, low quality writings, and low quality explanations. It’s not an easy journey as I’m studying Arduino IDE from scratch but it’s an amazing journey. I’m sorry for all the silly errors that I’ve made which causes confusion😵💫.. I’m sorry that I don’t use all of the components that I’ve listed in my first episode (well the only thing I didn’t use is oled, but maybe I’ll try using it on another occasions).
Thank u and goodbye!
Esther Regina
18221086
References :