Instantly share code, notes, and snippets.

Embed
What would you like to do?
Transform Squarespace leads in Google Sheets to TripleSeat form leads.
/**
* 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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment