Delete rows in Google Sheet after 30 days

Question

I have a Google Form that populates a Google Sheet with rider registrations for a local cycling club. The sheet has a filter in place to display only the riders for the current day. This filter action was recorded as a macro and it then triggers each day. All this is working fine. What I need is a script that will purge the sheet of rows older than 30 days based on the timestamp in the first column of each row (even if hidden from human view by the filter). I could then trigger this daily.

Searching StackExchange for similar problems, I had come across this answer from 2015 - https://webapps.stackexchange.com/a/85220

function cleanup() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var values = sheet.getDataRange().getValues();
  var today = Date.now()/86400000 + 25569;
  for (var i = values.length - 1; i >= 0;  i--) {
    if (values[i][2] - today > 1) {
      sheet.deleteRow(i+1);
    }
  }  
}

This seems close to what I need but relies on having formulae on each row of the sheet to 'help' with detecting the relevant rows. As my sheet is merely a receiver of the form data and grows row by row with new form submissions, I need the function to delete expired row without reference to additional formula (unless it is possible to add a script to append a calculated value as each line of the sheet is populated.

I'd describe myself as a 'dabbler' in scripting at no more than absolute beginner level. Any help much appreciated.

Answer 1

If the data is for viewing only and does not need to be editable, the simplest solution would probably be to choose Insert > New sheet and enter this formula in cell A1 of the new sheet:

=query('Form responses 1'!A1:F, "where toDate(A) < date '" & text(today(), "yyyy-mm-dd") & "' ", 1)

Answer 2

Working with dates passed from Google Sheets to Google Apps Script is easy but doing operations with them might look to be hard, so in scripts what will be doing an a lot of date calculations might be convenient to use a JavaScript library like moment.js, but for this case using it might be overkilling.

Here is script that do what you are looking for (I didn't test it yet, so test it on a copy of your data)

function deleteOldFormSubmissions(){
  const date = new Date();
  const ageInDays = 30;
  const threshold = new Date(
                      date.getFullYear(),
                      date.getMonth(),
                      date.getDate() - ageInDays)
                    .getTime();
  const sheet = 
     SpreadsheetApp
     .getActiveSpreadsheet() // Gets the active spreadsheet
     .getSheetByName('Sheet1') // Gets the sheet. Replace Sheet1 by the name of your sheet
  const values = 
     sheet
     .getRange('A:A') // Gets the first column assuming that this column have the form submissions timestamps
     .getValues() // Gets the values (timestamps). This returns a 2D array
     .flat(); // Flattening the values (Converts the 2D array into a 1D array)
  for (var i = values.length - 1; i >= 0;  i--) {
    if (
        values[i].getTime // Checks if the value is a date object
        && values[i].getTime() <= threshold 
       )
    {
      sheet.deleteRow(i+1); // This operation is very slow but good enough when deleting few rows
    } else {
      // do nothing
    }
  } 
}

NOTES:

1. The above script will only delete the form submissions from the sheet. Linking the form to another sheet, will included all the forms submissions. (If you need to remove them from the form, post a new question).
2. If you need to delete a lot of rows, use the Sheets Advanced Service. (Post a new question if you need help with this)