0

My internet research was vain. Getting the range of dates & comparing them with today's date. After running the script #1 below I receive Exception: The parameters (String,String,String) don't match the method signature for Utilities.formatDate., however when I run the script #2 for a single value (not a loop), everything is fine. How can I make the script #1 work? Thanks

script #1

    function abc() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var datA = ss.getSheetByName('Calendar').getDataRange().getValues();
var todayD = Utilities.formatDate(new Date(),'America/Los_Angeles', 'dd/MM/yyyy');
for (var i = 0; i < datA.length; i++){
  var mes = datA[i][0];
  var mes = Utilities.formatDate(mes,'America/Los_Angeles', 'dd/MM/yyyy');
   if (mes == todayD) {
    Logger.log("yes");}
}  
Logger.log(mes)
}

script #2

function missingDLemail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var datA = ss.getSheetByName('Calendar').getDataRange().getValues();
var todayD = Utilities.formatDate(new Date(),'America/Los_Angeles', 'dd/MM/yyyy');
// for (var i = 0; i < datA.length; i++){
  var mes = datA[6][0];
  var mes = Utilities.formatDate(mes,'America/Los_Angeles', 'dd/MM/yyyy');
   if (mes == todayD) {
    Logger.log("yes");}
// }  
Logger.log(mes)
}
1

Since script #2 works, the value in cell A7, i.e., datA[6][0], must be a date.

The fact that script #1 throws an error tells that at least one other value in column A is not a date. The error message you quote suggests that the problematic value is a text string — perhaps it looks like a date, but it is actually not a date.

Look for values like 2/30/2021, and date formats that are invalid in your spreadsheet locale. For example, 31/12/2021 would be invalid in the United States locale, and 12/31/2021 would be invalid in the United Kingdom locale. You can find whether a value is a date or a text string that looks like a date with the isnumber() spreadsheet function.

In Apps Script, you can test whether a value is a date with this statement as the first thing in the for loop:

  var mes = datA[i][0];
  if (Object.prototype.toString.call(mes) !== '[object Date]') {
    Logger.log('The value "' + String(mes) + '" is not a date.');
    continue;
  }
1
  • True :) , the solution for this specific problem is to run from i=1, i.e. for (var i = 1; i < datA.length; i++) before the first row is the heading.
    – S1ARHE1
    Nov 3 '21 at 22:34

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.