Log the noise in your classroom to Google Sheets

How loud is your classroom really, and when is it loudest? This lesson turns the ESP32 into a noise logger. The high-sensitivity sound sensor measures the loudness ten times a second, a red, yellow and green LED show the level like a traffic light, and every five minutes the average and the loudest moment go into a Google Sheets spreadsheet. After a school day you have a chart that shows breaks, group work and quiet reading at a glance.

What you'll learn

• How to measure loudness as the swing of a sound wave on the ESP32
• How to work out an average and a maximum over a time period
• How to send several values to Google Sheets at once
• How to show a level with three LEDs

What you need

• ESP32 board, expansion board and USB cable (ESP32 IoT Starter Kit)
• High-sensitivity sound sensor module (45-in-1 Sensor Kit)
• Red, yellow and green LEDs and 3 resistors, 220 Ω (ESP32 IoT Starter Kit)
• A free Google account (ask a parent or teacher if you are under 13)
• A 2.4 GHz Wi-Fi network
• The printed parts from the 3D printing box below (module stand), or the cardboard version described there
• Breadboard and jumper wires (ESP32 IoT Starter Kit)

Wiring

Prepare the spreadsheet

1. Create a blank spreadsheet at sheets.google.com called Noise log, and rename its first tab to Readings.
2. Type Time, Average and Loudest in the first row.
3. Open Extensions → Apps Script, replace the code with the script below and change YOUR_SECRET_WORD to a word only you know (letters and numbers only).
4. Click Deploy → New deployment → Web app, set Execute as: Me and Who has access: Anyone, deploy, allow access and copy the Web app URL (it ends in /exec). The Google Sheets lesson shows each step in detail.

const SECRET = 'YOUR_SECRET_WORD';

function doGet(e) {
  if (e.parameter.key !== SECRET) return ContentService.createTextOutput('wrong key');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Readings');
  sheet.appendRow([new Date(), Number(e.parameter.avg), Number(e.parameter.max)]);
  return ContentService.createTextOutput('OK');
}

The sketch

Replace YOUR_WIFI_NAME and YOUR_WIFI_PASSWORD with your own network details (2.4 GHz only).

Paste your web app URL into SCRIPT_URL and your secret word into SECRET.

#include <WiFi.h>
#include <WiFiClientSecure.h>
#include <HTTPClient.h>

const char* WIFI_NAME = "YOUR_WIFI_NAME";
const char* WIFI_PASSWORD = "YOUR_WIFI_PASSWORD";
const char* SCRIPT_URL = "https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec";
const char* SECRET = "YOUR_SECRET_WORD";

const int SOUND_PIN = 34;
const int GREEN_PIN = 25, YELLOW_PIN = 26, RED_PIN = 27;
const int YELLOW_FROM = 35;      // level (0-100) where yellow starts
const int RED_FROM = 65;         // ...and red
const unsigned long EVERY = 5UL * 60 * 1000;

long levelSum = 0;
int samples = 0;
int loudest = 0;
unsigned long lastSend = 0;

bool sendToSheet(const String& query) {
  WiFiClientSecure client;
  client.setInsecure();
  HTTPClient http;
  http.setFollowRedirects(HTTPC_STRICT_FOLLOW_REDIRECTS);   // Google answers with a redirect first
  http.begin(client, String(SCRIPT_URL) + "?key=" + SECRET + "&" + query);
  int code = http.GET();
  String answer = http.getString();
  http.end();
  Serial.printf("Google answered %d: %s\n", code, answer.c_str());
  return code == 200 && answer == "OK";
}

// Loudness 0-100 from the swing of the sound wave over 50 ms
int level() {
  int lowest = 4095, highest = 0;
  unsigned long start = millis();
  while (millis() - start < 50) {
    int v = analogRead(SOUND_PIN);
    lowest = min(lowest, v);
    highest = max(highest, v);
  }
  return constrain((highest - lowest) / 20, 0, 100);
}

void setup() {
  Serial.begin(115200);
  pinMode(GREEN_PIN, OUTPUT);
  pinMode(YELLOW_PIN, OUTPUT);
  pinMode(RED_PIN, OUTPUT);

  WiFi.begin(WIFI_NAME, WIFI_PASSWORD);
  Serial.print("Connecting to Wi-Fi");
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println(" connected");
  lastSend = millis();
}

void loop() {
  int now = level();
  levelSum += now;
  samples++;
  loudest = max(loudest, now);

  digitalWrite(GREEN_PIN, now < YELLOW_FROM);
  digitalWrite(YELLOW_PIN, now >= YELLOW_FROM && now < RED_FROM);
  digitalWrite(RED_PIN, now >= RED_FROM);
  delay(50);

  if (millis() - lastSend >= EVERY) {
    lastSend = millis();
    int average = levelSum / samples;
    Serial.printf("Last 5 min: average %d, loudest %d\n", average, loudest);
    if (WiFi.status() == WL_CONNECTED) sendToSheet("avg=" + String(average) + "&max=" + String(loudest));
    else WiFi.reconnect();
    levelSum = 0;
    samples = 0;
    loudest = 0;
  }
}

Tune and chart it

1. Watch the LEDs: in a quiet room only green should light. If yellow lights in silence, turn the sensor's small screw a little, or make YELLOW_FROM bigger.
2. For a quick test, change EVERY to 60000 (one row a minute).
3. After a few hours, select columns A to C in the sheet and choose Insert → Chart → Line chart.
4. Talk about it with your class: when was it loudest, and why?

How it works

As in the bar graph lesson, loudness is the swing between the lowest and highest reading in a short time. The ESP32's analog pins give 0 to 4095, so dividing by 20 turns a typical swing into a level from 0 to 100.

Instead of sending every measurement (3000 every five minutes), the sketch adds them up and counts them, so it can send the average and the loudest value at the end of each period. Summarising like this keeps the spreadsheet small and still tells the story of the day.

client.setInsecure() keeps the connection encrypted but skips checking the server's certificate. That keeps the sketch short and is fine for a learning project. For a device you leave running for years, load the server's root certificate with client.setCACert() instead.

Try this next

• Add a column for how many seconds were red in each period.
• Send an ntfy message when the average is red for 10 minutes.
• Put two loggers in different rooms and compare them on one chart.

Troubleshooting

• The red LED is always on: the sensor is too sensitive. Turn its screw, or make RED_FROM bigger.
• "Google answered 200: wrong key": the secret words in the sketch and the script must match.
• Values are always 0: the sensor must be on GPIO 34 and its AO pin.
• It never connects to Wi-Fi: check the network name and password (both are case-sensitive) and make sure the network is 2.4 GHz.
• A printed part is too tight or too loose: printers differ by a few tenths of a millimetre. Sand or file a tight hole, and fix a loose part with a drop of hot glue or a strip of tape.