Sending Data from ESP32 or ESP8266 to Google Sheets [2 Methods]

/**
 Created by M.Merati

 Email: [email protected]
*/

// This example shows how to create the spreadsheet, update and read the values.

#include <Arduino.h>
#if defined(ESP32) || defined(ARDUINO_RASPBERRY_PI_PICO_W)
#include <WiFi.h>
#elif defined(ESP8266)
#include <ESP8266WiFi.h>
#endif
#include <ESP_Google_Sheet_Client.h>
#include <stdlib.h>
#include "time.h"
const char* ntpServer = "pool.ntp.org";
const long gmtOffset_sec = 19800;
const int daylightOffset_sec = 0;
int SensValue = 0;

// For SD/SD_MMC mounting helper
#include <GS_SDHelper.h>

#define WIFI_SSID "Galaxy S20 FEDF80"
#define WIFI_PASSWORD "kdbi8870"

#define PROJECT_ID "logical-utility-388206"

// Service Account's client email
#define CLIENT_EMAIL "[email protected]"

// Your email to share access to spreadsheet
#define USER_EMAIL "[email protected]"

char numberArray[20];

// Service Account's private key
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCbBLtWIEx2OmTI\nzyNk53HtxjebDfGp0AcbAcC/ztN6CTPYKKO7k1jGcKkSUMe7oWGr4lB/QeS0XBFG\nkzjS3LpIuTMlBtf4erf7fcFqCF5VlYYWwuprOmZe3hyYvlc8oBUIcgQvOA6xQcxW\n0tJTAQXfT9g1IF3lRupZ6mCN1ILinxMQOA7zNLa7CLe2wq1hFX4kn8+YztHAiunp\nR7EMhzZAL9rwR2VpWoFZj24TZpQzEYS1S7n3iD8d0Ip3mVmoNcYDe39I5+ZE8xe1\nuqbc4Z5SzUe6RyytAMLL4tMAqDqvDTXtnD9Hw1f3DTymEWEFYjfjO3B6Pt90VLMG\n48r7veZvAgMBAAECggEAO5XzCGT73woNCV8NhNCt6y6g2xneBV7wDEJ9O30drq/w\n3KqqXQSDQCu34VFppMagi3g/ZtsGNQmanl0in3K2rBN8BESAKYPKSoIJIV+8GvuR\nghhEERlulhKcPV4UnDoQO6HP1/KdOydGiy0YKU2gWzkOq0UYsnOyywMbQAkqkj7n\n5ELfR//Leewx92zTiGrI4xns0sDm15rEYQvF8WQZeMFUblmJ+5FfuSK5hP0SaX4F\nvv9cx34TteUm1sro+v65HYTjY8Ozcur+ZjiuSNuAx4vs7CmpUM3kAjIN2451H9ka\ny3PY7I3bvFhy9tIHmQflFiokAFk3eH//HavpmBnroQKBgQDKJYElbW1b9L+5RHK4\nD8vRRbIgNVw1lcsBulwc4/VyAUORd3V9JQwfGvGBKykTVvJ75USR9T49BIDBZeR+\nfeO2RZtCaEXm5OtQgVM8p9GFUxhRk283CPf/uZbMkNkXXCLefcVWCBr+6+2extUV\nz+MMgghwFx4vjbJqe2RqFtA+vwKBgQDEURJ6zhKu7pJy1wNlPkfsnDwb2sbj/UmG\nmzA+9KEP9PQExxShRYBwyrsgGRB644kLtWv6tOTezib54SnWXAcVHXkkP1hWrw79\nfWY9tgPldwvlT9KMXTiG6NBHDZD/lwVtiMqulmKpXVr+AVSOp+t/vzquvOO8MKl2\nP5g5BvT0UQKBgQCX1S24uAWBIUd0V7Y3DB5R9KCDUrEMypMtnBWd2Zc85zgiJfEI\na3A8WNd25r02eoMtjho/602xNRWkrey/Gb2U5Zller6uW9lnoYusrnxQQQauFyhc\n1Q65dGORCWcWBa1nIl67bOEiAoF7VestM/VdKro9vw98NlkBY+cbTEFkLwKBgHno\nglpCR7XbrpOj05b2UwGqj2UYuXNwoUz2Z8JgdPgMu8+PtM7k8vqX2hX3mpVFYKAl\nu/UGii2VxPxdB3b21OsWz1hkAb5qnRtq2KevtMG06G0w4WWKqgSU5GmLEiS1qog3\nbu11s7TKpSugtdqUihhdLtq9r4n4ifNFUIjxOgHRAoGAbMlZi6KtyuIqfpnOvYej\nIO2SmKA85EBelrZCl2xKlIgGpLRITzfiQ5k+HH3ts22iw3ld+tBxpg82EX4Zuavj\niM57EIf6hzLsh8eLpic85ziD6PV14PBQUECS1Bb14fMJ4k3rcGcNFo2o9tgjlu9R\nKDx2yXeNgh/pToM7GLvlIOg=\n-----END PRIVATE KEY-----\n";

bool taskComplete = false;

#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
WiFiMulti multi;
#endif

void tokenStatusCallback(TokenInfo info);

