Closed Alaa-Aim closed 2 years ago
Hi @Alaa-Aim, glad you like it :) I'm not familiar with Google Sheet's, sorry. Suppose it is some kind of spread sheets but I doubt that esp32 has enough power to work with google API, never seen any of this.
In fact You can retrieve all the data from espem and with some scripting skills can convert it to any format you like.
Current metrics are available in a JSON format via http-request http://espem/getdata
and all the sampled data stored in RAM (the one that power chart is based on) could be acquired via http://espem/samples.json
. You can convert JSON to any format with some python (or whatever) scripts you like.
I have some plans to implement storing stats data from controller's RAM to SD-CARD but do not have time to work on it right now. Maybe a little bit later, keep in touch :)
samples.json
@vortigont , Many thanks for your reply and useful information, waiting for you new update on the ESPEM ......
@vortigont can I use the method used in this repository https://github.com/unreeeal/esp32-google-sheets
? how to combine it with your code, thanks in advanced.
I never used google sheets API, sorry. You can try to send some data same way you've set voltage alerts, might work I suppose. Uploading batches of data might be more complicated due to esp32 does not have much RAM to prepare the data request. Need some kind of streaming. Or even better to do this outside of ESP32 with a help of external scripting
I prefer local SQLite database storage for meter data, btw. A simple script running on raspberry store the data from espem int DB. You can find SQL schemas and some example requests in the repository.
I prefer local SQLite database storage for meter data, btw. A simple script running on raspberry store the data from espem int DB. You can find SQL schemas and some example requests in the repository.
@vortigont many thanks for your replies, I'll try Google Sheets First, then SQL, I'll let you know if I succeeded.
@vortigont I used the code below and worked like charm , but when I combine it with your code, the code work but nothing uploaded to the GS.
Adafruit_SSD1306 display(SCREEN_WIDTH, SCREEN_HEIGHT, &Wire, OLED_RESET); PZEM004Tv30 pzem(PZEM_SERIAL, PZEM_RX_PIN, PZEM_TX_PIN);
const char ssid = "XXX"; // type your Wifi name const char password = "XXX"; // Type your wifi password String GOOGLE_SCRIPT_ID = "XXXXXXXXXXXXX"; // Type your App Script id
const int sendInterval = 50; WiFiClientSecure client;
void setup() { // Debugging Serial port Serial.begin(115200); WiFi.mode(WIFI_STA); WiFi.begin(ssid, password); Serial.print("Connecting to Wi-Fi"); Serial.println(WiFi.localIP()); display.begin(SSD1306_SWITCHCAPVCC, 0x3C); // initialize with the I2C addr 0x3C (for the 128x64) display.display(); display.clearDisplay();
// Uncomment in order to reset the internal energy counter // pzem.resetEnergy() }
void loop() { // Print the custom address of the PZEM Serial.print("Custom Address:"); Serial.println(pzem.readAddress(), HEX);
// Read the data from the sensor float Voltage = pzem.voltage(); float Current = pzem.current(); float Power = pzem.power(); float Energy = pzem.energy(); float Frequency = pzem.frequency(); float Pf = pzem.pf(); //Writting Data to Google Sheets
write_google_sheet( "value1=" + String(Voltage) + "&value2=" + String(Current) + "&value3=" + String(Power) + "&value4=" + String(Energy) + "&value5=" + String(Frequency) + "&value6=" + String(Pf));
// Check if the data is valid if (isnan(Voltage)) { Serial.println("Error reading voltage"); } else if (isnan(Current)) { Serial.println("Error reading current"); } else if (isnan(Power)) { Serial.println("Error reading power"); } else if (isnan(Energy)) { Serial.println("Error reading energy"); } else if (isnan(Frequency)) { Serial.println("Error reading frequency"); } else if (isnan(Pf)) { Serial.println("Error reading power factor"); } else {
// Print the values to the Serial console
Serial.print("Voltage: "); Serial.print(Voltage); Serial.println("V");
Serial.print("Current: "); Serial.print(Current); Serial.println("A");
Serial.print("Power: "); Serial.print(Power); Serial.println("W");
Serial.print("Energy: "); Serial.print(Energy, 3); Serial.println("kWh");
Serial.print("Frequency: "); Serial.print(Frequency, 1); Serial.println("Hz");
Serial.print("PF: "); Serial.println(Pf);
}
if ((Voltage) >= 240.00) { Serial.println("What you doing?"); ESP.restart(); Serial.println(); delay(2000); }
String vString = String(Voltage);// using a , DEC for int, 3 for float, BIN for binary, HEX for Hexadecimal, none for display the value as is String mString = String(Current); String cString = String(Frequency);
display.clearDisplay(); display.invertDisplay(false);
ALAA("Voltage: ", 2, 8, 1, false); ALAA(vString, 52, 5, 2, false); //ALAA("V", 110, 5, 1, false);
ALAA("Current: ", 2, 28, 1, false); ALAA(mString, 72, 25, 1, false); ALAA("A", 110, 25, 1, false);
ALAA("Frequency: ", 2, 48, 1, false); ALAA(cString, 72, 45, 1, false); ALAA("Hz", 110, 45, 1, false);
display.display(); delay(1000); }
void write_google_sheet(String params) { HTTPClient http; String url = "https://script.google.com/macros/s/" + GOOGLE_SCRIPT_ID + "/exec?" + params; Serial.println(url); Serial.println("Updating Readings"); http.begin(url.c_str()); http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS); int httpCode = http.GET(); Serial.print("HTTP Status Code: "); Serial.println(httpCode);
String payload; if (httpCode > 0) { payload = http.getString(); Serial.println("Payload: " + payload); } http.end(); }
void ALAA(String text, int x, int y, int size, boolean d) {
display.setTextSize(size); display.setTextColor(WHITE); display.setCursor(x, y); display.println(text); if (d) { display.display(); }
}
Well, can't tell you much without seeing the code. At least you should have some log data printer over serial I suppose
@vortigont I cant compile and upload with debug on, as I've used all the pins, pin 1 & 3 used for the PZEM module, below is the code in main.cpp
`/* ESPEM - ESP Energy monitor
*/
// Main headers
Adafruit_SSD1306 display(SCREEN_WIDTH, SCREEN_HEIGHT, &Wire, OLED_RESET);
String GOOGLE_SCRIPT_ID = "AKfycby_n78L51euptIxZMd3cYTUp"; // Type your App Script id const int sendInterval = 50; WiFiClientSecure client;
int ST1 = 17; // Relay 1 17
int ST2 = 19; // Relay 2
int ST3 = 18; // Relay 3
int ST4 = 5; // Relay 4
int ST5 = 33; // Relay 5
int ST6 = 32; // Relay 6
int ST7 = 4; // Relay 7
int ST8 = 16; // Relay 8 16
int ST9 = 2; // Relay 9
int ST10 = 15; // Relay 10
int ST11 = 13; // Relay 11
int ST12 = 12; // Relay 12
int Sub1 = 14; // Relay 13
int Sub2 = 27; // Relay 14
int Sub3 = 26; // Relay 15
int Sub4 = 25; // Relay 16
const long interval = 1000; // Timer Setting in lieu of delay unsigned long currentMillis = 0; unsigned long previousMillis = 0;
const int numReadings = 50; // number of the array int readings[numReadings]; // the readings from the analog input int readIndex = 0; // the index of the current reading int total = 0; // the running total int average = 0; // the average
int analogInput = 34; float Voltage = 0; int Voltageact = 0;
extern "C" int clock_gettime(clockid_t unused, struct timespec *tp);
// PROGMEM strings // sprintf template for json version data
static const char PGverjson[] PROGMEM = "{\"ChipID\":\"%x\",\"Flash\":%u,\"Core\":\"%s\",\"SDK\":\"%s\",\"firmware\":\"" FW_NAME "\",\"version\":\"" FW_VERSION_STRING "\",\"git\":\"%s\",\"CPUMHz\":%u,\"Heap\":%u,\"Uptime\":%u}";
static const char PGverjson[] PROGMEM = "{\"ChipID\":\"%s\",\"Flash\":%u,\"SDK\":\"%s\",\"firmware\":\"" FW_NAME "\",\"version\":\"" FW_VERSION_STRING "\",\"git\":\"%s\",\"CPUMHz\":%u,\"Heap\":%u,\"Uptime\":%u}";
// Our instance of espem ESPEM *espem = nullptr; void Check(); void ALAA(); void mycheck(); void write_google_sheet(); Task t_checker;
// ---- // MAIN Setup void setup() { // Serial.begin(115200);
display.begin(SSD1306_SWITCHCAPVCC, 0x3C); // initialize with the I2C addr 0x3C (for the 128x64) display.display(); display.clearDisplay();
ESPEM_DEBUG.begin(BAUD_RATE); // start hw serial for debugging
LOG(println, F("Starting EspEM..."));
// Start framework, load config and connect WiFi embui.begin();
// create and run ESPEM object espem = new ESPEM();
if (espem && espem->begin(embui.paramVariant(FPSTR(V_UART)), embui.paramVariant(FPSTR(V_RX)), embui.paramVariant(FPSTR(V_TX))) ){ if ( espem->tsSet( embui.paramVariant(FPSTR(V_EPOOLSIZE)), embui.paramVariant(FPSTR(V_SMPL_PERIOD)) ) ){ espem->set_collector_state(mcstate_t::MC_RUN); } }
embui.server.on(PSTR("/fw"), HTTP_GET, [](AsyncWebServerRequest *request){ wver(request); });
ftp_setup(); // запуск ftp-сервера
//sync_parameters(); // sync UI params
embui.setPubInterval(WEBUI_PUBLISH_INTERVAL);
//run check every seconds t_checker.set( TASK_SECOND, TASK_FOREVER, mycheck ); ts.addTask(t_checker); t_checker.enableDelayed();
pinMode(ST1, OUTPUT); pinMode(ST2, OUTPUT); pinMode(ST3, OUTPUT); pinMode(ST4, OUTPUT); pinMode(ST5, OUTPUT); pinMode(ST6, OUTPUT); pinMode(ST7, OUTPUT); pinMode(ST8, OUTPUT); pinMode(ST9, OUTPUT); pinMode(ST10, OUTPUT); pinMode(ST11, OUTPUT); pinMode(ST12, OUTPUT); pinMode(Sub1, OUTPUT); pinMode(Sub2, OUTPUT); pinMode(Sub3, OUTPUT); pinMode(Sub4, OUTPUT);
digitalWrite(ST1, LOW); digitalWrite(ST2, LOW); digitalWrite(ST3, LOW); digitalWrite(ST4, LOW); digitalWrite(ST5, LOW); digitalWrite(ST6, LOW); digitalWrite(ST7, LOW); digitalWrite(ST8, LOW); digitalWrite(ST9, LOW); digitalWrite(ST10, LOW); digitalWrite(ST11, LOW); digitalWrite(ST12, LOW); digitalWrite(Sub1, LOW); digitalWrite(Sub2, LOW); digitalWrite(Sub3, LOW); digitalWrite(Sub4, LOW); }
// MAIN loop void loop() {
mycheck();
embui.handle();
ftp_loop(); // цикл обработки событий фтп-сервера
unsigned long currentMillis = millis();
total = total - readings[readIndex]; // subtract the last reading: readings[readIndex] = analogRead(analogInput); // read from the sensor: total = total + readings[readIndex]; // add the reading to the total: readIndex = readIndex + 1; // advance to the next position in the array: if (readIndex >= numReadings) { // if we're at the end of the array... readIndex = 0; // ...wrap around to the beginning: } average = total / numReadings; // calculate the average: Voltage = average * 0.2026862026862027; //0.1782661782661783; 830 instead of 730 PP maximum Voltageact = Voltage / 1.4545; if (average >= 3901){ ESP.restart(); } if (average <= 959) { digitalWrite(ST1, LOW); digitalWrite(ST2, LOW); digitalWrite(ST3, LOW); digitalWrite(ST4, LOW); digitalWrite(ST5, LOW); digitalWrite(ST6, LOW); digitalWrite(ST7, LOW); digitalWrite(ST8, LOW); digitalWrite(ST9, LOW); digitalWrite(ST10, LOW); digitalWrite(ST11, LOW); digitalWrite(ST12, LOW); digitalWrite(Sub1, LOW); digitalWrite(Sub2, LOW); digitalWrite(Sub3, LOW); digitalWrite(Sub4, LOW); }
if ((unsigned long )(currentMillis - previousMillis) >= interval) { //checking the time elapsed
Check();
previousMillis = currentMillis;
}
}
// send HTTP responce, json with controller/fw versions and status info void wver(AsyncWebServerRequest *request) { char buff[HTTP_VER_BUFSIZE];
timespec tp; clock_gettime(0, &tp);
snprintf_P(buff, sizeof(buff), PGverjson, ESP.getChipId(), ESP.getFlashChipSize(), ESP.getCoreVersion().c_str(), system_get_sdk_version(),
GIT_REV,
"-",
ESP.getCpuFreqMHz(),
ESP.getFreeHeap(),
(uint32_t)tp.tv_sec);
snprintf_P(buff, sizeof(buff), PGverjson, ESP.getChipModel(), ESP.getFlashChipSize(), ESP.getSdkVersion(),
GIT_REV,
"-",
ESP.getCpuFreqMHz(),
ESP.getFreeHeap(),
(uint32_t)tp.tv_sec);
request->send(200, FPSTR(PGmimejson), buff ); }
void ALAA(String text, int x, int y, int size, boolean d) {
display.setTextSize(size); display.setTextColor(WHITE); display.setCursor(x, y); display.println(text); if (d) { display.display(); } } void mycheck(){ const auto m = espem->pz->getMetricsPZ004(); // Serial.print("Voltage : "); // Serial.println(m->voltage/10); // Serial.printf("Frequincey: %.2f (Hz)\n", m->asFloat(pzmbus::meter_t::frq)); // Serial.printf("Current: %.1f (Amp)\n", m->asFloat(pzmbus::meter_t::cur)); // Serial.printf("Power Factor: %.2f (pf)\n", m->asFloat(pzmbus::meter_t::pf)); // Serial.printf("Power: %.2f (kWh)\n", m->asFloat(pzmbus::meter_t::pwr)); //Writting Data to Google Sheets float Voltaee = m->voltage/10; float Current = m->current; float Power = m->power; float Energy = m->energy; float Frequncy = m->freq; float Pf = m->pf;
write_google_sheet( "value1=" + String(Voltaee) + "&value2=" + String(Current) + "&value3=" + String(Power) + "&value4=" + String(Energy) + "&value5=" + String(Frequncy) + "&value6=" + String(Pf));
if (m->voltage/10 > 210){ // Serial.println("High Voltage!");
}
String vString = String(m->voltage/10);// using a , DEC for int, 3 for float, BIN for binary, HEX for Hexadecimal, none for display the value as is String mString = String(m->current); String cString = String(m->asFloat(pzmbus::meter_t::frq));
display.clearDisplay(); display.invertDisplay(false);
ALAA("Voltage: ", 2, 8, 1, false); ALAA(vString, 52, 5, 2, false); //ALAA("V", 110, 5, 1, false);
ALAA("Current: ", 2, 28, 1, false); ALAA(mString, 72, 25, 1, false); ALAA("A", 110, 25, 1, false);
ALAA("Frequency: ", 2, 48, 1, false); ALAA(cString, 72, 45, 1, false); ALAA("Hz", 110, 45, 1, false);
display.display();
}
void write_google_sheet(String params) { HTTPClient http; String url = "https://script.google.com/macros/s/" + GOOGLE_SCRIPT_ID + "/exec?" + params; http.begin(url.c_str()); http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS); int httpCode = http.GET();
String payload; if (httpCode > 0) { payload = http.getString(); Serial.println("Payload: " + payload); } http.end(); }
void Check() {
if ( (average > 960) && (average < 1050) ) {
digitalWrite(ST1, HIGH);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, HIGH);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, LOW);
}
if ( (average > 1051) && (average < 1130) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, HIGH);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, HIGH);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, LOW);
}
if ( (average > 1131) && (average < 1219) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, HIGH);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, HIGH);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, LOW);
}
if ( (average > 1220) && (average < 1326) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, HIGH);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, HIGH);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, LOW);
}
if ( (average > 1327) && (average < 1453) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, HIGH);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, HIGH);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, LOW);
}
if ( (average > 1454) && (average < 1614) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, HIGH);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, HIGH);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, LOW);
}
if ( (average > 1615) && (average < 1819) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, HIGH);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, HIGH);
digitalWrite(Sub4, LOW);
}
if ( (average > 1820) && (average < 2079) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, HIGH);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, HIGH);
digitalWrite(Sub4, LOW);
}
if ( (average > 2080) && (average < 2409) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, HIGH);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, HIGH);
digitalWrite(Sub4, LOW);
}
if ( (average > 2410) && (average < 2854) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, HIGH);
digitalWrite(ST11, LOW);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, HIGH);
}
if ( (average > 2855) && (average < 3440) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, HIGH);
digitalWrite(ST12, LOW);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, HIGH);
}
if ( (average > 3441) && (average < 3900) ) {
digitalWrite(ST1, LOW);
digitalWrite(ST2, LOW);
digitalWrite(ST3, LOW);
digitalWrite(ST4, LOW);
digitalWrite(ST5, LOW);
digitalWrite(ST6, LOW);
digitalWrite(ST7, LOW);
digitalWrite(ST8, LOW);
digitalWrite(ST9, LOW);
digitalWrite(ST10, LOW);
digitalWrite(ST11, LOW);
digitalWrite(ST12, HIGH);
digitalWrite(Sub1, LOW);
digitalWrite(Sub2, LOW);
digitalWrite(Sub3, LOW);
digitalWrite(Sub4, HIGH);
}
} //`
cant compile and upload with debug on, as I've used all the pins
You can use some pins temporary I suppose, just to fix the issue :) You need this to check if google respond something meaningfull
Serial.print("HTTP Status Code: ");
Serial.println(httpCode);
This is wrong!
// MAIN loop
void loop() {
mycheck();
}
You should NOT put a call to mycheck(); into loop(){}, you make it running on and on again without any delay. It's already handled by the scheduler every second.
@vortigont , I did that, now it's working like a charm, data uploaded to GS and locally displayed on the WebUI, many tanks for your works and efforts.
Cheers!
@vortigont , I'm been using this project for a while, it's very nice built and the WebUI is good, I want to keep a log for all data been displayed on the WebUI by sending it to Google Sheet, is it possible to do that ?