HOME ESP32 ESP8266 ESP32-CAM RASPBERRY PI MICROPYTHON RPi PICO ARDUINO REVIEWS

ESP32 Datalogging to Google Sheets (using Google Service Account)

In this project, you’ll learn how to log data to Google Sheets with the ESP32 securely and reliably using a Google Service Account and Google Sheets API. We’ll use the Arduino Google Sheets Client Library. After explaining the most important basic concepts, we’ll build a Datalogger that saves temperature, humidity, pressure, and corresponding timestamp on a Google spreadsheet.

ESP32 Datalogging to Google Sheets using Google Service Account

To successfully follow this tutorial, make sure you follow all the next steps in the following order:

  1. Prerequisites
  2. Google Service Account
  3. Installing the Arduino Google Sheet Client Library
  4. Creating a Google Spreadsheet
  5. Sharing the Spreadsheet with the Service Account
  6. ESP32 with BME280 Circuit
  7. ESP32 Datalogging to Google Sheets – Arduino Sketch

Note: we have another tutorial showing how to send data to Google Sheets, but using IFTTT: ESP32 Publish Sensor Readings to Google Sheets (ESP8266 Compatible).

1. Prerequisites

Before following this tutorial, make sure you check the following prerequisites:

Google Account

You need a Google Account to follow this project. If you don’t have a Google account, you can create one here.

ESP32 with Arduino IDE

We’ll program the ESP32 board using Arduino IDE. So, make sure you have the ESP32 add-on installed. Follow the next tutorial if you haven’t already:

Additionally, make sure you’re running the latest version of the ESP32 add-on. Go to Tools > Board > Boards Manager, search for ESP32, and check that you’re running the latest version.

2. Google Service Account

To log data securely to Google Sheets, we’ll use a Google Service Account. A service account, identified by its unique email address, is a special kind of account. It is typically used by an application or compute workload, like a Compute Engine instance, rather than being associated with a person. You can learn more about a service account.

Create a Google Project

You need to create a Google project and associate a Google service account to that specific project. You can do that on your main Google account or you can choose to do that on another secondary account. As always, we recommend that you use another account just for your IoT and ESP32 projects, and not your main account.

Create a Service Account

1. Go to Google Cloud Console.

2. Create a new project or choose an existing project. We’ll show you how to create a new project.

Create Google Project

3. Give a name to your project. Then, click Create.

Create Google Project

Your project will be created successfully.

Create google service account

4. Now, you need to create a service account for that project. At the left sidebar, click on Service accounts and then, click on + Create Service Account.

Create google service account 1

5. Insert a name for your Service account, then click Create and Continue.

Create google service account define set name

6. Select the service account role. Select Owner. Then, click Continue.

Create google service account 3

7. Finally, click Done.

Create google service account 2

You successfully create a Service Account. Now, you need to create a Key.

Creating a New Key

Select the project, click on the three dots icon, and then click on Manage Keys.

manage keys google cloud project

Then, create a new key.

create new key google cloud project

Then, select JSON and click Create.

Create private key google cloud

This will download a JSON file to your computer with the key.

Open the file, and you’ll get something similar, but with your own details:

{
  "type": "service_account",
  "project_id": "...",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY----- ...................\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": "https://www.googleapis.com/...",
  "universe_domain": "googleapis.com"
}

Copy the project_id, client_email, private_key_id and private_key from the .json file because you’ll need them later.

Enable Google Sheet API

Now that you have a project, you need to enable the Google Sheet API for that project. Click on the following link: https://console.cloud.google.com/apis/library/sheets.googleapis.com and enable Google Sheets API (you need to be on the same account where you created the project).

enable Google Sheets API

Enable Google Drive API

You also need to enable Google Drive API for your project. Open the following link https://console.cloud.google.com/apis/library/drive.googleapis.com and enable the Google Drive API.

enable Google Drive API

3. Installing the Arduino Google Sheet Client Library for Arduino devices

To publish readings to Google Sheets using the Google Service Account, we’ll use the ESP-Google-Sheet-Client library by Mobitz. This library comes with methods to create, read, and delete spreadsheets and write, update, and append data to the spreadsheet file. You can find all the library examples.

In the Arduino IDE, go to Sketch > Library > Manage Libraries. Search for ESP-Google-Sheet-Client and click Install.

install ESP Google Spreadsheet client library arduino ide

4. Creating a Google Spreadsheet

Go to Google Sheets and create a new spreadsheet. Give a title to your spreadsheet. For example ESP32 Datalogging.

spreadsheetid

