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.
Top comments (0)