Background info: My team uses a Google Sheets document to manage our restaurant reservations and events. We have one main sheet called “Current” where we track all upcoming bookings with their details, and another sheet called “Past” for storing old records.
What I need: I want to build an automated script that will look at column D (which contains dates) and find any entries from yesterday or older. For those matching rows, the script should copy the entire row to our “Past” sheet and then remove it from the “Current” sheet.
My experience level: I’m pretty new to Google Apps Script and don’t have much coding background. I’ve seen some solutions online but they were mostly for Excel with VBA code which I can’t really understand.
Any guidance or examples would be really appreciated!
I’ve done something similar for project deadlines. The trick is using getLastRow() to loop through your data correctly. Create a function that checks each date in column D against yesterday’s date (new Date() minus one day). When you find matches, grab the whole row with getRange() and getValues(), then stick it on your Past sheet using appendRow(). Here’s the gotcha - when deleting from Current sheet, loop backwards from the bottom. Otherwise you’ll mess up the row indexes as you delete. Set up a daily trigger in Apps Script editor to run this automatically. Pro tip: test on a copy first since you can’t undo deletions easily.
I created a simple function that grabs all reservation data with getDataRange(), then filters through each row. Compare your date column to new Date().setHours(0,0,0,0) - 86400000 - that’s yesterday at midnight. Found old entries? Use copyTo() to move the whole row to your Past sheet, then collect the row numbers for deletion. Delete them backwards with deleteRow() so you don’t mess up the indexing. Trust me, I deleted the wrong rows when I first tried this. Set up automation through Triggers in Apps Script - run it daily during off-hours when no one’s editing. Throw in some try-catch blocks too since network hiccups can kill the process halfway through.
hey josephk! for your needs, you can set up a function in Apps Script that’ll check col D for dates. if they’re from yesterday or before, it copies those rows to ‘Past’ and deletes them from ‘Current’. set a daily trigger and it’s all automated. good luck!