Back to All Cheatsheet Libraries cheatsheets

Google AppSheet

Data sources, view types, and automation reference for Google AppSheet.

AppSheet turns a spreadsheet into a real mobile app

Point it at a Google Sheet, and it reads the columns, infers types, and generates a working app with forms, lists, detail views, search, and offline sync — for iOS, Android, and browser at once. No code, and no app-store submission.

The thing to internalise early: your spreadsheet is the database. AppSheet is a UI and logic layer on top of it. Fix the sheet structure first, because everything else is generated from it.

Concept	What it is	Notes
Data source	Google Sheets, Excel, Cloud SQL, Salesforce, and others.	Sheets is the usual starting point. Move to Cloud SQL when row counts grow.
Table	One sheet tab, added to the app as a table.	Row 1 must be the header row and column names must be unique.
Column type	Text, Number, Date, Enum, Ref, Image, LatLong, Signature, and more.	AppSheet guesses on import and often guesses wrong. Always review these first.
Key	The column uniquely identifying each row.	Never let it default to a row number. Add an ID column with `UNIQUEID()` as its initial value.
Ref	A column pointing at another table's key — a foreign key.	Creating a Ref auto-generates a reverse-reference, which is how parent/child views appear.
Slice	A filtered, permission-limited subset of a table.	The main tool for security and for "show only my records" views.
View	How data is displayed: table, deck, gallery, map, calendar, chart, form, dashboard.	Several views can point at the same table or slice.
Action	A button that does something — edit, delete, open a URL, run a script.	Attach to a view or trigger from automation.
Bot	Automation: an event plus a process of tasks.	Replaces the older Workflow and Report features.
Virtual column	A column computed by expression, not stored in the sheet.	Recalculated constantly — the most common cause of a slow app.

Structure the sheet properly

One header row at row 1, unique column names, one record per row, no merged cells, no blank rows, no summary formulas mixed into the data. Merged cells in particular break AppSheet outright.

Give each table its own tab, and each tab a real ID column.

Create the app

From appsheet.com → Create → App → Start with existing data, or from the sheet itself via Extensions → AppSheet → Create an app.

Fix the column types immediately

Data → Columns. Correct anything mis-inferred, set the key column, and mark required fields. Do this before building views — changing a type later can invalidate expressions built on it.

Key column setup: Column name: ID Type: Text Key: ✓ Initial value: UNIQUEID() Show?: off (users never need to see it)

Link tables with Ref columns

Set a column's type to Ref and choose the target table. AppSheet then offers a dropdown of real records instead of free text, and adds the reverse reference on the parent.

Build views

UX → Views. Pick the view type that matches the data — Deck for browsing, Table for scanning, Map for anything with a LatLong, Calendar for dated records. Keep the primary navigation to five entries or fewer.

Control access with slices

Create a slice with a row filter such as [Assigned To] = USEREMAIL(), then point the view at the slice rather than the table. Also set the slice's allowed actions to restrict adds, edits, and deletes.

Test as a real user, then deploy

Use the preview pane, and the user switcher to test as someone else — security filters look fine until you view them as a different account. Then Manage → Deploy runs a deployment check and moves the app out of prototype mode.

Group	Expression	Does
User	`USEREMAIL()`	Signed-in user's email. The backbone of every per-user security filter.
User	`USERROLE()`	Returns Admin or User, if roles are configured.
Date	`TODAY()` / `NOW()`	Current date / current date-time.
Date	`TODAY() + 7`	Date arithmetic in days — no special function needed.
Date	`WORKDAY(TODAY(), 5)`	Skips weekends when adding days.
Logic	`IF(cond, a, b)`	Two-way branch.
Logic	`IFS(c1, v1, c2, v2)`	Multi-way branch — returns the first matching value.
Logic	`SWITCH(x, a, 1, b, 2, 0)`	Value lookup with a default. Cleaner than nested IFs.
Logic	`ISBLANK([Col])`	True when empty. Use rather than comparing to `""`.
Lookup	`LOOKUP(val, "Table", "KeyCol", "ReturnCol")`	Fetch a single value from another table.
Lookup	`SELECT(Table[Col], condition)`	Returns a list of values matching a condition. The workhorse expression.
Lookup	`ANY(SELECT(...))`	Takes the first item from a list — how you turn a SELECT into one value.
Lookup	`[Ref].[Column]`	Dereference — read a parent record's field. Cheaper than a SELECT.
Lists	`COUNT(list)`	Number of items.
Lists	`SUM(Table[Amount])`	Total a column, usually wrapped in a SELECT.
Lists	`IN(x, list)`	Membership test.
Lists	`[Related Orders]`	Auto-generated reverse reference — every child row pointing here.
Text	`CONCATENATE(a, " ", b)`	Join text values.
Text	`UNIQUEID()`	Random unique key. Use as a key column's initial value.
Text	`CONTEXT("View")`	Current view name — lets one expression behave differently per view.

