Sending Data from ESP32 or ESP8266 to Google Sheets

Are you tired of manually logging data from your ESP32? Would you like to automate the process? Well, the good news is that it’s very simple. In this article, we will cover a comprehensive, step-by-step process on how to send data from either an ESP32 or ESP8266 to Google Sheets., we use ESP32 for communicating with Google Sheets, sending and reading data.

In this project, we have used the ESP-WROOM-32 development board. But you can use the ESP-WROOM-32 module or any other ESP32 Board.

You can also use ESP8266, as all steps remain the same.

Step 1: Preparing Google Sheets

Create a “Service Account Private Key” for yourself and enable both the “Google Sheets API” and “Google Drive API.”First, sign in to your Google Account.

Tip :If you plan to use your account for a long time—without enabling the IP-changing software—you may lose access to Google services.It is recommended to regularly use IP-changing software if you plan on using it for a long time. If not possible, you can try the second method: “Google Apps Scrip.”

Enabling “Google Sheets API” and “Google Drive API”

To enable Google Sheets API, go to this link and click on “Enable.”Similarly, to enable Google Drive API, go to this link and click on “Enable.”

Creating a Service Account Private Key

Open the Google Cloud Console page and then follow the steps below:1- Create a new project to have a Service Account.2- Click on “Service Account” (see the image below).

3- Click on “+CREATE SERVICE ACCOUNT.”

4- Type in the name of the Service Account.5- After choosing the name for Service Account, if the Service Account ID is not automatically completed or if you want to choose another name, modify it with your desired name.6- Click on “Create and Continue.”

7- Select the “Role” type (here: Owner).8- Click “Continue.”9- Finally, click “Done.”10- Select the created Service Account.

11- Click on the “KEYS” button.12- Click “ADD KEY.”

13- Select “JSON” (for the Key Type) and click “Create.”

14- (You have now successfully created your private key and it will be downloaded automatically). Click “Close.”

Tip:Steps 15 and 16 are for entering the Service Account details in your Arduino code. You can skip them if you want the library commands to read the “Service Account JSON Key” file.

15. Open the downloaded JSON file with text editing software. It will open a text similar to the following:

{
  "type": "service_account",
  "project_id": "...",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "...",
  "client_id": "...",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "..."
}

Save “project_id, ” “client_email, ” “private_key_id, ” and “private_key” mentioned in the code. You will need them for Arduino coding.

Step 2: Wiring

Connect the sensor’s VCC and GND pins to the corresponding pins on the ESP32 board (VIN and GND or 5V and GND) and the AO pin of the sensor to pin #34 of the ESP32 board. The type of ESP32 board does not change the wiring.

Step 3: Installing Library

To start coding, you must first install the required library.In Arduino IDE, go to Sketch->Include Library->Manage Libraries.In the Library Manager window, search for “Sheet” and install “ESP Spreadsheet Client” from the options.If you need more help, read how to install a library in Arduino IDE.

Step 4: Arduino Code (for Logging Data from ESP32 to Google Sheets)

After installing the library, create a new Arduino sketch and copy the following code into it.

/**
 Created by M.Merati
 www.Electropeak.com
**/

// 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 "esp32test20230529@logical-utility-388206.iam.gserviceaccount.com"

// Your email to share access to spreadsheet
#define USER_EMAIL "majid.merati.2022@gmail.com"

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

For a detailed code explanation, you can read the full guide here.

Step 5: Uploading Code to ESP32

Now, program the ESP32 board. By running the code on ESP32, a sheet file will be created (as shown in the image below.)As you can see, the current system date and time are inserted in column A, while the gas sensor data is inserted in column B.

After running the code, the values read by ESP32 from Google Sheets are displayed in the serial port as shown below.

To verify the values, compare the numbers entered in Google Sheets with those displayed in the serial monitor.

Tip:If you want to use a pre-prepared sheet file for reading or writing data, it is necessary to share that file with the email address of the “Service Account Client.”The files created by the codes in this tutorial are automatically shared with your account.

You can also send ESP32 data to Google Sheets by using Google Apps Scripts.