Sending Data to Google Sheets

Question

Sending Data to Google Sheets

Alaa-Aim opened this issue 3 years ago · 12 comments

@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 ?

vortigont commented 3 years ago

Cheers!

👍1

Answer 1 · 2022-01-02T22:00:42.000Z

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 :)

Answer 2 · 2022-01-03T03:56:29.000Z

samples.json

@vortigont , Many thanks for your reply and useful information, waiting for you new update on the ESPEM ......

Answer 3 · 2022-01-04T13:00:37.000Z

@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.

Answer 4 · 2022-01-05T08:16:13.000Z

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

Answer 5 · 2022-01-08T20:04:27.000Z

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.

Answer 6 · 2022-01-09T04:43:36.000Z

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.

Answer 7 · 2022-01-12T10:17:56.000Z

@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.

#include <PZEM004Tv30.h>
#include <WiFi.h>
#include <HTTPClient.h>
#include <Wire.h>
#include <Adafruit_GFX.h>
#include <Adafruit_SSD1306.h>

#define PZEM_RX_PIN 16
#define PZEM_TX_PIN 17
#define PZEM_SERIAL Serial2
#define SCREEN_WIDTH 128 // OLED display width, in pixels
#define SCREEN_HEIGHT 64 // OLED display height, in pixels
#define OLED_RESET -1 // or -1 for ESP32 //// Reset pin # (or -1 if sharing Arduino reset pin)
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();
}

}

Answer 8 · 2022-01-12T12:45:40.000Z

Well, can't tell you much without seeing the code. At least you should have some log data printer over serial I suppose

Answer 9 · 2022-01-12T15:15:19.000Z

@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

A code for ESP8266 based boards to interface with PeaceFair PZEM PowerMeters
It can poll/collect PowerMeter data and provide it for futher processing in text/json format
(c) Emil Muratov 2017-2021

*/

// Main headers
#include "main.h"
#include "espem.h"
#include <EmbUI.h>
#include <Wire.h>
#include <Adafruit_GFX.h>
#include <Adafruit_SSD1306.h>

#define SCREEN_WIDTH 128 // OLED display width, in pixels
#define SCREEN_HEIGHT 64 // OLED display height, in pixels
#define OLED_RESET -1 // or -1 for ESP32 //// Reset pin # (or -1 if sharing Arduino reset pin)
Adafruit_SSD1306 display(SCREEN_WIDTH, SCREEN_HEIGHT, &Wire, OLED_RESET);

#include <HTTPClient.h>

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;

#ifdef USE_FTP
#include "ftpSrv.h"
#endif

extern "C" int clock_gettime(clockid_t unused, struct timespec *tp);

// PROGMEM strings
// sprintf template for json version data
#ifdef ESP8266
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}";
#elif defined ESP32
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}";
#endif

// 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();

#ifdef ESPEM_DEBUG
ESPEM_DEBUG.begin(BAUD_RATE); // start hw serial for debugging
#endif

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);
});

#ifdef USE_FTP
ftp_setup(); // запуск ftp-сервера
#endif

//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();

#ifdef USE_FTP
ftp_loop(); // цикл обработки событий фтп-сервера
#endif

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);
#ifdef ESP8266
snprintf_P(buff, sizeof(buff), PGverjson,
ESP.getChipId(),
ESP.getFlashChipSize(),
ESP.getCoreVersion().c_str(),
system_get_sdk_version(),
#ifdef GIT_REV
GIT_REV,
#else
"-",
#endif
ESP.getCpuFreqMHz(),
ESP.getFreeHeap(),
(uint32_t)tp.tv_sec);
#else
snprintf_P(buff, sizeof(buff), PGverjson,
ESP.getChipModel(),
ESP.getFlashChipSize(),
ESP.getSdkVersion(),
#ifdef GIT_REV
GIT_REV,
#else
"-",
#endif
ESP.getCpuFreqMHz(),
ESP.getFreeHeap(),
(uint32_t)tp.tv_sec);
#endif

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);

}

}
//`

Answer 10 · 2022-01-12T15:55:09.000Z

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.

Answer 11 · 2022-01-13T04:48:16.000Z

@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.