UK local government · Google AppSheet · practical delivery

A playbook for building AppSheet apps that are safe, supportable, and actually used.

This guide treats AppSheet as a serious delivery platform, not a shortcut. It combines official guidance, Google Workspace security patterns, and the realities of local government delivery into one opinionated reference for teams that need to ship internal tools and resident-facing services without creating long-term risk.

Start reading Jump to the pipeline

Overview

The short version: design the data first, lock down access, and govern change.

Design the data first

AppSheet lives or dies on the model underneath it. One row, one record. Clear keys. Controlled relationships.

Lock down access

Use authentication, domain restrictions, security filters, and PII handling as defaults, not extras.

Govern the lifecycle

Separate dev, test, and production; approve what reaches production; and maintain an app registry.

1. Introduction

AppSheet is for real work, not toy prototypes.

Who this is for

This playbook is for UK councils and local authorities using Google Workspace and AppSheet to replace spreadsheet-driven processes, digitise paper forms, build case management tools, and automate back-office workflows.

It is written for digital teams, software developers, IT governance leads, and anyone involved in delivering internal tools or resident-facing services.

Standards to read alongside

  • Technology Code of Practice and the Local Digital Declaration
  • The Service Standard, where resident or staff services are involved
  • UK GDPR and the Data Protection Act 2018
  • The Cross-Government Low-Code Community
  • The Local Government Association’s cyber, digital, and technology guidance

2. Data structure design

Good data structure is the cheapest risk reduction you can buy.

A poorly structured spreadsheet creates compounding problems with performance, security filtering, reporting, and maintainability. AppSheet is fundamentally a data-driven platform, so the app can only be as good as the data underneath it.

One row, one record

Each row should represent a single entity. Each column should represent a single attribute. No multi-value cells, no time-series columns.

Every table needs a key

Use a unique identifier column as the primary key. Prefer `UNIQUEID()` or a computed key, not row numbers.

Use Ref columns

Connect tables through explicit relationships so AppSheet can build parent-child navigation and reverse references automatically.

Keep column names clear

Use descriptive names, consistent key fields, and a bold header row. Avoid abbreviations and generic labels.

Typical local government model

  • Residents, addresses, cases, case notes, officers, and lookup tables
  • Separate PII-flagged columns from non-sensitive operational data
  • Use lookup tables for statuses, priorities, service areas, and categories

When to move beyond Sheets

  • Tables exceed roughly 10,000 rows
  • You need transactional integrity or audit logging
  • Multiple apps need a shared canonical data source
  • Sync times become unacceptable

Google Sheets guidance

  • Keep one sheet per table and avoid mixing unrelated data
  • Treat the sheet as a database and write through AppSheet, not directly
  • Avoid Sheets formulas in columns AppSheet reads on the server side
  • Use AppSheet expressions and virtual columns instead

Practical baseline structure

Residents, Addresses, Cases, Case_Notes, Officers, and lookup tables form a strong starting point for many council use cases, because they keep access controls, reporting, and relationships manageable from the start.

3. Shared drive strategy

Store apps and data where the organisation owns them.

Use Shared Drives, not personal drives

Every application and its data should live on a Google Shared Drive. That removes the single-leaver problem and keeps ownership with the organisation instead of an individual.

Set a clean drive structure

One Shared Drive per application or tightly coupled service area is the recommended baseline. It gives you access control granularity, PII containment, audit clarity, and clean decommissioning.

Least privilege matters

App developers and owners need appropriate drive access. End users should not be members of the Shared Drive at all. Their access should be mediated through the AppSheet app and its security filters only.

4. Security, roles, and data access

Security needs to be layered, explicit, and boring in the best way.

Authentication

Require sign-in for any app that touches operational or personal data. Restrict access to your domain and manage membership through groups.

Role-based access

Use a Users table, role fields, `USERROLE()`, and `USEREMAIL()` to control visibility and ownership at row level.

Performance

Security filters run on every sync. Keep them simple, avoid expensive nested lookups, and test at realistic scale.

PII handling

Mark sensitive columns as PII, minimise what you collect, and plan external retention and deletion processes.

What should be flagged as PII

  • Names, addresses, postcodes, email addresses, and phone numbers
  • National Insurance numbers, dates of birth, NHS numbers
  • Safeguarding notes, council tax references, tenancy references, and photographs

