Started Volunteering as a System Admin for an NPO
About two months ago, I started volunteering for an NPO in a system admin role. The organization is quite pre-modern in how it operates—user inquiries were handled entirely via email. Sometimes our IT-related team handled the issues, and other times we had to forward them to the appropriate department. Of course, that meant forwarding the emails. I hadn’t seen dozens of endless “Re:Re:Re:” threads in a while. It felt like time-traveling back to the 2000s when I first entered the workforce.
Fortunately, the NPO uses Google Workspace for Nonprofits, so we have access to the basic Google apps. I decided to centralize user inquiries using a Google Form. However, form responses are aggregated into a spreadsheet by default, and managing each case individually that way is clearly a pain. At the same time, it didn’t seem worth it to introduce full-fledged ticketing systems like JIRA or Salesforce. So, I built a simple ticket system using Google Apps Script (GAS).
I'm sharing the system structure and the lessons I learned here as a record. I hope it’s helpful for non-engineers, NPO staff, or small business operators who can’t spend much on tools.
System Architecture Overview
Since we're limited to Google Workspace, the setup is very simple. After designing the overall structure and creating a UI mockup in HTML, I had ChatGPT generate the implementation code, then manually tweaked the finer details.
Google Form
- Collects user inquiries
- Accessible to anyone with the link
Google Spreadsheet
The spreadsheet is split into two files based on purpose:
Sheet(1): FormResponses
- Auto-generated when collecting form data
- Script is triggered on each new entry
- Issues a Unique ID (UID)
- Copies the data into the ticket log sheet (TicketLogs)
- Sends UID to the user via email
- Also posts to a Google Chat thread for the support team
Sheet(2): TicketLogs
- Serves as a pseudo-database
- Not accessible to non-admins
- Edits are appended to the bottom
Google Apps Script
- Provides a UI to view and respond to inquiries
- Only shows the latest row of data from the logs
- Access restricted to users within the organization domain
System Diagram
Web App Functions and Process Overview
UI Screenshots
Here’s a screenshot of the ticket management screen. Basic filtering is implemented.
Clicking the "Edit" button opens a modal where you can edit individual tickets.
Code.gs
Code.gs handles the backend, retrieving data from the spreadsheet.
| Function Name | Description |
|---|---|
doGet() |
Returns the UI as a web app. Entry point that displays the HTML page. |
onFormSubmit(e) |
Triggered when a Google Form is submitted. Issues a ticket ID, logs it, sends an email, and posts to Google Chat. |
sendToGoogleChat(ticketId, name, category, inquiry) |
Notifies Google Chat about new tickets using a webhook. |
getTickets() |
Retrieves only the latest state for each ticket from the log sheet. Used to display the list in the UI. |
updateTicket(updateData) |
Appends updated info to the log sheet based on the given ticket data (no overwrite, keeps history). |
Ticket Screen (HTML + JS) Functions and Overview
The ticket management screen is displayed via Google Apps Script’s HtmlService.
Built using HTML (Bootstrap + FontAwesome) and JavaScript to create the UI and interact with GAS.
| Function / Element Name | Role / Description |
|---|---|
loadTickets(isRefresh) |
Calls getTickets() to fetch data, then runs applyFilters()
|
applyFilters(initialLoad) |
Filters the ticket list by number, category, status, and assignee |
renderTickets(tickets) |
Converts the ticket list into HTML and outputs it to #ticketList
|
openEditModal(...) |
Populates the modal with ticket data and displays it |
saveModalTicket() |
Gets data from modal, calls updateTicket(), closes modal, refreshes UI |
escapeHtml(str) |
Escapes HTML to prevent XSS |
DOMContentLoaded event |
Initialization: sets up event listeners and loads open tickets on first load |
HTML Structure Elements
| Selector / Element | Description |
|---|---|
.filters |
Filter UI: ticket number, category, status, assignee, etc. |
#ticketList |
Area to display the ticket list |
.modal |
Edit modal (built using Bootstrap Modal) |
Notes
Web App with GAS
What this GAS project does is display and manage spreadsheet data through a custom UI. While similar operations can be done directly in a spreadsheet, it's not convenient to record and edit everything in one sheet. When history is written directly into spreadsheet cells, inputs become messy, and it’s unclear whether to overwrite or append—leading to inconsistent user behavior. By providing a unified form, it becomes easier to trace past actions even if responsibilities are handed over to different team members.
Spreadsheet Operation
By separating the input sheet and the log sheet, mixed data is avoided. With a dedicated log sheet, admins can trace the edit history. Non-admins are denied access to the base data, reducing the risk of tampering. Ideally, version control-style diffs would have been great, but that felt like overkill and was left out.
Data Aggregation / Standardization
By centralizing the inquiry channel and standardizing the data format, we should be able to accumulate training data for a future FAQ chatbot. Current FAQs are based mostly on human intuition, so there’s definitely room for improvement using actual data.
AI-Based Implementation
Starting simple and refining based on clearly identified issues is the way to go. One bitter memory: the initial ChatGPT code was too simple, so I asked Claude to review it—only for it to completely rewrite things and break the implementation beyond repair. Since I had little front-end experience, I kept things simple and upgraded step by step. As a result, I picked up the knack for making precise fixes and tracking down bugs early on.
Conclusion
While focusing on design and review, the core implementation was completed in just a few hours. The ability to release only what you need at low cost is a true advantage of building things in-house.
That said, “maintenance” is now the next challenge. Can non-engineers maintain such a system with AI support? Despite the hype that AI can solve everything, as long as systems interact with people and organizations, a minimum level of technical literacy is still necessary to stay accountable—and to avoid building the wrong thing entirely. It’s a tough problem. Perhaps we’ll need a system where AI interviews the current admin to gather maintenance context. But then again—who will maintain that system? The recursion never ends.
Top comments (0)