Saturday, 10 November 2012

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 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!