How to send confirmation email that includes massaged Google Forms data?

Question

I've created a Google Form to enable respondents to calculate an estimate of costs by providing information through the form that is processed and returned with the estimate in an auto confirmation email message.

Form data is collected in the Form Responses spreadsheet and copied instantly to a second worksheet through function =Query('Form Responses 1'!A:K). Formulas are applied to the data in the second sheet to result in a dollar estimate.

I'm using a script in my Google sheet that I found here in order to generate the confirmation email: http://www.labnol.org/internet/auto-confirmation-emails/28386/#comment-1852825342

Where I'm stuck is in figuring out how to display the cost estimate in the confirmation email that goes to the form respondent. If I pull it from the second sheet into the last column in the Form Responses sheet, then it gets bumped to the next row when each new response is written, since each inserts a new row into the sheet.

On the other hand, I can't figure out how to populate the confirmation email message with fields from the second sheet instead of the first.

Any solutions for me?

Answer 1

I think you must create a Google app script in an other webpage. If your google-form send mail, you must add link for the user with an id ( id in the Google SP ) in your mail.

In this step , the user has a status "in confirmation".

This link must be a Google app script to change status for this user.

you can follow this solution and add more features after.

Answer 2

Great solution from Jean-Pierre Vermulst over on Google Docs Forums, incorporating two lines of code that cause the display of the total in the auto-confiration email:

function SendConfirmationMail(e) {

try {

    var ss, cc, sendername, subject, columns;
    var message, value, textbody, sender;

    // This is your email address and you will be in the CC
    cc = Session.getActiveUser().getEmail();

    // This will show up as the sender's name
    sendername = "[school name]";

    // Optional but change the following variable
    // to have a custom subject for Google Docs emails
    subject = "Tuition & Fees Estimate";

    // This is the body of the auto-reply

    message = "Following is an estimate of your family's tuition and fees for the upcoming school year ...;

    ss = SpreadsheetApp.getActiveSheet();
    columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];

    // This is the submitter's email address
    sender = e.namedValues["Email Address"].toString();

    var sheet = SpreadsheetApp.getActive().getSheetByName('Form Responses 1');

    // Only include form values that are not blank
    for ( var keys in columns ) {
        var key = columns[keys];
        if ( e.namedValues[key] ) {
            message += key + ' :: '+ e.namedValues[key] + "<br />"; 
        }
    }

    message += 'TOTAL :: $' + sheet.getRange(sheet.getLastRow(), 12).getValue();

    textbody = message.replace("<br>", "\n");

    Logger.log(message);

    GmailApp.sendEmail(sender, subject, textbody, 
                        {cc: cc, name: sendername, htmlBody: message});

} catch (e) {
    Logger.log(e.toString());
}

}