Webteam Form Notifications

Amend this script as needed

function sendGroupNotification(e) {
  // Ensure the trigger event object exists
  if (!e || !e.range) {
    Logger.log("Error: Script must be triggered by an On Form Submit event.");
    return;
  }
  var sheet = e.range.getSheet();
  var row = e.range.getRow();

  var lastCol = sheet.getLastColumn();
  var headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  var data = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
  var sheetUrl = "https://docs.google.com/spreadsheets/d/" 
             + sheet.getParent().getId() 
             + "/edit";
  var embedUrl = "https://ttc.org.nz/TTC/FormpageName";

  var notifyEmail = "ttc.shred.inbox@ttc.org.nz";
  var formName = '"' + sheet.getParent().getName() + '"'; // e.g. TTC Website Request Form
  // Build HTML body
  var html = 
    + "<h2>New " + formName + " submission</h2>"
    + "<table style='border-collapse:collapse; width:auto;'>;"

  for (var i = 0; i < headers.length; i++) {
    var question = headers[i];
    var answer = data[i];
    if (answer === "" || answer === null || answer === undefined) {
      answer = "<i>(No answer provided)</i>";
    } else if (Object.prototype.toString.call(answer) === "[object Date]") {
      // safer date detection
      answer = answer.toLocaleString();
    }

    html += "<tr>"
          +   "<td style='font-size:smaller; background:#f5f5f5; padding:5px; max-width:50%;'>" + question + "</td>"
          +   "<td style='padding:5px;'>" + answer + "</td>"
          + "</tr>";
    } //end for

  html += "</table>"
       + "<p style='font-size:smaller;color:#666;'>"
       + "This message was generated automatically from a Google Form submission."
       + "</p>";
  html += "<p style='font-size:smaller;color:#666;'>The Google Form is embedded at: "
      + "<a href='" + embedUrl + "'>" + embedUrl + "</a></p>";
  html += "<p style='font-size:smaller;color:#666;'>View all responses here: "
      + "<a href='" + sheetUrl + "'>" + formName + "</a></p>";

  MailApp.sendEmail({
    to: notifyEmail,
    replyTo: notifyEmail,
    name: "TTC Webteam Notifications",
    subject: "[TTC Forms] New " + formName + " response",
    htmlBody: html
    });
  }

Open the Google Form’s response spreadsheet

• Open Extensions > Apps Script
• Paste the HTML email notification script, modified from above, the script sends a formatted email to the selected shared inbox.
• customise the following variables
  • embedUrl
  • notifyEmail

Save and authorise the script

Apps Script needs permission to send email on your behalf.

• Click Save
• Click Run once
• Approve the authorisation prompts (Google will ask you to allow MailApp)

Create the trigger

This ensures the script runs automatically for every new form submission. In Apps Script: left sidebar → Triggers

• Add and configure the trigger:

• Save the trigger

Check Google Group posting permissions

Your script sends email as you, so your account must be allowed to post.

Open the destination Google Groups shared inbox

• Go to Permissions → Posting
• Ensure: “Anyone in the organization” or “Managers + Members” can post
• Confirm moderation is off or your address is auto‑approved

Submit a test form response

This verifies the workflow end‑to‑end.

• Open the form
• Submit a test entry
• Check the shared inbox inbox for the formatted HTML email

Currently used on

See also