# Security filter — users only see their own rows [Assigned To] = USEREMAIL() # Manager sees everything, everyone else sees their own OR( USEREMAIL() IN Managers[Email], [Assigned To] = USEREMAIL() ) # Order total from its line items (virtual column on Orders) SUM(SELECT(OrderLines[Line Total], [Order ID] = [_THISROW].[ID])) # Count of open tasks for this project COUNT(SELECT(Tasks[ID], AND([Project] = [_THISROW].[ID], [Status] <> "Done"))) # Dropdown limited to active items only (Valid If on a Ref column) SELECT(Products[ID], [Active] = TRUE) # Show an action button only when the row is still editable AND([Status] = "Draft", [Created By] = USEREMAIL()) # Required only when another field says so (Required If) [Status] = "Rejected"

Expression essentials

[_THISROW] refers to the current row from inside a SELECT — without it, the inner reference is ambiguous.
[_THIS] is the current column's own value, used in Valid If and formatting rules.
Use the Expression Assistant — it validates live, shows the result type, and explains errors. Never write expressions blind.
Not-equal is <>, not !=.
Text comparison is case-insensitive by default.

Choose the event

Data change (adds, updates, deletes on a table) or Scheduled (hourly, daily, weekly, monthly). Data-change events can carry a condition so the bot only fires on rows that matter.

Event: Data change on Orders Change type: Updates Condition: AND([Status] = "Approved", [_THISROW_BEFORE].[Status] <> "Approved")

That [_THISROW_BEFORE] comparison is what stops the bot re-firing on every subsequent save.

Add tasks to the process

Send an email, send a notification, send an SMS, create a file (PDF or Docs from a template), change data in a table, or call a webhook.

Build the document template

For file tasks, AppSheet generates a Google Docs template you edit directly. Use <<[Column]>> placeholders, and <<Start: SELECT(...)>> … <<End>> to repeat a block per child row — that's how invoices with line items are built.

Monitor it

Manage → Monitor → Automation Monitor shows every run, its inputs, and any failure. Check it after deploying — a silently failing bot is the classic AppSheet support ticket.

Automation gotchas

A Change data task that edits the same table its event watches can trigger itself. Always guard with a condition.
Scheduled bots run in the app owner's context, not a user's — USEREMAIL() resolves to the owner.
Email tasks send from AppSheet's servers by default; deliverability improves markedly if you configure your own SMTP.
Bots do not run while offline. Changes queue and the bot fires when the device syncs.
Test with the Test button on the bot before deploying — it runs against a real row and shows the output.

Performance

Why AppSheet apps get slow

Virtual columns are the usual culprit. Every one recalculates on every sync, for every row. Delete any you don't display.
Prefer [Ref].[Column] dereferences over SELECT() — much cheaper.
Use security filters rather than slices to limit data volume: a security filter stops rows ever reaching the device; a slice filters after download.
Google Sheets realistically tops out in the low tens of thousands of rows for a responsive app. Beyond that, move to Cloud SQL.
Turn on Delta sync and Server caching under Manage → Performance.
Images stored in Drive load lazily — many large photos in a deck view will feel slow on mobile data.

Things that catch people

Never edit the sheet's structure while the app is live without regenerating the table schema — renaming a column silently breaks every expression using it.
Don't put formulas in a column AppSheet writes to; they'll be overwritten. Use virtual columns instead.
Deleting a row in the sheet while a user holds an offline edit for it causes a sync conflict.
The key column must never change value. A key built from editable data breaks references the moment someone edits it.
Offline mode needs to be explicitly enabled per app, and images sync only when configured to.

Licensing

Building and prototyping is free. Deployed apps need a paid licence per active user.
Some Workspace tiers include AppSheet Core — check before buying separately.
The deployment check will tell you which features require which plan.
Cost scales with users, not apps — one licence covers a user across all your apps.

Tips

Test as another user

The preview pane's user switcher lets you view the app as any account. Security filters always look correct as the owner — this is the only way to actually verify them.

Barcode and NFC scanning

Set a column's Scannable property and the form field gains a scan button. Turns a phone into an inventory scanner with no extra work.

Signature and image capture

Signature, Image, and Drawing column types capture directly on device and store files in Drive. Delivery confirmations and inspection reports need nothing more.

LatLong columns

A LatLong column plus a Map view gives location tracking free. HERE() captures the current position as an initial value.

Copy the app to experiment

Manage → Copy app before any big restructure. There's no undo for schema changes, and this is the only safe rollback.

Read the audit log

Manage → Monitor → Audit History records every data change with the user and timestamp — invaluable for "who changed this and when".

Resources

AppSheet Help Center Official documentation covering data, views, automation, and deployment. support.google.com Expression function list Every function with syntax and examples — the reference you'll keep open. support.google.com Template gallery Working sample apps you can copy and pull apart — the fastest way to learn the patterns. appsheet.com Performance tuning Official guidance on sync speed, virtual columns, and security filters. support.google.com AppSheet community Active forum where most real-world expression problems already have answers. googlecloudcommunity.com Google Sheets The data layer under most AppSheet apps — structure it right and the app follows. build.ty1er.com