Turning a Spreadsheet Chore into Apps Script: The First Request a Non-Engineer Can Hand to Antigravity

Friday evenings, moving the same rows in the same order

I would find every row whose Status column said Shipped, copy it to another sheet, paste it, and delete the original. Every Friday, around dusk.

It took under ten minutes. Yet just before deleting, my hand stopped every single time. Did I paste into the right sheet? Was my selection off by one row? That small worry made ten minutes feel twice as long.

When I finally decided to hand this to Apps Script, my first request to Antigravity was simply "automate this."

My first request was far too vague

The code that came back worked. It also deleted the moved rows outright, with no way to keep the originals and no way to try it safely.

That was my doing. I hadn't said which columns mattered, what to do on failure, or what counted as "done." The problem was the request, not the code.

Anything that deletes gets a dry run and a safe order first. For a first automation, I think that single rule is enough to carry.

Four things to put in the request

Writing these four points makes the returned code noticeably safer.

A request along these lines works well:

Write one Apps Script for Google Sheets.
- In the "Orders" sheet, row 1 is the header: Date, Item, Qty, Price, Status
- Append every row whose Status is "Shipped" to the end of the "Done" sheet
- Then delete only those rows from "Orders"
- Put a DRY_RUN constant at the top. While it is true, change nothing
  and just log how many rows would move
- If a sheet or header is missing, throw an error that says what is missing

Adding "write comments in plain language" helps when you reread it later.

Reading what came back

This is the version I settled on. It looks long, but it only does three things: find, move, delete.

// While true, nothing is written; the script only logs how many rows would move
const DRY_RUN = true;
 
function archiveShipped() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const src = ss.getSheetByName('Orders');
  const dst = ss.getSheetByName('Done');
  if (!src || !dst) {
    throw new Error('Sheet not found. Check the names "Orders" and "Done"');
  }
 
  const values = src.getDataRange().getValues();
  const statusCol = values[0].indexOf('Status');
  if (statusCol === -1) {
    throw new Error('No "Status" header in row 1');
  }
 
  // Skip the header row and collect only the Shipped rows
  const moving = values.slice(1).filter(row => row[statusCol] === 'Shipped');
  Logger.log('Would move: ' + moving.length + ' rows (DRY_RUN=' + DRY_RUN + ')');
  if (moving.length === 0 || DRY_RUN) return;
 
  // Append first; delete only after that succeeds
  dst.getRange(dst.getLastRow() + 1, 1, moving.length, values[0].length)
     .setValues(moving);
 
  // Delete from the bottom so row numbers don't shift under us
  for (let i = values.length - 1; i >= 1; i--) {
    if (values[i][statusCol] === 'Shipped') src.deleteRow(i + 1);
  }
}

Three things to look for:

• DRY_RUN ends the function before anything is written
• The append (setValues) comes before the delete (deleteRow)
• Deletion runs bottom-up, because deleting from the top shifts the next row up and you skip it

You don't have to understand every line. If one of the three is missing, ask Antigravity to add a DRY_RUN switch and bottom-up deletion, and it will.

Paste, rehearse, then go live

Short steps:

1. Open the spreadsheet, then choose Extensions and Apps Script.
2. Clear the contents of Code.gs, paste the code, and save.
3. Pick archiveShipped in the function dropdown and press Run.
4. The first run shows an authorization prompt. Follow it and allow access with your own account.
5. If the execution log says "Would move: N rows," the rehearsal worked and the sheet is untouched.

Check that N matches what you expect, set DRY_RUN to false, and run it once more.

Before going live, I make a copy of the whole spreadsheet. It costs a minute, and the relief is out of proportion to it.

Running it weekly with a trigger

Click the clock icon (Triggers) in the editor's left menu, then Add Trigger.

Choose archiveShipped as the function, Time-driven as the event source, Week timer as the type, and pick a day and hour.

I chose early Saturday morning instead of Friday evening. A script that fires while someone is mid-edit can catch a row halfway through being updated.

Telling the usual snags apart

If the count stays at 0, trim values before comparing: String(row[statusCol]).trim() === 'Shipped' absorbs differences you can't see by eye. Asking Antigravity to "ignore surrounding whitespace when comparing" gets you the same thing.

What to do next

Pick the one chore that makes your hand stop most often, and write the request with those four points, in a form that never deletes without a rehearsal.

Once it works, let next week's version of you hand it over. I spend those ten minutes back on the work outside the spreadsheet.