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:

1. Prerequisites
2. Google Service Account
   • Create a Service Account
   • Creating a New Key
   • Enable Google Sheet API
   • Enable Google Drive API
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:

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

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.

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.

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.

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.

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.

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.

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:

• Learn ESP32 with Arduino IDE (eBook)
• Build Web Servers with ESP32 and ESP8266 (eBook)
• Firebase Web App with ESP32 and ESP8266 (eBook)
• SMART HOME with Raspberry Pi, ESP32, and ESP8266
• Free ESP32 Projects and Tutorials…

Thanks for reading.