2

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.

1

2 Answers 2

0

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)

0

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)
5
  • ... just trying this out (thank you) and have a couple of errors when run. Am I right in thinking that the 3rd line down should end with a semi-colon rather than a colon (got unexpected token error) and, secondly, I get ReferenceError: newDate is not defined. Jun 22, 2021 at 6:46
  • Those errors were caused by typos... They're fixed
    – Rubén
    Jun 22, 2021 at 9:48
  • Hmmm I wonder what is odd about my sheet as I'm still getting an error. 12:28:27 PM Notice Execution started 12:28:27 PM Error ReferenceError: date is not defined deleteOldFormSubmissions @ Code.gs:5 Is it possible that the fact that the timestamp is in UK format is a problem? 19/06/2021 16:58:10 Jun 22, 2021 at 11:30
  • I'm so sorry. The referred error ocurra b/c a script error. I changed it but I" still haven't tested, sorry.
    – Rubén
    Jun 22, 2021 at 12:12
  • No worries Rubén.. I appreciate your help. Absolutely no rush. Jun 22, 2021 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.