Dylan Banta
Dylan Banta

Reputation: 325

Checking if date equal in Google Sheets/Google Scripts

I have 2 sheets that both have dates saved to columns. My current date is saved on both Sheet1 and Sheet2 with the same code:

curDate = Utilities.formatDate(new Date(), "EST", "MM/dd/yyyy");

I want to have one of my scripts compare the dates from Sheet1 to Sheet2

On Sheet1 I used a small script to set the current date and then used the drag function to set the previous and next dates in the column using the same formatting here:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Update Dates')
      .addItem('Set Dates', 'setDatesUp')
      .addToUi();
}

function setDatesUp(){
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");

  var curDate = Utilities.formatDate(new Date(), "EST", "MM/dd/yyyy");

  sheet1.getRange("A4").setValue(curDate);
}

Once the dates on Sheet1 are set up I use the following script to compare the dates from Sheet1 and Sheet2, this script also sets the date for Sheet2 because when this script is activated it's supposed to mark the current date in the corresponding box.

function onEdit() {
  //Loops through all of Column C to determine which values are True and False
  //Saves all True Values Row # in SavedValues

  //Initialize Variables
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var sheet2 = ss.getSheetByName("Sheet2");

  //Sheet 2 S2
  var cValuesS2 = sheet2.getRange("C:C").getValues();
  var dValuesS2 = sheet2.getRange("D:D").getValues();
  var lastRowS2 = sheet2.getLastRow();

  //Variables
  var curDate;
  var curVar;

  //Loops through all S2 rows, stops at last row
  for (var i = 0; i <= lastRowS2; i++){
    //Checks checkboxes in S2C:C for True OR "Complete" and adds dates in corresponding D:D cells
    if (cValuesS2[i] == "true" || cValuesS2[i] == "Complete") {
      //If D:i is empty
      if (dValuesS2[i] == "") {
        //Sets current date
        curDate = Utilities.formatDate(new Date(), "EST", "MM/dd/yyyy");
        //Set current D:i cell (*+1 offset)
        curVar = ("D" + (i + 1));
        //Sets curVar value to curDate
        sheet2.getRange(curVar).setValue(curDate);
      }
      //Checks checkboxes in S2C:C for False OR "Incomplete" and deletes dates in corresponding D:D cells
    } else if (cValuesS2[i] == "false" || cValuesS2[i] == "Incomplete") {
      //If D:i is NOT empty
      if (dValuesS2[i] != "") {
        //Set current D:i cell (*+1 offset)
        curVar = ("D" + (i + 1));
        //Sets curVar to ""
        sheet2.getRange(curVar).setValue("");
      }
    } 
  }
  updateS1();
}

Then finally I have my script to compare the dates from the 2 Sheets together.

function updateS1() {
  //Initialize Variables
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2");

  //Sheet 1 S1
  var aValuesS1 = sheet1.getRange("A:A").getValues();
  var lastRowS1 = sheet1.getLastRow();

  //Sheet 2 S2
  var dValuesS2 = sheet2.getRange("D:D").getValues();
  var lastRowS2 = sheet2.getLastRow();

  //Variables
  var curVar;
  var curVar2;

  //Loop through Sheet 1 until the bottom
  //For each value in S1 A I use i
  for (var i = 0; i <= lastRowS1; i++) {
    //Loop through Sheet 2 until the bottom
    //For each value in S2 D I use j
    for (var j = 0; j <= lastRowS2; j++) {
        //TODO: Compare dates from S1 A:i to S2 D:j
        //If they are the same date the if statement will execute
    }
  }
}

I've already tried using the following

if (aValuesS1[i].toString() == dValuesS2[j].toString()) {
}

and

if (aValuesS1[i] == dValuesS2[j]) {
}

but neither option has worked. I've noticed that when I grab the "value" for the date in the Logger I get a lot of information that I don't want or need:

Thu Oct 30 2018 00:00:00 GMT-0400 (EDT)

instead of 8/30/18.
I think that this is the reason that I do not get "matching" values even if both of my boxes show the date formatted the same. I'm honestly stumped at how to solve this, so any help would be greatly appreciated.

Upvotes: 0

Views: 3771

Answers (1)

Dylan Banta
Dylan Banta

Reputation: 325

So as it turns out, I actually can compare in my if statement with .toString() it just takes quite a while for my loop to execute in the Google Apps Script.

Upvotes: 1

Related Questions