Google Apps Script Event Manager

Question

I'm currently trying to use Martin Hawksey's Google Apps event manager from his blog, but I am running into 2 problems.

#1) The date in the conformation emails is posting the "today's" date & time instead of the event date & time (it's in his original and he never fixed it). Most are saying has to do with this section not being right:

    var variableData = isDate(data[normalizeHeader(templateVars[i])]);
    email = email.replace(templateVars[i], variableData || "");
  }

  return email;
}

// Test if value is a date and if so format 
function isDate(sDate) {
  var scratch = new Date(sDate);
  if (scratch.toString() == "NaN" || scratch.toString() == "Invalid Date") {
    return sDate;
  } 
  else {
    return Utilities.formatDate(new Date(), TZ, "dd MMM yy HH:mm");
  }
}

#2) My other issue is in the template for joining instructions I can't call-to any variables (i.e. ${"Invoice"} or ${"Amount"} :: instead it returns "today's" date <-- I added more cells and added a column for each and they have data in them, and made correct adjustments in the script; still nothing.

ex.

Template: "Your Invoice # is: ${"Invoice"} and your total amount due is: ${"Amount"}"
Reality: "Your Invoice # is: 13 Feb 13 13:18 and your total amount due is: 13 Feb 13 13:18."

Here is my full script and changes I made (not too different from his original): https://gist.github.com/hakarune/4985606

Any and all help would be very much appreciated, the biggest and most important thing is that date though.... Thanks you

Answer 1

For problem #1, the comments for the isDate() function say that if the given sDate is a valid date, a formatted version of that date will be returned. But the call to formatDate() passes new Date(), which will be the current date & time. Instead, it should pass new Date(sDate).

return Utilities.formatDate(new Date(sDate), TZ, "dd MMM yy HH:mm");

For problem #2, looks like the problem is again with isDate(). The fillInTemplateFromObject() function is calling isDate() to format the template data if it's a date, expecting it will be left as-is otherwise. The problem is that every number will pass the isDate() check, since the test is simply whether new Date(sDate) will produce a date. See this reference, you'll see that it would end up being treated as new Date(milliseconds). You're getting the current date because of the bug described above... fix that and you'll get a different date, but still a date. Check Detecting an "invalid date" Date instance in JavaScript, it may provide a more conclusive test, if it works in apps-script.

Here's a fix of isDate() for you to try. It includes the fix for problem #1, and pulls in the isValidDate() routine from Detecting an "invalid date" Date instance in JavaScript to more accurately differentiate between dates and numbers.

// From https://stackoverflow.com/questions/1353684
// Returns 'true' if variable d is a date object.
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}

// Test if value is a date and if so format
// otherwise, reflect input variable back as-is. 
function isDate(sDate) {
  if (isValidDate(sDate)) {
    sDate = Utilities.formatDate(new Date(sDate), TZ, "dd MMM yy HH:mm");
  }
  return sDate;
}

If you're curious, this is the test code I ran in the debugger. The comments show what was displayed as values in the debugger.

var TZ = "GMT"; // isDate() uses a global variable for TimeZone, let's try GMT

function myFunction() {
  var a = new Date();     // Fri Feb 22 2013 20:48:07 GMT-0500 (EST)
  var b = isDate(a);      // "23 Feb 13 01:48"
  var c = 142312;         // 142312.0
  var d = isDate(c);      // 142312.0
  var e = 'test string';  // "test string"
  var f = isDate(e);      // "test string"
  var g = 'Feb 22, 2013'  // "Feb 22, 2013"
  var h = isDate(g);      // "Feb 22, 2013"
  debugger;
}