Save the spreadsheet ID. It’s highlighted in the picture above. The Spreadsheet ID is the last string of characters in the URL for your spreadsheet. For example, in the URL https://docs.google.com/spreadsheets/d/1aISQE8K79LS5c3vF18qFRcmfDRFn_9nE4nKveWBCtoQ/edit#gid=0, the spreadsheet ID is 1aISQE8K79LS5c3vF18qFRcmfDRFn_9nE4nKveWBCtoQ.

5. Share the Spreadsheet with the Service Account

For you to be able to log data to that spreadsheet using the Google Service Account, as we’ll do in this tutorial, you need to share the spreadsheet with the service account email. You should get the service account email in the JSON file you downloaded previously saved on the client_email variable.

At the top right corner click on Share. Paste the service account email and click Send.

Share spreadsheet with google service account

6. ESP32 with BME280 Circuit

For this particular example, we’ll log data from a BME280 sensor. So, you need to wire a BME280 sensor to your ESP32. You can also use any other sensor you’re familiar with or adjust the code to publish random values if you don’t have a sensor at hand.

Not familiar with the BME280 sensor? Read our getting started guide: ESP32 with BME280 Sensor using Arduino IDE (Pressure, Temperature, Humidity).

Schematic Diagram

We’re going to use I2C communication with the BME280 sensor module. For that, wire the sensor to the default ESP32 SCL (GPIO 22) and SDA (GPIO 21) pins, as shown in the following schematic diagram.

ESP32 Wiring to BME280 Schematic Diagram

Recommended reading: ESP32 Pinout Reference: Which GPIO pins should you use?

7. ESP32 Datalogging to Google Sheets – Arduino Sketch

Copy the following code to the Arduino IDE. Don’t upload it yet. You need to fill in some details before uploading it to the board.

/*
  Rui Santos
  Complete project details at https://RandomNerdTutorials.com/esp32-datalogging-google-sheets/
  
  Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files.
  The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
  Adapted from the examples of the Library Google Sheet Client Library for Arduino devices: https://github.com/mobizt/ESP-Google-Sheet-Client
*/

#include <Arduino.h>
#include <WiFi.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.h>
#include "time.h"
#include <ESP_Google_Sheet_Client.h>

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

#define WIFI_SSID "REPLACE_WITH_YOUR_SSID"
#define WIFI_PASSWORD "REPLACE_WITH_YOUR_PASSWORD"

// Google Project ID
#define PROJECT_ID "REPLACE_WITH_YOUR_PROJECT_ID"

// Service Account's client email
#define CLIENT_EMAIL "REPLACE_WITH_YOUR_CLIENT_EMAIL"

// Service Account's private key
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----\ REPLACE_WITH_YOUR_PRIVATE_KEY\n-----END PRIVATE KEY-----\n";

// The ID of the spreadsheet where you'll publish the data
const char spreadsheetId[] = "YOUR_SPREADSHEET_ID";

// Timer variables
unsigned long lastTime = 0;  
unsigned long timerDelay = 30000;

// Token Callback function
void tokenStatusCallback(TokenInfo info);

// BME280 I2C
Adafruit_BME280 bme;
// Variables to hold sensor readings
float temp;
float hum;
float pres;

// NTP server to request epoch time
const char* ntpServer = "pool.ntp.org";

// Variable to save current epoch time
unsigned long epochTime; 

// Function that gets current epoch time
unsigned long getTime() {
  time_t now;
  struct tm timeinfo;
  if (!getLocalTime(&timeinfo)) {
    //Serial.println("Failed to obtain time");
    return(0);
  }
  time(&now);
  return now;
}

void setup(){

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

    //Configure time
    configTime(0, 0, ntpServer);

    // Initialize BME280 sensor 
    if (!bme.begin(0x76)) {
      Serial.println("Could not find a valid BME280 sensor, check wiring!");
      while (1);
    }

    GSheet.printf("ESP Google Sheet Client v%s\n\n", ESP_GOOGLE_SHEET_CLIENT_VERSION);

    // Connect to Wi-Fi
    WiFi.setAutoReconnect(true);
    WiFi.begin(WIFI_SSID, WIFI_PASSWORD);
  
    Serial.print("Connecting to Wi-Fi");
    while (WiFi.status() != WL_CONNECTED) {
      Serial.print(".");
      delay(1000);
    }
    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);

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

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

    if (ready && millis() - lastTime > timerDelay){
        lastTime = millis();

        FirebaseJson response;

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

        FirebaseJson valueRange;

        // New BME280 sensor readings
        temp = bme.readTemperature();
        //temp = 1.8*bme.readTemperature() + 32;
        hum = bme.readHumidity();
        pres = bme.readPressure()/100.0F;
        // Get timestamp
        epochTime = getTime();

        valueRange.add("majorDimension", "COLUMNS");
        valueRange.set("values/[0]/[0]", epochTime);
        valueRange.set("values/[1]/[0]", temp);
        valueRange.set("values/[2]/[0]", hum);
        valueRange.set("values/[3]/[0]", pres);

        // For Google Sheet API ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
        // Append values to the spreadsheet
        bool success = GSheet.values.append(&response /* returned response */, spreadsheetId /* spreadsheet Id to append */, "Sheet1!A1" /* range to append */, &valueRange /* data range to append */);
        if (success){
            response.toString(Serial, true);
            valueRange.clear();
        }
        else{
            Serial.println(GSheet.errorReason());
        }
        Serial.println();
        Serial.println(ESP.getFreeHeap());
    }
}

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

