Categories: Share an idea : Sheets :

User confirmation Email after submitting Google Form - Script

Showing 1-9 of 9 messages
User confirmation Email after submitting Google Form - Script TheDarkMist 3/15/10 4:37 AM
Does some programmer made some public script and/or tutorial to make possible to send an email to a form user? Probably with the new scripts feature it is possible?

Re: User confirmation Email after submitting Google Form - Script ahab 3/15/10 4:57 AM
The problem with handling this automatically from a script is that there is no trigger function that would allow an unattended reaction as a result of the submit - there is no onSubmit trigger (like the onOpen and onEdit triggers that are available).

One could try to create a user defined function that would react on a spreadsheet change when there is newly submitted data but I fear user defined functions are not allowed to invoke actions like sending e-mails and b) the chances are very high if a function would be allowed to do so would be resending an e-mail every time the spreadsheet is recalculated - there is no way to make the spreadsheet remember if an action had been taken or not - the spreadsheet has no memory of its previous state and it is very hard (if not impossible) to automate setting state (as user defined functions are not allowed to interact with the spreadsheet other than by parameter passing and return values).
Re: User confirmation Email after submitting Google Form - Script TheDarkMist 3/15/10 5:35 AM
I'm not a programmer, so I cannot do such things :(
Re: User confirmation Email after submitting Google Form - Script michealhunt 3/24/10 6:56 AM
you can save state, by creating another column that is not being used by the form, however; you are still blocked by the fact that there is no onSubmit trigger.  The on edit event is not fired by a new form entry, so there is no way to do this in script.  What I have done as a work around is to set up a script that will fire off emails for all new entry (using additional columns for tracking which entries have been processed) and putting a new menu item on the spreadsheet to fire the script.  The manager of the spreadsheet then needs to check the sheet each day and click the script to fire off emails for any new entries that have happened since last time.  This is not ideal, but it works until Google gets us more events to work with.

Micheal Hunt
Re: User confirmation Email after submitting Google Form - Script dieginjo 4/11/10 9:54 PM
Use this as a template:
function sendEmails() {
 
var sheet = SpreadsheetApp.getActiveSheet();
 
var startRow = 2;  // First row of data to process
 
var numRows = 2;   // Number of rows to process
 
// Fetch the range of cells A2:B3
 
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
 
// Fetch values for each row in the Range.
 
var data = dataRange.getValues();
 
for (i in data) {
   
var row = data[i];
   
var emailAddress = row[0];  // First column
   
var message = row[1];       // Second column
   
var subject = "Sending emails from a Spreadsheet";
   
MailApp.sendEmail(emailAddress, subject, message);
 
}
}

and just pay a monkey to run the script a few times a day.



Re: User confirmation Email after submitting Google Form - Script dieginjo 4/11/10 10:04 PM
Scratch that:
Follow this link for the comprehensive tutorial.
Re: User confirmation Email after submitting Google Form - Script michealhunt 5/20/10 6:16 AM
see: http://www.google.com/google-d-s/scripts/events_overview.html  it is now possible to have your script fire up to every minute to check for changes (i.e. new form entries).  While this still isn't quite the same as an onSubmit trigger, it will allow you to get the result that you're looking for.
Re: User confirmation Email after submitting Google Form - Script RGuz 6/27/10 12:55 AM
I've been working on a signup form and I wanted to send a confirmation email when a user submits the form.

Google has added a working "On form submit" trigger, so I decided to tackle the other half of the problem, tracking which submissions have already had confirmation emails sent.

I started with the Simple Mail Merge Tutorial code from here: http://www.google.com/google-d-s/scripts/mail_merge.html

I made a few improvements (it now looks at all active columns, accesses the tabs by name rather than by index, and does some error handling when sending the email)

I then modified it to track which rows have already been emailed. The script looks for a "yes" in the rightmost column in the sheet, with the header "confirmed". It only creates and sends an email if the value in that row is not equal to "yes". Once it has sent the email it writes a "yes" to all rows in that final column.


To see the script code, open the Tools menu, go to Scripts, and then Scripts Editor. You can make a copy of the spreadsheet and then play around with it. To get the error handling emails to work replace z...@zzz.com with your real email address.

Let me know if you have any questions or ideas for improvements.
Re: User confirmation Email after submitting Google Form - Script ahab 6/27/10 7:34 AM
Some users may want't to start with a slightly more simple script that directly works with the data actually submitted . See [1].