Q:

Trigger not firing for any event

I have the following very basic script that I want to fire when someone submits a form on a Squarespace site. This is the form:
/* Send Confirmation Email with Google Forms */

function SendConfirmation() {

  var name, body, email, subject;

  name = 'Matt';
  email = 'myemailaddress@gmail.com';
  subject = "Thanks for signing up for DOPPS!";
  body = "Thank you " + name;

  MailApp.sendEmail(email, subject, body);

}

It simply sends an email to my email address and it works perfectly when I run the code from the script editor.
I have the following trigger set up:

I've tried "On form submit" and "On Edit", and neither of these fire when the form is submitted from the website. However, Squarespace's "myFunction" fires and sends an email to the owner account, so I know that submitting the form is firing some event.
Note that I'm using a Squarespace form that populates a Google Sheet.

A:

It is possible to send automatic confirmation with Squarespace Forms and Google Sheets, using Google Scripts. @Zig Mandel was correct in indicating that you have to create a time-driven trigger that runs every minute (simulating automatic confirmation). It's not the most elegant way to do it, but it's worked nonetheless. The following script works in a spreadsheet with 3 form fields (Timestamp, Name, and Email). I added a fourth column "Confirmation Sent" to track who has been sent an email and who hasn't. Here is the script:
/* Send Confirmation Email with Squarespace Forms */

function emailConfirmation(row, sheet) {
  var email = sheet.getRange("C" + row).getValue();
  var body = “Thank you for signing up!”
  var subject = “Welcome to my site!";
  Logger.log(row);

  // Send Email
  MailApp.sendEmail( email, subject, body);

  // Register email sent in spreadsheet
  sheet.getRange("D" + row).setValue('sent');
};

function findIndex(columnValues) {
  var rowNum = [];
  for (var i = 0; i < columnValues.length; i++) {
    if (columnValues[i] == '') {   
      rowNum.push(i + 2);
    }
  }
  return rowNum;
};

function searchForNew(sheet) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0]; // gets the first and only sheet
  var column = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();
  var columnValues = sheet.getRange(2, column, sheet.getLastRow() - 1).getValues(); // returns all values in "Confirmation Sent" column
  var searchResult = findIndex(columnValues); // returns rows that do not have a value of 'sent'
  if(searchResult.length > 0) {
    for (var i = 0; i <= searchResult.length - 1; i++) {
      var row = searchResult[i];
      emailConfirmation(row, sheet);
    }
  } else {
    Logger.log('No new registrants.');
  }
}

The trigger should run the searchForNew() function in this scenario. I've written a detailed blog post about this workaround if anyone is interested in understanding more about this. I spent a good 3 days trying to figure out a solution so I figured others might benefit from it.