View raw code

Insert your network credentials on the following variables.

#define WIFI_SSID "REPLACE_WITH_YOUR_SSID"
#define WIFI_PASSWORD "REPLACE_WITH_YOUR_PASSWORD"

Insert your project ID, your client email, and your private key (you can find these details on the JSON file you downloaded in the previous steps).

// Google Project ID
#define PROJECT_ID "REPLACE_WITH_YOUR_PROJECT_ID"

// Service Account's client email
#define CLIENT_EMAIL "REPLACE_WITH_YOUR_CLIENT_EMAIL"

// Service Account's private key
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----\ REPLACE_WITH_YOUR_PRIVATE_KEY\n-----END PRIVATE KEY-----\n";

Finally, insert the ID of the spreadsheet you want to publish your data.

// The ID of the spreadsheet where you'll publish the data
const char spreadsheetId[] = "YOUR_SPREADSHEET_ID";

Note: if your Google Sheets is not in English language, you might need to change Sheet1 with the corresponding in your language on the following line.

bool success = GSheet.values.append(&response /* returned response */, spreadsheetId /* spreadsheet Id to append */, "Sheet1!A1" /* range to append */, &valueRange /* data range to append */);

You can now upload the code to your ESP32 board.

How the Code Works

Continue reading to learn how the code works, or skip to the Demonstration section.

Including Libraries

First, include the required libraries. We need the WiFi library to connect our board to the internet, the Adafruit_sensor and Adafruit_BME280 to interface with the BME280, the time.h library to get the timestamp and finally, the ESP_Google_Sheet_Client to interface the ESP32 with Google Sheets.

Network Credentials

Insert your network credentials on the following variables.

#define WIFI_SSID "REPLACE_WITH_YOUR_SSID"
#define WIFI_PASSWORD "REPLACE_WITH_YOUR_PASSWORD"

Project Details

Insert your project ID, service account email, and account private key. All these details can be found on the JSON file you downloaded previously.

//Google Project ID
#define PROJECT_ID "REPLACE_WITH_YOUR_PROJECT_ID"

// Service Account's client email
#define CLIENT_EMAIL "REPLACE_WITH_YOUR_CLIENT_EMAIL"

// Service Account's private key
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----\ REPLACE_WITH_YOUR_PRIVATE_KEY\n-----END PRIVATE KEY-----\n";

Insert the google spreadsheet id on the following variable.

const char spreadsheetId[] = "YOUR_SPREADSHEET_ID";

BME280 Variables

Create an instance for the BME280 called bme and create variables to save temperature, humidity, and pressure.

// BME280 I2C
Adafruit_BME280 bme;
// Variables to hold sensor readings
float temp;
float hum;
float pres;

Timestamp Variables

We create a variable with the ntp server we’ll request time from and a variable to save the current epoch time.

// NTP server to request epoch time
const char* ntpServer = "pool.ntp.org";

// Variable to save current epoch time
unsigned long epochTime;

We also create a function to return the current epoch time called getTime().

// Function that gets current epoch time
unsigned long getTime() {
  time_t now;
  struct tm timeinfo;
  if (!getLocalTime(&timeinfo)) {
    //Serial.println("Failed to obtain time");
    return(0);
  }
  time(&now);
  return now;
}

Learn more about epoch time with the ESP32: Get Epoch/Unix Time with the ESP32 (Arduino).

setup()

In the setup(), initialize the Serial Monitor for debugging purposes.

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

Configure the time.

//Configure time
configTime(0, 0, ntpServer);

Initialize the BME280 sensor.

// Initialize BME280 sensor 
if (!bme.begin(0x76)) {
  Serial.println("Could not find a valid BME280 sensor, check wiring!");
  while (1);
}

Connect the ESP32 to your local network.

