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.

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

Prerequisites
Google Service Account
Installing the Arduino Google Sheet Client Library
Creating a Google Spreadsheet
Sharing the Spreadsheet with the Service Account
ESP32 with BME280 Circuit
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:

Install the ESP32 Board in Arduino IDE

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.

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

Your project will be created successfully.

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.

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.

7. Finally, click Done.

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.

Then, create a new key.

Then, select JSON and click Create.

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

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.

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.

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.

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, 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 »

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…

ESP32 I2C Communication: Set Pins, Multiple Bus Interfaces and Peripherals (Arduino IDE)

Enjoyed this project? Stay updated by subscribing our newsletter!

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

Felix

January 19, 2024 at 4:47 am

Thanks for publishing! Very interesting material and I’ve already found a use for it.
Reply
- Sara Santos
  
  January 19, 2024 at 8:24 pm
  
  Hi.
  That’s great!
  Thanks for letting me know.
  Regards,
  Sara
  Reply
  - SRINITH K
    
    October 16, 2024 at 4:23 am
    
    It is very useful for my project
    Reply
    - Sara Santos
      
      October 16, 2024 at 1:05 pm
      
      That’s great.
      Regards,
      Sara
      Reply
Petros Khachatryan

January 19, 2024 at 3:55 pm

Hello. Thank you so much for informative tutorial. One question. Cau I use library with ESP8266?
Reply
- Sara Santos
  
  January 19, 2024 at 8:22 pm
  
  Hi.
  Yes. It is compatible with the ESP8266.
  Just make sure you use the ESP8266WiFi library and related methods instead.
  Regards,
  Sara
  Reply
Eric Emond

January 19, 2024 at 9:20 pm

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
- Sara Santos
  
  January 19, 2024 at 10:58 pm
  
  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
  - Eric Emond
    
    February 2, 2024 at 2:08 pm
    
    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
Steven Falivene

January 20, 2024 at 9:07 am

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
Hans

January 20, 2024 at 10:18 am

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
- Sara Santos
  
  January 20, 2024 at 11:43 pm
  
  Hi.
  Thanks for letting me know.
  I already added a note about that issue in the tutorial.
  Regards,
  Sara
  Reply
Ed DeLauter

January 20, 2024 at 10:44 pm

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
Ed DeLauter

January 20, 2024 at 11:03 pm

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
- Sara Santos
  
  January 20, 2024 at 11:47 pm
  
  Hi.
  I’m not sure.
  I assume that it was shared when you hit the send button.
  Regards,
  Sara
  Reply
Felix

January 21, 2024 at 12:10 am

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
Herve

January 21, 2024 at 3:49 pm

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
- Sara Santos
  
  January 22, 2024 at 7:20 pm
  
  Hi.
  How did you include the library?
  Regards
  Sara
  Reply
  - Herve
    
    January 23, 2024 at 6:56 am
    
    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
    - Sara Santos
      
      January 23, 2024 at 10:02 am
      
      Great.
      I’m glad the issue is solved.
      Regards,
      Sara
      Reply
Francisco

January 23, 2024 at 4:26 pm

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
- Sara Santos
  
  January 23, 2024 at 9:54 pm
  
  Hi.
  I’m not sure… but check your credentials and details.
  Regards,
  Sara
  Reply
  - Francisco
    
    January 27, 2024 at 2:25 pm
    
    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
    - Frank
      
      February 1, 2024 at 6:18 pm
      
      I am getting the exact same error and cannot figure out a solution.
      Reply
    - Francisco
      
      February 4, 2024 at 12:19 pm
      
      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
Joan

January 24, 2024 at 11:33 am

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
- Sara Santos
  
  January 24, 2024 at 2:39 pm
  
  That’s great!
  Thank you for your feedback.
  Regards,
  Sara
  Reply
Marie

January 31, 2024 at 12:13 am

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
- Sara Santos
  
  January 31, 2024 at 3:50 pm
  
  Hi.
  Unfortunately, we don’t have any tutorials about that subject.
  Regards,
  Sara
  Reply
Frank

February 1, 2024 at 6:22 pm

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
- Frank
  
  February 29, 2024 at 7:24 pm
  
  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
  - Sara Santos
    
    March 1, 2024 at 3:17 pm
    
    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
Chris

February 5, 2024 at 9:34 am

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
- Sara Santos
  
  February 5, 2024 at 9:38 am
  
  Hi.
  Thanks great.
  I’m glad it was helpful.
  Regards,
  Sara
  Reply
Rob

February 7, 2024 at 4:38 pm

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
Elle Ligaya

February 10, 2024 at 10:19 am

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
Hossein

February 15, 2024 at 5:21 pm

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
Frank

February 22, 2024 at 6:59 pm

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
sz

February 28, 2024 at 3:59 pm

is it a biiling service?

regards
Reply
- Sara Santos
  
  February 29, 2024 at 10:46 am
  
  No.
  It’s free.
  Reply
  - sz
    
    March 1, 2024 at 1:32 pm
    
    thanks Sara
    Reply
Antoine Najjar

March 6, 2024 at 6:13 am

Very nice work interesting.
What is the fastest time achieved for data upload in sample per second ?
Reply
Matt

March 7, 2024 at 11:22 pm

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
- Frank
  
  March 9, 2024 at 1:57 pm
  
  GSheet.setPrerefreshSeconds(10 * 60);
  
  Have you lowered the refresh time? Try 300 vs 600
  Reply
  - Matt
    
    March 11, 2024 at 11:31 pm
    
    Hi Frank, thanks for the reply! Yes I’ve tried giving that a go to alter, but doesnt seem to help. Its almost like my ESP32 goes to sleep after 5 minutes of inactivity. The tutorial above is geared to constant logging whereas my application only logs on a button press so not sure if thats something to do with it? If i constantly press my button every few seconds it continues to work perfectly past 5 minutes without fail, its the period of inactivity thats causing it. Wondering if its losing authentification or similar?
    
    I get the below writing to serial monitor after the 5 minute mark once I press the button, then after this if i press the button again it works as normal posting to google sheets (almost waking it up and resetting connections?):
    
    Append spreadsheet values…
    
    unknown error
    
    216432
    Reply
    - Mark Schuurman
      
      March 12, 2024 at 9:51 am
      
      Maybe, just a guess, this ‘about 5 minutes’ of action of your code has something to do with the limited execution time of Google Apps Scripts:
      https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Bot-executes-Script-Trigger-twice-due-to-timeout/m-p/598675#:~:text=The%20free%20version%20of%20App,have%20to%20account%20for%20that.
      Reply
- me-chiel
  
  April 28, 2024 at 6:02 pm
  
  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
claudio

March 9, 2024 at 12:17 pm

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
- claudio
  
  March 9, 2024 at 12:20 pm
  
  …..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
  - Dominggus
    
    March 12, 2024 at 2:42 pm
    
    @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
    - claudio
      
      March 14, 2024 at 1:18 pm
      
      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
Mark Schuurman

March 10, 2024 at 1:35 pm

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
Tanathorn Saewong

March 11, 2024 at 9:16 am

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
Dominggus

March 12, 2024 at 2:46 pm

FYI the library also supports using a GSM client (TinyGSM), using the 4G LTE modem. instead of relying on a Wifi/ethernet connection..
Reply
- Mark Schuurman
  
  March 12, 2024 at 9:13 pm
  
  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
Manucr75

March 29, 2024 at 4:13 pm

Merci pour cet exemple très bien documenté qui m’a fait gagner du temps !
Reply
Alex

April 4, 2024 at 3:42 am

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
- Mark
  
  April 10, 2024 at 6:38 pm
  
  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
iulian

April 14, 2024 at 2:29 pm

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
- Sara Santos
  
  April 15, 2024 at 8:47 pm
  
  Hi.
  What version of the ESP32 boards do you have installed?
  You may need to update.
  Regards,
  Sara
  Reply
Me-Chiel

April 27, 2024 at 2:28 pm

If you changed the hostname of your ESP32 (https://randomnerdtutorials.com/esp32-useful-wi-fi-functions-arduino/#hostname) it is possible the only IP you get connected to is 255.255.255.255.

Change this in your code:
//WiFi.config(INADDR_NONE, INADDR_NONE, INADDR_NONE, INADDR_NONE);
WiFi.config(((u32_t)0x0UL),((u32_t)0x0UL),((u32_t)0x0UL));
WiFi.setHostname(hostname.c_str()); //define hostname

kind regards Me-Chiel
Reply
Mani Prakash

May 16, 2024 at 7:17 am

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
HOLAS

May 17, 2024 at 7:18 pm

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
Jean

May 21, 2024 at 7:57 pm

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
ShibaInu

June 3, 2024 at 8:23 am

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
- Sara Santos
  
  June 3, 2024 at 8:36 am
  
  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
Mark

June 12, 2024 at 3:51 am

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
Mark

June 14, 2024 at 2:24 am

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
Gerald

August 23, 2024 at 6:50 pm

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
Gerald

August 23, 2024 at 10:12 pm

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
Francisca

October 9, 2024 at 3:00 pm

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

ESP32 Datalogging to Google Sheets (using Google Service Account)

1. Prerequisites

Google Account

ESP32 with Arduino IDE

2. Google Service Account

Create a Google Project

Create a Service Account

Creating a New Key

Enable Google Sheet API

Enable Google Drive API

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

4. Creating a Google Spreadsheet

6. ESP32 with BME280 Circuit

Schematic Diagram

7. ESP32 Datalogging to Google Sheets – Arduino Sketch

How the Code Works

Including Libraries

Network Credentials

Project Details

BME280 Variables

Timestamp Variables

setup()

loop()

Demonstration

Wrapping Up

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

Recommended Resources

What to Read Next…

ESP32 I2C Communication: Set Pins, Multiple Bus Interfaces and Peripherals (Arduino IDE)

ESP32/ESP8266 Digital Inputs and Digital Outputs with MicroPython

ESP8266 NodeMCU with HC-SR04 Ultrasonic Sensor with Arduino IDE

Enjoyed this project? Stay updated by subscribing our newsletter!

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

Leave a Comment Cancel reply

Download Our Free eBooks and Resources

1. Prerequisites

Google Account

ESP32 with Arduino IDE

2. Google Service Account

Create a Google Project

Create a Service Account

Creating a New Key

Enable Google Sheet API

Enable Google Drive API

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

4. Creating a Google Spreadsheet

5. Share the Spreadsheet with the Service Account

6. ESP32 with BME280 Circuit

Schematic Diagram

7. ESP32 Datalogging to Google Sheets – Arduino Sketch

How the Code Works

Including Libraries

Network Credentials

Project Details

BME280 Variables

Timestamp Variables

setup()

loop()

Demonstration

Wrapping Up

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

Recommended Resources

What to Read Next…

ESP32 I2C Communication: Set Pins, Multiple Bus Interfaces and Peripherals (Arduino IDE)

ESP32/ESP8266 Digital Inputs and Digital Outputs with MicroPython

ESP8266 NodeMCU with HC-SR04 Ultrasonic Sensor with Arduino IDE

Enjoyed this project? Stay updated by subscribing our newsletter!

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

Leave a Comment Cancel reply

Download Our Free eBooks and Resources