Building the Standards on Google Workspace

1. What this is, and what it is not

This is an implementation option, and the second one we have published. The compliance calendar, rejected pack, database reactivation, debtor chasing, invoice check and cited answer standards are the specification. Nothing here overrides them. If this page and a standard disagree, the standard wins and this page has a bug.

It is the companion to building the standards on Microsoft 365. The two pages describe the same build: same objects, same gates, same six measures, different substrate. Read whichever one matches the licence you already hold, and do not read both looking for a winner. There is no general answer to which stack is better, only an answer for a business that already pays for one of them.

Every standard has a per-standard build plan on Microsoft today. The Workspace equivalents are being written. Until they land, this page and the standard are enough to build from, and the Microsoft plan for the same standard is still worth reading for the mapping: it names which check is enforced by what, and between the two stacks only the names of the components change.

• the compliance calendar, mapped check by check
• the rejected pack, and the binding layer that refuses
• debtor chasing, and the suppression gate per send
• multi-site conformance, and the key contract between branches

Start with the standard, not the plan. The plan assumes you have read it and it will not repeat the argument.

2. What the subscription already covers

This is the part worth checking before anyone quotes you for a platform. At the time of writing, Google includes an AppSheet Core licence at no additional cost with Business Starter, Standard and Plus, with Enterprise Starter, Standard and Plus, with Frontline Starter and Standard, with Enterprise Essentials Plus, with Education Standard and Plus, and with Workspace for Nonprofits. If you pay for Workspace on a business plan, you are already paying for the app builder and the automation engine sitting unused inside it.

Looker Studio has a no-cost tier and that tier is the whole reporting layer here. The Pro subscription adds team content management and support, not the charts and not the viewer filtering. Apps Script has never been licensed separately. Drive, Groups and Chat you already have. The build lands on what you already pay for, which is the entire reason this page exists.

Now the part a vendor will not open with. What is in the box has ceilings, and three of them shape the design rather than merely annoying you later.

• ✓Apps Script runs in six minute slices. One execution gets six minutes, all your triggers together get six hours a day, and you get twenty triggers per user per script. A nightly job that loops the whole book is fine at two hundred subjects and dead at two thousand. Write the derive as a batch with a stored cursor, or as a bot that fires per row, and you never meet this limit.
• ✓AppSheet Core does not talk to a database. BigQuery, SQL Server, PostgreSQL, Oracle, Salesforce and the AppSheet API itself are Enterprise features. On Core the data lives in AppSheet tables, Sheets and Drive, and anything from a job system arrives through Apps Script calling that system’s own API. That is a real constraint on path B below, and it is better priced now than discovered in week three.
• ✓Sending has a daily number. Apps Script reaches 1,500 external recipients a day on a Workspace account and 2,000 inside the domain; AppSheet Core sits in the hundreds per hour and the thousands per day. For a compliance book that is irrelevant. For a reactivation campaign across a large list it is the first wall you hit, and the answer there is a sending product rather than a workaround.

Check all three against Google’s current documentation before you design around them. Editions and limits move, and a page is a worse source than the admin console you are already signed into.

Against Microsoft the trade is straightforward, and it runs both ways. Field capture is cheaper here, because the mobile app that photographs the evidence is in the box rather than a premium licence. Reporting governance is weaker here, because row level security is a filter on the viewer’s email address rather than group membership enforced by the directory. If the book is sensitive enough that a filter is not good enough, that is the point where this stack stops being free.

3. Two ways to land it

Path B is the more common of the two, and it is where the Core connector limit bites. There is no drag-and-drop connector to your job system here. The sync is a script somebody writes, it runs on a schedule, and it needs the three things any sync needs: a watermark so it only pulls what changed, an upsert against the shared key, and a failure that is loud. None of that is hard. All of it is work somebody has to own.

Same gates and same reports on both paths. The thing that must not differ between them, or between two branches on the same path, is the shape of the records. Extra fields are fine. Different keys are not, and a fortnight is long enough for two branches to invent incompatible ones.

4. The objects, as tables

One AppSheet database, one shared drive, one set of keys. The names below are generic on purpose: a fire business, a property manager and a subcontractor each read them differently and the shape does not change. They match the Microsoft plan field for field, because these are the standard's objects rather than a stack's.

Database: Operations

Rules          rule_id, instrument, clause, jurisdiction,
               frequency, effective_from, effective_to
Subjects       subject_id, client, location, class, rule_id,
               last_pass_at, status (active | blocked | retired)
Work           work_id, subject_id, window, assignee,
               result, queue
Evidence       work_id, drive_file_id, captured_by,
               captured_at, kind
Exceptions     work_id or subject_id, code (closed list),
               owner, review_by
Contacts       subject_id, channel, outcome, contacted_at
People         person, competency, expires_on, assessed_by
Segments       segment, group_email, owner

Shared drive   /Evidence   /Instruments   /Output

Keys are shared. A branch may add columns. A branch may not
rename or fork subject_id, rule_id or work_id.

Evidence is a table holding a Drive file id rather than a cell holding a link, because a link cannot be queried, cannot be counted, and cannot tell you what is missing. Counting what is missing is the entire job. The drive is a shared drive, never an individual's My Drive, or the evidence resigns when they do.

5. The gates, which must be bots

This is the part that decides whether you implemented a standard or drew one. A gate is a rule a person cannot type past. In this stack that means an AppSheet bot, a column expression the app enforces, or a scheduled script that reconciles and flags. It does not mean a cell, a filter view, a conditional format, or a line in an induction document.

