Google Scripts - Deleting information from Sheets based on date

Question

I have a Sheets document that has a Master sheet that gets its information from a Google Form. I currently have a script to duplicate the Master sheet on the 1st of each month and then name that sheet as the month before.

What I want to do is then get the new sheet to remove all data from before that previous month. For example, on the 1st of November, the script will run, it will duplicate the Master slide and name the sheet Oct-18. I then want it to remove all data from before October so that it is purely a sheet of data from October.

Column A on my sheet is the timestamp inputted by Forms, it has the date and time within it.

My current script to duplicate and rename the sheet is below, the second part I have tried to add in is a part to count back 32 days and delete everything from before that day however the snag I’ve hit is that the name of the sheet changes based on the first part of the script, so I don’t know how to get the script to find the newly created and variably named sheet then delete old data from that. Where it is named "Sheet" I would need it to be named as the sheet we just created. (I would count back 32 days so that it covers all months, it's not the ideal solution as it will ultimately skew the data a little but mostly I need to work out how to pick up the variable data).

The only other way I can think to do it would be to get yesterdays date and try and get it to pick out the month then search between the 1st of that month and yesterdays date and delete everything outside of that range.

    // Duplicate & Rename
    function Duplicate() {
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var tz = ss.getSpreadsheetTimeZone();
    var now = new Date();
    var yesterday = new Date();
    yesterday.setDate(now.getDate()-1);
    var date = Utilities.formatDate(yesterday, tz, 'MMM-YY');
    ss.getSheetByName('Log')
    .copyTo(ss)
    .setName(date)
        .getRange(1,1000)
        .getValue()

    }

    // Delete Old Data
    {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Sheet1");
    var datarange = sheet.getRange(1,1000);
    var values = datarange.getValues();

    var currentDate = new Date();
    var onemonthago = new Date();
    onemonthago.setDate(currentDate.getDate() - 32);

    for (i=lastrow;i>=2;i--) {
    var tempdate = values[i-1][2];

    if(tempdate < onemonthago)  
    {
    sheet.deleteRow(i);
    }
    }
    }

Hope this makes sense.

Thanks in advance.

just one suggestion from my understanding, as you are running the script once in the month, so just process the data array of the master sheet to get the data starting from the 1st of the last month. you can do it easily looping through array and adding an if condition to only gather data if it's date column value is greater than 1st of last month. After than set the array in the sheet. this will reduce the overhead of adding whole data to new sheet and then deleting it. Also it will save a lot of running time. Hope this seems workable. — shabnam bharmal

WT1 WT1 · Accepted Answer · 2018-10-18T13:08:03

Managed to fix it with the help of one of our Computer Science students!

See below if this can help anyone else.

//Delete Old Data
var sheet = ss.getSheetByName(date);
var datarange = sheet.getRange(1,1,sheet.getLastRow());
var values = datarange.getValues();

var currentDate = new Date();
var onemonthago = new Date();
onemonthago.setDate(currentDate.getDate() - 32);

for (i=sheet.getLastRow();i>=2;i--) {

var tempdate = values[i-1];

if(new Date(tempdate) < onemonthago)  
{
  sheet.deleteRow(i);

Google Scripts - Deleting information from Sheets based on date

1 Answers