I’m building a Flask web app for a Model UN conference with around 350-400 registered delegates.
OCs (Organizing Committee members) log in.
They scan delegate IDs (QR codes or manual input).
The app then fetches delegate info from a Google Sheet and logs attendance in another sheet.
All delegate, OC, and attendance data is currently stored in Google Sheets
Whenever a delegate is scanned, the app seems to make many Google Sheets API calls (sometimes 20–25 for a single scan).
I already tried to:
Cache delegates (load once from master sheet at startup).
Cache attendance records.
Batch writes (
append_rowsin chunks of 50).
But I still see too many API calls, and I’m worried about hitting the Google Sheets API quota limits during the event.
After rewriting the backend, I still get around 10 API calls for one instance, now I'm not sure is it because of the backend or frontend, here I've attached MRE of my backend and have attached the HTML code for home page
from flask import Flask, request, render_template, redirect, url_for, session, flash
import gspread, os, json
from google.oauth2.service_account import Credentials
from datetime import datetime, timedelta
app = Flask(__name__)
app.secret_key = "dev"
app.permanent_session_lifetime = timedelta(days=1)
SCOPE = ["https://www.googleapis.com/auth/spreadsheets"]
service_account_info = json.loads(os.environ['GOOGLE_SERVICE_ACCOUNT_JSON'])
creds = Credentials.from_service_account_info(service_account_info, scopes=SCOPE)
client = gspread.authorize(creds)
master_sheet = client.open("Master_Sheet").worksheet("Sheet1")
attendance_sheet = client.open("Attendance_Log").worksheet("Sheet1")
ocs_sheet = client.open("OC_Details").worksheet("Sheet1")
oc_list = {r["OC_ID"]: r["Password"] for r in ocs_sheet.get_all_records()}
# Delegates loaded once
delegates = {r["Delegate_ID"]: r for r in master_sheet.get_all_records()}
attendance_cache = {r["Delegate_ID"]: r for r in attendance_sheet.get_all_records()}
pending_attendance = []
BATCH_SIZE = 50
def flush_pending():
global pending_attendance
if not pending_attendance:
return 0
rows = [[r["Delegate_ID"], r["name"], r["committee"], r["scanned_by"], r["timestamp"]]
for r in pending_attendance]
attendance_sheet.append_rows(rows)
for r in pending_attendance:
attendance_cache[r["Delegate_ID"]] = r
count = len(pending_attendance)
pending_attendance = []
return count
@app.route("/")
def home():
if "oc_id" not in session:
return redirect(url_for("login"))
return render_template("home.html", oc_id=session["oc_id"], delegate=None)
@app.route("/login", methods=["GET", "POST"])
def login():
if request.method == "POST":
oc_id = request.form.get("oc_id")
password = request.form.get("password")
if oc_id in oc_list and oc_list[oc_id] == password:
session["oc_id"] = oc_id
return redirect(url_for("home"))
return "❌ Invalid credentials"
return render_template("login.html")
@app.route("/scan/<delegate_id>")
def scan(delegate_id):
if "oc_id" not in session:
return redirect(url_for("login"))
delegate = delegates.get(delegate_id)
if not delegate:
return f"❌ Delegate {delegate_id} not found."
record = attendance_cache.get(delegate_id)
return render_template("home.html", delegate=delegate, record=record)
@app.route("/validate/<delegate_id>", methods=["POST"])
def validate(delegate_id):
if "oc_id" not in session:
return redirect(url_for("login"))
if delegate_id in attendance_cache:
return redirect(url_for("scan", delegate_id=delegate_id))
oc_id = session["oc_id"]
timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
record = {
"Delegate_ID": delegate_id,
"name": delegates[delegate_id]["name"],
"committee": delegates[delegate_id]["committee"],
"scanned_by": oc_id,
"timestamp": timestamp,
}
pending_attendance.append(record)
if len(pending_attendance) >= BATCH_SIZE:
flush_pending()
return redirect(url_for("scan", delegate_id=delegate_id))
if __name__ == "__main__":
app.run(debug=True)
home.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>OM MUN Attendance</title>
<link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
</head>
<body>
<div class="container">
{% if delegate %}
<div class="delegate-card">
<span class="oc-id">Logged in as: {{ oc_id }}</span>
<div class="card-buttons">
<a href="{{ url_for('refresh_route') }}" class="btn">Refresh Cache</a>
<a href="{{ url_for('logout') }}" class="btn">Logout</a>
</div>
<h2>{{ delegate.name }} ({{ delegate_id }})</h2>
<p>Committee: {{ delegate.committee }}</p>
<p>Portfolio: {{ delegate.portfolio }}</p>
<p>Country: {{ delegate.country }}</p>
<p>Liability Form: {{ delegate.liability_form }}</p>
<p>Transport Form: {{ delegate.transport_form }}</p>
{% if delegate.scanned_by %}
<p class="scanned">✅ Already scanned by {{ delegate.scanned_by }} at {{ delegate.timestamp }}</p>
{% else %}
<form method="POST" action="{{ url_for('validate', delegate_id=delegate_id) }}">
<button type="submit">Confirm Attendance</button>
</form>
{% endif %}
</div>
{% endif %}
<form method="POST" action="{{ url_for('manual_scan') }}" class="manual-form">
<input type="text" name="delegate_id" placeholder="Enter Delegate ID" required>
<button type="submit">Scan</button>
</form>
<p>Pending Attendance Records: {{ pending_count }}</p>
<a href="{{ url_for('flush_route') }}" class="btn flush-btn">Flush to Google Sheets</a>
{% with messages = get_flashed_messages() %}
{% if messages %}
<div class="flash-messages">
{% for message in messages %}
<p>{{ message }}</p>
{% endfor %}
</div>
{% endif %}
{% endwith %}
</div>
</body>
</html>
Questions:
Why is gspread making so many API calls per scan — is it caused by my backend code, or how the frontend reloads the page?
How can I reduce Google Sheets API calls efficiently while still keeping attendance logging reliable?
