How to Write Google Sheets Formulas and Apps Script Version-Locked

Sheets prompts invent VBA and an Owner column. Sample row is required. If it is missing, stop. Prefer IF over LAMBDA when IF works.

The matching generator is the Google Sheets Formula and Apps Script Prompt, Version-Locked (New vs Legacy, Sample Row Required) (Google Sheets Formula And Apps Script Version Locked New Vs Legacy Sample Row) prompt. Browse related cards in the PromptDig library (Browse more prompts). When a filled run survives, share the version you actually use (Share a prompt).

Refuse to write formulas without a sample row

Write a Google Sheets formula plus Apps Script from a sample row. Lock new vs legacy Sheets. Not an Excel VBA card. Start by filling Inputs, not by asking the model to remember last week's run. If a field is blank, write NONE or NOT IN INPUTS and leave it blank through Generate. The card is built so the model cannot honestly invent a number, owner, URL, or command that you did not paste.

Paste these fields before you hit run:

Job (what the formula and script should do): [Job]
Sample row (headers + one data row): [Sample]
Sheets version I lock (new / legacy / UNKNOWN): [Version]
Locale I allow (or UNKNOWN): [Locale]
Words I must not use: [Banned]
What I must never invent: [Never]
Output format: [Format]
Language for comments: [Lang]
Script trigger I allow (or NONE): [Trigger]
What this is not: [Not]

That inventory is the honesty ledger. Anything that does not appear there is forbidden in the draft. If you catch yourself adding a nice-to-have after the run, you are no longer using the card. You are ghostwriting. Put the extra fact in Inputs and run again.

Lock new Sheets vs legacy constructs

Generate is numbered on purpose. Do not skip a step because the first paragraph looked done. The early steps exist to stop later prose from smuggling claims.

Walk the Generate list in order:

1. Gate: if Sample is blank or NONE, stop. Write SAMPLE ROW REQUIRED and do not output a formula.
2. Honesty ledger: Job, Sample headers, Version, Locale, Trigger, Lang. Forbidden: Excel VBA, invented Sheets functions, extra columns.
3. Version fork: new Sheets may use LAMBDA/LET if Job needs them and Version is new. Legacy: no LAMBDA; use helper columns named from Sample only. If UNKNOWN, write VERSION UNKNOWN and pick the older construct.
4. Formula: one cell formula using only Sample headers. Print the result on the sample row.
5. Apps Script: bound script. Uses SpreadsheetApp. Trigger only if Trigger is not NONE. Idempotent: running twice does not duplicate rows.
6. Refuse: Excel VBA Sub, Range("A1").Value as VBA, Office Scripts if not named.
7. Never: invent a column not in Sample. Invent a custom menu if not in Job.
8. Compliance pass: quote Banned and Never hits. Print Version. Format as Format.

If a step asks for a version lock, quote the version from Inputs in the output. If a step asks for a refuse list, keep the refuse list in the published artifact, not in a sidebar you delete. Reviewers should see what the model was not allowed to do.

Print the sample-row result

Most failures are the same shape: a missing field gets a confident fill. A conversion rate appears. A Gradle task appears. A flash point appears. A caption appears on a job that asked for slide text only. Your review is to search the draft for numbers, names, and commands, then grep Inputs. No match means cut.

Honor the constraints as hard stops, not vibes:

• Google Sheets formula + Apps Script from Job and Sample. Not Excel VBA.
• Sample row is required.
• Honor new vs legacy lock.
• No emojis.

When the card says not legal advice, not certification, not an exam dump, or not a caption engine, that sentence belongs at the top of the output. Deleting it to look more finished is how you inherit risk.

Refuse Excel VBA and invented columns

Finish with the compliance pass the prompt already asks for. Quote the banned-word hits. Cut them. Print character counts when the job has a cap. Print word counts when the job has a budget. List gaps as gaps. Five missing facts are more useful than one smooth paragraph.

Tags on the card (google sheets formula apps script, sheets new vs legacy lock, sample row required not excel vba) are a reminder of the job shape, not an invitation to wander into a neighboring cluster. If you need a different surface, open a different PromptDig card rather than stretching this one.

Fill the card, then run

Replace every bracket. Run on ChatGPT, Claude, or Gemini. Read the ledger first, then the artifact. If the model invents a commit, KPI, DOI, PEL, bid, or logo, discard the run. Tighten Inputs. Run again. Share the filled card that survived, not the first draft that sounded done.