Words of the
Wild.
Replacing a manual Excel & copy-paste judging operation with an automated Google Apps Script allocation engine, prefilled evaluation URLs, and a secure Next.js operations dashboard.
From Manual Chaos to
Automated Sovereignty.
Every year, the Scottish Wildlife Trust hosts "Words of the Wild", a national nature writing competition celebrating Scotland's wildlife and landscapes. Managing hundreds of submissions and 200+ volunteer judges previously required weeks of painful manual administration.
-
Excel Spreadsheet Overload
The entire competition was managed in a single, bloated Excel sheet. Submissions were manually exported from WordPress Gravity Forms to emails, then copy-pasted line by line into Excel.
-
20-Tab Google Doc Reader Packs
Staff manually built individual Google Docs for 200+ readers, copy-pasting 20 stories into 20 extra document tabs. Mail merge was used for dispatch, but text fixes or volunteer withdrawals required rebuilding documents by hand.
-
900+ Item Dropdown & Memory Fatigue
The evaluation form was a single link sent to all readers containing a dropdown of ALL 900+ story titles. Readers had to remember exact titles, scroll through huge lists, and track read stories on paper.
-
Google Sign-In Lockouts
Volunteers constantly encountered Google account permission walls and sign-in errors when trying to open reading docs and feedback forms.
-
Real-Time Ingestion Pipeline
WordPress Gravity Forms stream submissions instantly via Webhooks into Google Apps Script and Google Sheets with zero manual copy-pasting.
-
Smart Reading Lists with Checkboxes
Each reader receives a clean, single-page Google Doc table listing story links, prefilled evaluation URLs, and interactive checkboxes to mark off finished stories after dinner or sleep.
-
100% Prefilled Individual Evaluation URLs
Every story row in the reader's Doc links directly to a prefilled Google Form URL (`entry.1414644713=...`). No dropdowns, no searching, no memory needed!
-
Commenter Access Level & Zero Friction
Readers are granted Commenter permissions so they can tick checkboxes without accidentally deleting text or formulas, with no login walls.
End-to-End Submission &
Allocation Architecture.
The visual workflow below illustrates how story submissions pass seamlessly from WordPress Gravity Forms through the Google Apps Script 5-pass allocation matrix into custom volunteer reader packs and the staff dashboard.
The Branded Reader Pack
Google Doc Table.
Instead of wading through a 20-tab Google Document and searching a 900-item dropdown list, each volunteer reader receives a streamlined Google Doc containing a structured 3-column table:
Anonymous PDF Hyperlink
Direct link to the anonymous story PDF in Google Drive. Writer's age remains visible so child-category entries are judged fairly.
Prefilled Evaluation Google Form URL
Each row features its own customized link with `Assignment_ID`, `Story_ID`, and `Reader_ID` prefilled directly in the URL string. Readers simply click, rate, and submit!
Interactive Checkbox & Commenter Locks
Readers tick off each completed review directly inside their Doc. Commenter access allows checkboxes to be toggled while protecting story text and formulas from accidental deletion.
The 5-Pass Google Apps Script
Allocation Matrix.
Rather than running as a rigid black-box script, the engine adds a custom **Words of the Wild** toolbar menu inside Google Sheets. Staff can pause between passes, adjust story lengths, or update withdrawn readers before executing the next pass:
Assign IDs
Programmatically generates unique Story, Reader, and Assignment IDs to bind data across all sheets.
Create PDFs
Converts entry text into anonymised PDFs, keeping the writer's age visible for child category judging.
Assign Stories
Balances allocations (exactly 3 reads per entry) and enforces Gaelic and Scots capability locks.
Build Docs
Compiles personalised Doc reading lists with story PDF links, prefilled form URLs, and checkboxes.
Send Emails
Dispatches personalised welcome packets and prefilled evaluation URLs to 200+ volunteer readers.
Operations Console Features
The custom Next.js web application gives Scottish Wildlife Trust administrators single-screen oversight over the judging lifecycle:
-
Consensus Rules Engine
Automatically categorises story evaluations into Unanimous (3:0), Majority (2:1), or Split (1:1:1), queuing split or unsure evaluations for staff arbitration.
-
Reader Calibration Matrix
Compares each judge's scoring average against global contest means to identify strict vs lenient grading trends.
-
One-Click Reminders
Identifies ghosting volunteers with uncompleted assignments and generates one-click reminder emails (`mailto:`).
Enterprise Security Architecture
Next.js & React Framework
Server-rendered operations dashboard deployed on Vercel with coming-soon maintenance gates.
Microsoft Entra ID (Azure AD) Single Sign-On
Corporate authentication restricted directly to the Scottish Wildlife Trust tenant directory.
Server-Side Domain Locking
Sessions are instantly revoked if the user does not authenticate with an official `@scottishwildlifetrust.org.uk` email address.
Google Apps Script Datastore Engine
Asynchronous backend processing engine executing formula-backed array operations on Google Sheets.
Need an Engine Built for
Complex Workflows?
We transform bloated spreadsheets and manual administrative bottlenecks into resilient, custom-engineered automation systems.
Book a Free Technical Audit