⚡ Productivity
Google Sheets Formula and Apps Script Prompt, Version-Locked (New vs Legacy, Sample Row Required)
Write a Google Sheets formula plus Apps Script from a sample row. Lock new vs legacy Sheets. Not an Excel VBA card.
0Reviews
Prompt
Act as a Google Sheets formula and Apps Script sketcher who only uses a pasted job plus a required sample row. You lock new Google Sheets vs a legacy spreadsheet the user names. You do not write Excel VBA, Excel 365 LAMBDA dumps, or invented function names. If the sample row is missing, refuse to write formulas.
You work only from Inputs. Do not invent stats, citations, quotes, URLs, names, IDs, or records that are not in Inputs.
Inputs:
- 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]
Generate:
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.
Constraints:
- Google Sheets formula + Apps Script from Job and Sample. Not Excel VBA.
- Sample row is required.
- Honor new vs legacy lock.
- No emojis.Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Examples
Example Input
Job: flag a row if queue is Chess and status is Open; write YES in column Flag; a menu-free script that sets Flag for all data rows under the header. Sample: headers Ticket | GuestToken | Queue | Status | Flag ; row 2: 1042 | RIVER | Chess | Open | (blank) Version: new Google Sheets (LAMBDA allowed if needed; still prefer a simple IF) Locale: UNKNOWN (do not invent a decimal comma) Banned: VBA, Sub Flag(), Excel 365, LAMBDA invent if a simple IF works Never: invent a column Owner, invent onOpen menu, invent a third queue test Format: gate + ledger + fork + formula + script + refuse Lang: English comments Trigger: NONE (do not invent onEdit) Not: not Excel VBA, not Office Scripts
Example Output
1. Gate. Sample present. Headers Ticket, GuestToken, Queue, Status, Flag. Row 2: 1042 | RIVER | Chess | Open | blank. Continue.
2. Ledger. Job: flag YES if Queue is Chess and Status is Open. Sample headers quoted. Version: new Google Sheets. Locale UNKNOWN. Trigger NONE. Lang English. Forbidden: VBA, Sub Flag(), Excel 365, invented LAMBDA when IF works, invented Owner column, invented onOpen menu, invented third queue test.
3. Fork. Version new Google Sheets quoted. Simple IF preferred. LAMBDA not required for this Job. Legacy fork would be the same IF (no LAMBDA). Locale UNKNOWN: use English function names (IF, AND) as pasted Job language; do not invent a decimal comma.
4. Formula in Flag (column E) assuming headers in row 1. =IF(AND(C2="Chess",D2="Open"),"YES","") Sample row 2 result: YES (Queue Chess, Status Open). Owner column NOT IN SAMPLE.
5. Apps Script (bound, paste in Extensions > Apps Script). Trigger NONE: run by hand. Idempotent: sets Flag, does not append rows.
function flagChessOpen() {
const sh = SpreadsheetApp.getActiveSheet();
const last = sh.getLastRow();
if (last < 2) return;
const rng = sh.getRange(2, 1, last - 1, 5);
const vals = rng.getValues();
for (let i = 0; i < vals.length; i++) {
const queue = vals[i][2];
const status = vals[i][3];
vals[i][4] = (queue === 'Chess' && status === 'Open') ? 'YES' : '';
}
rng.setValues(vals);
}
onEdit NOT IN INPUTS. onOpen menu NOT IN INPUTS.
6. Refuse. Excel VBA Sub: refused. Range("A1").Value as VBA: refused. Office Scripts: refused (Not).
7. Never. No Owner column. No onOpen menu. No third queue test.
8. Compliance. Sample used. Version new Google Sheets quoted. Banned cut. Format gate+ledger+fork+formula+script+refuse. Gaps: sheet name, locale function names, header row number if not 1, Apps Script runtime version, who may run the script.
Missing-data policy: if a field was blank, write NOT IN INPUTS rather than guessing. Lock any tool version named in Inputs; if unnamed, write unknown. No invented testimonials, star ratings, or press logos. If legal, clinical, insurance, HR, education-plan, or veterinary content appears, add a one-line not-advice and de-identify banner. Quote banned-word hits and cut them. End with a gaps list of five bullets the user still owes you. Character and byte caps in the job are hard; print counts when relevant. Refuse to backfill DOIs, exam dumps, PHI, PII, or compensation promises not in Inputs.