squarespace-apps-script-sendgrid.gs

// If true, the emails will be sent to the support email
var DEV_MODE = false;

// Index of some column that is not used.
var SENT_COLUMN = 15;
// Place your Grid API Key here.
var SENDGRID_KEY = PUT_YOUR_SEND_GRID_API_KEY_HERE;
// The emails will be send here.
var EMAIL_TO = "[email protected]";
// Errors will be send here
var SUPPORT_EMAIL = "[email protected]";
// Subject of the email
var SUBJECT = "FORM SUBMISSION";

var NAME_COLUMN_NAME = "Name";
var EMAIL_COLUMN_NAME = "Email Address";
 
/**
 * Creates a timebased trigger for the current spreadsheet.
 * Run this function to initialize the email sending script.
 */
function Initialize() {
  var triggers = ScriptApp.getProjectTriggers();
 
  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  
  ScriptApp.newTrigger("processSpreadsheetForNewSubmissions")
  .timeBased()
  .everyMinutes(1)
  .create();  
}

/**
 * Main function that searches the spreadsheet for row that haven't been processed, and sends emails.
 */
function processSpreadsheetForNewSubmissions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0]; // gets the first and only sheet
  var column = SENT_COLUMN;
  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];
      processSubmission(row, sheet);
    }
  }
}

/**
 * Helper function to find rows, which haven't been processed.
 */
function findIndex(columnValues) {
  var rowNum = [];
  for (var i = 0; i < columnValues.length; i++) {
    if (columnValues[i] != 'sent' && columnValues[i] != 'error') {   
      rowNum.push(i + 2);
    }
  }
  return rowNum;
};
 
/**
 * Helper function for processing the submission in the given row
 */
function processSubmission(row, s) {  
  try {           
    var columns = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
    var values = s.getRange(row,1,1,s.getLastColumn() - 1).getValues()[0];    
    
    // Compose the message
    var message = "";
    var htmlMessage = "";    
    var name = "";
    var emailFrom = "";
    
    // Only include form fields that are not blank
    for ( var keys in columns ) {
      var key = columns[keys];
      if (key && values[keys] && (values[keys] != "") ) {
        message += key + ' :: '+ values[keys] + "\n\n"; 
        htmlMessage += "<b>" + key + "</b><br/>" + values[keys] + "<br/><br/>";
        if (key == NAME_COLUMN_NAME) {
          name = values[keys];
        } 
        if (key == EMAIL_COLUMN_NAME) {
          emailFrom = values[keys];
        }
      }
    }
            
    var result = sendEmail(!DEV_MODE ? EMAIL_TO : SUPPORT_EMAIL, emailFrom, name, SUBJECT, message, htmlMessage);
    
    // Mark the row as processed
    s.getRange(row,SENT_COLUMN,1, 1).setValues([[result]]);
  } catch (e) {
    reportError(e.toString());
  }   
}

function sendEmail(toEmail, fromEmail, fromName, subject, message, htmlMessage) {
  var url = "https://api.sendgrid.com/api/mail.send.json";  
  var params = {
    "from": fromEmail,
    "fromname": fromName,
    "to": toEmail,
    "subject": subject,
    "replyto": fromEmail,    
    "text": message,
    "html": htmlMessage
  };
  
  var options = {
    'method': 'post',
    'payload': params,
    'headers': {
      'Authorization': 'Bearer ' + SENDGRID_KEY
    }
  };

  try {
    var response = JSON.parse(UrlFetchApp.fetch(url, options));
    if (response && response.message == "success") {
      return 'sent';
    } else {
      reportError("Invalid response: " + JSON.stringify(response));
      return 'error';
    }    
  } catch (e) {
    reportError(e.toString());
    return 'error';
  }  
}

/**
 * Helper function to report error;
 */
function reportError(message) {
  MailApp.sendEmail(SUPPORT_EMAIL, "Error Occured - " + SUBJECT, message);
}

An example Apps Script for customizing Squarespace form submissions using SendGrid. See more details: https://medium.com/@tokudu/how-to-customize-squarespace-form-submission-emails-using-apps-script-and-sendgrid-740480db99b8