Archiving Form Responses that are NOT TODAY using google script

Question

I am brand new to coding and trying to automate a process for a small organization. There is a form that employees fill out each day. I am attempting to write a script that I can run each morning that will remove any responses from dates that are NOT TODAY and archive them in a separate tab.

The key problem is that I can't seem to figure out how to write my code so that:

new Date(); === [the DATE value from the form response sheet]

returns TRUE when they are both the same day.

These are the variables I'm using:

var today = new Date();

var todayNoTime = today.setHours(0,0,0,0); // this was an attempt at a fix that isn't working so far

var dateRange = incomingSheet.getRange(1,1,incomingSheet.getLastRow(),1).getValues(); // these are all the timestamps from the form submissions

Then for the purposes of testing whether I've figured it out i'm using the following for loop:

for (i=0;i<incomingSheet.getLastRow();i++){

Logger.log((dateRange[i],1));

if (dateRange[i][1]===todayNoTime){Logger.log("row " + i + "matches todays date")}else{Logger.log("row " + i + " does not match todays date")}

I tried using .setHours(0,0,0,0) on each date range

ex. dateRange[i][1].setHours(0,0,0,0); but that doesn't work.

I tried using Utilities.formatDate to match the "var today;" to "var dateRange;" but the time remains a problem.

I've added a column to my sheet that uses the INT() function to convert the timestamp date to one of google's 5 digit date values, but I don't know how to convert "var today;" to one of those 5 digit codes automatically so that it works every day.

Any assistance or ideas would be much appreciated.

Comparing dates numerically requires that you use functions that return milliseconds which normally requires methods such as valueOf() or getTime(). Understanding Date() objects can be quite difficult. Here's a reference that I use. Some people convert them to strings via Utilities.formatDate() however than only allow equivalence comparisons and not comparisons such as greater than or less than which are more useful in situations like yours. — Cooper
Also you will be a lot more successful at SO if you learn how to post your code properly. Welcome to StackOverFlow please take this opportunity to take the tour and learn how to How to Ask, format code, minimal reproducible example and Learn More — Cooper

Cooper Cooper · Accepted Answer · 2021-04-08T17:27:03

This will delete rows of data if the date in column one is older than n days. It assumes that the date is in column A and that there is only one header row and that data starts on line 2

function deleteDatesOlderthanNday(n = 15) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const [hA, ...vs] = sh.getDataRange().getValues();
  const dt = new Date();
  const threshold = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate() - n).valueOf();//return milliseconds for midnight n days ago
  let d = 0;
  //entry date is assumed to be in column one or A
  vs.forEach((r, i) => {
    if (new Date(r[0]).valueOf() < threshold) {
      sh.deleteRow(i + 2 - d++);//delete rows that are older than n days
    }
  });
}

Archiving Form Responses that are NOT TODAY using google script

1 Answers