The edge cases that break simple Google Apps Script automations

Noah Federovitch — Thu, 29 Oct 2026 02:32:12 +0000

Most "move a row when Status is Done" scripts you find online are about ten lines. They work in a demo and then quietly lose data in a real sheet. Here are the failures I hit while writing a few dozen of these, and the fixes.

1. Two quick edits leave a row behind

The usual pattern is onEdit(e): if the edited cell says Done, move that row. Set two rows to Done within a second and both triggers run at once. The first deletes its row, so the second one's row number now points at the wrong row.

Fix: take LockService.getDocumentLock(), then rescan the whole tab bottom-up for Done rows instead of trusting e.range.getRow(). A missed trigger gets picked up by the next edit, too.

2. `appendRow` changes your data

appendRow(values) re-parses values, so a zip code like 02134 becomes 2134 and a formula becomes its result. Use range.moveTo(destination), which keeps values, formats and formulas exactly.

3. You can't delete the last non-frozen row

deleteRow throws "not possible to delete all non-frozen rows" when the moved row is the last one under a frozen header. moveTo already emptied the source row, so catch the error and leave the empty row in place.

4. The destination tab runs out of grid

A new sheet has 1,000 rows and 26 columns. Writing to row 1,001 throws. Grow the grid with insertRowsAfter / insertColumnsAfter before you move.

5. Calendar events land on the wrong day

A date cell arrives as an instant at midnight in the spreadsheet's time zone. Pass it straight to createAllDayEvent from a script running in a time zone further west, and the event lands on the day before. Format the day in the spreadsheet's zone (Utilities.formatDate(date, ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd')), then build the event time from that string.

6. Copied rows steal each other's events

If you store the Calendar event ID in the row, a user who copies the row also copies the ID. Now two rows update one event. De-duplicate IDs top-down on every sync: the first row keeps the event, and copies get a new one.

7. The 6-minute limit eats your progress

Apps Script kills a run at 6 minutes, and a killed run skips your finally, so IDs created so far are never written back. Stop yourself at around 4 minutes, save a resume point in PropertiesService, and start the next run there. Wrap around, so the tail of a big sheet isn't starved.

8. "Hide this tab" fails on the last visible tab

Sheets won't hide the only visible tab. Compare tabs with getSheetId(), not ===: two Sheet objects for the same tab are different objects in Apps Script.

I hit these while building a set of free, MIT-licensed scripts you can paste into your own sheet: Free Google Sheets scripts. They cover mail merge, Doc merge to PDF, Sheets to Calendar, Gmail to Sheets, row cleanup and more.

If none of them fits, I also write custom ones.

DEV Community: Noah Federovitch