I need help creating an automated solution for my Google Sheets problem.
I have a Google Form that collects data and sends it to a spreadsheet. The issue is that I have to manually go in every week and clear out all the form submissions. This is getting really tedious.
What I’m looking for is a way to write a script that can automatically clear these rows from my sheet. I want it to run once a week on Saturdays without me having to do anything.
Is there a way to set up this kind of automation? I’m not very experienced with Google Apps Script but I’m willing to learn. Any code examples or step-by-step guidance would be really helpful.
for sure! just use a time-driven trigger to have it run every week. write a function that clears all rows but leaves the header intact. remember to add the trigger in the Apps Script editor tho. lost my header once, and it sucked!
I had the same issue a few months ago. Best solution is a simple deletion function with a weekly trigger. Use deleteRows() on your sheet range - grab your sheet with SpreadsheetApp.getActiveSheet() and find your data range with getLastRow(). Delete from bottom to top so you don’t mess up the row indexes. Once it’s working, set up a time-driven trigger in the Apps Script editor. I pick weekly on Saturday. Just test it on a copy first since you can’t easily undo deletions.
Had this exact problem with my team’s weekly survey data. Here’s something the other answers missed - don’t delete the data completely. Move it to another sheet instead. I learned this the hard way when my boss wanted historical data from three months back and I’d already purged everything. Now I create an archive sheet where the script moves old submissions before clearing the main sheet. Takes maybe two extra lines of code but saves major headaches later. Use copyTo() to duplicate the data to your archive sheet first, then delete. Also, time-driven triggers can be finicky, so I always throw in some basic error handling.