Squarespace Forms: Splitting Names into First and Last

The Squarespace Form Block is a great way to collect data from customers easily and with nice-looking forms. Squarespace has a native MailChimp integration, but sometimes you want your data to connect with other services, like Active Campaign, Zapier, or ConvertKit. 

Google Sheets As a Form Processor

Squarespace Forms integrates with Google Sheets, allowing you to send data directly into a spreadsheet:

Squarespace Forms automatically record the Submitted Date and completed fields into a Google Sheet

Not All Fields Are Created Equally

Squarespace's Name Field collects both the First and Last Names and stores them as a single value. There are circumstances when you may need the First Name and Last Name separated. In some Mailing List services, like Active Campaign, you also need the fields in a particular order. 

Active Campaign requires the Email Address to be in the first column, followed by the First Name, then Last Name.

ArrayFormula to the Rescue

You can automate the process of data manipulation, moving the Email field, or splitting the Name field, using Google's ArrayFormula function. 

In my example, I'm using a Google Sheet named: Funnels

 Google Sheet integration in Squarespace Form Block

Google Sheet integration in Squarespace Form Block

The first worksheet in the Funnels Sheet is named SQS and stores the Squarespace-provided data

Add a second worksheet in the Funnels Sheet

I named my worksheet AC and it's where I'll manipulate the data for Active Campaign.

Type the Field Names into Row 1

Tell Google to grab the Email Address whenever a new row is inserted on the SQS worksheet

Click on Cell A2 and enter this equation:

=ARRAYFORMULA(IF(SQS!C2:C="",,SQS!C2:C))

Click in Cell A2 and enter the ArrayFormula equation

Tell Google to calculate the "First" Name whenever a new row is inserted on the SQS worksheet

Click on Cell B2 and enter this equation:

=ARRAYFORMULA(IF(SQS!D2:D="",,LEFT(SQS!B2:B,FIND(" ",SQS!B2:B,1)-1)))

Click in Cell B2 and enter the ArrayFormula equation

Tell Google to calculate the "Last" Name whenever a new row is inserted on the SQS worksheet

Click on Cell C2 and enter this equation:

=ARRAYFORMULA(IF(SQS!D2:D="",,RIGHT(SQS!B2:B,LEN(SQS!B2:B)-FIND(" ",SQS!B2:B,1))))

Click in Cell C2 and enter the ArrayFormula equation

Final Notes

Now, whenever the Squarespace adds a New Row with the completed fields to the SQS worksheet, the ArrayFormulas on the second worksheet will automatically update with the new data. When you are importing this data into Zapier, Active Campaign, or another service, be sure to specify the "new" (second) worksheet with the correctly calculated data.