1

I need to run a loop that checks if dates are the same and if they are delete the duplicate. We have visitors that come to our store multiple times. Data entry gets convoluted. So I need to run my database through a loop that cleans up duplicated dates for individual customers. I'm struggling with the date issue. I thought I could use getTime(). But it is returning the current time not the logged entry. I used a practice loop of only 3 entries, but the loop would run through about 12,000 entries.

function cleanUpBebacks() {
  const myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  const importSheet = myGoogleSheet.getSheetByName('Import from Hubspot');

  var lastRow1 = importSheet.getLastRow();

  for (r = 2; r <= 4; r++) {

    var dateVisited1 = Date("importSheet.getRange(r,36).getTime()");
    var dateVisited2 = Date("importSheet.getRange(r,37).getTime()");
    var dateVisited3 = Date("importSheet.getRange(r,38).getTime()");
    var dateVisited4 = Date("importSheet.getRange(r,39).getTime()");
    var dateVisited5 = Date("importSheet.getRange(r,40).getTime()");
    var beBackRange = importSheet.getRange(r, 42).getValue();

    if (dateVisited5 === dateVisited4) {
      importSheet.getRange(r, 40).setValue("");
    }

    else if (dateVisited4 === dateVisited3) {
      importSheet.getRange(r, 39).setValue("");
    }

    else if (dateVisited3 === dateVisited2) {
      importSheet.getRange(r, 38).setValue("");
    }


    else if (dateVisited2 === dateVisited1) {
      importSheet.getRange(r, 37).setValue("");
    }

    else if (dateVisited2 === "") {
      getRange(beBackRange.setValue("No"));
    }

    Logger.log(lastRow1);
    Logger.log(dateVisited1);
    Logger.log(dateVisited2);
    Logger.log(dateVisited3);
    Logger.log(dateVisited4);
    Logger.log(dateVisited5);
    Logger.log(beBackRange);
  }
}
sam
  • 1,767
  • 12
  • 15
Brenda Harrison
  • 29
  • 2
  • 1
    I miss some details about your problem: 1-What is the aim of having 5 date columns in your datasheet? 2-What is the result you expect after cleaning the repeated values? 3-What accuracy do you demand to consideer two dates the same: only date, or date plus time? – Little Santi Nov 02 '21 at 21:15
  • Are these normal JS dates? If so, see https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript – kmoser Nov 03 '21 at 03:28
  • 1) We have customers that come several times to our location. I need an accurate history of these visits. The only way to accomplish this is with separate date fields. 2)I need to clean up the dates from incorrect input. 3) I only need dates no time please. – Brenda Harrison Nov 04 '21 at 17:34

0 Answers0