Tip: Making a Google Doc submission form email you the results

In an earlier article we detailed how you can use Google Docs to create a submission form in your website. Step 5 of that article showed how you can be notified whenever a new submission is made; the only trouble with this is that it sends you a notice email that makes you then go back to the Google Docs spreadsheet to view the new submission... not the most user-friendly experience!

The script and instructions below advance the Google Docs submission form functionality so that it automatically emails you (or all of the form editors) the contents of the submission.

Step 1.
Using your existing Google Docs form spreadsheet, go to the spreadsheet view. Go to Tools –> Script Editor and copy-paste the following code in that code editor window:


function sendFormByEmail(e) {
var emailSubject = "Google Docs Form Submission";  

// Set with your email address or a comma-separated list of email addresses.
var yourEmail     = "YOUR EMAIL ADDRESS";

// Set with your spreadsheet's key, found in the URL when viewing your spreadsheet.
var docKey       = "YOUR KEY";

// If you want the script to auto send to all of the spreadsheet's editors, set this value as 1.
// Otherwise set to 0 and it will send to the yourEmail values.
var useEditors     = 1;

// Have you added columns that are not being used in your form? If so, set this value to 
// the NUMBER of the last column that is used in your form.
// for example, Column C is the number 3
var extraColumns = 0;

if (useEditors) {
var editors = DocsList.getFileById(docKey).getEditors();
if (editors) { 
var notify = editors.join(',');
} else var notify = yourEmail;
} else {
var notify = yourEmail;
}

// The variable e holds all the submission values in an array.
// Loop through the array and append values to the body.

var s = SpreadsheetApp.getActive().getSheetByName("Sheet1");
if (extraColumns){
var headers = s.getRange(1,1,1,extraColumns).getValues()[0];
} else var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];

var message = "";
for(var i in headers) {
message += headers[i] + ' = '+ e.values[i].toString() + '\n\n'; 
}

MailApp.sendEmail(notify, emailSubject, message); 
}


(a clean version of the code is here).

Step 2.
Replace the value of variables in the script with your own variable values. Name the project (where it says "Untitled project" at the top of the page). Control-S to save (or click the disk icon).

Step 3.
Go to Resources –> Current Script’s Triggers. It will say "no triggers set up. click to add one now."; click that link to add a trigger. Click on the "notifications" link to associate the script to your email address, then choose "immediately" in the second drop down box. Hit ok to close that window, then hit the "Save" button.

Step 4.
A message will be shown to authorize the script; click on "Authorize". It will approve the authorization for the trigger, and when it brings you back to the screen hit the "Save" button.

You can now close the window and go back to your spreadsheet, you're all set!


Common issues:

1) Make sure that the name of your sheet in the spreadsheet is the same as the sheet name in this line:
var s = SpreadsheetApp.getActive().getSheetByName("Sheet1");

2) Make sure that your sheet name does not have any spaces; this can cause an error.

3) Do not have empty columns interspersed with the columns that you have content being submitted to. This will cause alignment issues and the script to fail.

4) Seeing an error like "cannot call method 'tostring' of undefined? Chances are this is because you added extra columns at the end of your spreadsheet that aren't being used by your form. The way to get around this error, while still having those columns, is to...

Change this line:
var headers = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];

Replace "s.getLastColumn()" with the column number of the last column used in your form.
(this update/issue is no longer valid! our revised script posted above fixes this error!)

5) Sometimes column names can cause issues. If you're having trouble try changing the column names to 1-word values without special characters.


Don't forget!

We have quite a few other articles on creating and extending forms for use in your Blogger site, check them out!


Before you leave a comment...!

Before you leave a comment saying there is an error or it doesn't work, it does. We have this script running on dozens of client projects. So if there is a problem, it's something unique to your form, script, or variables. If you need help, we are available to help, but at our billable rates. Please use the contact form and we'll get back to you as soon as possible to work through the trouble, but understand that we have billable client work to be done.