Using Google Sheets and ESP32 as a data logger

gistfile1.txt

// below is the code that runs on the ESP32
#include <Wire.h>
#include <Adafruit_BMP085.h>
#include <OneWire.h>
#include <DallasTemperature.h>
#include <WiFi.h>
#include <HTTPClient.h>
#include "DHT.h"

#include <Arduino.h>
#include <SPI.h>

#define ONE_WIRE_BUS 4
OneWire oneWire(ONE_WIRE_BUS);
DallasTemperature DS18B20_sensors(&oneWire);

#define DHTPIN 12
#define DHTTYPE DHT11
DHT dht(DHTPIN, DHTTYPE);

Adafruit_BMP085 bmp;

/*
When you 'Deploy' the Google Sheets script you will received:
Deployment ID: AKfycbxbUsxcolUsTtXgeXIU-kDDEWeIhy6opPfq9UPDjGNV4ug3el_clipped
url: https://script.google.com/macros/s/AKfycbxbUsxcolUsTtXgeXIU-kDDEWeIhy6opPfq9UPDjGNV4ug3el_clipped/exec

To manually test the Google Sheet from a browser use the following:
https://script.google.com/macros/s/{Deployment ID}/exec?bmpTempF=99.9&ds18b20Temp1=88.8
*/

const char *ssid = "fill this in";
const char *password = "fill this in";
const String Deployment_ID = "AKfycbxbUsxcolUsTtXgeXIU-kDDEWeIhy6opPfq9UPDjGNV4ug3el_clipped"; 

float bmpTempC;
float bmpTempF;
float bmpadjustment = 10;  // derived by comparing pressures from Austin, Houston, Brenham Airport and Artesian Station
float bmp_press_hPa;
float bmp_press_inHg;
float ds18b20Temp1;
float ds18b20Temp2;
float dht11humidity;
float dht11tempf;
float dht11HeatNdx;
int wifiRSSI;

unsigned int sleepMinutes = 10; // Sleep duration in minutes
unsigned long sleepMicroseconds = sleepMinutes * 60UL * 1000000UL;  // Sleep duration in microseconds


void sendDataToGoogleSheets();

void setup() {
  Serial.begin(115200);
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(1000);
    Serial.println("Connecting to WiFi...");
  }
  Serial.println("Connected to WiFi");

  // Initialize the BMP180 sensor
  if (!bmp.begin()) {
    Serial.println("Could not find a valid BMP180 sensor, check wiring!");
    while (1) {} // Halt program if BMP180 not found
  }

  
  DS18B20_sensors.begin();  // Initialize the DS18B20 sensor
  dht.begin();   // Initialize the DHT11
}

void loop() {
  bmpTempC = bmp.readTemperature();  // Read temperature from BMP180
  bmpTempF = (bmpTempC * 9.0/5.0) + 32.0; // Convert to °F
  bmp_press_hPa = (bmp.readPressure()/100) + bmpadjustment;
  bmp_press_inHg = (bmp_press_hPa*100) * 0.00029530; // 1 Pa = 0.00029530 inHg
  DS18B20_sensors.requestTemperatures();  // Read temperature from DS18B20
  ds18b20Temp1 = DS18B20_sensors.getTempFByIndex(0); // Assuming only one DS18B20 sensor
  ds18b20Temp2 = DS18B20_sensors.getTempFByIndex(1); // Index 1 for the second sensor
  dht11humidity = dht.readHumidity();
  dht11tempf = dht.readTemperature(true);
  dht11HeatNdx = dht.computeHeatIndex(dht11tempf, dht11humidity);
  wifiRSSI = WiFi.RSSI();
  sendDataToGoogleSheets();    // Send data to Google Sheets
  //delay(20000); // Delay before taking the next reading
  esp_deep_sleep(sleepMicroseconds);

}

void sendDataToGoogleSheets() {
  HTTPClient http;

  // Replace with your Google Sheets script URL and parameters
  String url = "https://script.google.com/macros/s/" + Deployment_ID + "/exec";
  url += "?bmpTempF=" + String(bmpTempF);
  url += "&bmp_press_hPa=" + String(bmp_press_hPa);
  url += "&bmp_press_inHg=" + String(bmp_press_inHg);
  url += "&ds18b20Temp1=" + String(ds18b20Temp1);
  url += "&ds18b20Temp2=" + String(ds18b20Temp2);
  url += "&dht11humidity=" + String(dht11humidity);
  url += "&dht11HeatNdx=" + String(dht11HeatNdx);
  url += "&wifiRSSI=" + String(wifiRSSI);
  Serial.println("Sending data to Google Sheets...");

  http.begin(url);
  int httpCode = http.GET();

  if (httpCode == HTTP_CODE_OK) {
    String payload = http.getString();
    Serial.println("Data sent successfully.");
    Serial.println("Response: " + payload);
  } else if (httpCode == 302) {
    Serial.println("302 (redirect) received, spreadsheet is likely updated");
    //Serial.println("Response: " + payload);
  } else {
    Serial.println("Check spreadsheet - the error code was " + String(httpCode));
  }

  http.end();
}


// below is the code that runs as a script/macro in the Google Sheet
function doGet(e) {
  return handleRequest(e);
}

function doPost(e) {
  return handleRequest(e);
}

function handleRequest(e) {
  // Get the parameters from the request
  var bmpTempF       = e.parameter.bmpTempF;
  var bmp_press_hPa  = e.parameter.bmp_press_hPa;
  var bmp_press_inHg = e.parameter.bmp_press_inHg;
  var ds18b20Temp1   = e.parameter.ds18b20Temp1;
  var ds18b20Temp2   = e.parameter.ds18b20Temp2;
  var dht11humidity   = e.parameter.dht11humidity;
  var dht11HeatNdx   = e.parameter.dht11HeatNdx;
  var wifiRSSI       = e.parameter.wifiRSSI

  // Open the spreadsheet by its ID taken from the URL when accessing the sheet
  var sheet = SpreadsheetApp.openById('{update this part with the ID for your sheet}').getActiveSheet();

  // Append the data to the spreadsheet
  sheet.appendRow([new Date(), bmpTempF, bmp_press_hPa, bmp_press_inHg, ds18b20Temp1, ds18b20Temp2, dht11humidity, dht11HeatNdx, wifiRSSI]);

  // Return a message
  return ContentService.createTextOutput("Data added to Google Sheet ESP32Logger2");
}