Oh No You Didn't, Google!

Yes, I am aware that I'm writing this on blogger, officially Google app/program/property/whatever.  But the attitude alluded to in the post title is good attitude - I have discovered Google's best-kept-secret.  Okay, maybe you knew about it, but I guarantee that other guy over there didn't.

If you liked Google Drive and Apps, you will love this.  They made up their own cryptic JavaScript dialect so you, yes you, can affect the functionality of your Google Docs.

For example, I was tasked with creating a Google Form to email to clients to confirm their mailing addresses so we can mail them holiday gifts.  Sounds simple enough - Google Forms as it is allows you to put the form in an email, a link to the form in an email, and embed the form in your own website.  Then the results are put into a spreadsheet of your choosing.  Wow!

But... when we decided to pre-populate the form with the addresses we had on file and clients could then either edit and submit, or approve and submit the address instead of having to enter the address all over again, well, it got complicated.  Suddenly, I needed some way to take data from the existing address spreadsheet, put those values into a form as values, then put that form into an email.  Sounds awesome, but as with many awesome things, easier said than done.

(I might mention that in the middle of this project I got a little frustrated and decided I needed a "win", however small, and so put a favicon on the website, which took about ten minutes but was worth as least an hour of feeling like I actually accomplished something).

I happen to really like JavaScript (which anyone who has visited my website, jessicawicksnin.com, can attest to) so I decided to give Google Apps Script a try.  You can now see the results at my github page (username jwicksnin)!

I want to point out the fatal flaw in my solution: the actual form is NOT embedded in the email.  I dare you to try to find a way to do this - if you do, please let me know.  However, this is the results I did come up with (as HTML in the body of the email):

Hello <client name>
<Address we have on file>
Please confirm <pre-filled URL> or change <same URL> so we can send you a gift.

Taking advantage of the pre-filled url idea, the client can see the address we're going to send to, and then go to a pre-populated form by following the link where they can easily change or simply confirm the address. Yay!