Platform controls to use

  • Require authentication and restrict to your domain
  • Disable external sharing where personal data is involved
  • Apply governance policies at the Workspace level
  • Use audit trails and DPIAs for personal-data apps

Platform security

  • SOC 2 Type II certified controls
  • Encryption in transit and at rest
  • Cached offline data only on end-user devices
  • Audit logging through the Google Workspace admin console

External sharing

Disable external sharing for apps processing personal data and enforce the policy at the organisational unit level, not as an optional app-by-app habit.

5. Environment strategy

Three environments are not a luxury. They are the minimum safe setup.

01

Development

A separate copy where you experiment, break things, and iterate without touching production.

02

Testing

A review environment for trusted testers using synthetic or anonymised data that mirrors production structure.

03

Production

The live app, promoted only after testing and deployment checks have passed.

How to implement it

AppSheet’s copy and upgrade flow is the basic mechanism for separating environments. Create production on its own data source, copy it for development, and use synthetic or anonymised data in non-production copies.

Deployment checklist

  • Run AppSheet deployment checks and resolve issues
  • Test on actual devices with different user roles
  • Verify bots, file uploads, and sync times
  • Update documentation and secure sign-off before promotion

6. Automation and Apps Script

Automation should be reusable infrastructure, not one-off glue.

Bot principles

  • One bot per business event
  • Reuse processes and tasks where it makes sense
  • Prefer scheduled bots for maintenance and reporting
  • Test automation in development first

Where Apps Script fits

Use the Apps Script connector when you need document generation, external APIs, calendar actions, file management, or more complex validation than AppSheet expressions can provide.

Shared libraries

Shared Apps Script libraries give you consistency, maintainability, and governance across multiple apps. Keep them focused, versioned, and owned by the IT or digital team.

Common library use cases

  • PDF generation
  • Backups
  • File management
  • Deduplication
  • Notifications and communication

Versioning and access control

Release library versions deliberately, pin consuming scripts to numbered releases, and keep edit access limited to the core team. Consumers should not need edit rights to the library itself.

7. Deployment pipeline

Design, build, test, promote, and operate with intention.

Design

Define the problem, complete the DPIA if needed, sketch the data model, and agree roles and dependencies.

Development

Build data connections, views, actions, and automation in a Shared Drive-backed development app.

Testing

Use synthetic test data, recruit real users, iterate on issues, and run the deployment check before promotion.

Production

Apply structural changes, upgrade the app, verify critical flows, and communicate the release clearly.

Ongoing operations

Review logs weekly, usage monthly, data quality quarterly, and the DPIA and access model annually. The goal is not just to launch an app. It is to keep it trustworthy.

8. Governance

Citizen development only works when the guardrails are real.

Centre of Excellence

Create a small AppSheet CoE to own shared libraries, review apps before production, maintain the playbook, manage governance policies, and mentor citizen developers.

Governance controls

  • Restrict allowed data sources
  • Disable risky features where needed
  • Require authentication and domain restriction
  • Use an approval workflow before deployment

Training and onboarding

  • AppSheet fundamentals training
  • Data protection essentials
  • Local onboarding for data structure, security, environments, and shared libraries
  • Paired building for the first app

App registry

Maintain a central registry with app name, owner, data sources, DPIA status, version, deployment state, and shared library dependencies.

Why the registry matters

A central registry is essential for audit, impact analysis when shared libraries change, and decommissioning apps cleanly when owners leave or services are retired.

9. Offline and performance

Build for field work, poor connectivity, and low-friction sync.

Offline considerations

AppSheet can cache data locally and sync later. Keep synced datasets small, test offline use properly, and design workflows to minimise conflicts when users are working away from a reliable connection.

Performance optimisation

  • Use security filters to reduce data volume
  • Avoid volatile expressions unless required
  • Limit virtual columns on large tables
  • Use slices for focused views
  • Keep image sizes under control

Performance targets

Monitor sync times in the AppSheet admin dashboard and aim for routine syncs under five seconds. If performance degrades, reduce data volume, simplify expressions, and reassess the data model before the problem spreads.

Closing note

AppSheet can work very well in local government when it is treated with discipline.

This playbook is built around that idea: careful data design, clear ownership, deliberate environments, strong governance, and automation that can be maintained after the original builder moves on.