Webteam Form Notifications

This capability enhances the notification to shared inboxes of forms submitted in the TTC website.
This information provides the steps to follow to add a notification script to the responses spreadsheet of any form in the TTC Google Workspace.

Install the script

• Open the Google Form’s response spreadsheet
• Open Extensions > Apps Script
• Paste the HTML email notification script, see below, the script sends a formatted email to the selected shared inbox.
• customise the following variables
  • embedUrl, e.g. to https://ttc.org.nz/Webteam/TestEmbed
  • notifyEmail, e.g. to ttc.webteam@ttc.org.nz

Save and authorise the script

The 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

The script sends email from your account, e.g. givenname.fmailyname@ttc.org.nz, so the account must be allowed to post to the inbox.

Open the destination Google Groups shared inbox

• Go to Permissions → Posting
• Ensure: “Anyone in the organisation” 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

Script

Amend this script as needed

• embedUrl
• notifyEmail

The webteam has a TestForm which can be used to test changes to the script.

function sendGroupNotification(e) {
  const NL = "\n";
  // 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 sheetParent = sheet.getParent();

  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/" 
             + sheetParent.getId() 
             + "/edit";
  var formName = '"' + escapeHtml(sheetParent.getName()) + '"'; // e.g. TTC Website Request Form

  var embedUrl = "https://ttc.org.nz/TTC/FormpageName"; // update this value
  var notifyEmail = "ttc.shared.inbox@ttc.org.nz"; // update this value

  // Build HTML body
 var html = NL + "<h2>New " + formName + " submission</h2>"
          + NL + "<table style='border-collapse:collapse; width:auto;'>"
          + NL + "<tbody>";

  for (var i = 0; i < headers.length; i++) {
    var question = escapeHtml(headers[i]);
    var answer = "";
    var rawAnswer = (i < data.length) ? data[i] : "";
    if (rawAnswer === "" || rawAnswer === null || rawAnswer === undefined) {
      answer = "<i>(No answer provided)</i>";
    } else if (Object.prototype.toString.call(rawAnswer) === "[object Date]") {
        answer = formatSmartDate(rawAnswer);
    } else {
      answer = escapeHtml(String(rawAnswer));
    } // end if
    html += NL + "<tr>"
          + NL + "<td style='font-size:smaller; background:#f5f5f5; padding:5px; max-width:50%;'>" + question + "</td>"
          + NL + "<td style='padding:5px;'>&nbsp;" + answer + "</td>"
          + NL + "</tr>";
  } // end for

  html += NL + "</tbody></table>";
  html += NL + "<p style='font-size:smaller;color:#666;'>"
       + "This message was generated automatically from a Google Form submission."
       + "</p>";
  html += NL + "<p style='font-size:smaller;color:#666;'>The Google Form is embedded at: "
      + "<a href='" + embedUrl + "'>" + embedUrl + "</a></p>";
  html += NL + "<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 Webform Notifications",
    subject: "New " + formName + " response",
    htmlBody: html
    });
  } // end function sendGroupNotification

function formatSmartDate(rawAnswer) {
  var tz_local = Session.getScriptTimeZone();
  var localYear = rawAnswer.getFullYear();
  var localHour = rawAnswer.getHours();
  var localMin  = rawAnswer.getMinutes();
  var localSec  = rawAnswer.getSeconds();

  // 1. Time-only field (1899 epoch)
  if (localYear === 1899 || localYear === 1900) {
    return Utilities.formatDate(rawAnswer, tz_local, "HH:mm");
  }

  // 2. Date-only field (00:00:00 local midnight)
  if (localHour === 0 && localMin === 0 && localSec === 0) {
    return Utilities.formatDate(rawAnswer, tz_local, "yyyy-MM-dd (EEE)");
  }

  // 3. Full submission timestamp
  return Utilities.formatDate(rawAnswer, tz_local, "yyyy-MM-dd HH:mm:ss (EEE)");
} // end function formatSmartDate

function escapeHtml(text) {
  if (text === null || text === undefined) return "";
  return String(text)
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#39;");
} // end function escapeHtml