From one spreadsheet to three portals: how TRD runs its own operations

Every project at The Research Desk connects several people and dates: a client, an assigned specialist, a deadline, revisions, an invoice and a payment. All of it lived in one Google Sheets workbook, which the whole operation depended on.

A workbook can hold that information well. What it can't do on its own is act on it: remind a team member that a deadline is tomorrow, tell a client their file has arrived, produce an invoice, or show each person only the work that belongs to them. Those steps depended on someone doing them by hand, every day.

We treated the workbook as the source of truth and built around it rather than replacing it. Two rules guided the work:

• Formulas before scripts. Where a formula could do the job (for example distributing tasks into each team member's own spreadsheet), we used a formula. Scripts were reserved for things formulas can't do: sending, scheduling and generating documents.
• Build in stages. Each automation went live on its own and was checked against real work before the next one started, so the business never depended on something half-finished.

Later, as the portals grew, we rewrote the codebase from scratch around a shared configuration file and separate handlers for each audience, removing duplicated functions that had built up over time.

Automations on the workbook

• Deadline reminders run every morning at 9 AM, grouped by priority.
• Team members receive project notification emails, with protection against the same email being sent twice.
• Each team member has their own spreadsheet, shared with them automatically, showing only their work.
• Bulk emails to clients and team members use TRD-branded HTML templates with personalisation and duplicate protection.
• A button generates a single-page PDF invoice from the selected project row, pulling the client's email from the contacts sheet.
• A sidebar app inside the workbook brings together a dashboard, projects, client and team ledgers, invoicing and quotations.

Three portals in one web app

• Client Portal: an overview, the client's own projects, invoices with PDF download and a form to request new work. Clients can upload revision files, which are stored in a separate folder per client, and message the team in threads.
• Team Portal: assigned tasks, file hand-offs and revision threads.
• Admin Portal: revision and task requests, team workload panels and announcements with a live preview.

• Data: the existing Google Sheets workbook, unchanged as the source of truth.
• Logic: Google Apps Script, split into a shared configuration file and separate handlers for the master, client and admin audiences.
• Web app: a single Apps Script web app that serves the team, client or admin portal depending on the URL.
• Files: Google Drive, with a folder per client for revision uploads.
• Email: Gmail, sent from a no-reply address with admin notifications routed to the team inbox.

Reminders, notifications and invoices now happen without anyone having to remember them, and every client, team member and admin sees only the work that belongs to them.

Because the system was built on a clean, well-understood workbook, it has also made the next step straightforward: TRD is now moving the same business rules onto Next.js and a Postgres database, with the rule preserve the business, replace the infrastructure.