0

I'm currently trying to make a script or literally anything that will be able to delete a row after the given date in Column C.

The site is a giveaway site so I need the rows/entries to delete themselves once the date specified on Column C is passed.

Eg: If one giveaway had an expiration date @ 20/13/2016, once the date reaches this date of 20/13/2016 it will delete the row. I am following the metric system of dd/mm/yy as a note.

I saw a question similar to this at Google Sheets - Script to delete date expired rows but the code won't work for my needs.

Here is the code that was used in the other question.

 var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Foglio1");
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var currentDate = new Date();
var oneweekago = new Date();
oneweekago.setDate(currentDate.getDate() - 7);

for (i=lastrow;i>=2;i--) {
var tempdate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]

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

If you could change it to work for my above needs it will be greatly appreciated!

Community
  • 1
  • 1
  • looks like removing oneweekago.setDate(currentDate.getDate() - 7); could do the trick. Did you try that? – Jeremy Kahan Dec 13 '16 at 05:55
  • no i havent, would that be it? I want it to delete past the specified date within the row –  Dec 13 '16 at 06:00
  • tempdate is getting the date in the row. If that is less than today, we would delete. truth is to use a variable named oneweekago to stand for today because we we are not taking 7 days off is very unreadable. better to say if (tempdate – Jeremy Kahan Dec 13 '16 at 06:04
  • alright will do! That should do it right? Also how do I specify the column? –  Dec 13 '16 at 06:10
  • I think so, but run it on something you are testing first, that you do not mind deleting if I messed up. The column is the 2 in var tempdate = values[i-1][2]; which is column C since A=0 B=1 C=2. If you want to change it, that is what to change. – Jeremy Kahan Dec 13 '16 at 06:15
  • here is a link to help you see what I'm working with. Will the code still work with this? https://s28.postimg.org/jsu4tzl31/snip1.png –  Dec 13 '16 at 06:18
  • Oh, and if it is a delete on date, probably say if (tempdate <= currentDate). – Jeremy Kahan Dec 13 '16 at 06:19
  • you need to add some logic to handle those n/a dates. – Jeremy Kahan Dec 13 '16 at 06:22
  • Hmmm, well I'm not that good with this sorta code. Mind doing it for me? I'll pay $5 AUD for it to be done. PayPal or whatever you want really. I just want it to work w/ the logic for N/A and for the rows to delete depending on their specified date. Thanks! –  Dec 13 '16 at 06:27
  • got to sleep now, but thanks. I think it would look like if (IsDate(tempdate) and (tempdate<=currentDate)) – Jeremy Kahan Dec 13 '16 at 06:30
  • alright well I'm actually not in a rush lol. Are you willing to do the job in your spare time, up to you really, I'll still pay $5 no doubt 'bout that, just means my coffee budget will be a little more stricter lol. Anywhere we can talk? –  Dec 13 '16 at 06:33
  • I am going to answer this. If it works, just give me a check mark and donate. If not, just give a kind comment, and I will take it down. – Jeremy Kahan Dec 13 '16 at 06:40
  • would you like me to provide the Spreadsheet file? –  Dec 13 '16 at 06:42

1 Answers1

2

Assuming your dates are in column C as stated, this should do it. The adjustment is just to the date to which we compare and to handle missing dates. I am also messing with the case on some names for readability.

function DeleteOldEntries() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Live Events");//assumes Live Events is the name of the sheet
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var currentDate = new Date();//today

for (i=lastrow;i>=3;i--) {
var tempDate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
if ((tempDate!=NaN) && (tempDate <= currentDate))
{
  sheet.deleteRow(i);
}//closes if
}//closes for loop
}//closes function
Jeremy Kahan
  • 3,796
  • 1
  • 10
  • 23
  • Thanks I will try it now. Will report back if it works, if so then I'll donate $5 immediately. –  Dec 13 '16 at 06:57
  • Ok. I just changed it to delete only back to row 3, since 0,1, and 2 are headers in yours. – Jeremy Kahan Dec 13 '16 at 06:59
  • so will this code work for any dates I add to Column C, such as if I post another giveaway and fill in the information. because I will have multiple listings of giveaways and I can't keep going back and forth between the code. –  Dec 13 '16 at 07:06
  • um, I mean you have to run it periodically to purge things. It is not running itself. But the code itself should not need changing. – Jeremy Kahan Dec 13 '16 at 07:08
  • Yeah I will go back into it back not repeatedly for each new giveaway that is added. Also I'm getting an error while saving the code https://s28.postimg.org/hu0lg05h9/snip2.png –  Dec 13 '16 at 07:10
  • fixed it. Should have said if((isdate(tempDate)) and (tempDate <= currentDate)) – Jeremy Kahan Dec 13 '16 at 07:12
  • hmm, I'm still getting an error https://s23.postimg.org/876ohrs5n/snip3.png but this one says 'after condition' the other had a different error –  Dec 13 '16 at 07:16
  • if (isdate(tempDate)) and (tempDate <= currentDate) and I saved it here, so I know that much works. – Jeremy Kahan Dec 13 '16 at 07:24
  • isdate does not work. But if I remove it, it deletes too much. hmmm – Jeremy Kahan Dec 13 '16 at 07:28
  • Saved successfully but got this error when I tried to run it https://s28.postimg.org/q2ky7yljh/snip4.png –  Dec 13 '16 at 07:29
  • better replace the line above with if (and(isdate(tempdate), (tempDate <= currentDate))) as now above. But you need to leave "Live Events" alone. It names the tab/sheet within the workbook, not the entire thing. No, now it says and is not defined. so frustrating – Jeremy Kahan Dec 13 '16 at 07:36
  • if ((tempDate!=NaN) && (tempDate <= currentDate)). That compiles and runs, and I think works. – Jeremy Kahan Dec 13 '16 at 07:48
  • hmm so do i need to run the script every time for it to delete old ones? It worked perfectly! –  Dec 13 '16 at 07:54
  • nope. See http://stackoverflow.com/questions/3018875/is-it-possible-to-automate-google-spreadsheets-scripts-e-g-without-an-event-to to see how to schedule the script to run. Or you could have it run each time you open the sheet. – Jeremy Kahan Dec 13 '16 at 07:56
  • so the code works when the date expires right? You sir are a freaking legend! How do I donate? –  Dec 13 '16 at 07:56
  • I am sure there is some place in AU that feeds the hungry. Please give to them. Good night from Chicago. The code works whenever it runs based on the current date and removes all the expired rows. It seems to me you can schedule it to run itself on a time basis or each time the sheet is opened. – Jeremy Kahan Dec 13 '16 at 08:01