void setup()
{

 Serial.begin(115200);
 Serial.println();
 Serial.println();

#if defined(ESP32) || defined(ESP8266)
 WiFi.setAutoReconnect(true);
#endif

#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
 multi.addAP(WIFI_SSID, WIFI_PASSWORD);
 multi.run();
#else
 WiFi.begin(WIFI_SSID, WIFI_PASSWORD);
#endif

 Serial.print("Connecting to Wi-Fi");
 unsigned long ms = millis();
 while (WiFi.status() != WL_CONNECTED)
 {
 Serial.print(".");
 delay(300);
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
 if (millis() - ms > 10000)
 break;
#endif
 }
 Serial.println();
 Serial.print("Connected with IP: ");
 Serial.println(WiFi.localIP());
 Serial.println();

 // Set the callback for Google API access token generation status (for debug only)
 GSheet.setTokenCallback(tokenStatusCallback);

 // The WiFi credentials are required for Pico W
 // due to it does not have reconnect feature.
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
 GSheet.clearAP();
 GSheet.addAP(WIFI_SSID, WIFI_PASSWORD);
#endif

 // Set the seconds to refresh the auth token before expire (60 to 3540, default is 300 seconds)
 GSheet.setPrerefreshSeconds(10 * 60);

 // Begin the access token generation for Google API authentication
 GSheet.begin(CLIENT_EMAIL, PROJECT_ID, PRIVATE_KEY);

 configTime(gmtOffset_sec, daylightOffset_sec, ntpServer);
}

void loop()
{
 // Call ready() repeatedly in loop for authentication checking and processing
 bool ready = GSheet.ready();

 if (ready && !taskComplete)
 {
 FirebaseJson response;

 Serial.println("\nCreate spreadsheet...");
 Serial.println("------------------------");

 FirebaseJson spreadsheet;
 spreadsheet.set("properties/title", "Gas Sensor Data Log");
 spreadsheet.set("sheets/properties/gridProperties/rowCount", 100 );
 spreadsheet.set("sheets/properties/gridProperties/columnCount", 2);

 String spreadsheetId, spreadsheetURL;
 bool success = false;

 // For Google Sheet API ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/create

 success = GSheet.create(&response /* returned response */, &spreadsheet /* spreadsheet object */, USER_EMAIL /* your email that this spreadsheet shared to */);
 response.toString(Serial, true);
 Serial.println();
 

 if (success)
 {
 // Get the spreadsheet id from already created file.
 FirebaseJsonData result;
 response.get(result, FPSTR("spreadsheetId")); // parse or deserialize the JSON response
 if (result.success)
 spreadsheetId = result.to<const char *>();

 // Get the spreadsheet URL.
 result.clear();
 response.get(result, FPSTR("spreadsheetUrl")); // parse or deserialize the JSON response
 if (result.success)
 {
 spreadsheetURL = result.to<const char *>();
 Serial.println("\nThe spreadsheet URL");
 Serial.println(spreadsheetURL);
 }

 struct tm timeinfo;
 char timeStringBuff[50];
 String asString;
 char buffer[40];


 FirebaseJson valueRange;
 for (int counter = 0; counter < 10; counter++)
 {
 Serial.println("\nUpdate spreadsheet values...");
 Serial.println("------------------------------");
 if (!getLocalTime(&timeinfo)) {
 Serial.println("Failed to obtain time");
 return;
 }
 strftime(timeStringBuff, sizeof(timeStringBuff), "%A, %B %d %Y %H:%M:%S", &timeinfo);
 asString = timeStringBuff;
 asString.replace(" ", "-");
 SensValue = analogRead(34);
 itoa(SensValue, numberArray, 10);

 sprintf(buffer, "values/[%d]/[1]", counter);
 valueRange.set(buffer, numberArray);
 sprintf(buffer, "values/[%d]/[0]", counter);
 valueRange.set(buffer, asString);
 sprintf(buffer, "Sheet1!A%d:B%d", 1 + counter, 10 + counter);

 success = GSheet.values.update(&response /* returned response */, spreadsheetId /* spreadsheet Id to update */, "Sheet1!A1:B1000" /* range to update */, &valueRange /* data to update */);
 response.toString(Serial, true);
 Serial.println();
 //valueRange.clear();
 delay(5000);
 }

 // For Google Sheet API ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update

 if (success)
 {

 Serial.println("\nGet spreadsheet values...");
 Serial.println("------------------------------");

 success = GSheet.values.get(&response /* returned response */, spreadsheetId /* spreadsheet Id to read */, "Sheet1!A1:B10" /* range to read */);
 response.toString(Serial, true);
 Serial.println();



#if defined(ESP32) || defined(ESP8266)
 Serial.println(ESP.getFreeHeap());
#elif defined(PICO_RP2040)
 Serial.println(rp2040.getFreeHeap());
#endif
 }
 }

 taskComplete = true;
 }
}

void tokenStatusCallback(TokenInfo info)
{
 if (info.status == esp_signer_token_status_error)
 {
 Serial.printf("Token info: type = %s, status = %s\n", GSheet.getTokenType(info).c_str(), GSheet.getTokenStatus(info).c_str());
 Serial.printf("Token error: %s\n", GSheet.getTokenError(info).c_str());
 }
 else
 {
 Serial.printf("Token info: type = %s, status = %s\n", GSheet.getTokenType(info).c_str(), GSheet.getTokenStatus(info).c_str());
 }
}

       spreadsheet.set("properties/title", "ENTER YOUR FILE NAME HERE");

        spreadsheet.set("sheets/properties/title", "ENTER YOUR SHEET TITLE HERE");
        spreadsheet.set("sheets/properties/sheetId", TYPE SHEET ID HERE (Ex. 1));
        spreadsheet.set("sheets/properties/sheetType", “ENTER SHEET TYPE HERE (Ex. "GRID")”);
        spreadsheet.set("sheets/properties/gridProperties/rowCount", ENTER SHEET ROW COUNT HERE (EX. 20) );
        spreadsheet.set("sheets/properties/gridProperties/columnCount", ENTER SHEET COLUMN COUNT HERE (EX. 10));

var sheet_id = '1Ffxy21v1vhflxuzJLBeuH0Y_nyqVw-DQq9pVF-66Xrc'; // Spreadsheet ID
var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet();