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



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 »

Enjoyed this project? Stay updated by subscribing our newsletter!

32 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
  1. 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
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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

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.