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:
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.
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
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.
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:
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:
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:
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.