Log RFID check-ins to Google Sheets

Clubs and classrooms often keep a list of who is there. Let's make it automatic. Everyone gets an RFID card or key fob, and taps it on the reader station when they arrive and leave. The ESP32 looks up the name, sends it to a Google Sheet, and the sheet works out whether it's an "In" or an "Out". A green light and one beep mean "welcome", two beeps mean "goodbye", and the red light means something went wrong.

What you'll learn

• How to read an RFID card on the ESP32
• How to look up a name for a card number
• How a Google Apps Script can make decisions, not just store data
• How to give quick feedback with lights and sounds

What you need

• ESP32 board, expansion board and USB cable (ESP32 IoT Starter Kit)
• RC522 RFID module with card and key fob (ESP32 IoT Starter Kit)
• Green and red LEDs and 2 resistors, 220 Ω (ESP32 IoT Starter Kit)
• Passive buzzer (ESP32 IoT Starter Kit)
• More RFID cards or tags, one per person (optional)
• 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 (tap station), or the cardboard version described there
• Breadboard and jumper wires (ESP32 IoT Starter Kit)

Wiring

Privacy: a check-in list is personal data. Only log people who agree to it, keep the sheet private, and delete old rows when you no longer need them. Use first names or nicknames.

Prepare the spreadsheet

1. Create a blank spreadsheet at sheets.google.com called Check-ins, and rename its first tab to Log.
2. Type Time, Name, Card and In/Out 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. 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('Log');
  const card = e.parameter.card;
  // Find this card's last entry, from the bottom up, and do the opposite
  const rows = sheet.getDataRange().getValues();
  let direction = 'In';
  for (let i = rows.length - 1; i > 0; i--) {
    if (String(rows[i][2]) === card) {
      direction = rows[i][3] === 'In' ? 'Out' : 'In';
      break;
    }
  }
  sheet.appendRow([new Date(), e.parameter.name, card, direction]);
  return ContentService.createTextOutput('OK ' + direction);
}

The sketch

In the Arduino IDE, open Library Manager and install the MFRC522 library by GithubCommunity.

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. Then tap each card once, read its number in the Serial Monitor, and add it with a name to the PEOPLE list.

#include <WiFi.h>
#include <WiFiClientSecure.h>
#include <HTTPClient.h>
#include <SPI.h>
#include <MFRC522.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 SS_PIN = 5, RST_PIN = 22;
const int GREEN_PIN = 25, RED_PIN = 26, BUZZER_PIN = 27;
MFRC522 reader(SS_PIN, RST_PIN);

struct Person {
  const char* card;          // the card number as the Serial Monitor shows it
  const char* name;
};
const Person PEOPLE[] = {
  {"A1B2C3D4", "Alex"},
  {"0F1E2D3C", "Sam"},
};

String cardNumber() {
  String id;
  for (byte i = 0; i < reader.uid.size; i++) {
    if (reader.uid.uidByte[i] < 0x10) id += "0";
    id += String(reader.uid.uidByte[i], HEX);
  }
  id.toUpperCase();
  return id;
}

String nameFor(const String& card) {
  for (const Person& p : PEOPLE) {
    if (card == p.card) return p.name;
  }
  return "Unknown";
}

String urlEncode(const String& s) {
  String out;
  for (char c : s) {
    if (isalnum(c)) out += c;
    else {
      char hex[4];
      snprintf(hex, sizeof(hex), "%%%02X", (unsigned char)c);
      out += hex;
    }
  }
  return out;
}

String sendToSheet(const String& card, const String& name) {
  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 + "&card=" + card + "&name=" + urlEncode(name));
  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 : "";
}

void signal(int pin, int beeps) {
  digitalWrite(pin, HIGH);
  for (int i = 0; i < beeps; i++) {
    tone(BUZZER_PIN, pin == GREEN_PIN ? 1500 : 300, 120);
    delay(200);
  }
  delay(800);
  digitalWrite(pin, LOW);
}

void setup() {
  Serial.begin(115200);
  pinMode(GREEN_PIN, OUTPUT);
  pinMode(RED_PIN, OUTPUT);
  SPI.begin();
  reader.PCD_Init();

  WiFi.begin(WIFI_NAME, WIFI_PASSWORD);
  Serial.print("Connecting to Wi-Fi");
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.print(" connected. Open http://");
  Serial.println(WiFi.localIP());
  Serial.println("Ready. Tap a card!");
}

void loop() {
  if (!reader.PICC_IsNewCardPresent() || !reader.PICC_ReadCardSerial()) return;
  String card = cardNumber();
  reader.PICC_HaltA();
  String name = nameFor(card);
  Serial.println("Card " + card + " = " + name);

  digitalWrite(GREEN_PIN, HIGH);         // "working on it"
  String answer = WiFi.status() == WL_CONNECTED ? sendToSheet(card, name) : "";
  digitalWrite(GREEN_PIN, LOW);

  if (answer == "OK In") signal(GREEN_PIN, 1);        // welcome
  else if (answer == "OK Out") signal(GREEN_PIN, 2);  // goodbye
  else {
    signal(RED_PIN, 3);                               // something went wrong
    if (WiFi.status() != WL_CONNECTED) WiFi.reconnect();
  }
}

How it works

The ESP32 talks to the RFID reader over SPI, a fast connection with a clock wire (SCK), a wire for each direction (MOSI and MISO) and a "you're the one I'm talking to" wire (SDA). The card number comes as a few bytes, which cardNumber() writes as hexadecimal text such as A1B2C3D4.

The sketch doesn't decide whether someone is coming or going: the Apps Script does. It looks through the sheet from the bottom up for the card's last entry and writes the opposite. Keeping that logic in the sheet means it still works if you use two stations, one at each door.

Names can contain spaces or letters like "é", which aren't allowed in a web address. urlEncode() turns every such character into a % code, for example a space into %20.

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 "Minutes" column that the script fills in on every "Out".
• Show a chart of how many people were in each day.
• Make the station speak the name with a different beep tune per person.

Troubleshooting

• The red light blinks three times: the sheet didn't answer "OK". Check the URL, the secret word, and that the script was deployed with access for "Anyone".
• Nothing happens when you tap: check the RFID wires, especially 3V3 and GND, SDA on GPIO 5 and RST on GPIO 22.
• Everyone is "Unknown": copy the card numbers from the Serial Monitor into PEOPLE exactly, in capital letters.
• 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.