Triggering mail sending on HTML form submit using Google Apps Script

Question

I created an HTML form which successfully submits data to Google Sheets via Google Apps Script.

Now I would like to trigger a sending of confirmation email to an address entered in the email input field.

I’m really struggling to expand the functionality of my script despite spending many hours on this problem. I would greatly appreciate any help.

---

The code for the HTML form:

<form action="https://script.google.com/macros/s/AKfycbzz-KveHder1A3CX8GcqZI6GR2MQj66PDRWNKoatIET_LXNqQs/exec" method="post" target="response">
<fieldset>
  <legend>Select Foobar</legend>
  <label><input type="checkbox" name="Foobar" value="Foo">Foo</label>
  <label><input type="checkbox" name="Foobar" value="Bar">Bar</label>
  <label><input type="checkbox" name="Foobar" value="Baz">Baz</label>
</fieldset>
<fieldset>
  <legend>Choose Xyzzy</legend>
  <label><input type="radio" name="Xyzzy" value="Quux">Quux</label>
  <label><input type="radio" name="Xyzzy" value="Quuz">Quuz</label>
</fieldset>
<fieldset>
  <legend>Enter Personal Details</legend>
  <input type="text" placeholder="John Doe" name="Name" required><br>
  <input type="email" placeholder="john.doe@example.com" name="Email"><br>
  <textarea placeholder="Lorem ipsum dolor sit amet…" name="Description"></textarea>
</fieldset>
<input type="submit" value="Submit">
</form>

<iframe name="response"></iframe>

Which submits to this Google Sheet:

https://docs.google.com/spreadsheets/d/10VHS6bozcdNFYcRskkoONMT8Rt-2CwJ_LJGQWdkTJq4/

---

The relevant Google Apps Script code:

var sheetName = "Sheet1";
var scriptProperties = PropertiesService.getScriptProperties();

function intialSetup() {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  scriptProperties.setProperty("key", activeSpreadsheet.getId());
}

function doPost(e) {
  var lock = LockService.getScriptLock();
  lock.tryLock(3000);

  try {
    var doc = SpreadsheetApp.openById(scriptProperties.getProperty("key"));
    var sheet = doc.getSheetByName(sheetName);

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow() + 1;

    var newRow = headers.map(function(header) {
      if (typeof e.parameters[header] !== "undefined") {
        return header === "Date" ? new Date() : e.parameters[header].join(", ");
      } else {
        return header === "Date" ? new Date() : e.parameters[header];
      }
    });

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    return HtmlService.createHtmlOutputFromFile(
      "Index"
    ).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  } catch (e) {
    return HtmlService.createHtmlOutputFromFile(
      "Error"
    ).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  } finally {
    lock.releaseLock();
  }
}

Answer 1

• You want to send an email to the inputted email when the submit button is clicked.

If my understanding is correct, how about this answer? In this answer, the email is sent at the Google Apps Script side. Please think of this as just one of several answers.

Modified script:

For example, how about adding the following script after the line of sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]); in your Google Apps Script?

var email = e.parameter.Email;
MailApp.sendEmail({
  to: email,
  subject: "sample subject",
  body: "sample text body", 
  htmlBody: "sample HTML body"
});

• In this script, the inputted email is used as the email.

Note:

• From your question, I couldn't find the body of email. If you want to include the inputted values of the form, please tell me.
• When you modified the script of Web Apps, please redeploy Web Apps as new version. By this, the script with the latest version is reflected. Please be careful this.

Reference:

• Class MailApp

If I misunderstood your question and this was not the direction you want, I apologize.

Added:

When you want to send the HTML body of email with a HTML file which is separated from Google Apps Script, you can use the following script.

Sample script:

MailApp.sendEmail({
  to: email,
  subject: "sample subject",
  body: "sample text body", 
  htmlBody: HtmlService.createHtmlOutputFromFile("htmlBody").getContent()
});

• In this case, when a HTML file with the filename of htmlBody is added to the script editor including the Google Apps Script, HtmlService.createHtmlOutputFromFile("htmlBody").getContent() returns the raw HTML as the string including the tags.

Reference:

• Class HtmlService