Date math

This short post forms part of our series explaining how the Snippets Bakery developed a new online order form with snazzy order confirmation emails which accurately handles date input. Today we're using those dates so it helps that we've recorded them in a helpful format!

If you've come from VBA then you'll be used to being able to add and subtract dates. In Excel, dates are stored as serial numbers equating to the number of days since 0-Jan-1900 (actually one greater than this number due to a deliberately continued bug in Lotus 123) with time as a decimal portion of a day.

In JavaScript, times are stored as the number of milliseconds since midnight 1-Jan-1970. What does that mean for us if we want to calculate the numbers of days between two dates? Well, firstly we need to get to the milliseconds (quickly achieved with getTime) and then we can subtract the smaller value from the larger - in this case we  are asking users to input a date in the future so we subtract the submission timestamp from this. Then we have with a bunch of milliseconds on our hands so we divide this number by 86,400,000 (the number of milliseconds in a day) and we are left with a number of days. In all likelihood this will be a fraction so I have rounded it down with a Math.floor function.

Google Forms and JavaScript Dates

Updated: +Eric Koleda was kind enough to point out JavaScript's date parsing capabilities which are much more elegant than my previous workaround so this post has been edited to avoid leading anyone else astray!

Last time in our Snippets Bakery Project we looked at constructing HTML tables in confirmation emails. This time we're looking at working with dates - crucial when your products have a short shelf life!

One of the most common pieces of information you need to collect in a form is a date. Unfortunately, Google Forms doesn't include a flashy jQuery-like date picker and the most common approach seems to be providing a free text box and hoping that respondents fill in dates in a useful format. However, responses that vary from "Monday" to "Monday the nineteenth of November" to "19-11-2012" are less than ideal if you need to perform calculations based on a specified date.

3 x the fun fields

A work around I have used isn't terribly graceful in that it requires three fields to be filled in but it is relatively simple to code and does the job.

So, what do we want to achieve with this snippet?

• A date string form that is easily interpreted by humans
• A JavaScript date instance with which we are able to do arithmetic
• To avoid any international ambiguity over day and month order

Transatlantic Translation

So how do we go about it? To achieve the third aim I want to build using (shortened) month names in to the specification. It could be argued that naming the field "Month" or the fact that there's only 12 numerical options rather than 31 could be enough but if you were in a hurry and wanted to select the second of January it is conceivable that you could select the first of February in error if you select 1-2. So: month names it is.

To create a human readable date string, all we really need to do is attach the values with a splitter character of your preference. In JavaScript you can easily join strings with the concatenation operator (+). 

Handily, JavaScript will parse datestrings in some formats easily so having Months as names doesn't present us with a problem. This post was much simpler than I originally envisaged!

Further reading - 10 ways to format time and date using JavaScript - Working with dates

Creating a formatted HTML table in an email body

This is the second part of a short series looking at the Snippets Bakery's Project which entails an Order Form and automatic response emails.

Let's take a look at how we got that (relatively!) nicely styled table in to the body of the email. The Google Apps Script MailApp.SendEmail method allows for an HTML body so we could have just hand written the table out as follows (note that we would need to switch the response values to the references to the object properties where the response values are stored - I took the HTML right from my browser console which is why they are just plain text):

Writing out that table would be incredibly tedious and involve a lot of repetition, not to mention that if we want to change any of the variables we would constantly need to edit it - there must be a better way. Luckily, there is. We can get Google Apps Script to write the table for us!

With this code we are aiming to generate all of the HTML we need and add it to the HTMLtable string. We are using a for..in loop to iterate over all of the properties in our NewSubmission object - the object where we stored all of our form response values.

We've adjusted this object slightly so let's have a look at how it's created.

We're still using a for loop as previously discussed however we've add an if..else statement to capture the Section Headings that we've added in. As these aren't associated with any of the response values we need to treat them differently (their values are arbitrarily set as space character, " ", not an empty string for reasons we will arrive at shortly) and we need to adjust the value of i when we pass in e.values[i] as they will be out of sync due to these empty properties we've added. That is why we've added in the variable iSkip, incremented it for each heading and subtracted it from i when getting items from the e.values[] array.

Right, back to our table. Three points to make:

• As we iterate over the properties in NewSubmission, we've added a check that the length of these properties is non-zero; that is, the question they refer to has actually been answered. This isn't strictly necessary in our case as as each question on our form is designated as "Required" - however there may be situations where this is not the case. This is the reason our the values of our Section Headings properties are not set to empty strings - if they were, this check would eliminate them.
• We've added a check for Section Headings - defined as property names which start "Section". We format these differently and don't include their values which are inconsequential.
• Using the modulus operator we check if our table row count is odd or even. If it's odd we apply a different background colour - this is how we achieve the stripped table effect. The row count value starts at zero and is incremented each time we successfully add a row to the HTMLtable string.
• Finally, don't forget to close the <table> tags outside of the loop or the rest of your email will sit inside your table. It's not a very pleasant sight, I assure you!

Snippets Bakery Project

Now that we know how to set up a Google Form and code a simple notification email based on form submission values that are easier to understand, let's have a look at a more fully-fledged confirmation email with an HTML Table and some date and cost calculations.

This is the Snippets Bakery Order Form and these are the submitted orders. Anyone else hungry? Whilst I was writing this I was pretty disappointed that my orders weren't actually going to result in any cakes being delivered!

The aim of our code is to create something like this confirmation email:

Automatically generated confirmation email with HTML table of responses

In the next few posts I will breakdown various bits of the code and explain how some of the more intricate results were achieved. If you're happy to dive right in, you can get all the code here.

Features and explanatory articles! 

• Styled HTML Table (formatted with HTML as CSS support in email clients is patchy at best) with responses split in to two sections - part two
• Work around for the lack of date picker in Google Forms - part three
• Difference between two dates calculated - part four
• Total Cost Calculated based on three variables - part five
• Orders with less than 3 days notice until the fulfilment date are automatically rejected with a polite alternative email.