0

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.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Dylan Banta
  • 165
  • 1
  • 2
  • 17
  • I forgot to mention, I also think that using the drag function to change the dates on Sheet1 might be causing the dates to be formatted differently but I'm not sure how to set dates other than the current date (2 days ago, or 2 days from no for example) – Dylan Banta Aug 30 '18 at 13:12
  • 1
    Possible duplicate of [Compare two dates with JavaScript](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) – tehhowch Aug 30 '18 at 13:55
  • 1
    When Apps Script reads a Sheets cell via `getValue`/`s` that contains something parsable as a JavaScript `Date`, it returns the corresponding `Date` object. Viewing this variable in the debugger will show the object representation (e.g. `Date#toString()`), hence the "extra information" you see. Per the link, compare `Date`s for equality numerically. Comparing via `<` and `>` will handle it automatically. – tehhowch Aug 30 '18 at 13:59
  • 1
    If all you care about is the string, try using `aValuesS1[i].getValue().toString() == dValuesS2[j].getValue().toString()`. That way it gets the cell value and then converts it to a string to compare. – Brian Aug 30 '18 at 14:00
  • It seems that I can't use `aValuesS1[i].getValue().toString() == dValuesS2[j].getValue().toString()` In my if statement I stop getting any response from either true or false of the if statement. I'm looking at https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript now to see if it will give me a way to fix this – Dylan Banta Aug 30 '18 at 14:16

1 Answers1

1

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.

Dylan Banta
  • 165
  • 1
  • 2
  • 17
  • 1
    Sounds like you need to follow the ["Best Practices"](https://developers.google.com/apps-script/guides/support/best-practices#use_batch_operations) - I'm guessing you call `getValue` and `setValue` on a `Range` within your loops, which is a no-no. – tehhowch Aug 30 '18 at 15:52
  • I am, thanks for the link I'll start optimizing right away! – Dylan Banta Aug 30 '18 at 15:59