CODEWITHSUNDEEP
google-apps-scriptgoogle-formsgoogle-workspace
Luke Mason
Luke Mason

Reputation: 1

Google Form - Delete rows/records older than 3 years

I have a google form that records sales of certain items to customers. I need to automatically delete rows that are older than 3 years. This is for GDPR data retention reasons.

I though I could have a trigger than runs nightly to run the script but I am unsure how to code this.

The timestamp currently sits in row A.

I am assuming that deleting the data from the spreadsheet will also delete the record of it on the form.

Upvotes: 0

Views: 135

Answers (1)

DilZhan Yapa
DilZhan Yapa

Reputation: 11

To achieve this task, you can use Google Apps Script to create a time-driven trigger that runs nightly and deletes rows older than 3 years from your Google Sheet. Here's an example script to get you started:

  1. Open your Google Sheet.

  2. Click on "Extensions" > "Apps Script."

  3. Replace any existing code in the script editor with the following code:

     function deleteOldRows() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var currentDate = new Date();
    var threeYearsAgo = new Date(currentDate);
    threeYearsAgo.setFullYear(currentDate.getFullYear() - 3);

    var data = sheet.getDataRange().getValues();
    var rowsToDelete = [];

    for (var i = 1; i < data.length; i++) { // Assuming the timestamp is in column A, starting from the second row
       var timestamp = new Date(data[i][0]);
       if (timestamp < threeYearsAgo) {
       rowsToDelete.push(i + 1); // Adding 1 to convert to sheet index (1-based)
       }
    }

 // Delete rows in reverse order to avoid shifting issues
    for (var j = rowsToDelete.length - 1; j >= 0; j--) {
       sheet.deleteRow(rowsToDelete[j]);
    }
}

// Trigger to run the deleteOldRows function nightly
function createTrigger() {
   // Adjust the time zone to your preference
   ScriptApp.newTrigger('deleteOldRows')
     .timeBased()
     .everyDays(1) // Run every day
     .create();
 }

  4. Save the script (File > Save).

  5. You can run the createTrigger function manually the first time to set up the trigger. After running it once, you can close the script editor.

Now, the deleteOldRows function will run every night and delete rows older than 3 years from your sheet. Make sure to test the script on a copy of your data first to avoid accidental data loss.

Remember that deleting rows from the sheet will also delete the corresponding records on the form, as they are linked to the rows in the sheet.

Upvotes: 1

Related Questions