// Connect to Wi-Fi
WiFi.setAutoReconnect(true);
WiFi.begin(WIFI_SSID, WIFI_PASSWORD);
  
Serial.print("Connecting to Wi-Fi");
while (WiFi.status() != WL_CONNECTED) {
  Serial.print(".");
  delay(1000);
}
Serial.println();
Serial.print("Connected with IP: ");
Serial.println(WiFi.localIP());
Serial.println();

Then, the following lines configure and initiate the authentication process for accessing Google APIs, including setting up a callback for token generation status, specifying a token refresh interval, and starting the generation of an access token with the relevant authentication credentials (CLIENT_EMAIL, PROJECT_ID, and PRIVATE_KEY that you’ve defined previously).

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

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

loop()

In the loop(), you need to call the following line so that it constantly checks the authentication.

bool ready = GSheet.ready();

Then, we’ll periodically publish sensor readings to Google Sheets. You can adjust the interval on the timerDelay variable defined at the start of the code.

if (ready && millis() - lastTime > timerDelay){

We create a JSON object called valueRange where we’ll add our data. The Google Sheets Client library we’re using with the ESP32 uses the FirebaseJson library to handle JSON objects. To keep compatibility with other examples in the library, we’re also using that library.

FirebaseJson valueRange;

We then get new BME280 readings and a corresponding timestamp.

// New BME280 sensor readings
temp = bme.readTemperature();
//temp = 1.8*bme.readTemperature() + 32;
hum = bme.readHumidity();
pres = bme.readPressure()/100.0F;
// Get timestamp
epochTime = getTime();

These values are added to a FirebaseJson object (valueRange).

valueRange.add("majorDimension", "COLUMNS");
valueRange.set("values/[0]/[0]", epochTime);
valueRange.set("values/[1]/[0]", temp);
valueRange.set("values/[2]/[0]", hum);
valueRange.set("values/[3]/[0]", pres);

The valueRange object is configured to have a “COLUMNS” major dimension, indicating that the data will be organized column-wise. The timestamp will be located at the first column, first row; the temperature in the second column, first row, etc. If you want to organize in rows, you can pass “ROWS” as an argument instead.

Then, append this data to a specific sheet (Sheet1) starting at cell A1 in a Google Spreadsheet identified by its spreadsheetId. The values in valueRange are mapped to specific cells in the sheet, such as the timestamp in column A and the sensor readings in subsequent columns.

// Append values to the spreadsheet
bool success = GSheet.values.append(&response /* returned response */, spreadsheetId /* spreadsheet Id to append */, "Sheet1!A1" /* range to append */, &valueRange /* data range to append */);
if (success){
    response.toString(Serial, true);
    valueRange.clear();
}
else{
    Serial.println(GSheet.errorReason());
}

Note: if your Google Sheets is not in English language, you might need to change Sheet1 with the corresponding in your language on the following line.

bool success = GSheet.values.append(&response /* returned response */, spreadsheetId /* spreadsheet Id to append */, "Sheet1!A1" /* range to append */, &valueRange /* data range to append */);

Demonstration

After uploading the code to the ESP32, open the Serial Monitor to check the process.

The ESP32 should successfully connect to your Wi-Fi network and after 30 seconds, it should publish its first reading.

ESP32 Publishing to Google Sheets Serial Monitor

Open the spreadsheet where you’re publishing the values. You should see new values being published in real-time. The first column contains the epoch time, then the temperature in Celsius degrees, the humidity, and finally the pressure in hPa.

ESP32 Data Published to Google Spreadsheet

We’re using the timestamp in epoch time. To convert it to a readable format, you can simply use the EPOCHTODATE() function.

Wrapping Up

We hope you’ve found this project useful. As an example, we’ve shown you how to log data from a BME280 sensor, but you easily modify this project to use any other sensor. We have tutorials for more than 25 sensors with the ESP32.

The Arduino Google Sheets Client library has many other features and useful methods that might be handy for your project. For example, instead of you having to create the spreadsheet manually, the library comes with a method to automatically create the spreadsheet. It can also read, update, and append data to the spreadsheet. We recommend taking a look at the library examples to have an idea of what this powerful library can do.

We also have a similar project, but using a different approach. Instead of using a Google Service Account, we use IFTTT services: ESP32 Publish Sensor Readings to Google Sheets using IFTTT. This method is more limited, but might also be a good option depending on your project requirements.

Looking for other data logging methods? Read: ESP32: How to Log Data (9 Different Ways)

Learn more about the ESP32 with our resources:

Thanks for reading.



SMART HOME with Raspberry Pi ESP32 and ESP8266 Node-RED InfluxDB eBook

SMART HOME with Raspberry Pi, ESP32, ESP8266 [eBook]

Learn how to build a home automation system and we’ll cover the following main subjects: Node-RED, Node-RED Dashboard, Raspberry Pi, ESP32, ESP8266, MQTT, and InfluxDB database DOWNLOAD »
Learn how to build a home automation system and we’ll cover the following main subjects: Node-RED, Node-RED Dashboard, Raspberry Pi, ESP32, ESP8266, MQTT, and InfluxDB database DOWNLOAD »

Recommended Resources

Build a Home Automation System from Scratch » With Raspberry Pi, ESP8266, Arduino, and Node-RED.

Home Automation using ESP8266 eBook and video course » Build IoT and home automation projects.

Arduino Step-by-Step Projects » Build 25 Arduino projects with our course, even with no prior experience!

What to Read Next…


Enjoyed this project? Stay updated by subscribing our newsletter!

71 thoughts on “ESP32 Datalogging to Google Sheets (using Google Service Account)”

    • Hi.
      Yes. It is compatible with the ESP8266.
      Just make sure you use the ESP8266WiFi library and related methods instead.
      Regards,
      Sara

      Reply

  3. This is great! I was able to update my project that was using IFTTT applet with this publishing method and it worked!
    I am publishing OBDII data from my EV car. Some values were of “Char” type. I had to modify those otherwise I was getting compile errors. Other thing, since I am using french language, my google sheet was named “Feuille 1” instead of “Sheet1”. This was preventing parsing since it could not find a sheet names “Sheet1”.

    Reply

    • Hi.
      That’s great. This method is much better than the one with IFTTT.
      Thank for telling me abou the Sheet1 issue. I wasn’t aware of that. I’ll add a note about that issue.
      Regards,
      Sara

      Reply

      • Hello Sara,

        As I’m using the esp32 in my car, I have setup my phone as a wifi hotspot to access internet. When I’m in an area where the cellular signal is weak, I get errors sending data to Google sheet. After some unsuccessful transmit, the ESP32 crashes and reboots. I was not getting these ESP32 reboot when using the IFTTT applet.

        I am using a 3.5″ display, I didn’t want the displayed values to be interrupted while data is sent to Google sheet, so I use xTaskCreatePinnedToCore to run the Datalogging on Core 0 instead of having the code in the Loop. I set the Task priority to 0 and stack size to 10000. I was not able to capture the error message since this behavior is a bit random and I don’t have a Serial monitor.

        In the following code:
        if (success){
        response.toString(Serial, true);
        valueRange.clear();
        }
        else{
        Serial.println(GSheet.errorReason());
        }

        valueRange is cleared if data if successfully sent to Google sheet. Should I clear it also in case of an error?

        Reply

  4. This is great, it makes transmission of data much easier. I am curious approximately when was this service of being able to use this api to send data directly made available, I have been trying to find a direct way such as this for years?

    Reply

  5. Hi Sara, thanks a lot for this excellent project. It works fine!
    Only one comment for those not living in an English speaking country:
    When addressing the spreadsheet, one eventually must change the sheet name into the local name.
    For instance here in Germany one must use “Tabelle1!A1” instead of “Sheet1!A1”
    It took me a while to debug this small problem.
    Thank for your great work, Sara!

    Reply

  6. Hello,

    Everything went smoothly til Step 5 where I was unable to paste the service account email address from the .json file into the Share function of the Spreadsheet Log.
    There is a data entry block for Add People and Groups but it will not allow me to paste
    the service account email address into the field.
    Any ideas,

    Ed Delauter

    Reply

  7. Update to my prior inquiry –
    I manually entered the email address and hit ‘Done’. It seemed to take it. Is there a way to verify if the link was setup? thanks.

    Reply

  8. I found it useful to double click on the sheet name and change it to something meaningful. That makes it easier to use multiple sheets in the same workbook for different data sources.

    Reply

  9. Thank you for this post. It works very well with the Arduino IDE but on the other hand I cannot get it to work with VS Code and Platformio. I have this error: c:\users\herve\documents\arduino\libraries\esp-google-sheet-client\src\gs_helper.h:183:35: error: ‘class WiFiClass’ has no member named ‘getTime’; did you mean ‘getMode’?

    Reply

      • thank you, in fact I think I have a conflict with an old library installed on my PC. to solve the problem I modified the platformio.ini like this: [env:esp32dev]
        platform = espressif32
        build_flags = -D PIO_FRAMEWORK_ARDUINO_MMU_CACHE16_IRAM48_SECHEAP_SHARED
        board = esp32dev
        framework = arduino
        ;lib_extra_dirs = ~/Documents/Arduino/libraries
        lib_deps = mobizt/ESP-Google-Sheet-Client@^1.4.3

        Reply

  10. Dear Sara;
    This is my error message in serial monitor: Any suggestion?
    Thanks in advance anyway.
    Francisco.

    ESP Google Sheet Client v1.4.3
    Connecting to Wi-Fi..
    Connected with IP: 192.168.24.121

    Token info: type = OAuth2.0 access token, status = on initializing
    Token info: type = OAuth2.0 access token, status = error
    Token error: code: -111, message: System time or library reference time was not set. Use GSheet.setSystemTime to set time.

    Reply

      • Thanks again Sara;
        I improve a little, dunno why: Of course nothing in the sheet.

        ESP Google Sheet Client v1.4.3
        Connecting to Wi-Fi…………….
        Connected with IP: 172.20.10.2

        Token info: type = OAuth2.0 access token, status = on initializing
        Token info: type = OAuth2.0 access token, status = on signing
        Token info: type = OAuth2.0 access token, status = on exchange request
        Token info: type = OAuth2.0 access token, status = ready

        Append spreadsheet values…

        unknown error

        247652

        Reply

        • Finally I got the values, just changing the WiFi Server to my iphone hotspot and renaming “Sheet1” to “Hoja1” and no blank spaces.¿?
          Thanks again for a great practice.
          Best regards.

          Reply

  11. Great tutorial. I have followed it blindly without knowing exactly how does it work, but it works. Now that I’m able to save data from my ESP32 on a Google spreadsheet, I will take my time to understand what I’m doing. Thanks very much for your help !

    Reply

  12. Hi,
    Thank you very much for this tutorial. I found the steps in section 2 for the Google service account very useful.
    Do you have a similar one for datalogging from ESP 32 to Amazon S3?

    Regards,
    Marie

    Reply

  13. Help .. Any Ideas ? Where can I find an explanation of the error codes?
    Connected
    IP address = 192 .168 .1 .102
    spreadsheetId : ESP32 Datalogging
    PROJECT_ID : xxxxxx
    CLIENT_EMAIL : [email protected]
    PRIVATE_KEY : —–BEGIN PRIVATE KEY—–xxxx\n—–END PRIVATE KEY—–\n

    ESP Google Sheet Client v1.4.3

    Token info: type = OAuth2.0 access token, status = on initializing
    Token info: type = OAuth2.0 access token, status = on signing
    Token info: type = OAuth2.0 access token, status = error
    Token error: code: -107, message: BearSSL, isRSA: BearSSL, isRSA: BearSSL, isRSA: …….
    Token info: type = OAuth2.0 access token, status = error
    Token error: code: -107, message:
    Token info: type = OAuth2.0 access token, status = error
    Token error: code: -107, message:
    Token info: type = OAuth2.0 access token, status = error
    Token error: code: -107, message:
    ERROR

    Reply

    • Why is it no one noticed in the display above the spreadsheetId was a name,
      ESP32 Datalogging, and it needs an id, eg ….7ce0F06xY3MyqusXc272PPMMD90YEgcjMdxZOCM….
      provided by google and NOT the name of the document?
      Would have saved me hours of frustration.
      My problem still exist but this was one of the issues.

      Reply

      • Hi.
        What do you mean?
        We mention in the tutorial that you need the spreadsheet ID and we even have it highlighted in yellow in the screenshot.
        In the code, there’s also a comment mentioning it is the ID of the spreadsheet,
        // The ID of the spreadsheet where you’ll publish the data
        const char spreadsheetId[] = “YOUR_SPREADSHEET_ID”;

        Please double-check all your details.

        Regards,
        Sara

        Reply

  14. Excellent Tutorial! Now that IFTTT has made Webhooks a pro version, my ESP32 logging if IFTTT stopped working. Your tutorial made it very easy to convert my code to directly logging with the Google sheets API. Thanks so much.

    Reply

  15. hey, when we are entering private key into “const char PRIVATE_KEY[] PROGMEM =” we are supposed to put private key id or all private key info?

    Reply

  16. Hi,

    Would there be any chance of a tutorial like this but using a different sensor like MLX90614? Actually, I already am trying it with MLX90614 but it keep on displaying “Token errors….”

    Thanks,
    Elle

    Reply

  17. Great project … thank you.

    I was receiving this error message in Serial Monitor and Google Sheet wasn’t getting updated:

    Guru Meditation Error: Core 1 panic’ed (LoadProhibited). Exception was unhandled.

    The issue was that I wasn’t including the Private Key properly. In Jason file the Private Key is a long string. Copy the entire line from Jason file into Arduino Sketch.

    Once I entered the Private Key completely, it started to work and Google sheet updates with the data now.

    Reply

  18. I am still fighting with this error:
    Token info: type = OAuth2.0 access token, status = on initializing
    Token info: type = OAuth2.0 access token, status = on signing
    Token info: type = OAuth2.0 access token, status = error
    Token error: code: -107, message:
    ERROR

    Anyone have any idea where to look to resolve this?

    Looking at the GS_errors.h
    #define ESP_GOOGLE_SHEET_CLIENT_ERROR_RANGE -100
    and
    #define ESP_GOOGLE_SHEET_CLIENT_ERROR_TOKEN_PARSE_PK /* */ (ESP_GOOGLE_SHEET_CLIENT_ERROR_RANGE – 7)

    Both point to the SHEET_CLIENT_ERROR_RANGE but this really doesn’t tell me anything.

    Frank

    Reply

  21. Hi Sara, great tutorial! I’ve made a slightly amended version of the code in your example, whereby rather than sending periodic sensor values through to google sheets, it does so when an event occurs (button press), reading the time and date from an RTC module, which all works fine so far. What I’m struggling with is that after 5 minutes of inactivity, the module fails to post to google sheets, but then on a second press will start working again as usual (until a further 5 minutes of inactivity). Any ideas on how to get around this? I’m trying to use as a rain gauge which will have large amounts of inactivity. Thanks, Matt

    Reply

    • I seem to have the same problem. Here a sensor should be sent every 10 minutes to the Google sheet. Tried experimenting with the GSheet.setPrerefreshSeconds(10 * 60); but not entrily sure what is happening there.
      if I choose an interval of 5 minutes it mostly skips each second one. so all in all I get on reading every 10 minutes in the Sheet. But I don’t think that’s how it should work.
      using the GSheet.begin(CLIENT_EMAIL, PROJECT_ID, PRIVATE_KEY);
      didn’t improve any of the behaviour either.

      tips are welcome!

      Reply

  22. HI Sara,
    me too sometimes, as others have said in previous posts, randomly get the following error:

    Add Spreadsheet Values…

    Token info: type = OAuth2.0 access token, status = on initializing
    Token info: type = OAuth2.0 access token, status = on signing
    Token info: type = OAuth2.0 access token, status = on exchange request
    Token info: type = OAuth2.0 access token, status = ready
    unknown error

    For example, if I set a data transmission every 15 minutes, I sometimes get 15 minutes and other times 30 minutes because one out of two (or more) I get the aforementioned error!

    have you or anyone any suggestions please?
    I can’t do it alone and I really need your help, please

    Thank you in advance
    Claudio

    Reply

    • …..other times I obtain this messages

      Token info: type = OAuth2.0 access token, status = on initializing
      Token info: type = OAuth2.0 access token, status = on signing
      Token info: type = OAuth2.0 access token, status = on exchange request
      Token info: type = OAuth2.0 access token, status = error
      Token error: code: 408, message: request timed out
      request timed out

      Reply

      • @claudio, i had this too. fixed with every time force get a new OAuth token by re-initializating the GSheet library (call GSheet.begin again).

        Reply

        • I Dominggus,
          Thanks for your answer.
          so I should use GSheet.begin every time before

          bool success = GSheet.values.append(&response, spreadsheetId, “Sheet1!A1”, &valueRange);

          if (success){
          response.toString(Serial, true);
          valueRange.clear();
          }
          else{
          Serial.println(GSheet.errorReason());
          }
          Serial.println();
          Serial.println(ESP.getFreeHeap());
          …..

          Other questions: what is it for
          – response.toString(Serial, true); ?
          and
          – ready = GSheet.ready(); ?

          Reply

  23. Yo! This works for me, thank you so much!
    I think it is usefull to notify that compiling (before uploading to the board) took several minutes at my laptop.
    I thought it hung, but eventually it terminates with uploading the sketch. So: don’t panic if it takes quite a while!

    Reply

  24. Please to help me, in my google sheets can’t show decimal numbers when ESP32 sent decimal numbers in the google sheets show 0.
    if ESP sent integer numbers in the google sheet show normally.

    Reply

  25. FYI the library also supports using a GSM client (TinyGSM), using the 4G LTE modem. instead of relying on a Wifi/ethernet connection..

    Reply

    • Hi Dominggus, that’s exactly what I’m willing to create. Do you have any hints, tips or examples on setting up such a connection and post into a GSheet?
      I’ld be very gracefull 😉

      Reply

  27. Thank you very much for the explanation, it worked for me!
    But I have a question, every time I turn esp32 off and on it doesn’t write the data until it reaches an empty cell, isn’t there a way to replace Sheet1!A1 with some reference that directly finds the next empty cell of A?

    Reply

    • I don’t know whether it can be done directly from this library used here.

      I would work around your goal by:
      – preparing a new, empty sheet
      – use that sheet name in this script here so the data gets written there
      – write some code in Google’s Script Editor that
      + copies these data
      + finds the last non empty row on Sheet1
      + dumps the additional data from that point on
      + delete the data on the prepared sheet

      However, I’m comfortable with Script Editor, don’t know about you.

      Reply

  28. Hi there!
    I’m just following your steps and I can’t get past the compile. I get the error
    exit status 1
    ‘GSheet’ does not name a type
    on line
    GSheet.begin(CLIENT_EMAIL, PROJECT_ID, PRIVATE_KEY);
    tired version 1.4.3 and 1.4.4. of the library… just doesn’t work and also I have no clue.
    any ideas?

    Reply

  30. I am trying to log data of two or more esp32’s which sends data to a slave esp32 from there i should be able to log the data in google sheets , the thing is i was able to get the data from the esp32’s to the slave esp32 but if i have to log data when ever i receive data i am facing issue can anyone help me on this, i was able to log data in google sheet but i am missing out some data which should be logged.

    by the way thanks for the detailed tutorials which explains the code super neat.
    thank you , any inputs on this is appreciated.

    Reply

  31. hello, very interesting tutorial, it works great. But how can i modify If I dont want time stamp like “1715969704” But I would like have column Date (17.05.2024) and Time (21:15)? I guess it is in :
    unsigned long getTime() {
    time_t now;
    struct tm timeinfo;
    if (!getLocalTime(&timeinfo)) {
    //Serial.println(“Failed to obtain time”);
    return(0);
    }
    time(&now);
    return now;
    }

    but i am not able to figure out.

    Thank you very much

    Reply

  32. Hello Sara, Thanks for the tutorial, It works well, but I noticed that when I try to connect to a WiFi Router of an Internet provider that has 4 minutes shifted of time (my time now is 14:49 but the time in the router is 14:53) the program send me an error of token autentication

    Token info: type = OAuth2.0 access token, status = on initializing
    Token info: type = OAuth2.0 access token, status = error
    Token error: code: -111, message: System time or library reference time was not set. Use GSheet.setSystemTime to set time.
    Token info: type = OAuth2.0 access token, status = error

    I was looking for information about how to use GSheet.setSystemTime, but I didn´t find info

    Regards

    Reply

  33. Thanks for an useful and easy-understanding tutorial. After compiling, i run into a problem, which is “Unable to parse range: Sheet1!A1”. Can you help me with this?
    Regards.

    Reply

    • Hi.
      Make sure your google sheets has a sheet called Sheet1.
      If your system is not in english, you may need to change “Sheet1” to whatever shows on your sheet name in google sheets.
      Regards,
      Sara

      Reply

  34. Well done Sara. I have a Firebeetle ESP32-E which doesn’t support an SD card. During compilation, this error pops
    c:\Users\Me13W\Documents\Arduino\libraries\SD\src/utility/Sd2PinMap.h:524:2: error: #error Architecture or board not supported.
    Is there an edit I can make to a file that simply eliminates the SD call?

    Thank you.

    Reply

  35. This is Mark regarding c:\Users\Me13W\Documents\Arduino\libraries\SD\src/utility/Sd2PinMap.h:524:2: error: #error Architecture or board not supported.

    The library path was incorrect. All fixed.

    Reply

  36. Great Tutorial.
    But I encountered a problem.
    It seems that my environment variable “TZ” {setenv(“TZ”,xxxx)} information is being wiped out whenever I do a GSheet.append.
    Any suggestions?

    Reply

  37. More info on my previous post.

    It seems when I make the call
    bool ready = GSheet.ready();
    my environment variable “TZ” get changed
    from PST8PDT,M3.2.0,M11.1.0
    to UTC0DST0.
    To verify this I put Serial.println(getenv(“TZ”));
    both before and after the call.

    Reply

  38. Hello Sara,
    I have been exploring this library as I also want to add a chart to my spreadsheet.
    Have you tried using the batchUpdate method with an addChart request?

    I keep having the following error:
    “Compilation error: no matching function for call to ‘ESP_Google_Sheet_Client::batchUpdate(FirebaseJson*, String&, FirebaseJson&)’ ”

    and I do not understand what it means. Can you give me a hint?

    Thank you,
    Francisca

    Reply

Leave a Comment

Download Our Free eBooks and Resources

Get instant access to our FREE eBooks, Resources, and Exclusive Electronics Projects by entering your email address below.