Transform Squarespace leads in Google Sheets to TripleSeat form leads.

postLead.js

/**
* Post SquareSpace form entry to TripleSeat Form API.
*
* 0a. SquareSpace is connected to the SquareSpace form sheet.
* 0b. When a user makes a form entry in SquareSpace it is sent to a Google Sheet
*
* 1. This is a special function that looks for rows in this GoogleSheet that have not been "sent". 
* 2. This function then sends a POST request with the form data to TripleSeat. 
* 3. Then this function updates "TripleSeat Status" field to "Sent" in each row that was sent to TripleSeat.
* 4. Create a trigger in this project to run this function every 5 minutes, add yourself for script failure notifications
*/

function postLead() {

  // Get leadform and key from TripleSeat
  var formURL = "http://api.tripleseat.com/v1/leads/create.js?lead_form_id=1234&public_key=1a2b3c4d5e6f7g8h"
  
  // Assign SquareSpace fields to TripleSeat fields here. Any fields not listed here will be put to notes. 
  var thKeys = {
   // Squarespace field : Tripleseat field
   "company_name"       : "company",
   "Email        "      : "email_address",
   "Event Details"      : "event_description",
   "Attendance Number"  : "guest_count",
   "Phone"              : "phone_number",
   "Event date"         : "event_date",
   "Name"               : "Name",
 };

  // Get data rows, or leads, from googlesheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var leadList = ss.getSheetByName("Leads");
  
  var leads = leadList.getSheetValues(1,1,leadList.getLastRow(),leadList.getLastColumn());   // Get array of lead status to find which leads need to be "Sent"

  var headers = leads.splice(0,1)[0];  // Remove the header column from leads list
  
  var statusIndex = headers.indexOf('Tripleseat Status');
  
  if (leads[leads.length-1][statusIndex] == "Sent") { // End script if last row has already been sent.
   Logger.log( "No leads to send.");
   return;
  }

  // Reverse look through leads find the index of the last lead sent.
  var indexOfLastSent;
  
  for (i = leads.length-1; i >= 0; i--) {
    indexOfLastSent = i;
    if (leads[i][statusIndex] == "Sent"){
      break;
    }
  }
  
  leads.splice(0, indexOfLastSent+1); // Delete leads that have been sent - splice them out

  // Make a POST request with a JSON payload.
  // Get Lead data into an array

  var spaceIndex;       // where to split the name field
  var leadData    = []; // Holds leads formatted for sending to TripleSeat
  var lead        = {}; // Holds individual lead data for formatting
  var notesText   = ""; // note text in a style similar to SquareSpace lead received email
  var currentTime = new Date();

  for (var row=0; row<leads.length;row++){ // iterate through rows

  for (var item=0; item<leads[row].length-1;item++){ // interate through items in row


  if (leads[row][item].toString() != "" && item > 0){ // Don't add blank fields to notes. Don't add "submission_Date" to notes
    notesText += headers[item] + ": " + isDate(leads[row][item]) + "\n"; // Format lead item for the Additional Notes" field
  }

  if (headers[item] in thKeys) {
        lead[ thKeys[headers[item]] ] = leads[row][item]; // match header with row item
      } 
    }
    
    // Fix name field, Squarespace name field sucks.... 
    // Squarespace combines the name into one, even though it asks for First and Last name. 
    spaceIndex = lead['Name'].indexOf(" ");
    lead['first_name'] = lead['Name'].substr(0,spaceIndex);
    lead['last_name'] = lead['Name'].substr(spaceIndex+1);
    
    // Tripleseat rejects dates before today's date - trying to make an event in the past
    // Catch those dates and set to 1 day in the future
    if ( lead['event_date'] < currentTime ) { lead['event_date'] = currentTime; } 

    lead["additional_information"] = notesText; 
    
    leadData.push(lead); 

    // reset for next row
    lead = {};
    notesText = "";
  }
  
  // Add back a row for headers, add back row to be on next row, unknown 3rd add
  indexOfLastSent += 3; 

  for (var row = 0; row<leadData.length; row++) {
    Logger.log(leadData[row].Name);
    var options = {
      'method' : 'post',
      'contentType': 'application/json',
      // Convert the JavaScript object to a JSON string.
      'payload' : JSON.stringify(leadData[row])
    };

    var response = UrlFetchApp.fetch(formURL, options);
    Logger.log(response.getContentText());
    
    if(response.getResponseCode() == 200) {
      leadList.getRange(indexOfLastSent, statusIndex+1).setValue("Sent");
    }
    indexOfLastSent++;
  }
  return;
}

// To convert 1pm from Squarespace to 1pm in Tripleseat - check if value is a date and if it is - format date.
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}

function isDate(sDate) {
  if (isValidDate(sDate)) {
    sDate = Utilities.formatDate(new Date(sDate), "PST", "HH:mm a");
  }
  return sDate;
}