# Greenhouse ambient conditions with Ruuvi, ESP32 and Google sheets

**URL:** <https://forum.arduino.cc/t/greenhouse-ambient-conditions-with-ruuvi-esp32-and-google-sheets/1331271>\
**Category:** Home Automation\
**Created:** [December 11, 2024, 1:43pm UTC](https://forum.arduino.cc/t/greenhouse-ambient-conditions-with-ruuvi-esp32-and-google-sheets/1331271 "2024-12-11T13:43:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jjjochennn](https://dub1.discourse-cdn.com/arduino/user_avatar/forum.arduino.cc/jjjochennn/32/1115973_2.png) [@jjjochennn](https://forum.arduino.cc/u/jjjochennn)\
**Post date:** [December 11, 2024, 1:43pm UTC](https://forum.arduino.cc/t/greenhouse-ambient-conditions-with-ruuvi-esp32-and-google-sheets/1331271/1 "2024-12-11T13:43:15Z")

</div>

Hi all,

I would like to show you my first project with an ESP32. This year, I have built a garden shed with a small greenhouse attached to it:

 ![IMG_20241211_133032](https://europe1.discourse-cdn.com/arduino/original/4X/a/9/5/a95d312bc61c79bb839d31ac29a54fbd94836cc1.jpeg)  
I thought, it would be nice to somehow see how warm or cold it gets over the year. As I can get Ruuvi tags for free from work, I was looking for a convenient way to read their data without having to walk out with my phone and then learned about ESP32 microcontrollers.  
Here is the finished device in it's place:  
 ![IMG_20241211_132940](https://europe1.discourse-cdn.com/arduino/original/4X/8/7/0/87088ac1b0b7bdd6bc8c6d3552e596e47b40d537.jpeg)

The hardware is wired as follows - I am using a 5 Ah LiPo battery that I also got from company trash, which I can charge via a TP4056 charging module/USB C. The ESP32 itself is powered by a Pololu 3.3V voltage regulator:

 ![image](https://europe1.discourse-cdn.com/arduino/original/4X/3/a/7/3a782bb1d10b85d4efb0bd4b74f03af95d2263ac.png)  
I can check on the battery level by measuring the battery voltage on an analog IN over a voltage divider on the ESP32.

The wiring is a bit bonkers, but it works:

 ![image](https://europe1.discourse-cdn.com/arduino/original/4X/d/3/f/d3f28445bb4482500eeaf82eeb8651a967f9dab6.jpeg)

There are two Ruuvi tags - one inside and one just outside the greenhouse. The ESP32 is scanning BLE until both Ruuvi's data is received, then sends the raw Hex strings over my WiFi to a google sheet. Then it deep sleeps for 15 minutes. Here is the code:

```arduino
#include <HTTPClient.h> // version 2.2.0
#include <WiFi.h>
#include <NimBLEDevice.h> // version 1.4.3
#include <esp_sleep.h>

#define uS_TO_S_FACTOR 1000000 // Conversion factor for micro seconds to seconds
#define TIME_TO_SLEEP 890 // Time ESP32 will go to sleep (in seconds) - 15 minutes minus the average runtime.

HTTPClient http; // Declare HTTPClient object globally
BLEScan* pBLEScan; 

const int analogPin = 32; // ADC pin
int adcValue;

const char* ssid = "SSID";
const char* password = "PASSWORD";

String googleURL = "https://script.google.com/macros/s/AKfycbxxxxxxxMIfZjTdIYfxw46fAEJ5PWxxxxxxx/exec?";
String googleURL_with_parameters = "";

const char* MAC_inside_sensor = "E8:B9:9A:E5:54:BE";
const char* MAC_outside_sensor = "CE:6A:CC:E0:7E:1E";
char HEX_inside_sensor[128] = "";
char HEX_outside_sensor[128] = "";
char macAddress[128] = "";

int BLEscanTime = 15; // In seconds. Long scan time will be stopped once both sensors are found.

// Function to convert a string to uppercase. Used for MAC Addresses and Data of Ruuvi sensors.
void toUpperCase(char* str) {
    for (int i = 0; str[i]; i++) {
        str[i] = toupper(str[i]);
    }
}

void connectToWiFi() {
    delay(1000); //before setting up WiFi
    WiFi.disconnect();
    delay(1000);
    WiFi.begin(ssid, password);
    unsigned long startAttemptTime = millis();
    while (WiFi.status() != WL_CONNECTED && millis() - startAttemptTime < 10000) { // 10 seconds timeout
        delay(1000);
        Serial.println("Connecting to WiFi...");
    }
    if (WiFi.status() == WL_CONNECTED) {
        Serial.println("Connected to WiFi");
    } else {
        Serial.println("Failed to connect to WiFi. Reconnecting...");
        // Retry mechanism
        for (int i = 0; i < 5; i++) {
            WiFi.begin(ssid, password);
            delay(2000);
            if (WiFi.status() == WL_CONNECTED) {
                Serial.println("Connected to WiFi");
                break;
            }
        }
        if (WiFi.status() != WL_CONNECTED) {
            Serial.println("Failed to connect to WiFi after multiple attempts.");
        }
    }
}

class MyAdvertisedDeviceCallbacks: public BLEAdvertisedDeviceCallbacks {
    void onResult(BLEAdvertisedDevice* advertisedDevice) {
        strncpy(macAddress, advertisedDevice->getAddress().toString().c_str(), sizeof(macAddress) - 1);
        macAddress[sizeof(macAddress) - 1] = '\0'; // Ensure null-termination
        toUpperCase(macAddress);
        if (strcmp(MAC_inside_sensor, macAddress) == 0 && HEX_inside_sensor[0] == '\0') {
            char* hexData = BLEUtils::buildHexData(nullptr, (uint8_t*)advertisedDevice->getManufacturerData().c_str(), advertisedDevice->getManufacturerData().length());
            snprintf(HEX_inside_sensor, sizeof(HEX_inside_sensor), "%s", hexData);
            free(hexData); // Free the allocated memory
            toUpperCase(HEX_inside_sensor);
            Serial.println("Found inside sensor: " + String(macAddress));
        }
        if (strcmp(MAC_outside_sensor, macAddress) == 0 && HEX_outside_sensor[0] == '\0') {
            char* hexData = BLEUtils::buildHexData(nullptr, (uint8_t*)advertisedDevice->getManufacturerData().c_str(), advertisedDevice->getManufacturerData().length());
            snprintf(HEX_outside_sensor, sizeof(HEX_outside_sensor), "%s", hexData);
            free(hexData); // Free the allocated memory
            toUpperCase(HEX_outside_sensor);
            Serial.println("Found outside sensor: " + String(macAddress));
        }
        if (HEX_inside_sensor[0] != '\0' && HEX_outside_sensor[0] != '\0') {
            Serial.println("Found both sensors");
            pBLEScan->stop();
            Serial.println("STOPPING SCAN");
        }
    }
};

void transmitData() {
    if (HEX_inside_sensor[0] != '\0' && HEX_outside_sensor[0] != '\0'){
        googleURL_with_parameters = googleURL + "&HEXin=" + HEX_inside_sensor + "&HEXout=" + HEX_outside_sensor + "&SRCPOW=" + adcValue;
        Serial.println("URL: " + googleURL_with_parameters);
        Serial.println("Data transmission starting... Heap memory: " + String(esp_get_free_heap_size()) + " bytes");
        if (WiFi.status() == WL_CONNECTED) {
            http.begin(googleURL_with_parameters);
            int httpResponseCode = http.GET();
            if (httpResponseCode > 0) {
                String response = http.getString();
                Serial.println(httpResponseCode);
                //Serial.println(response);
            } else {
                Serial.print("Error on HTTP request: ");
                Serial.println(httpResponseCode);
                connectToWiFi();
                http.end();
                http.begin(googleURL_with_parameters);// try again if first try did not succeed.
            }
            http.end();
        } else {
            Serial.println("Error in WiFi connection. Re-initializing WiFi...");
            connectToWiFi();
        }
        HEX_inside_sensor[0] = '\0';
        HEX_outside_sensor[0] = '\0';
        Serial.println("Hex values resetted.");
        Serial.println("Data transmission done. Heap memory: " + String(esp_get_free_heap_size()) + " bytes");
    }
}

void setup() {
    Serial.begin(115200);
    delay(2000);
    // Configure wake up source
    esp_sleep_enable_timer_wakeup(TIME_TO_SLEEP * uS_TO_S_FACTOR);
    Serial.println("Setup ESP32 to sleep for every " + String(TIME_TO_SLEEP) + " Seconds");
    //read the analog pin for the battery voltage
    adcValue = analogRead(analogPin);
    Serial.println("ADC Value for PIN32: " + String(adcValue));
    //BLE
    BLEDevice::init("");
    pBLEScan = BLEDevice::getScan(); // create new scan
    pBLEScan->setAdvertisedDeviceCallbacks(new MyAdvertisedDeviceCallbacks());
    pBLEScan->setActiveScan(true); // active scan uses more power, but gets results faster
    pBLEScan->setInterval(100);
    pBLEScan->setWindow(99); // less or equal setInterval value
    connectToWiFi();
    http.setTimeout(20000); // Set timeout to 20 seconds
    while (HEX_inside_sensor[0] == '\0' || HEX_outside_sensor[0] == '\0'){
        Serial.println("STARTING NEW SCAN");
        BLEScanResults foundDevices = pBLEScan->start(BLEscanTime, false);
        pBLEScan->clearResults(); // delete results from BLEScan buffer to release memory
        delay(1000);
    }
    transmitData(); 
    Serial.println("Going to sleep now");
    esp_deep_sleep_start();
}

void loop() {
    //not needed here
}

```

The raw Hex values get converted and processed by a google apps script:

```arduino
function doGet(e) {
  var dataSheetName = 'data';
  var sheet = SpreadsheetApp.openById('1kZG9-848u-M0Ixxxxx').getSheetByName(dataSheetName);
  
  // Extract and log each parameter
  var in_HEX = e.parameter.HEXin || "IN not found";
  var out_HEX = e.parameter.HEXout || "OUT not found";
  var batteryRaw = e.parameter.SRCPOW || "BATTERY not found";

  // Append the data to the sheet
  var in_data = decodeRuuvi(in_HEX);
  var out_data = decodeRuuvi(out_HEX);
  var batteryVoltage = convertPower(batteryRaw);
  // Insert a new row at position 4
  sheet.insertRowBefore(4);

  sheet.getRange(4, 1, 1, 12).setValues([[new Date(), in_data.temp, in_data.hum, in_data.pressure, in_data.txpower, in_data.voltage,
                   out_data.temp, out_data.hum, out_data.pressure, out_data.txpower, out_data.voltage, batteryVoltage]]);

  return ContentService.createTextOutput('Success');
}

function convertPower(rawValue){
  var correctionFactor = 1.3628; //derived from the voltage divider with R1=32,6k and R2=11,86k
  var convertedValue = 3.3 * rawValue / 4095.0 * correctionFactor;
  return convertedValue;
}

function decodeRuuvi(hex_data) {
  var data = {}; // Create an object to hold the decoded data

  if (hex_data.substring(4, 6) == "05") { // Check if the data format is supported
    data.temp = decodeTemperature(hex_data.substring(6, 10)); // Decode temperature
    data.hum = hexadecimalToDecimal(hex_data.substring(10, 14)) * 0.0025; // Decode humidity
    data.pressure = hexadecimalToDecimal(hex_data.substring(14, 18)) * 1 + 50000; // Decode pressure

    data.ax = hexadecimalToDecimal(hex_data.substring(18, 22)); // Decode acceleration in X-axis
    data.ay = hexadecimalToDecimal(hex_data.substring(22, 26)); // Decode acceleration in Y-axis
    data.az = hexadecimalToDecimal(hex_data.substring(26, 30)); // Decode acceleration in Z-axis

    if (data.ax > 0xF000) { // Adjust for negative values in X-axis
      data.ax = data.ax - (1 << 16);
    }
    if (data.ay > 0xF000) { // Adjust for negative values in Y-axis
      data.ay = data.ay - (1 << 16);
    }
    if (data.az > 0xF000) { // Adjust for negative values in Z-axis
      data.az = data.az - (1 << 16);
    }

    var voltage_power = hexadecimalToDecimal(hex_data.substring(30, 34)); // Decode voltage and TX power

    var voltage_mask = 0b1111111111100000; // Mask for the first 11 bits (voltage)
    var voltage_bits = voltage_power & voltage_mask; // Apply mask to get voltage bits
    voltage_bits = voltage_bits >> 5; // Align the bits after masking
    data.voltage = voltage_bits + 1600; // Voltage in millivolts
    data.voltage = data.voltage / 1000.0; // Convert to volts

    var txpower_mask = 0b0000000000011111; // Mask for the last 5 bits (TX power)
    data.txpower = (voltage_power & txpower_mask) - 40; // TX power in dBm

  } else {
    Logger.log("Error: Unsupported data format"); // Log error if data format is unsupported
  }
  return data; // Return the decoded data
}

function decodeTemperature(hexVal) {
  var temp = hexadecimalToDecimal(hexVal);
  if (temp > 0x7FFF) { // Check if the MSB is set (indicating a negative value)
    temp = temp - (1 << 16); // Convert from two's complement to negative decimal
  }
  return temp * 0.005; // Apply the scaling factor
}

// Function to convert hexadecimal string to decimal
function hexadecimalToDecimal(hexVal) {
  var len = hexVal.length; // Get the length of the hexadecimal string
  var base = 1; // Initialize base value to 1 (16^0)
  var dec_val = 0; // Initialize decimal value to 0

  for (var i = len - 1; i >= 0; i--) { // Loop through each character of the hexadecimal string
    var char = hexVal[i];
    if (char >= '0' && char <= '9') { // If the character is a digit
      dec_val += (char.charCodeAt(0) - 48) * base; // Convert to decimal and add to dec_val
      base = base * 16; // Update base to next power of 16
    } else if (char >= 'A' && char <= 'F') { // If the character is a letter (A-F)
      dec_val += (char.charCodeAt(0) - 55) * base; // Convert to decimal and add to dec_val
      base = base * 16; // Update base to next power of 16
    }
  }
  return dec_val; // Return the decimal value
}

function lowVoltageWarning(){
  var dataSheetName = 'data';
  var sheet = SpreadsheetApp.openById('1kZG9-848u-M0I6bekcFO8ZPDLU0Ta8Cr_sLxEPXinOI').getSheetByName(dataSheetName);
  var cellValue = sheet.getRange("L4").getValue();
  
  if (cellValue < 3.5) {
      var emailAddress = "x@gmail.com"; // Replace with your email address
  var subject = "AMBIENT CONTROL Alert: Battery Voltage Below 3.5 V";
  var message = "The value in cell L4 on the 'data' sheet is below 3.5.";
  
  MailApp.sendEmail(emailAddress, subject, message);
  }
}

```

Then I am displaying the humidity and temperature for both in- and outside on a dashboard, day- and week-charts as well as a battery level chart:

 ![Screenshot_20241211-142511](https://europe1.discourse-cdn.com/arduino/original/4X/7/0/3/7035e5acf06244a4a0b1ca12aa24cb23e82564a5.jpeg) ![Screenshot_20241208-203113](https://europe1.discourse-cdn.com/arduino/original/4X/8/e/c/8ec8b767637c77883c585f6ee4d8b4c228449daa.png)

 ![Screenshot_20241208-203129](https://europe1.discourse-cdn.com/arduino/original/4X/a/2/2/a22ba6a7ea1c38a12f12b253458f2643d440bc5c.png) ![Screenshot_20241211-131758](https://europe1.discourse-cdn.com/arduino/original/4X/9/7/e/97ebb81686fdf9d510e026a910a7cd07b1d4f474.png)

I am happy how this all turned out for doing all that stuff for the first time. Here I would like give a big **T H A N K Y O U ! ! !** to @kmin and @kenb4 for [helping me to get this working](https://forum.arduino.cc/t/ambient-data-from-ruuvi-esp32-google-sheet-http-connection-issue/1316054)!

regards  
Jochen

---

<div class="post-metadata">

**Author:** ![kmin](https://dub1.discourse-cdn.com/arduino/user_avatar/forum.arduino.cc/kmin/32/1147370_2.png) [@kmin](https://forum.arduino.cc/u/kmin)\
**Post date:** [December 11, 2024, 2:25pm UTC](https://forum.arduino.cc/t/greenhouse-ambient-conditions-with-ruuvi-esp32-and-google-sheets/1331271/2 "2024-12-11T14:25:06Z")

</div>

Nice job!  
What was the reason/benefit for using NimBLE instead of stock BLE?  
Ps. Your voltage divider seems to be grounded on both ends on the scheme.

---

<div class="post-metadata">

**Author:** ![jjjochennn](https://dub1.discourse-cdn.com/arduino/user_avatar/forum.arduino.cc/jjjochennn/32/1115973_2.png) [@jjjochennn](https://forum.arduino.cc/u/jjjochennn)\
**Post date:** [December 13, 2024, 7:35am UTC](https://forum.arduino.cc/t/greenhouse-ambient-conditions-with-ruuvi-esp32-and-google-sheets/1331271/3 "2024-12-13T07:35:56Z")

</div>

Hey kmin, if you recall the other thread: I had this strange memory leak with every cycle of the BLE connection with the standard BLE library. nimBLE just worked fine.  
Now with using deep sleep, I could probably switch back to the standard BLE library, but why bother as long as it just works 🙂

And yes, the scheme is wrong 😑  
The divider should connect to OUT+ instead of OUT-, the actual wiring of course is correct.

Thanks again 🙂

---

<div class="post-metadata">

**Author:** ![system](https://dub1.discourse-cdn.com/arduino/user_avatar/forum.arduino.cc/system/32/1140315_2.png) [@system](https://forum.arduino.cc/u/system)\
**Post date:** [June 11, 2025, 7:36am UTC](https://forum.arduino.cc/t/greenhouse-ambient-conditions-with-ruuvi-esp32-and-google-sheets/1331271/4 "2025-06-11T07:36:48Z")

</div>

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.