I am trying to create a function to check the dates in 'Dt' sheet and return Match or Not Match.

It works fine with when I display my answer in MsgBox, but when I try to do it in one of the spreadsheet loop process goes to the end of the date range.

Can anybody help me out what am I doing wrong here?

function CheckDates(Month,Day,Year) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dt')
  var sheet = ss.getSheetId()[0];     

  var startRow = 1;
  var endRow = ss.getLastRow();
  var endCol = ss.getLastColumn()

  var Month = "4";
  var Day = "21";
  var Year = "2013";

  var myDate =  new Date(Month + '/' + Day + '/' + Year);
  var dtStr = 1 + myDate.getMonth();
  dtStr = dtStr + '/' + myDate.getDate().toString() + '/' +

   for (var r = startRow; r <= endRow; r++) {        
     var cellValue = ss.getRange(r,1,endRow,endCol).getValue();
     var dt = new Date(cellValue);
     var str = 1 + dt.getMonth(); //getMonth() start with 0
     str = str + '/' + dt.getDate().toString() + '/'+ 

       if(str == dtStr) {
         x = "Match"
       } else {
         x = "Not Match"
  return x; 


There are two ways for you to approach this. The first is via good old formula's. The second is via a Google Apps Script (GAS). The latter is done when the logic in the formula becomes too complex or too slow.


(1) Formula
=ARRAYFORMULA(IF(Dt!A2:A5=DATEVALUE("13/03/2013"),"Match","No Match"))

(2) Google Apps SCript
function myCheck(range,year,month,day) {
  var output = [], myDate = new Date(year,month-1,day);

  for(var i in range) {
    var ckDate = new Date(range[i]);
    if(ckDate-myDate === 0) {
    } else {
      output.push(["No Match"]);
  return output;


  1. The formula converts the string date to a date value and compares it to the range. If one of them is equal, then it will show/display the word "Match".
  2. The code can be written without API calls. All dates are compared (substracted) and the outcome is pushed into an array and returned.


I've created an example file for you: Looping Through Dates

Remarks to your Code

  • Fiddling around with dates in GAS can be quite difficult. Use the Logger.log() method to capture intermediate results, so that you can evaluate them.
  • When you declare something, use it; var sheet isn't used.
  • If you want something to be declared, then do it; x isn't explicitly declared.
  • I've rearranged your code a bit. See example file. Outcome is reversed to what you wanted....
  • See script editor for your original code. I've added Logger.log() to see the intermediate results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.