HSRW EOLab Students Wiki

User Tools

Site Tools

Trace:
amc:ss2025:group-f:start

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
amc:ss2025:group-f:start [2025/07/28 20:21] – [3.6 Google Apps Script and Google sheets] 33784_students.hsrwamc:ss2025:group-f:start [2025/07/29 22:36] (current) – paul-christian.thoma
Line 1: Line 1:
 ======  Smart Plant Watering with ESP32 and MQTT====== ======  Smart Plant Watering with ESP32 and MQTT======
 +**Applied Measurement and Control 2025**
 +
 Paul-Christian Thoma(32436)-Elham Mohammadi(32475)-Deniz-Zeynep Adem(33784) Paul-Christian Thoma(32436)-Elham Mohammadi(32475)-Deniz-Zeynep Adem(33784)
  
Line 16: Line 18:
 By combining local sensing, cloud-based logic, and remote monitoring tools, this system provides a scalable, modular approach to smart irrigation in home or laboratory environments. By combining local sensing, cloud-based logic, and remote monitoring tools, this system provides a scalable, modular approach to smart irrigation in home or laboratory environments.
  
-{{ amc:ss2025:group-f:prototype2.jpeg |}}+{{ :amc:ss2025:group-f:prototype2.jpeg?direct&600 |{{ amc:ss2025:group-f:prototype2.jpeg |}}}}
 **Figure 1.** Automated Plant Watering System Prototype **Figure 1.** Automated Plant Watering System Prototype
  
Line 123: Line 125:
 The following diagrams, created using Tinkercad, illustrate the complete hardware connections for both the Sensor and Pump nodes in the system.  The following diagrams, created using Tinkercad, illustrate the complete hardware connections for both the Sensor and Pump nodes in the system. 
  
-{{ amc:ss2025:group-f:moisture_sensor_circuit.png |}}+{{ :amc:ss2025:group-f:moisture_sensor_circuit.png?direct&600 |}}
 **Figure 2.** Schematic Diagram of Sensor Setup (designed by Deniz-Zeynep Adem) **Figure 2.** Schematic Diagram of Sensor Setup (designed by Deniz-Zeynep Adem)
                                      
  
-{{ amc:ss2025:group-f:pump_wiring_with_adaptor.png |}}+{{ :amc:ss2025:group-f:pump_wiring_with_adaptor.png?direct&600 |}}
 **Figure 3.** Schematic Diagram of Pump Setup (designed by Deniz-Zeynep Adem) **Figure 3.** Schematic Diagram of Pump Setup (designed by Deniz-Zeynep Adem)
  
Line 138: Line 140:
  
  **Sensor Node Code:**  **Sensor Node Code:**
 +
 <code cpp> <code cpp>
 #include <WiFi.h>              // Library for WiFi connection #include <WiFi.h>              // Library for WiFi connection
Line 151: Line 154:
  
 // Google Apps Script webhook URL for sending data to Google Sheets // Google Apps Script webhook URL for sending data to Google Sheets
-const char GOOGLE_SHEET_URL[] = "https://script.google.com/macros/s/XYZ/exec"; // Replace XYZ with your actual script ID+const char GOOGLE_SHEET_URL[] = "https://script.google.com/macros/s/AKfycbwWx8W6ov2rufz6ODUtUngPOhJMdYazwkHozmzk8-3n6gyL-qNCGgX9YxKtBTL7Qm5Vfg/exec";
  
 // MQTT Topics // MQTT Topics
Line 185: Line 188:
  
   // Format data as JSON payload   // Format data as JSON payload
-  String jsonPayload = "{\"device\":\"ESP32_Sensor_001\",\"event\":\"read\",\"moisture\":\"" + String(moisture) + "\",\"status\":\"" + status + "\"}";+  String action; 
 +  if (status == "Dry") { 
 +    action = "Pump is ON 💧"; 
 +  } else if (status == "Moist") { 
 +    action = "OK 👍"; 
 +  } else { 
 +    action = "Alert ⚠️ Maintenance Needed"; 
 +  } 
 + 
 +  String jsonPayload = "{\"moisture\":\"" + String(moisture) + 
 +                       "\",\"status\":\"" + status + 
 +                       "\",\"action\":\"" + action + "\"}"; 
   Serial.println("POSTing: " + jsonPayload);   Serial.println("POSTing: " + jsonPayload);
  
