Related Atlas entry
Purpose
Provide technicians a clear, role-filtered view of device refresh work so swaps can be executed without manual reconciliation. This playbook documents the end-to-end workflow from data extraction to the technician-facing portal.
When to use this playbook
- Before each refresh cycle or bulk device swap window.
- When updating access-control rules or technician site assignments.
- When the portal shows stale or inconsistent data.
Signals to stop or escalate
- Extract schema changes break the portal view.
- Access rules expose data outside intended sites.
- Refresh data is missing or significantly out of date.
Audience and access
- Primary operators: IT technicians and site support.
- Data stewards: IT leadership or data services staff.
- Required access: SQL extract source, Google Sheets owner access, Apps Script deployment rights, and Google Sites edit access.
System overview
Data flow:
- SQL extract produces the canonical staff/device dataset.
- Data is loaded into a Google Sheet as the boundary object.
- Apps Script web app renders filtered views based on user identity and role/site config.
- Portal is embedded in a Google Site for easy access and adoption.
Patterns in use
Inputs
- SQL extract of staff/device assignments.
- Role and site access config sheet.
- Optional status fields or override notes (TBD if implemented).
Outputs
- Role-filtered technician views.
- Device swap lists and status snapshots.
- Operational visibility for leadership (optional views).
Preconditions
- SQL extract job is defined and producing a consistent schema.
- Access control sheet is accurate and up to date.
- Apps Script deployment is current and published.
- Google Site link is visible to the intended audience.
Run steps (data refresh cycle)
- Generate the SQL extract
- Run the extract against the source system (Snipe-IT or asset DB).
- Validate row counts and required columns.
- Load data into Sheets
- Upload or sync the dataset into the canonical Sheet.
- Preserve headers and column order to avoid script breakage.
- Validate access controls
- Review role/site mapping for recent staffing changes.
- Confirm that technician accounts are mapped to the correct sites.
- Deploy Apps Script
- Publish the latest code if changes occurred.
- Confirm web app permissions and deployment URL.
- Verify portal output
- Spot-check a technician view for a sample site.
- Confirm role-based filters are applied as expected.
Technician workflow (day-to-day)
- Open the portal in Google Sites.
- Filter by assigned site (automated based on login).
- Review the list of devices due for refresh.
- Execute swaps and follow the operational process for tracking completion (TBD if tracked in-sheet).
Validation checklist
- Each technician only sees their assigned sites.
- Device counts match expectations from the extract.
- No stale data older than the refresh cadence (TBD cadence).
- Exceptions (missing device records or staff mismatches) are logged for follow-up.
Monitoring and observability
- Track refresh timestamps in the sheet.
- Review Apps Script logs for errors or access denials.
- Track portal usage via Google Sites analytics if available (TBD).
Failure modes and recovery
- Schema changes in extract break the UI
- Mitigation: pin headers and add schema validation in script.
- Incorrect role mapping exposes data
- Mitigation: require monthly access review and spot checks.
- Portal shows stale data
- Mitigation: re-run extract and confirm upload.
Security and privacy
- Enforce least-privilege access in Google Workspace.
- Prevent cross-site visibility with config-driven filters.
- Avoid embedding sensitive notes unless required.
Change management
- Document schema changes to the extract and update the script.
- Log major UI or access-control changes in the decision log.
- Keep a backup of the canonical sheet before major revisions.
Open items and improvements
- Define a standard refresh cadence and SLA for data updates.
- Add telemetry for swap completion progress.
- Determine whether technicians should update statuses directly in the portal (TBD).