Capture HTML Form Responses in Google Sheets and send a Confirmation Email to the Form Submitter

Question

I want the script to send the created html email template email.html to the person who last submitted the form.

Below is the success message I get in the console:

Apps Script Console

Below is the actual email that is received by the person who submits the form:

Email

The email.html is formatted correctly and appears perfectly when sent manually.

I hope this is reprex enough.

EDIT: Maybe it's easier if I include the code

function sendEmail () {

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var data = ss.getRange(lr,3);
var email = data.getValue();

var message = HtmlService.createTemplateFromFile('email');
var subject = "Test Subject"
GmailApp.sendEmail(email,subject, message);
}

You said `I succeed in capturing form responses in google sheets using the onSubmit trigger.` But most users capture there own form responses using doGet() or doPost() or even google.script.run — Cooper, Oct 20 '22 at 21:59
Welcome to [so]. The question needs more focus. In other words, don't include code that isn't directly causing the issue / problem. Ref. [mcve]. — Rubén, Oct 20 '22 at 23:34
@Cooper please see the last bit of code added to the explanation. I'm open to a completely different approach. My goal is to capture html form submission in a google sheet and fire off a thank you email to the submitter when the form is submitted. — RamonT92, Oct 23 '22 at 21:09
@Rubén Apologies for the bulk of info. I'm not a dev, I have no idea where the problem is. — RamonT92, Oct 23 '22 at 21:11
No problem Ramon, but it's still required that the question fits the site guidelines. Start by creating a [mcve] (the idea of creating a "mcve" is to find where the problem is and/or what you have to learn) — Rubén, Oct 23 '22 at 21:15
@Rubén I have adapted my question to what I believe to be minimally reprex. Let me know if I need to make it more specific. Thanks for the responses so far! — RamonT92, Oct 24 '22 at 22:27

Rubén · Answer 1 · 2022-10-25T01:52:06.677

It's not clear why you are using HtmlService.createTemplateFromFile, but from the image it's clear there at least one error, the script misses two methods:

HtmlService.HtmlTemplate.evaluate() to evaluate the Templated HTML
HtmlService.HtmlOutput.getContent() to get the HTML from the HtmlService.HtmlOutput object returned by the previous method.

Another option that looks to be an error is the use of GmailApp.sendEmail(string,string,string) method, as the third parameter should be a string to be used as the email plain text content. If you want to pass HTML, instead use GmailApp.sendEmail(string,string,string, Object)

Capture HTML Form Responses in Google Sheets and send a Confirmation Email to the Form Submitter

1 Answers1