Friday, September 21, 2012

Auto delete old entries from Google Spreadsheet submitted using forms

Auto delete old entries from Google Spreadsheet submitted using forms


Following is a script that can be set (triggered) to auto deletes old entries every day from Google Spreadsheet submitted using forms:

Insert the following script in your spreadsheet project using script editor:


function DeleteOldEntries() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //give your sheet name below instead of Sheet1
  var sheet = ss.getSheetByName("Foglio1");
  
  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();


  var currentDate = new Date();
  var oneweekago = new Date();
  oneweekago.setDate(currentDate.getDate() - 7);
    
  for (i=lastrow;i>=2;i--) {
    var tempdate = sheet.getRange(i, 1).getValue();
    
    if(tempdate < oneweekago)
    {
      sheet.deleteRow(i);
    }
  }
}



The above script will delete rows (entries) that are one week old, you can change the duration in it as per your requirement.


If you are not familiar with writing scripts and don't know where to start from then have a look at the following link:
Here is post for how to work with scripting in google spreadsheets:

You have to add triggers so that it executes it daily (or weekly or as per your requirement)



Following link will make you understand how to set a trigger on an event:



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,


6 comments:

  1. Hello Kishan,
    I'd like to know, how this script can be modified to delete all entries that are older than 2 hours ?
    I tried it with using time(); but had no luck at all... Could you please help me?

    Thanks,
    tetuo

    ReplyDelete
  2. I was wondering why you were counting down in the for loop, but I see why now. Counting up from 1 has some kind of bug where it's skipping rows for deletion. Thanks for the post.

    ReplyDelete
  3. Does this delete the entire row or just the data within the row?

    ReplyDelete
  4. Can you please set the program to every seconds deletion instead of every 1 week?please

    ReplyDelete
  5. TypeError: Cannot call method "getDataRange" of null. (line 7, file "Code")

    Getting this error when I run the above code

    Help would be highly appreicated

    ReplyDelete
  6. Superb script, thanks! Note that you will need to write a script that removes the response from the form too. I have simply added the following:

    var form, urlForm = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
    if (urlForm) {
    form = FormApp.openByUrl(urlForm);
    if (form) form.deleteAllResponses();
    }
    }

    ReplyDelete