Creative requests can become difficult to manage when they arrive through different channels. A request may start in Messenger, continue through email, and end with a file shared in a separate Google Drive folder. The design work itself may be manageable, but the surrounding communication can quickly become confusing.
That was the problem behind the OSC Request Form, Tracker, and Dashboard project. I wanted to create one connected system where requests could be submitted, reviewed, assigned, monitored, and completed without relying on scattered messages.
Starting with the workflow, not the interface
Before working on the visual design, I mapped the actual stages of a request. A user submits the details. The team confirms receipt. The request is assigned. The status changes as the work progresses. The final project link and completion details are recorded. Each of these stages needed to be visible without forcing the team to check several different places.
Google Sheets became the central database because it was already familiar to the team, easy to maintain, and flexible enough for the project. Google Apps Script handled the logic between the sheet and the web interface.
The system was divided into three main parts:
- A request form for collecting complete project information
- A tracker where requesters could check progress using a tracking number
- An internal dashboard for managing assignments, statuses, links, and completion details
Designing the request form
The form needed to collect enough information for the creative team to begin working without becoming overwhelming. I grouped the fields according to how the request would be processed: requester information, request type, project details, schedule, dimensions, references, assets, and required delivery date.
This structure reduced follow-up questions because the requester could provide the context, files, and specifications in one submission. It also gave the team a more consistent set of data for every request.
The interface used Panpacific University colors and a lightweight responsive layout. I wanted it to feel institutional and professional while remaining simple enough for users who were not familiar with technical systems.
Building the tracker and dashboard
Each request receives a unique tracking number. The public tracker searches the spreadsheet through Apps Script and returns only the information needed by the requester, such as the request title, status, assigned staff member, date needed, and project link.
The internal dashboard provides a broader operational view. Requests can be grouped as unassigned, open, ongoing, completed, or closed. The team can update assignments, project links, notes, and completion dates without editing raw spreadsheet cells directly.
I also added logic for common workflow actions. Marking a request as completed can update several related fields at once, including status, ticket state, email indicators, and completion date. This reduces repetitive manual work and keeps the records consistent.
What I learned from building it
The biggest lesson was that internal tools should be designed around the people who will use them every day. A technically advanced feature is not automatically useful. The most valuable improvements were often simple: clearer labels, fewer clicks, better grouping, and visible status information.
I also learned to treat the spreadsheet as a structured database rather than an ordinary table. Column names, status values, and identifiers needed to remain consistent because the web application depended on them. Small changes in naming could affect the entire workflow.
Another important lesson was the value of versioning. As the system expanded, I started documenting changes, keeping backups, and displaying a visible version number. This made testing and troubleshooting much safer.
The result
The project transformed an informal request process into a connected operational system. It now serves as a request intake form, public status tracker, internal dashboard, and project record in one workflow.
More importantly, it showed me that design can solve operational problems—not only visual ones. The interface matters, but the real value comes from making a process clearer, faster, and easier to maintain.
The system is still designed to evolve. Future improvements may include deeper analytics, automated summaries, expanded notifications, and more administrative controls. The foundation, however, is already in place: one source of information and a workflow that is easier for both requesters and the creative team.