Line 213: Line 228:
   if (millis() - lastRead > readInterval) {   if (millis() - lastRead > readInterval) {
     int value = analogRead(sensorPin);               // Read analog moisture value     int value = analogRead(sensorPin);               // Read analog moisture value
-    String status = value > threshold ? "Dry" : "Moist"; // Determine status+    String status = value > threshold ? "Dry ☹️" : "Moist 😊"; // Determine status
  
     // Publish raw value and status to MQTT topics     // Publish raw value and status to MQTT topics
Line 229: Line 244:
  
     // If soil is dry, send water command     // If soil is dry, send water command
-    if (status == "Dry") {+    if (status.startsWith("Dry")) {
       mqtt.publish(COMMAND_TOPIC, "WATER"); // Triggers pump       mqtt.publish(COMMAND_TOPIC, "WATER"); // Triggers pump
     }     }
Line 246: Line 261:
 #include <WiFi.h>              // WiFi library for ESP32 #include <WiFi.h>              // WiFi library for ESP32
 #include <PubSubClient.h>      // MQTT library #include <PubSubClient.h>      // MQTT library
-#include <Servo.h>             // Library for controlling servo motor+#include <Servo.h>             // For controlling the servo pump 
 +#include <HTTPClient.h>        // For HTTP POST to Google Sheets
  
-// WiFi credentials +// Wi-Fi credentials 
-const char* ssid = "YOUR_WIFI_SSID"; +const char* ssid = "iotlab"; 
-const char* password = "YOUR_WIFI_PASSWORD";+const char* password = "iotlab18";
  
-// MQTT broker address+// MQTT broker
 const char* mqtt_server = "broker.hivemq.com"; const char* mqtt_server = "broker.hivemq.com";
 +const char* control_topic = "plant/pump/control";
  
-WiFiClient espClient;          // WiFi client +// Google Apps Script Web App URL 
-PubSubClient client(espClient); // MQTT client+const char* google_script_url = "https://script.google.com/macros/s/AKfycbwWx8W6ov2rufz6ODUtUngPOhJMdYazwkHozmzk8-3n6gyL-qNCGgX9YxKtBTL7Qm5Vfg/exec";
  
-Servo pumpServo;               // Servo object to control the pump +WiFiClient espClient; 
-const int servoPin = 5;        // Pin connected to the servo+PubSubClient client(espClient); 
 + 
 +Servo pumpServo; 
 +const int servoPin = 5;
  
-// Function to connect to WiFi 
 void setup_wifi() { void setup_wifi() {
 +  Serial.print("Connecting to WiFi");
   WiFi.begin(ssid, password);   WiFi.begin(ssid, password);
   while (WiFi.status() != WL_CONNECTED) {   while (WiFi.status() != WL_CONNECTED) {
Line 271: Line 291:
 } }
  
-// Callback function that handles incoming MQTT messages+void postToGoogleSheet(const String& status, const String& action) { 
 +  if (WiFi.status() == WL_CONNECTED) { 
 +    HTTPClient http; 
 +    http.begin(google_script_url); 
 +    http.addHeader("Content-Type", "application/json"); 
 + 
 +    // Build JSON payload 
 +    String payload = "{\"status\":\"" + status + "\",\"moisture\":\"-\",\"action\":\"" + action + "\"}"; 
 + 
 +    int httpResponseCode = http.POST(payload); 
 +    Serial.print("Google Sheet POST response: "); 
 +    Serial.println(httpResponseCode); 
 +    http.end(); 
 +  } else { 
 +    Serial.println("WiFi not connected. Could not send to Google Sheets."); 
 +  } 
 +} 
 void callback(char* topic, byte* payload, unsigned int length) { void callback(char* topic, byte* payload, unsigned int length) {
-  String msg = "";  // String to hold message +  String msg = ""; 
-  for (int i = 0; i < length; i++) msg += (char)payload[i]; +  for (unsigned int i = 0; i < length; i++) { 
-  msg.trim(); // Remove any trailing whitespace or newline+    msg += (char)payload[i]; 
 +  } 
 +  msg.trim();
  
-  Serial.print("Received on ");+  Serial.print("Message received [");
   Serial.print(topic);   Serial.print(topic);
-  Serial.print(": ");+  Serial.print("]: ");
   Serial.println(msg);   Serial.println(msg);
  
-  // Check if the topic is the one for pump control +  if (String(topic) == control_topic) { 
-  if (String(topic) == "pump/control") { +    if (msg == "WATER" || msg == "ON") { 
-    if (msg == "ON") { +      pumpServo.write(180); 
-      pumpServo.write(180); // Move servo to ON position+      Serial.println("Pump is ON 💧"); 
 +      postToGoogleSheet("Dry ☹️", "Pump is ON");
     } else if (msg == "OFF") {     } else if (msg == "OFF") {
-      pumpServo.write(90);  // Move servo to OFF position+      pumpServo.write(90); 
 +      Serial.println("Pump is OFF ⛔"); 
 +      postToGoogleSheet("Moist 🙂", "Pump is OFF");
     }     }
   }   }
 } }
  
-// Function to (re)connect to MQTT and subscribe to topic 
 void reconnect() { void reconnect() {
   while (!client.connected()) {   while (!client.connected()) {
-    Serial.print("Connecting to MQTT..."); +    Serial.print("Attempting MQTT connection..."); 
-    if (client.connect("ESP32Pump")) {+    if (client.connect("ESP32_Pump_Node")) {
       Serial.println("connected");       Serial.println("connected");
-      client.subscribe("pump/control"); // Subscribe to control topic+      client.subscribe(control_topic);
     } else {     } else {
       Serial.print("failed, rc=");       Serial.print("failed, rc=");
-      Serial.println(client.state()); +      Serial.print(client.state()); 
-      delay(2000); // Retry every 2 seconds+      delay(2000);
     }     }
   }   }
Line 308: Line 349:
  
 void setup() { void setup() {
-  Serial.begin(115200);       // Start serial monitor +  Serial.begin(115200); 
-  setup_wifi();               // Connect to WiFi +  setup_wifi(); 
-  pumpServo.attach(servoPin); // Attach servo to the pin +  pumpServo.attach(servoPin); 
-  pumpServo.write(90);        // Set initial servo position (OFF) +  pumpServo.write(90); // Default OFF position 
- +  client.setServer(mqtt_server, 1883); 
-  client.setServer(mqtt_server, 1883);   // Set MQTT broker +  client.setCallback(callback);
-  client.setCallback(callback);         // Set message handler+
 } }
  
 void loop() { void loop() {
-  if (!client.connected()) reconnect(); // Reconnect if disconnected +  if (!client.connected()) { 
-  client.loop();                        // Process incoming MQTT messages+    reconnect(); 
 +  } 
 +  client.loop();
 } }
 </code> </code>
Line 409: Line 451:
  
  
-Step-by-Step Guide to Connect ESP32 to Google Sheets+ 
 +The saved data can be accessed through this link: 
 + 
 +''https://docs.google.com/spreadsheets/d/1N0_HnzShdQWwzD-0LYbjcm9xt-VcR1N9Uz19G0Y0euU/edit?usp=sharing'' 
 + 
 + 
 + 
 + 
 +//**Step-by-Step Guide to Connect ESP32 to Google Sheets**//
  
 **1. Create a Google Sheet** **1. Create a Google Sheet**
  
   * Open [Google Sheets](https://sheets.google.com) and create a new sheet.   * Open [Google Sheets](https://sheets.google.com) and create a new sheet.
-  * Name the sheet something like **"Soil Moisture Log"**.+  * Name the sheet something like **"Automated Plant Watering Data"**.
   * Rename the first sheet tab to **"Sheet1"** (or match it with the script).   * Rename the first sheet tab to **"Sheet1"** (or match it with the script).
   * Add column headers in Row 1:   * Add column headers in Row 1:
Line 448: Line 498:
 **4. Integrate the Web App URL into Your ESP32 Code** **4. Integrate the Web App URL into Your ESP32 Code**
  
-In your ESP32 Arduino code, update the `GOOGLE_SHEET_URL`:+In your ESP32 Arduino code, update the `GOOGLE_SHEET_URL` with the copied URL from your own project:
  
 '' ''
Line 457: Line 507:
 '' ''
  
- //Don't forget to replace `"your_script_id"` with the full Web App URL you copied!// 
  
  
 **5. Upload and Run the ESP32 Code** **5. Upload and Run the ESP32 Code**
  
-Upload your finalized ESP32 code via Arduino IDE or PlatformIO.+Upload your finalized ESP32 code via Arduino IDE.
  
   * **The ESP32 will:**   * **The ESP32 will:**
Line 485: Line 534:
 **Google Apps Script Codes:** **Google Apps Script Codes:**
  
 +<code cpp> 
 +function doPost(e) {
   try {   try {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");     const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
     const data = JSON.parse(e.postData.contents);     const data = JSON.parse(e.postData.contents);
 +
     const status = data.status || "";     const status = data.status || "";
     const moisture = data.moisture || "";     const moisture = data.moisture || "";
     const action = data.action || "";     const action = data.action || "";
     const timestamp = new Date();     const timestamp = new Date();
-    // Insert new row at top (after header)+ 
 +    // Add new data to top (2nd row)
     sheet.insertRows(2, 1);     sheet.insertRows(2, 1);
-    sheet.getRange(2, 1).setValue(timestamp);   // Column A: Timestamp +    sheet.getRange(2, 1).setValue(timestamp); 
-    sheet.getRange(2, 2).setValue(action);      // Column B: Action (e.g., Pump is ON) +    sheet.getRange(2, 2).setValue(action); 
-    sheet.getRange(2, 3).setValue(moisture);    // Column C: Moisture value +    sheet.getRange(2, 3).setValue(moisture); 
-    sheet.getRange(2, 4).setValue(status);      // Column D: Status (e.g., Moist 😊) +    sheet.getRange(2, 4).setValue(status); 
-    // Optional: Send email alert when there's a maintenance alert+ 
 +    // Send emails if there is an alert
     if (action.toLowerCase().includes("alert")) {     if (action.toLowerCase().includes("alert")) {
-      const emailAddress = "youremail@example.com"; // Replace with your email +      MailApp.sendEmail("youremail@example.com", "⚠️ Plant Alert",  
-      const subject = "⚠️ Plant System Alert"; +        `Soil Alert!\nStatus: ${status}\nMoisture: ${moisture}\nAction: ${action}`);
-      const message = `An alert has been logged in your system:\n\n` + +
-                      `Status: ${status}\nMoisture: ${moisture}\nAction: ${action}\nTime: ${timestamp}`; +
-      MailApp.sendEmail(emailAddress, subject, message);+
     }     }
 +
     return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);     return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);
-  } catch (error) { +  } catch (err) { 
-    Logger.log("Error: " + error); +    return ContentService.createTextOutput("Error: " + err)
-    return ContentService.createTextOutput("Error: " + error)+
                          .setMimeType(ContentService.MimeType.TEXT);                          .setMimeType(ContentService.MimeType.TEXT);
   }   }
 +}
 +
 +</code>
 ======  4. Node-RED Overview====== ======  4. Node-RED Overview======
  
Line 523: Line 576:
 Below is a video showcasing the node-Red flow and showing the code and possible errors that came up and how they were fixed. Starting with the discussion part of this documentation.  Below is a video showcasing the node-Red flow and showing the code and possible errors that came up and how they were fixed. Starting with the discussion part of this documentation. 
  
-https://student-wiki.eolab.de/lib/exe/fetch.php?w=120&h=120&tok=b20d52&media=amc:ss2025:group-f:whatsapp_image_2025-07-28_at_12.23.27_am.jpeg +{{ :amc:ss2025:group-f:file_2025-07-28_14.25.34.png?direct&600 |}}
  
 **Figure 4.** Documentation of the data in note-red (designed by Paul-Christian Thoma) **Figure 4.** Documentation of the data in note-red (designed by Paul-Christian Thoma)
Line 535: Line 587:
 The project successfully demonstrates an automated plant watering system based on real-time soil moisture monitoring and MQTT communication. Separating control logic from the sensor node to Node-RED allows flexible and remote decision-making. Integration with Google Sheets and email notifications adds monitoring and alert capabilities. The project successfully demonstrates an automated plant watering system based on real-time soil moisture monitoring and MQTT communication. Separating control logic from the sensor node to Node-RED allows flexible and remote decision-making. Integration with Google Sheets and email notifications adds monitoring and alert capabilities.
  
-The setup of the hardware is quick and easy especially since the water pump had a build in relay. As long as the right amount of power in this case 9V can be supplied to the pump. Most of the effort was to programm the software and setup the connection to a cloud based spreadsheet.  +The setup of the hardware is quick and easy especially since the water pump had a build in relay. As long as the right amount of power in this case 9V can be supplied to the pump. Most of the effort was to program the software and setup the connection to a cloud based spreadsheet.  
-When setting up the email-Alert extra steps where needed to allow node-red to send an email. For example creating an app password for the gmail email and enabling 2FA authentication. If feasible it might be an option to rely on sms messaging or a telegram bot for communicating the alert. A possible advantage would be real time communication with no delay. Additionally it was necessary to include a command to prevent the alert from being triggered multiple times during the same event. Otherwise if the pump would malfunction once the alert would continously be send every 10 seconds until it was fixed which would lead to the inbox being filled with e-mails.  + 
 +When setting up the email-Alert extra steps where needed to allow node-red to send an email. For example creating an app password for the gmail email and enabling 2FA authentication. If feasible it might be an option to rely on sms messaging or a telegram bot for communicating the alert.  
 + 
 +A possible advantage would be real time communication with no delay. Additionally it was necessary to include a command to prevent the alert from being triggered multiple times during the same event. Otherwise if the pump would malfunction once the alert would continously be send every 10 seconds until it was fixed which would lead to the inbox being filled with e-mails.   
 As of right now the system is only suitable for indoor application of plants inside a living room. Since the current setup is not weatherproof/waterproof.  As of right now the system is only suitable for indoor application of plants inside a living room. Since the current setup is not weatherproof/waterproof. 
 +
 To make it self sustainable it could be considered to use a PV-module for supplying power and charging the battery which is powering the pump. It should be connected to a charging circuit to prevent overcharging of the battery. To safe energy a deep sleep function could be integrated so the sensor is not constantly reading values but rather every couple hours since the moisture will most likely not rapidly change.  To make it self sustainable it could be considered to use a PV-module for supplying power and charging the battery which is powering the pump. It should be connected to a charging circuit to prevent overcharging of the battery. To safe energy a deep sleep function could be integrated so the sensor is not constantly reading values but rather every couple hours since the moisture will most likely not rapidly change. 
  When setting it up outside a stable connection to a local WiFi network needs to be possible.   When setting it up outside a stable connection to a local WiFi network needs to be possible. 
Line 559: Line 616:
   * **Wi-Fi Dependency:** The system requires stable Wi-Fi to function properly. In areas with poor connectivity, MQTT communication and cloud logging (e.g., to Google Sheets) may fail, disrupting automatic watering and notifications.   * **Wi-Fi Dependency:** The system requires stable Wi-Fi to function properly. In areas with poor connectivity, MQTT communication and cloud logging (e.g., to Google Sheets) may fail, disrupting automatic watering and notifications.
  
-* **Lack of Enclosure or Waterproofing:** The electronics are currently exposed on a breadboard. In a real-world indoor or greenhouse setup, a proper waterproof enclosure would be necessary for safety and durability.+  * **Lack of Enclosure or Waterproofing:** The electronics are currently exposed on a breadboard. In a real-world indoor or greenhouse setup, a proper waterproof enclosure would be necessary for safety and durability.
  
  
Line 567: Line 624:
  
  
-{{youtube>sK5l7mIVL0s?}}+{{youtube>sK5l7mIVL0s?}}                                          
 + 
 +{{youtube>vU9CFVkbPiY?}} 
 + 
 +{{youtube>XZt8VXC4Ths?}} 
  
  
amc/ss2025/group-f/start.1753726913.txt.gz · Last modified: 2025/07/28 20:21 by 33784_students.hsrw