Websites‎ > ‎Google Sites How-tos‎ > ‎

Google Apps Script - Contact Us Form - Email

posted 9 Jul 2010, 10:11 by Stephen Hind   [ updated 11 Apr 2014, 06:43 by Sam Smullen ]

Google Apps Script - Contact Us Form - Email

Google now provide scripts you can add to spreadsheets, so now when a Google Sites visitor uses your Google Spreadsheet Form you can have the response e-mailed to you, rather than having to go to the spreadsheet.


Instructions

  1. Go to Google Docs (either http://drive.google.com for a personal account or http://drive.google.com/a/example.com for Google Apps uses, where example.com is your domain)

  2. Use the Create button then choose Form to make a new form

  3. In the form you need to make three required questions

    1. Name
    2. E-mail Address (so you can get back in touch)
    3. Message (or whatever you want to call it)

    For further information on how to create forms see Forms: Creating Forms - Google Docs Help Centre

  4. In the form editor use View responses button to open the spreadsheet of responses. If cannot see a View responses button use the Choose response destination button and choose to make a new spreadsheet (give it a sensible name) for your responses. Once created the Choose response destination button changes to a  View responses button.

  5. Once in the spreadsheet use the Tools menu, then Script editor...
  6. In the Google Apps Script dialogue box choose to make a Blank Project.

  7. In the Script editor give your script a sensible name, (rather than Untitled project) delete all the existing text and paste in the script below

    function nl2br_(input_string){
      return input_string.replace(/(\r\n|\r|\n)/g,'<br />');
    }

    function contactUsMailer(e) {
      //  This script e-mails the contents of a form to a given recipient
      //  The form must have three fields in the order of: name; e-mail address; and message
      //  You must change the recipient variable below to your e-mail address
      try {
        var recipient = '';
        var timestamp = e.values[0];
        var name = e.values[1];
        var email = e.values[2];
        var message = e.values[3];
        var body = name+' <'+email+'> sent the following message: '+message;
        var bodyHTML = '\
            <p>'+name+' <a href="mailto:'+email+'">'+email+'</a> sent the following message: </p>\
            <blockquote>'+nl2br_(message)+'</blockquote>\
            <p>Sent by the <a href="http://www.steegle.com/">Steegle.com</a> Contact Us Form Google Apps Script</p>';
        var advancedArgs = {htmlBody:bodyHTML , replyTo:email};
        MailApp.sendEmail(recipient, "Contact Us Form", body, advancedArgs);
      } catch(e){
        MailApp.sendEmail(recipient, "Error - Contact Us Form", e.message);
      }
    }


  8. Enter your e-mail address in between the quotation marks on the 
    var recipient = '';
    line, so it should now look something like
    var recipient = 'someone@example.com'
    ;
    and use the Save button (it's a little icon of a floppy disc)
    Google Apps Script Save Button
  9. While still in the Script Editor use the Resources menu, then Current project's triggers...

  10. In the Current Project's Triggers box use the No triggers set up. Click here to add one now link

  11. From the drop-down boxes make sure that contactUsMailer, From spreadsheet and On form submit are selected and use the Save button.

  12. Close the Script Editor and return to your spreadsheet. While you're there save the spreadsheet and close it.

  13. Go to your Google Site and use the Edit Page button, then the Insert menu and then Spreadsheet form

  14. Choose the form you have created and use the Select button, then choose the display options you want for the form (width, height title, border, etc) and then use the Save button

  15. Use the Save button (top-right corner) to save the page and then you can test your form.

I have included the form below, so please do use the form to let me know what you think of the script.

The Form


Screen Shots

click for larger images

Google Form - Choose response destination

Google Form - Choose response destination

Google Sheets - Tools menu - Script Editor

Google Sheets - Tools menu - Script editor

Google Apps Script - Blank Project

Google Apps Script - Blank Project

Script Editor with new blank project

Script Editor - New Blank Project

Script Editor with pasted script

Script editor with pasted script

Resources - Current Project's Triggers Menu

Google Apps Script - Resources menu - Current Project's Triggers Menu

Current Project's Triggers

Google Apps Script - Current Project's Triggers