Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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() + '/' +
    myDate.getFullYear().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() + '/'+ 
       dt.getFullYear().toString();

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

share|improve this question
    
Perhaps you can share a doc with us? –  Jacob Jan Tuinstra Apr 29 '13 at 12:24
add comment

1 Answer 1

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.

Solutions

(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) {
      output.push(["Match"]);
    } else {
      output.push(["No Match"]);
    }
  }
  return output;
}

Explained

  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.

Example

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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