Hello, I have been puzzled by how I can fix these dublicating messages?
Long story short. I have this ESP32 connected to arduino IoT Cloud, from IoT Cloud I send some data through a webhook to a Google Sheet.
I have a bunch of variables but it is only one variable I want to use with google sheet.
The problem is that I can see everytime one of the other variables updates it sends a new message to google sheet, which causes an overflow of errors and makes the webhook link become inactive after few minuts.
In order to make it work I have changed the update interval from every 1 seconds to every 600 seconds for the variables I dont use in google sheet and every 30 seconds for the variable I want to use with google sheet. The webhook no longer becomes inactive but I still recieve dublicated messages.
What can I do to fix this issue?
This is my google script:
// Opret en daglig trigger til at køre funktionen dailyBackupAndClear kl. 06:00
function createDailyTrigger() {
// Slet eksisterende triggers for at undgå duplikater
deleteTriggers();
// Opret en ny trigger, der kører dagligt kl. 06:00
ScriptApp.newTrigger("dailyBackupAndClear")
.timeBased()
.atHour(9) // Kl. 06:00
.nearMinute(25) // 32 minutter
.everyDays(1) // Hver dag
.create();
}
// Slet eksisterende triggers
function deleteTriggers() {
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
ScriptApp.deleteTrigger(triggers[i]);
}
}
// Daglig funktion til at kopiere hele projektet og rydde specifikke ark
function dailyBackupAndClear() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Opret en kopi af hele projektet med dags dato MINUS 1 dag som navn
var date = new Date();
date.setDate(date.getDate() - 1); // Træk én dag fra den aktuelle dato
var dateString = Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");
var copyName = "Maskinstatus (" + dateString + ")";
var file = DriveApp.getFileById(spreadsheet.getId());
var copy = file.makeCopy(copyName);
// Flyt kopien til mappen "maskinstatus logbog"
var folderId = "1VvjB6pOpwmYn9KbvAb09pCRFleSbwVC7"; // Mappe-ID for "maskinstatus logbog"
var folder = DriveApp.getFolderById(folderId);
folder.addFile(copy);
DriveApp.getRootFolder().removeFile(copy); // Fjern kopien fra rodmappen
// Ryd indholdet af alle ark, hvor navnet indeholder "_maskinStatus" eller hedder "Unexpected Data"
var sheets = spreadsheet.getSheets();
for (var i = 0; i < sheets.length; i++) {
var sheetName = sheets[i].getName();
if (sheetName.includes("_maskinStatus") || sheetName === "Unexpected Data") {
sheets[i].clearContents(); // Ryd indholdet af arket
// Sæt overskrifterne tilbage for _maskinStatus ark
if (sheetName.includes("_maskinStatus")) {
sheets[i].getRange(1, 1).setValue("Timestamp");
sheets[i].getRange(1, 2).setValue(sheetName + " (10)");
sheets[i].getRange(1, 3).setValue(sheetName + " (5)");
sheets[i].getRange(1, 4).setValue(sheetName + " (0)");
}
}
}
Logger.log("Daglig kopiering og rydning fuldført: " + copyName);
}
function doPost(e) {
try {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var MAX_ROWS = 1000000;
var HEADER_ROW = 1;
var TIMESTAMP_COL = 1;
var properties = PropertiesService.getScriptProperties();
var processedEventIds = JSON.parse(properties.getProperty("processedEventIds") || "{}");
var data;
try {
data = JSON.parse(e.postData.contents);
} catch (parseError) {
logUnexpectedData(e.postData.contents, "JSON Parse Error: " + parseError.toString());
return ContentService.createTextOutput("JSON Parse Error: " + parseError.toString());
}
var values = data.values;
if (!values || values.length === 0) {
logUnexpectedData(e.postData.contents, "No data received");
return ContentService.createTextOutput("No data received");
}
var eventId = data.event_id;
if (processedEventIds[eventId]) {
logUnexpectedData(e.postData.contents, "Message discarded (duplicate)");
return ContentService.createTextOutput("Message discarded (duplicate)");
}
processedEventIds[eventId] = new Date().getTime();
properties.setProperty("processedEventIds", JSON.stringify(processedEventIds));
cleanupProcessedEventIds(processedEventIds, properties);
var maskinStatusVars = ['c81_maskinStatus', 'c85_maskinStatus', 'd73_maskinStatus', 'd78_maskinStatus', 'i79_maskinStatus', 'i80_maskinStatus', 'i84_maskinStatus'];
var maskinStatuses = values.filter(v => maskinStatusVars.includes(v.name));
if (maskinStatuses.length === 0) {
logUnexpectedData(e.postData.contents, "No matching _maskinStatus values found");
return ContentService.createTextOutput("No matching _maskinStatus values found");
}
var updatedAtUTC = new Date(values[0].updated_at);
var nowUTC = new Date();
if (nowUTC - updatedAtUTC > 30 * 1000) {
logUnexpectedData(e.postData.contents, "Message discarded (too late)");
}
maskinStatuses.forEach(maskinStatus => {
var variableName = maskinStatus.name;
var sheet = spreadsheet.getSheetByName(variableName);
if (!sheet) {
sheet = spreadsheet.insertSheet(variableName);
sheet.getRange(HEADER_ROW, TIMESTAMP_COL).setValue('Timestamp');
sheet.getRange(HEADER_ROW, TIMESTAMP_COL + 1).setValue(variableName + ' (10)');
sheet.getRange(HEADER_ROW, TIMESTAMP_COL + 2).setValue(variableName + ' (5)');
sheet.getRange(HEADER_ROW, TIMESTAMP_COL + 3).setValue(variableName + ' (0)');
}
var value = maskinStatus.value;
var column;
if (value === 10) {
column = TIMESTAMP_COL + 1;
} else if (value === 5) {
column = TIMESTAMP_COL + 2;
} else if (value === 0) {
column = TIMESTAMP_COL + 3;
} else {
logUnexpectedData(e.postData.contents, "Invalid value: " + value + " for " + variableName);
return;
}
if (sheet.getLastRow() >= MAX_ROWS + HEADER_ROW) {
sheet.deleteRow(MAX_ROWS + HEADER_ROW);
}
var lastRow = sheet.getLastRow() + 1;
var timeString = Utilities.formatDate(updatedAtUTC, Session.getScriptTimeZone(), "HH:mm");
sheet.getRange(lastRow, TIMESTAMP_COL).setValue(timeString);
sheet.getRange(lastRow, column).setValue(value);
});
logSuccessfulData(e.postData.contents, eventId);
return ContentService.createTextOutput("Data processed successfully");
} catch (error) {
logUnexpectedData(e ? e.postData.contents : "No post data", "Unexpected Error: " + error.toString());
return ContentService.createTextOutput("Unexpected Error: " + error.toString());
}
}
function logSuccessfulData(rawData, eventId) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Unexpected Data");
if (!sheet) {
sheet = spreadsheet.insertSheet("Unexpected Data");
sheet.appendRow(["Timestamp", "Status", "Event ID", "Raw Data"]);
}
var timestamp = new Date();
sheet.appendRow([timestamp, "Success", eventId, rawData]);
var MAX_UNEXPECTED_ROWS = 100000;
if (sheet.getLastRow() > MAX_UNEXPECTED_ROWS) {
sheet.deleteRow(2);
}
}
function logUnexpectedData(rawData, errorMessage) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Unexpected Data");
if (!sheet) {
sheet = spreadsheet.insertSheet("Unexpected Data");
sheet.appendRow(["Timestamp", "Status", "Event ID", "Raw Data"]);
}
var timestamp = new Date();
sheet.appendRow([timestamp, "Error: " + errorMessage, "", rawData]);
var MAX_UNEXPECTED_ROWS = 100000;
if (sheet.getLastRow() > MAX_UNEXPECTED_ROWS) {
sheet.deleteRow(2);
}
}
function cleanupProcessedEventIds(processedEventIds, properties) {
var now = new Date().getTime();
for (var eventId in processedEventIds) {
if (now - processedEventIds[eventId] > 60 * 60 * 1000) {
delete processedEventIds[eventId];
}
}
properties.setProperty("processedEventIds", JSON.stringify(processedEventIds));
}
This is my Arduino Code:
/*
Sketch generated by the Arduino IoT Cloud Thing "Untitled"
https://create.arduino.cc/cloud/things/5ece6a0e-3cc6-4c10-8c6d-3cd3f8b3b7cb
Arduino IoT Cloud Variables description
The following variables are automatically generated and updated when changes are made to the Thing
String c81_driftBesked;
float c81_hydraulikTemperatur;
float c81_luftTemperatur;
int c81_maskinStatus;
int c81_relativFugtighed;
int cyklusTid;
int kommando;
int sekunder;
bool reset;
Variables which are marked as READ/WRITE in the Cloud Thing will also have functions
which are called when their values are changed from the Dashboard.
These functions are generated with the Thing and added at the end of this sketch.
*/
const int ledIdle = 25;
const int ledRun = 26;
const int ledStop = 27;
#define Watchdog 0
#define MaskinNumber "C81 "
#define MaskinCali "TAGER TID"
#define MaskinRun "KØRER"
#define MaskinIdle "VENTER"
#define MaskinError "FEJL"
#define MaskinHidden " "
#include "thingProperties.h"
#include "OneWire.h"
#include "DallasTemperature.h"
//Data pin. Husk 4.7kohm pullup modstand imellem Data og VCC
#define ONE_WIRE_BUS 23
OneWire oneWire(ONE_WIRE_BUS);
DallasTemperature sensors(&oneWire);
//ESP32 Pins 21=SDA 22=SCL
#include <Adafruit_AHTX0.h>
Adafruit_AHTX0 aht;
#define trButton 19 // training/reset button (being eliminated/modified)
#define processInput 19 // needs the sensor from the process
#define faultLED 3
#define readyLED 4
#define runningLED 5
bool needCalibration; // set this to force a calibration run
enum bar { IDLE = 0,
MEASURE,
CHECK,
IFAULT,
NFAULT,
};
/* Declaring a global variabl for sensor data
int c81_maskinStatus = 0;
float c81_luftTemperatur = 0;
int c81_relativFugtighed = 0;
float c81_hydraulikTemperatur = 0;
*/
void setup() {
// Define LED pins
pinMode(readyLED, OUTPUT);
pinMode(runningLED, OUTPUT);
pinMode(faultLED, OUTPUT);
pinMode(trButton, INPUT_PULLUP);
pinMode(processInput, INPUT_PULLUP);
pinMode(ledIdle, OUTPUT);
pinMode(ledRun, OUTPUT);
pinMode(ledStop, OUTPUT);
needCalibration = true;
// Initialize serial and wait for port to open:
Serial.begin(9600);
// This delay gives the chance to wait for a Serial Monitor without blocking if none is found
delay(1500);
aht.begin();
sensors.begin();
pinMode(12, OUTPUT);
// Defined in thingProperties.h
initProperties();
// Connect to Arduino IoT Cloud
ArduinoCloud.begin(ArduinoIoTPreferredConnection);
/*
The following function allows you to obtain more information
related to the state of network and IoT Cloud connection and errors
the higher number the more granular information youâll get.
The default is 0 (only errors).
Maximum is 4
*/
setDebugMessageLevel(2);
ArduinoCloud.printDebugInfo();
}
unsigned int counter;
unsigned char state = IDLE; // does. is.
unsigned long now;
unsigned long timeLimit;
unsigned long timeLimit1;
unsigned long timer1;
unsigned long previousMillis;
int startTime;
long timerMode;
void loop() {
ArduinoCloud.update();
// Your code here
if (watchdog == 0){
sekunder = 0;
c81_maskinStatus = 0;
c81_driftBesked = MaskinNumber MaskinHidden;
}
if (now - previousMillis > 1800000) {
needCalibration = true;
previousMillis = now;
}
cyklusTid = (timeLimit / 1000);
now = millis();
bool controlX = digitalRead(processInput) == HIGH;
bool buttonState = digitalRead(trButton) == HIGH;
if(controlX == HIGH || buttonState == HIGH){
sekunder = ((now - startTime) / 1000.0) + 0.5;
if(sekunder > 7200) {
sekunder = 0;
}
}
else {
startTime = now;
sekunder = 0;
}
static bool lastButtonState;
static bool buttonPress;
if (buttonState != lastButtonState) {
if (buttonState) {
buttonPress = !buttonPress;
}
lastButtonState = buttonState;
}
switch (state) {
case IDLE:
c81_maskinStatus = 5;
c81_driftBesked = MaskinNumber MaskinIdle;
digitalWrite(readyLED, HIGH);
digitalWrite(runningLED, LOW);
if (controlX) {
timer1 = now;
// if (buttonPress || needCalibration) {
if (needCalibration) {
state = MEASURE;
Serial.print("training for time\n");
buttonPress = false;
needCalibration = false;
} else {
state = CHECK;
Serial.print("X up. monitoring time\n");
}
}
break;
case CHECK:
c81_maskinStatus = 10;
c81_driftBesked = MaskinNumber MaskinRun;
digitalWrite(readyLED, LOW);
digitalWrite(runningLED, HIGH);
if (now - timer1 > timeLimit1) {
state = IFAULT;
break;
}
if (!controlX) {
Serial.print("X down made it by ");
Serial.println(timeLimit - (now - timer1));
state = IDLE;
}
break;
case MEASURE:
c81_maskinStatus = 10;
c81_driftBesked = MaskinNumber MaskinCali;
digitalWrite(readyLED, LOW);
digitalWrite(runningLED, HIGH);
if (now - timer1 > 1000000){
state = IFAULT;
break;
}
if (!controlX) {
timeLimit = now - timer1 + 1000;
timeLimit1 = timeLimit * 1.10; // comfort margarine 10 percent here
Serial.print("X down new period = ");
Serial.println(timeLimit);
state = IDLE;
}
break;
case IFAULT:
Serial.print(" initialize fault mechanism");
state = NFAULT;
Serial.print(" / perpetuate fault mechanism\n");
c81_maskinStatus = 0;
c81_driftBesked = MaskinNumber MaskinError;
digitalWrite(faultLED, HIGH);
buttonPress = false; // eat any stray button presses
break;
case NFAULT:
digitalWrite(readyLED, LOW);
digitalWrite(runningLED, LOW);
digitalWrite(faultLED, millis() & 512 ? HIGH : LOW);
if (buttonPress) {
Serial.print("sytem to IDLE\n");
digitalWrite(faultLED, LOW);
buttonPress = false;
needCalibration = true;
state = IDLE;
}
break;
}
sensors_event_t humidity, temp;
aht.getEvent(&humidity, &temp);// populate temp and humidity objects with fresh data
c81_luftTemperatur = temp.temperature;
c81_relativFugtighed = humidity.relative_humidity;
sensors.requestTemperatures();
c81_hydraulikTemperatur = sensors.getTempCByIndex(0);
}
/*
Since Kommando is READ_WRITE variable, onKommandoChange() is
executed every time a new value is received from IoT Cloud.
*/
void onKommandoChange() {
// Add your code here to act upon Kommando change
if (kommando == 1){
digitalWrite(ledIdle, LOW);
digitalWrite(ledRun, HIGH);
digitalWrite(ledStop, LOW);
}
if (kommando == 2){
digitalWrite(ledIdle, HIGH);
digitalWrite(ledRun, LOW);
digitalWrite(ledStop, LOW);
}
if (kommando == 3){
digitalWrite(ledIdle, LOW);
digitalWrite(ledRun, LOW);
digitalWrite(ledStop, HIGH);
}
}
/*
Since Reset is READ_WRITE variable, onResetChange() is
executed every time a new value is received from IoT Cloud.
*/
void onResetChange() {
// Add your code here to act upon Reset change
if (reset == true) {pinMode(9, OUTPUT);}
}
/*
Since CyklusTid is READ_WRITE variable, onCyklusTidChange() is
executed every time a new value is received from IoT Cloud.
*/
void onCyklusTidChange() {
// Add your code here to act upon CyklusTid change
}
thingProperties:
// Code generated by Arduino IoT Cloud, DO NOT EDIT.
#include <ArduinoIoTCloud.h>
#include <Arduino_ConnectionHandler.h>
const char DEVICE_LOGIN_NAME[] = "****************";
const char SSID[] = SECRET_SSID; // Network SSID (name)
const char PASS[] = SECRET_OPTIONAL_PASS; // Network password (use for WPA, or use as key for WEP)
const char DEVICE_KEY[] = SECRET_DEVICE_KEY; // Secret device password
void onKommandoChange();
void onResetChange();
String c81_driftBesked;
float c81_hydraulikTemperatur;
float c81_luftTemperatur;
int c81_maskinStatus;
int c81_relativFugtighed;
int cyklusTid;
int kommando;
int sekunder;
bool reset;
void initProperties(){
ArduinoCloud.setBoardId(DEVICE_LOGIN_NAME);
ArduinoCloud.setSecretDeviceKey(DEVICE_KEY);
ArduinoCloud.addProperty(c81_driftBesked, READ, ON_CHANGE, NULL);
ArduinoCloud.addProperty(c81_hydraulikTemperatur, READ, 60 * SECONDS, NULL);
ArduinoCloud.addProperty(c81_luftTemperatur, READ, 60 * SECONDS, NULL);
ArduinoCloud.addProperty(c81_maskinStatus, READ, 30 * SECONDS, NULL);
ArduinoCloud.addProperty(c81_relativFugtighed, READ, 60 * SECONDS, NULL);
ArduinoCloud.addProperty(cyklusTid, READ, 60 * SECONDS, NULL);
ArduinoCloud.addProperty(kommando, READWRITE, ON_CHANGE, onKommandoChange);
ArduinoCloud.addProperty(sekunder, READ, 30 * SECONDS, NULL);
ArduinoCloud.addProperty(reset, READWRITE, ON_CHANGE, onResetChange);
}
WiFiConnectionHandler ArduinoIoTPreferredConnection(SSID, PASS);
When the webhook goes 'inactive' in a Thing, I just click the webhook setting to reset it. I don't even change the link string. I would think that a spam flag might try to block more than the same request, perhaps the whole originating domain!