Reputation: 325
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
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