Google Apps Script — Complete Request Tracker Practical
The local preview is a simulation, not connected to Google Sheets. Code.gs must be pasted into your own spreadsheet-bound Apps Script project. Lessons below contain English technical steps with multilingual interface guidance.
Local output preview — sample request dashboard
Use fictional entries to understand how the actual Sheet will summarize request status.
Complete working Apps Script example
Copy the entire Code.gs, then run setup → demoAddRequest → updateSummary → demoUpdateRequest → runSelfTests. This is an educational spreadsheet-bound example, not a deployed web service.
A Google Form writes a row to a response Sheet. Apps Script can validate, transform and report on that data. Separate collection, processing and reporting to avoid duplicate actions.
Practical steps
Create a new Google Sheet named Service Requests. Add a tab Requests with headers Request ID, Timestamp, Name, Type, Description, Status, Updated At. Add a second tab Summary.
Expected output
The two sheets and exact seven headers are visible.
02. Google Apps Script editor and permissions Starting
A bound script is attached to a spreadsheet. Authorization grants access to data; review scopes before approving. Never share personal credentials or authorization tokens.
Practical steps
In your spreadsheet choose Extensions → Apps Script. Rename project Request Tracker. Paste the supplied Code.gs. Save. Run setup once and review the authorization dialog.
Expected output
A successful setup creates or verifies the Requests tab and header row.
Errors & solutions
Authorization denied: check account permissions. Script editor missing: verify you can edit the spreadsheet.
03. Data model and unique IDs Intermediate
A request ID is an immutable reference. A UUID reduces collision risk. Timestamps must be Date objects, not locale-dependent strings.
Practical steps
Inspect addRequest. It generates a UUID, checks input, and appends a seven-field row. Run demoAddRequest to insert fictional data.
Expected output
One row appears with an ID, date, request type and New status.
Errors & solutions
Wrong sheet: run setup. Empty values: addRequest rejects missing name/type/description.
04. Validation and safe writes Intermediate
Validate length, types and allowed status values before saving. LockService reduces concurrent-write conflicts but does not replace permission controls.
Practical steps
Change the demo name to empty and run demoAddRequest: note validation error. Restore name, run again. Review LockService.waitLock and releaseLock.
Expected output
Invalid input is rejected; valid input appends one row.
Errors & solutions
Lock timeout: retry later. Permission error: confirm the account can edit the sheet.
05. Filtering and dashboard Intermediate
Read rows once and compute totals in memory to reduce calls. Use status counts to monitor outstanding work.
Practical steps
Run updateSummary after adding sample requests. Open Summary and compare counts with Requests.
Expected output
Summary shows total requests and counts by status.
Errors & solutions
Totals differ: ensure Status is one of New, In Progress, Done.
06. Update request status Advanced
Update by immutable request ID, never by row number supplied by a client. Whitelist allowed status transitions and validate the target.
Practical steps
Copy a generated Request ID. Run demoUpdateRequest after replacing the placeholder with that ID. Run updateSummary again.
Expected output
The matching row changes from New to In Progress and Updated At changes.
Errors & solutions
Not found: verify the exact ID. Invalid status: use New, In Progress or Done.
07. Time-driven triggers and quotas Advanced
Installable triggers run under the creator account and are subject to Google quotas. Repeated triggers may duplicate reports; design idempotent jobs.
Practical steps
In Apps Script open Triggers → Add Trigger. Select updateSummary, Time-driven, Day timer. For initial learning, run manually before enabling the trigger.
Expected output
Summary updates when the trigger runs; check Executions for errors.
Errors & solutions
Trigger not firing: check owner authorization and Executions. Quota exceeded: reduce frequency.
08. Logging, testing and privacy Full Advanced
Logs should record safe event metadata, not private descriptions. Use fictional data for testing. Limit spreadsheet access and do not publish an unrestricted web app.
Practical steps
Run runSelfTests; inspect Execution log. Add a request with fictional data and verify summary. Share only with a test editor if needed.
Expected output
Self-tests pass and no secrets appear in logs.
Errors & solutions
Tests fail: restore constants and rerun. Data exposed: revoke unnecessary sharing immediately.
09. Deployment and client handover Professional
A production workflow needs a schema, role ownership, backup, change log, rollback and training. This sample is not a secure multi-tenant application.
Practical steps
Make a copy of the spreadsheet. Document columns and triggers. Test editor access, invalid input, status updates and recovery from a copied file.
Expected output
A handover checklist and verified backup are available.
Errors & solutions
Triggers on a copied file may require re-creation and authorization.
Final assignment and verification
Build a fictional request register with 5 rows; validate a blank name; update two statuses; compare the Summary counts; check Executions for errors; save a backup copy; document trigger ownership. Do not use real employee data in the exercise.
Acceptance criteria: 7 headers match, IDs are unique, invalid inputs rejected, totals consistent, no unintended sharing, and backup can be opened.