Batch 19 — Google Apps Script Approval Tracker

Theory & Why: A request needs a unique ID, requester, status, reviewer and an audit trail. Define Pending → Approved/Rejected before writing code.

Practical: Draw three states and list who may change them.

Expected Output: A clear state diagram and a role table.

Errors / Safety: A reviewer cannot approve the same request twice.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: A bound script uses the current spreadsheet as its data store; avoid mixing with personal records.

Practical: Create a blank Google Sheet named Astra Tracker Practice.

Expected Output: An empty workbook ready for two tabs.

Errors / Safety: Do not use real personal data in the training workbook.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: A bound script runs with access to its container spreadsheet after authorization.

Practical: In Sheets choose Extensions → Apps Script.

Expected Output: The Apps Script editor opens for that spreadsheet.

Errors / Safety: If the menu is missing, verify account permissions and browser mode.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: The source contains menu actions, validation, row writes, locks and audit history.

Practical: Replace the editor Code.gs content with the downloadable source below.

Expected Output: A single Code.gs file containing the tracker functions.

Errors / Safety: Copy all code including the CFG object; avoid partial snippets.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: Approval checks require a known reviewer account; sample placeholder emails must be replaced.

Practical: Edit CFG.APPROVERS to include your authorized Google account email.

Expected Output: The allowlist matches your account identity.

Errors / Safety: Session.getActiveUser().getEmail() can be blank in some account contexts; deny by default.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: Syntax mistakes prevent the custom menu and functions from running.

Practical: Save the script, review the function names and check for missing braces.

Expected Output: The editor saves without a syntax error.

Errors / Safety: Never paste API keys or passwords into a public project.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: Initialization creates Requests and Audit with fixed headers. It refuses to silently replace incompatible headers.

Practical: Select initializeTracker and Run; approve the requested Google authorization.

Expected Output: Requests and Audit tabs appear with frozen header rows.

Errors / Safety: Only authorize scripts you understand; this demo requires spreadsheet access.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: onOpen registers a custom menu whenever the sheet is opened.

Practical: Return to the spreadsheet and refresh it.

Expected Output: Astra Tracker menu appears in the toolbar.

Errors / Safety: If missing, reopen the sheet and check Apps Script executions.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: A sample request demonstrates data capture without exposing actual client information.

Practical: Use Astra Tracker → Add sample request. Enter Demo User and Website QA review.

Expected Output: A Pending request with a generated AST- ID appears.

Errors / Safety: The script validates empty and overlong input.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: UUID-derived IDs avoid most accidental duplicates; do not treat a short ID as cryptographic authorization.

Practical: Compare the ID in Requests with the CREATED event in Audit.

Expected Output: The same ID links request and history.

Errors / Safety: Use a database and stronger controls for high-scale production systems.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: Consistent columns make filters and reporting possible.

Practical: Read the nine Requests headers and identify Status, Reviewer, Decision Note.

Expected Output: You can explain every field in one sentence.

Errors / Safety: Do not reorder headers unless you also update the code.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: An append-only event log helps trace actions, but spreadsheet editors can still modify it.

Practical: Open Audit and locate the CREATED event.

Expected Output: The event includes timestamp, request ID and actor.

Errors / Safety: For tamper-resistant audits use a backend with restricted permissions.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: The review action reads the currently selected request row.

Practical: In Requests click any cell on the Pending request row.

Expected Output: The active selection belongs to a Pending request.

Errors / Safety: Do not select the header row or another tab.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: A decision should record the reviewer and reason, not only a status change.

Practical: Choose Astra Tracker → Review selected request; enter APPROVE and a short note.

Expected Output: Status becomes Approved; reviewer and timestamp are filled.

Errors / Safety: If identity is unavailable, the script blocks approval instead of bypassing checks.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: The Requests current state and Audit decision event should agree.

Practical: Find the APPROVE event with the same request ID in Audit.

Expected Output: A matching event records reviewer, time and reason.

Errors / Safety: Audit entries are for demonstration; restrict edit access in real workflows.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: A state transition must not repeat when status is no longer Pending.

Practical: Select the Approved row and try the review action again.

Expected Output: The app rejects the second decision.

Errors / Safety: Do not manually edit Status to bypass the workflow.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: Rejected is a separate final state with its own reason.

Practical: Add a second fictional request and review it with REJECT.

Expected Output: Rejected row and REJECT audit event appear.

Errors / Safety: Always test both approval paths.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: The allowlist check must reject users not explicitly configured.

Practical: In a separate authorized test account with sheet access, try Review without adding it to APPROVERS.

Expected Output: The menu blocks review with an authorization message.

Errors / Safety: Shared spreadsheet edit rights alone are not a secure authorization boundary.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: A simple dashboard can summarize pending and completed work without changing workflow data.

Practical: Create a new Summary tab; use =COUNTIF(Requests!F2:F,"Pending") and similar formulas.

Expected Output: Visible counts for Pending, Approved and Rejected.

Errors / Safety: Avoid editing the protected source headers.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.

Theory & Why: Real business approvals need authentication, server-side rules, permissions, backups and retention.

Practical: Write a checklist for protected data, access restrictions, incident response and backup recovery.

Expected Output: A documented list of controls missing from this educational prototype.

Errors / Safety: Do not deploy as a sensitive HR/payroll approval system without security review.

Independent practice: Explain the purpose of this point in your own words, repeat it using fictional data, and capture the result.