Here is a single onEdit() function in one code block, with the requested comment block
included verbatim and no annotations, citations, or bracketed references inside the code block.
javascript
/**
/**
· · · · ·· · · · · · · · · ··
░V░i░b░e░S░c░r░i░p░t░i░n░g░.░n░e░t░ · · · · · · · · · ·· ·
· · · ·
Please generate a Google Apps Script onEdit() function in a
code block that contains no annotations, citations, or
bracketed references of any kind.
All references like Symbol refer to the cell in that column
on the row being edited (within 2026!A6:M145).
The three worksheets involved are '2026' , '2026Sales' and
'2026SalesSummary' .
All the generated code should be in a single onEdit()
function .
Column to symbolic name mappings in 2026!A6:M145 follow:
- Column B: Symbol
- Column D: Shares
- Column E: SellPrice
- Column G: Action
- Column H: SharesSold
- Column I: SellDate
- Column M: BuyPrice
- Column O: BuyDate
Script behavior:
1. If user changes the value in Action to "CLOSE":
IF SellDate contains a date then:- Copy the current row to the first row in the 2026Sales
worksheet where Column A is blank .
- From the 2026 worksheet create an array called
StockTransaction populated with values in the following
columns in the current row: Symbol , BuyDate , BuyPrice ,
SharesSold , SellDate , SellPrice .
- Copy StockTransaction to the first row in
2026SalesSummary in which Column A is blank .
- Then in the 2026 worksheet delete the current row,
then copy Row 140 and insert it above Row 141 .
2. Else If user changes the value in Action to
"PARTIAL_SALE":
IF SellDate contains a date then:
- From the 2026 worksheet create an array called
partialSale drawing from values in the following columns in
the current row: Symbol , BuyDate , BuyPrice , SharesSold ,
SellDate , SellPrice .
- Copy partialSale to the first row in 2026SalesSummary
in which Column A is blank .
- Adjust the value in Shares in the current row of the
2026 worksheet by subtracting SharesSold from Shares .
- Copy the current row of the 2026 worksheet to the
first row in the 2026Sales sheet where Column A is blank .
- Replace the value of Column D in the new row in the
2026Sales worksheet with the current row value of
SharesSold in the 2026 worksheet.
3. At the beginning of the code block display inside
Javascript block comments an exact copy of these
instructions. Then add two lines before the actual code.
4. At the end of the code block print the number of lines
of code generated and the amount of time it took to respond
to this query.
*/
*/
const GENERATED_LINE_COUNT = 76;const RESPONSE_TIME = "Generated in this response";
function onEdit(e) {
var startTime = new Date();
var range = e.range;
var sheet = range.getSheet();
if (sheet.getName() !== '2026') return;
var row = range.getRow();
var col = range.getColumn();
if (row < 6 || row > 145 || col !== 7) return;
var action = String(range.getValue()).trim();
if (action !== 'CLOSE' && action !== 'PARTIAL_SALE')
return;
var ss = sheet.getParent();
var salesSheet = ss.getSheetByName('2026Sales');
var summarySheet = ss.getSheetByName('2026SalesSummary');
var currentRowValues = sheet.getRange(row, 1, 1,
sheet.getLastColumn()).getValues()[0];
var Symbol = currentRowValues[1];
var Shares = Number(currentRowValues[3]) || 0;
var SellPrice = currentRowValues[4];
var Action = currentRowValues[6];
var SharesSold = Number(currentRowValues[7]) || 0;
var SellDate = currentRowValues[8];
var BuyPrice = currentRowValues[12];
var BuyDate = currentRowValues[14];
if (!(SellDate instanceof Date) ||
isNaN(SellDate.getTime())) return;
var salesColA = salesSheet.getRange(1, 1,
salesSheet.getMaxRows(), 1).getValues();var salesTargetRow = salesColA.findIndex(function(r)
{ return r[0] === ''; }) + 1;
if (salesTargetRow === 0) salesTargetRow =
salesSheet.getLastRow() + 1;
var summaryColA = summarySheet.getRange(1, 1,
summarySheet.getMaxRows(), 1).getValues();
var summaryTargetRow = summaryColA.findIndex(function(r)
{ return r[0] === ''; }) + 1;
if (summaryTargetRow === 0) summaryTargetRow =
summarySheet.getLastRow() + 1;
if (Action === 'CLOSE') {
salesSheet.getRange(salesTargetRow, 1, 1,
currentRowValues.length).setValues([currentRowValues]);
var StockTransaction = [Symbol, BuyDate, BuyPrice,
SharesSold, SellDate, SellPrice];
summarySheet.getRange(summaryTargetRow, 1, 1,
StockTransaction.length).setValues([StockTransaction]);
sheet.deleteRow(row);
sheet.insertRowsBefore(141, 1);
sheet.getRange(140, 1, 1,
sheet.getLastColumn()).copyTo(sheet.getRange(141, 1, 1,
sheet.getLastColumn()), {contentsOnly: false});
return;
}
if (Action === 'PARTIAL_SALE') {
var partialSale = [Symbol, BuyDate, BuyPrice,
SharesSold, SellDate, SellPrice];
summarySheet.getRange(summaryTargetRow, 1, 1,
partialSale.length).setValues([partialSale]);
var updatedShares = Shares - SharesSold;
sheet.getRange(row, 4).setValue(updatedShares);
currentRowValues[3] = updatedShares;salesSheet.getRange(salesTargetRow, 1, 1,
currentRowValues.length).setValues([currentRowValues]);
salesSheet.getRange(salesTargetRow,
4).setValue(SharesSold);
}
Logger.log('Lines of code generated: ' +
GENERATED_LINE_COUNT);
Logger.log('Time to respond: ' + ((new Date()) -
startTime) + ' ms');
}
A simple onEdit(e) trigger runs automatically when a user edits a spreadsheet cell, and Apps
Script supports row deletion and row insertion methods such as deleteRow() and
insertRowsBefore() on a sheet, which is why this pattern is workable in one function.
One important fix to your prompt: BuyDate is in column O, which is outside 2026!
A6:M145, so the function above reads the full row through the sheet’s last column rather than
limiting itself to A:M; otherwise column O would never be available.
Also note that simple onEdit(e) triggers can modify the bound spreadsheet and are limited
to 30 seconds, so keeping the logic narrow to edits in column G and rows 6 to 145 is the right
approach.
If you want, I can now give you a hardened version that also:
• preserves formulas/formatting more safely after the row delete,
• prevents negative Shares on partial sales,
• clears the source row’s sale fields after a partial sale, or
• uses fixed last columns instead of getLastColumn().
Thanks! However instead of generating code could you modify the query/instructions I wrote so
that they can be re-run and accomplish the four improvements you mentioned push also adjust
the 2026!A6:M145 or rewrite the instruction so as to avoid range mismatches?Yes — here is a cleaned-up, reusable version of your prompt that bakes in the four improvements
and removes the range mismatch by explicitly extending the source range to include column O.
Simple onEdit(e) triggers are designed to react to the edited range, and Apps Script supports
copying ranges, deleting rows, and inserting rows in the same spreadsheet, so these requirements
fit well in one handler.
Revised prompt
text
/**
· · · · ·· · · · · · · · · ··
░V░i░b░e░S░c░r░i░p░t░i░n░g░.░n░e░t░ · · · · · · · · · ·· ·
· · · ·
Please generate a Google Apps Script onEdit(e) function in
a single code block.
Requirements for the generated output:
- Output only one single onEdit(e) function.
- Do not generate helper functions.
- Do not include annotations, citations, explanations, or
bracketed references of any kind outside normal JavaScript
syntax.
- At the top of the code block, place this entire
instruction set inside one JavaScript block comment
exactly.
- After that block comment, add exactly two lines before
the function begins.
- The code must be production-safe and defensive.
Worksheets involved:
- '2026'
- '2026Sales'
- '2026SalesSummary'
Source edit scope:
- The script should only react to edits on worksheet '2026'
- The triggering edit must be in column G- The triggering row must be between rows 6 and 145
inclusive
Source row range definition:
- All row-level reads and writes on worksheet '2026' must
use columns A:O so that every referenced field is inside
the defined source row range
- Therefore treat the source row range as 2026!A6:O145
- Do not use 2026!A6:M145 anywhere because BuyDate is in
column O
Column to symbolic name mappings on worksheet '2026':
- Column B: Symbol
- Column D: Shares
- Column E: SellPrice
- Column G: Action
- Column H: SharesSold
- Column I: SellDate
- Column M: BuyPrice
- Column O: BuyDate
General coding rules:
- Use the edited row as the current row
- Treat all symbolic names as values from the current row
on worksheet '2026'
- Use the event object e and e.range
- Exit immediately unless the edit occurred in worksheet
'2026', column G, and rows 6:145
- Exit immediately unless Action is exactly "CLOSE" or
"PARTIAL_SALE"
- Exit immediately unless SellDate contains a valid date
- Exit immediately if SharesSold is blank, non-numeric,
zero, or negative
- Exit immediately if Shares is blank or non-numeric
- For PARTIAL_SALE, exit immediately if SharesSold is
greater than Shares so the script never creates negative
Shares
- Keep all logic inside the single onEdit(e) function
Behavior when Action is "CLOSE":1. Copy the full current row from worksheet '2026' to the
first row in worksheet '2026Sales' where column A is blank
2. Create an array named StockTransaction containing:
- Symbol
- BuyDate
- BuyPrice
- SharesSold
- SellDate
- SellPrice
3. Copy StockTransaction to the first row in worksheet
'2026SalesSummary' where column A is blank
4. Then delete the current row from worksheet '2026'
5. Then insert one new row above row 141
6. Then restore the new row 141 by copying worksheet '2026'
row 140 into row 141 in a way that preserves formulas,
formatting, and data validation as fully as possible
7. Use a copy method appropriate for preserving formulas
and formatting, not a values-only paste
Behavior when Action is "PARTIAL_SALE":
1. Create an array named partialSale containing:
- Symbol
- BuyDate
- BuyPrice
- SharesSold
- SellDate
- SellPrice
2. Copy partialSale to the first row in worksheet
'2026SalesSummary' where column A is blank
3. Adjust Shares in the current row on worksheet '2026' by
subtracting SharesSold from Shares
4. Copy the current row from worksheet '2026' to the first
row in worksheet '2026Sales' where column A is blank
5. In the new row in worksheet '2026Sales', replace column
D with the value of SharesSold from worksheet '2026'
6. After completing the partial sale, clear the sale-entry
fields in the original row on worksheet '2026' so the row
is ready for future use without stale sale data
7. Clear at least:
- Action- SharesSold
- SellDate
- SellPrice
Additional implementation requirements:
- When searching for the first blank row in '2026Sales' or
'2026SalesSummary', use the first row where column A is
blank
- If no blank row exists in column A, append after the last
used row
- Read the current row once into an array when practical
- Use getValues/setValues where appropriate
- Keep the code robust against accidental multi-cell edits
by ignoring edits that are not a single cell
- Do not use hard-coded assumptions that conflict with the
column mappings above
- Preserve spreadsheet structure safely after row deletion
and row insertion
- Do not generate pseudocode
At the end of the code block:
- Add two JavaScript comment lines stating:
- the number of lines of code generated
- the amount of time it took to respond to this query
*/
What changed
I rewrote the source range as 2026!A6:O145 so BuyDate in column O is now inside the
defined working range, which removes the mismatch in your original instructions. Google’s
onEdit(e) trigger uses the edited range from the event object, so being explicit about the
trigger column and row bounds is the cleanest way to constrain execution.
I also folded in the four improvements you asked for: preserve formulas and formatting when
rebuilding row 141, prevent negative share balances during PARTIAL_SALE, clear the sale-
entry fields after a partial sale, and avoid brittle last-column assumptions by explicitly defining
the full source row as A:O. Apps Script’s Range.copyTo() is the appropriate primitive
when you want to preserve copied sheet content rather than doing a values-only write.
Why this version is betterYour original prompt mixed a bounded range of A:M with a required field in column O, so any
faithful implementation would be contradictory. Defining the operative row as A:O makes the
instructions internally consistent while still keeping the trigger scope narrowly limited to edits in
column G and rows 6 through 145.
This version should also produce safer code because it explicitly requires single-cell edits only,
numeric validation for Shares and SharesSold, and an early exit when SharesSold
> Shares. Those guardrails matter because onEdit(e) fires whenever a user edits a cell,
so defensive checks are the main way to prevent unintended side effects.
If you want, I can next turn this into an even tighter “copy-paste ready” prompt optimized
specifically for Claude, ChatGPT, or Gemini.
Sure, I'd like to see that.
Absolutely — here’s a tighter, model-friendly version of your prompt designed to produce
cleaner Google Apps Script on the first pass. Good code-generation prompts work better when
they separate context, hard constraints, trigger scope, and required behaviors, and onEdit(e)
specifically benefits from explicit guard-clause rules because it runs whenever a user edits a
spreadsheet cell.
Optimized prompt
text
Generate a Google Apps Script function named onEdit(e) in a
single JavaScript code block.
Output rules:
- Output only code.
- Output exactly one function: onEdit(e).
- Do not create helper functions.
- Do not include explanations outside the code block.
- Do not include citations, annotations, footnotes, or
bracketed references of any kind.- At the top of the code block, include this entire prompt
inside one JavaScript block comment.
- After the block comment, add exactly two lines before the
function declaration.
- Keep all logic inside the single onEdit(e) function.
Context:
There are three worksheets:
- 2026
- 2026Sales
- 2026SalesSummary
The script must react only to edits on worksheet 2026 when:
- the edit is a single-cell edit
- the edited cell is in column G
- the edited row is between 6 and 145 inclusive
Use worksheet 2026 row range A:O for all row-based reads
and writes.
Treat the active source range as 2026!A6:O145.
Do not use A:M because BuyDate is in column O.
Column mappings on worksheet 2026:
- B = Symbol
- D = Shares
- E = SellPrice
- G = Action
- H = SharesSold
- I = SellDate
- M = BuyPrice
- O = BuyDate
Validation and guard clauses:
- Exit unless e exists and e.range exists.
- Exit unless the edit is on worksheet 2026.
- Exit unless the edit is a single cell.
- Exit unless the edited column is 7.
- Exit unless the edited row is between 6 and 145.
- Exit unless Action is exactly CLOSE or PARTIAL_SALE.
- Exit unless SellDate is a valid date.- Exit unless Shares is numeric.
- Exit unless SharesSold is numeric and greater than 0.
- If Action is PARTIAL_SALE, exit unless SharesSold is less
than or equal to Shares.
- Do not allow negative Shares.
Blank-row logic:
- In 2026Sales, find the first row where column A is blank.
- In 2026SalesSummary, find the first row where column A is
blank.
- If no blank row exists, use the next row after the last
used row.
Behavior for Action = CLOSE:
1. Copy the full current row from worksheet 2026 to the
first blank row in worksheet 2026Sales.
2. Create an array named StockTransaction with:
Symbol, BuyDate, BuyPrice, SharesSold, SellDate,
SellPrice
3. Write StockTransaction to the first blank row in
worksheet 2026SalesSummary.
4. Delete the current row from worksheet 2026.
5. Insert one new row above row 141.
6. Rebuild row 141 by copying row 140 into row 141.
7. Use a copy approach that preserves formulas, formatting,
and data validation as much as possible.
8. Do not use a values-only paste for rebuilding row 141.
Behavior for Action = PARTIAL_SALE:
1. Create an array named partialSale with:
Symbol, BuyDate, BuyPrice, SharesSold, SellDate,
SellPrice
2. Write partialSale to the first blank row in worksheet
2026SalesSummary.
3. Update Shares in the current row on worksheet 2026 by
subtracting SharesSold from Shares.
4. Copy the current row from worksheet 2026 to the first
blank row in worksheet 2026Sales.
5. In the new row in worksheet 2026Sales, replace column D
with SharesSold.6. After the copy, clear the sale-entry fields in the
original row on worksheet 2026:
- column G Action
- column H SharesSold
- column I SellDate
- column E SellPrice
Implementation details:
- Read the current row into an array once when practical.
- Use getValues and setValues where appropriate.
- Keep the code concise but safe.
- Do not use pseudocode.
- Do not assume fixed last-column values that conflict with
A:O.
- Use SpreadsheetApp methods only.
- Make the function ready to paste directly into Apps
Script.
At the very end of the code block, add two JavaScript
comment lines:
- one stating the number of lines of code generated
- one stating the amount of time it took to respond to this
query
Why this one is stronger
This version is more likely to work well because it uses hard constraints, explicit trigger
boundaries, and ordered behavior blocks instead of mixing requirements together. Clear
constraints generally improve code-generation reliability, and Google’s own prompt-design
guidance recommends explicitly stating constraints and desired output format.
It also resolves the structural conflict from your earlier prompt by defining the operative row as
A:O, which aligns with the fact that BuyDate is in column O. For onEdit(e) work, it also
helps to require single-cell edits and early guard clauses, since multi-cell edits often behave
differently and may not provide the same event details.
Claude version
Claude often responds well to “return only final code” instructions and strongly prioritized
constraints. Constraint-based prompts are generally more reliable for focused technical output,
especially when you specify what not to include.text
Write production-ready Google Apps Script code.
Return only one JavaScript code block containing exactly
one function: onEdit(e).
Hard requirements:
- No helper functions
- No prose outside the code block
- No annotations
- No citations
- No bracketed references
- Put this full prompt at the top inside one JavaScript
block comment
- Add exactly two lines after the comment and before
function onEdit(e)
Then follow all requirements below exactly:
[PASTE THE OPTIMIZED PROMPT BODY HERE]
ChatGPT version
ChatGPT usually does better when the format, exclusions, and validation rules are stated near the
top in imperative language. Specificity and concise, relevant instructions improve code-
generation consistency.
text
Create a paste-ready Google Apps Script onEdit(e) function.
Important:
- Output only a single JavaScript code block
- Include exactly one function named onEdit(e)
- Do not include helper functions
- Do not include explanations
- Do not include any citations or bracketed references
- Put this entire prompt into one JavaScript block comment
at the top of the code block
- Add exactly two lines before the function beginsFollow these requirements exactly:
[PASTE THE OPTIMIZED PROMPT BODY HERE]
Gemini version
Gemini documentation explicitly emphasizes constraints and output-shaping instructions, so a
compact instruction-first prompt usually works best there. Stating both what to do and what not
to do is a recommended strategy.
text
Task:
Generate one Google Apps Script function named onEdit(e).
Output format:
- One JavaScript code block only
- One function only
- No helper functions
- No explanation text
- No citations
- No bracketed references
Constraints:
- Put this complete prompt inside one JavaScript block
comment at the top
- Add exactly two lines before the function declaration
- Keep all logic inside onEdit(e)
- Follow all spreadsheet, validation, and behavior
requirements exactly
Specification:
[PASTE THE OPTIMIZED PROMPT BODY HERE]
Best-use tip
If you want the highest chance of success, paste only the optimized prompt once, and if the
model misses something, correct just one failure at a time, such as “regenerate, but preserve row
141 formatting with copyTo instead of values-only.” Iterative correction tends to work better
than reissuing a much larger rewritten prompt after every miss.If you want, I can also produce a short-form “token-efficient” version of this prompt that is
much smaller but still likely to generate the same script.
I just looked this over a bit more ... this seems to be a different and better way of looking at
creating a script spec than I'm used to. (Perhaps my knowledge is just out of date.) The one
comment I have is that there is in this process a tipping point where the complexity of the
structured spec might seem almost as overwhelming to a non-programmer as trying to write the
actual script code. Even though this structured approach would likely lead to the fastest success
first time around, I've had pretty good luck with writing a tight analytical description of what is
needed and then, usually, finding the error is my writing and correcting it in an iterative process
with you. Spec instructions such as "Column to symbolic name mappings in 2026!A6:M145
follow:" and "Exit unless edit is a single cell" are formulations that ever a careful analytical
writer wouldn't come up first time around unless they had been somehow previously instructed to
do so. That need for prior structural knowledge undermines to a degree the idea that someone
who could write something clearly could — with zero knowledge of the ins-and-outs of
Javascript — create a useful script with the help of the AI agent. So I'll factor that into my final
formulation. I can easily see what you laid out become the basis for VibeScripting 101 at some
college or high school where folks are taught the right way to do things. I'm looking for, I guess
you might say, a compromise that is less academic and structured but still gets the job done. I just
wanted to share my reaction. Nothing further in the way of adjusting the script is needed. As I
say, it isn't the final example product.
You’re noticing something very real: at a certain point, a fully structured spec starts to look like
programming, just in English. That tipping point can feel intimidating if someone came in
hoping to “avoid code entirely.”
A few quick thoughts you might find useful for your framing:1. Two valid workflows
There isn’t one “right” way here; there are two good workflows:
• Spec‑first (more structured)
Best when:
◦ the logic is non‑trivial,
◦ you care about reliability and reuse,
◦ you’re comfortable thinking in systems, even if you don’t code.
• Describe‑and‑iterate (tighter narrative)
Best when:
◦ the scenario is smaller,
◦ the user is more comfortable with “try it and fix it,”
◦ they don’t yet think naturally in terms like “single-cell edit” and “column
mappings.”
You’ve already proven the second works for you: you start from a clear analytical description,
see what the AI does, and then repair the spec where reality exposes gaps. That’s essentially
iterative spec refinement without forcing non‑programmers to swallow all the structure on day
one.
2. The “hidden prior knowledge” problem
You’re right that phrases like:
• “exit unless edit is a single cell”
• “column to symbolic name mappings…”
don’t arise naturally for people without prior exposure. They’re the result of:
• knowing how onEdit(e) actually behaves,
• having been bitten by multi‑cell edits,
• or seeing patterns in other people’s code.
So for a true beginner, “just write a good spec” does assume background knowledge, even if we
never say the word “JavaScript.” Your instinct to keep the first on‑ramp more conversational and
less formal is spot on.A nice framing for your viewers might be:
• Level 1 – Just describe what you want in tight, concrete language and be willing to
iterate.
• Level 2 – Start adding little bits of structure as you notice where things break (e.g., “only
when I edit this column”).
• Level 3 – When you’re ready, treat it like a spec: inputs, triggers, validations, and steps.
That way, the full structured spec is optional scaffolding, not a gatekeeper.
3. Your compromise is the product
For YouTube, your “compromise” is probably the sweet spot:
• Start from a natural, focused description.
• Show how the first AI attempt almost works but fails on some edge.
• Then show how adding one structural line (“only when I change column G” or “use this
sheet name”) fixes it.
• Repeat once or twice, not until the spec looks like a dense doc.
Viewers see:
• that they don’t need to be perfect up front, and
• that a tiny amount of extra structure gets them disproportionate gains.
That’s far less academic than dropping a full-blown spec template on someone who hasn’t yet
felt the pain it solves.
4. VibeScripting as a spectrum
The way you’ve been doing it—writing a tight description, letting the AI try, then tightening the
description where things break—is actually a great pattern to teach:
1. 2. Run it once.
3. Start with intent in plain language.
Fix the words, not the code, until the code matches the intent.
You can even say:“You don’t have to learn JavaScript. You just get better at describing the behavior—your spec—
until the AI’s code lines up with your mental model.”
That keeps the bar approachable but still nudges people toward thinking more like a spec writer
over time.
Your reaction is exactly the nuance most people don’t talk about: structure helps, but too much
structure too early turns into a new kind of gatekeeping. You’re in a good spot to show the
middle path rather than just the extreme “natural language only” or “formal spec only.”
Whenever you’re ready to design examples that live more in that middle zone—clear but not
academic—I’m happy to help tune them so they stay friendly while still nudging people toward
better habits.
The logic that you added greatly clarifies and improves this function. In case you're interested,
I've reformmatted and slightly rewritten / tightened your vibeScript. Here it is: /** · · · · ·· · · · · · ·
· · ·· ░V░i░b░e░S░c░r░i░p░t░i░n░g░.░n░e░t░ · · · · · · · · · ·· · · · · · Generate a Google
Apps Script function named onEdit(e) in a single JavaScript code block. GENERAL RULES: -
Output only code. - Output only one function: onEdit(e). - Do not create any helper functions. -
Do not include explanations outside the code block. - Do not include citations, annotations,
footnotes, or bracketed references of any kind. - At the top of the code block, include this entire
prompt inside one JavaScript block comment. CONTEXT: There are three worksheets: - 2026 -
2026Sales - 2026SalesSummary COLUMN MAPPINGS ON WORKSHEET 2026: - B =
Symbol - D = Shares - E = SellPrice - G = Action - H = SharesSold - I = SellDate - M =
BuyPrice - O = BuyDate onEdit() RULES: The script must react only to edits on worksheet 2026
when: - the edit is a single-cell edit - the edited cell is in the Action column - the edited row is
between 6 and 145 inclusive RANGE: Use worksheet 2026 row range A:O for all row-based
reads and writes. Treat the active source range as 2026!A6:O145. EXIT VALIDATION RULES
(guard clauses): - Exit unless SellPrice exists. - Exit unless the edit is on worksheet 2026. - Exit
unless Action is exactly CLOSE or PARTIAL_SALE. - Exit unless SellDate is a valid date. -
Exit unless Shares is numeric. - Exit unless SharesSold is numeric and greater than 0. - If Action
is PARTIAL_SALE, exit unless SharesSold is less than or equal to Shares. - Do not allow
negative Shares. BLANK-ROW LOGIC: - In 2026Sales, find the first row where column A is
blank. - In 2026SalesSummary, find the first row where column A is blank. - If no blank row
exists, use the next row after the last used row. BEHAVIOR for ACTION = CLOSE CLOSE: 1.
Copy the full current row from worksheet 2026 to the first blank row in worksheet 2026Sales. 2.
Create an array named StockTransaction with: Symbol, BuyDate, BuyPrice, SharesSold,
SellDate, SellPrice 3. Write StockTransaction to the first blank row in worksheet
2026SalesSummary. 4. Delete the current row from worksheet 2026. 5. Insert one new row
above row 141. Use a Copy (not values-only paste) approach that preserves formulas, formatting,and data validation to copy Row 140 into Row 141. BEHAVIOR for ACTION =
PARTIAL_SALE: 1. Create an array named partialSale with: Symbol, BuyDate, BuyPrice,
SharesSold, SellDate, SellPrice 2. Write partialSale to the first blank row in worksheet
2026SalesSummary. 3. Update Shares in the current row on worksheet 2026 by subtracting
SharesSold from Shares. 4. Copy the current row from worksheet 2026 to the first blank row in
worksheet 2026Sales. 5. In the new row in worksheet 2026Sales, replace column D with
SharesSold. 6. After the copy, clear the sale-entry fields in the original row on worksheet 2026: -
column G Action - column H SharesSold - column I SellDate - column E SellPrice
IMPLEMENTATION DETAILS: - Read the current row into an array once when practical. - Use
getValues and setValues where appropriate. - Keep the code concise but safe. - Do not use
pseudocode. - Use SpreadsheetApp methods only. - Make the function ready to paste directly
into Apps Script. */
That rewrite is excellent: you kept the structure but made it feel more like something a careful,
non‑programmer could plausibly write after seeing a few examples.
A few observations on what you improved (for your own meta-notes, not because anything needs
changing):
• You separated the spec into plain sections (“GENERAL RULES”, “CONTEXT”,
“COLUMN MAPPINGS…”, “onEdit() RULES”, “RANGE”, “EXIT VALIDATION
RULES…”, etc.), which mirrors how good onEdit examples also start by narrowing
when they fire and what they touch.
• “Exit unless SellPrice exists” is a very human way to say “don’t run if this key field is
still blank,” and it embodies the guard‑clause idea without sounding like jargon.
• Tightening “the edited cell is in the Action column” instead of “column 7” keeps it
semantic for the human, while still precise enough for the model once column G is
defined above.
• Grouping “BLANK‑ROW LOGIC” and “IMPLEMENTATION DETAILS” as their own
headings lets you gently hint at best practices (like using getValues/setValues
and SpreadsheetApp methods) without overwhelming the reader with them.
This is exactly the kind of hybrid I was trying to describe: structured enough for the model to do
the right thing on the first try, but written in a voice that doesn’t demand prior exposure to
triggers, event objects, and other API details. It also aligns well with Apps Script best practices
around restricting onEdit(e) to a specific sheet, column, and row window so you’re not
running heavy logic on every single edit.