1. Derive the date. On add or update of a Subject, a bot computes the next obligation from the rule and the anchor. A person may override only into a separate override field carrying their name and a reason, and the override shows in the reporting.
2. Own the write path. This step is specific to this stack, and it is the one most builds skip. If the book is a Sheet twenty people can open, every gate above it is advisory. Either the app owns the table and nobody edits the data directly, or the ranges are protected and a scheduled script re-derives and flags what was changed by hand.
3. Hold the clock when blocked. A subject that cannot be reached does not silently roll forward. The status changes, the obligation stays live, and a review date is set.
4. Refuse the close. Work cannot reach a completed state until the evidence rows the class requires exist. Missing evidence is its own queue, and that queue is not the same thing as a pass.
5. Write the rank. A scheduled bot scores and stores the rank on the row. Nobody sorts a sheet, and the reason a row is top is a value somebody can read.
6. Suppress before contacting. The contact step checks the suppression conditions first. This is a gate rather than a filter in a view, because a view is per person and a gate is not.
7. Check competency before assigning. Work of a class cannot be assigned to somebody whose competency is missing or expired. The check reads the People table at assignment time.
8. Escalate on a clock, not on a feeling. Anything in a queue past its threshold posts to a named Chat space with the record attached and an acknowledgement recorded.

Chat is where the gates surface, not where they live. A card is a good way to ask a person for a decision and a bad place to keep the only record of it. Whatever the person chooses is written back to the table.

Run the triggers and the bots from an account the business owns, not from the login of whoever built it. A trigger installed under a personal account runs as that person, and the day they leave, the derive stops and the only notification goes to a mailbox nobody is reading.

6. Looker Studio holds the argument

The reporting layer is not decoration here and it is not a dashboard exercise. Every standard makes a claim about how the work runs. Looker Studio is where that claim is either true or visibly not, in front of people who can do something about it.

Build one report over the tables rather than a report per team. The blends follow the keys: Rules to Subjects, Subjects to Work, Work to Evidence and Exceptions, Segments to Subjects. Six measures do most of the work, and they belong in the data source as calculated fields rather than inside a single chart where only their author can find them.

• ✓Derived rate. The share of records whose date was computed rather than typed. It should approach one, and the gap is your list of exceptions.
• ✓Unbound records. Work marked done with no evidence attached. The target is zero and the useful chart is how long each one has been open.
• ✓Override rate. Every manual override with who did it and why. A rising line here is the standard being worked around, not broken.
• ✓Coverage. The share of the book with a live future obligation. This is the number that says whether the system describes the business or only part of it.
• ✓Actioned. Ranked items that got a job or a logged contact inside the window. Ranking nobody works is a report, not a process.
• ✓Gate latency. Time from something entering a queue to a person acting on it. The gates are the design; this says whether they are staffed.

Row level security is the honest weak spot, and it is the one place this page will not tell you the two stacks are equal. Looker Studio does it by asking the viewer for their email address and filtering the data source against it. That works on the no-cost tier and it is genuinely enough for a branch that should only see its own book. It is a filter, though, not a boundary the directory enforces, and anyone who can open the underlying table sees all of it. Map the filter to the Segments table and the group email, never to a name typed into a chart. If the data is sensitive enough that a filter will not do, the answer is to put the book in BigQuery, and that is the moment this stops being covered by the subscription.

Schedule delivery into the Chat spaces the bots already post to. An email delivery is a backup. If the only place the numbers appear is a monthly attachment, the reporting layer is not part of the operating rhythm and it will stop being maintained.

7. Seven things not to hand-roll here

Each of these is easy in a spreadsheet, works for about a month, and fails in a way that looks like a people problem rather than a design one.

8. Provider: what to stub, and what not to hand-roll

Almost all of this is yours to build, which is the point of the page. Two things are worth naming, and one of them is a warning rather than a recommendation.

stub in development
  - a table with a handful of rows, one bot on a manual trigger
  - a script that logs instead of sending
  - one Looker Studio page, no viewer filter

production
  - record, gates, reporting: your own Workspace domain.
    Ordinary build.
  - field capture: the AppSheet mobile app, or the job system's
    own app where path B applies. Do not build two capture
    surfaces. This is the step that costs extra on the Microsoft
    side and does not here.
  - voice on the phone number: a product, where the enquiry
    standard calls for one. It writes to the same tables.
  - the person on each gate: a person. No product.

do not hand-roll
  - the derive step and the refuse-to-close step. Everything
    else here degrades gracefully. Those two are the standard.

A warning specific to this stack: a licence is not an implementation. Having AppSheet in the subscription is what makes this cheap, and it is also what makes a half-built version look finished, because an app that opens on a phone reads as a system whether or not anything behind it ever refuses. The conformance checklist below is the difference.

9. Conformance checklist

Hold a DIY build or a vendor to this. If a box is empty, it is not in production.

• ✓1. Dates are written by a bot, not typed into a cell. A manual edit is either blocked or recorded as a named override
• ✓2. The app owns the write path, or drift is detected and flagged. Editing the table directly is either impossible or visible within a day
• ✓3. Completion is a rule that can refuse. Work with missing evidence cannot reach a completed state
• ✓4. Rank is stored or calculated, never a sort order. Anyone can read why a row is at the top
• ✓5. Suppression is checked at the contact step. Not a filter on somebody's personal view
• ✓6. One set of keys across every branch and both paths. Extra columns are fine. Renamed or forked keys are not
• ✓7. Evidence files live in a shared drive, with the file id on the row. Nothing that matters sits in an individual's My Drive
• ✓8. Triggers and bots run as an account the business owns. Nothing stops because one person left or reset a password
• ✓9. Looker Studio reads the tables, and viewer filtering maps to Segments. One report rather than one per branch, and no names typed into a chart
• ✓10. Someone other than the builder can explain what it does, and it runs on a domain the business owns. The apps, the scripts and the report survive the person who set them up