Google Sheets Scripts, IF date of a cell

Question

I ve been getting some issues trying to use IF statement using items on the sheets as answers, mostly i been trying either using as firt statement a string directly or using another cell of the sheets with the statement comparing to the one i ve been trying to detect with out any results. Mostly my objective would be to, with a daly trigger, use an IF statement to detect every day if the date of today is the same as a date marked on a cell, my only current solution is and only thing is to make it detect if a cell is empty or not, so i just been using google Formulas that do the detect of the date, and if True make it empty, and if not make something appear

Here are some of the tests I ve been trying

Test 1:

var Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName");
else if (Sheet.getRange(1, 1).getValue() == "2/2/2020"){}

(cell A1=2/2/2020)

Test 2:

var Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName");
else if (Sheet.getRange(1, 1).getValue() == Sheet.getRange(1, 2).getValue()){}

(cell A1=2/2/2020) (cell B1=2/2/2020)

OneInAMillion OneInAMillion · Accepted Answer · 2021-04-20T14:08:35

Here are two solutions depending on what you are trying to accomplish.

This first one gets the values, formats them to a date format, and then compares them

function myFunction() {
  var tz = Session.getScriptTimeZone();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SheetName');
  var data = sheet.getDataRange().getValues();
  
  var aVal = Utilities.formatDate(data[0][0],tz,'MM/dd/YYYY');
  var bVal = Utilities.formatDate(data[0][1],tz,'MM/dd/YYYY');
  
  Logger.log([aVal,bVal])

  if(aVal == bVal) {
  
    //do something
    
  }

}

This second section gets the display values and compares them. The first solution compares the date values while this second solution compares the dates as a string. Good luck!

function myFunction1() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SheetName');
  var data = sheet.getDataRange().getDisplayValues();
  
  var aVal = data[0][0]
  var bVal = data[0][1]
  
  Logger.log([aVal,bVal])

  if(aVal == bVal) {
  
    //do something
    
  }

}

Google Sheets Scripts, IF date of a cell

1 Answers