,
The SharpSpring Checkbox Group lead field in our opinion is one of the most powerful custom fields when it comes to segmentation. Useful for business that host events or offer multiple services, the Checkbox Group in SharpSpring allows a business to segment their audiences on one or more options. Updating a Checkbox Group use Zapier or custom API can lead to issues with overwriting the existing data. Here’s our workaround to fixing this issue using Zapier and Google Sheets.
Our workaround: Store the Checkbox Group data in Google Sheets, use a formula to combine the data by Email Address, and then import the combined values as one item into SharpSpring
Checkbox Group data is imported into the “data” sheet (when a SharpSpring form is submitted or imported via API), and then the “trigger” sheet groups the checkbox values by unique emails from the “data” sheet.
We triggered a zap to fire when a Google Sheet Row was updated
We utilised the Google Sheets Zapier trigger. It looks for changes in the Trigger sheet (rows), and we pick the data from Column B and insert that into the SharpSpring Checkbox Group field.
As you can see, we’re able to use Google Sheets to store and transform SharpSpring Checkbox Group data, and then utilise Zapier to send the combined data to SharpSpring and apply it to the contacts profile.
Overview
We used a combination of tools including Zapier and Google Sheets. We stored all the data for this particular field in a Google Sheet and imported any new data for this field into the same Sheet. Then a custom formula grouped the data by email address, and then we used this new data as the field to import into SharpSpring.
Why did we do it this way?
The only way to add an additional value to a checkbox group field in SharpSpring without overwriting the existing values is to append the new value with the existing values using a comma as the separator for each value. This creates a new variable like this “option 1, option 2, option 3”.
We’re still “overwriting” the checkbox group field with values, but we’re now overwriting it with a merge of any existing values and new values.
We used a Google Sheets formula to group all the items by a comma, using the email address as the field to group the items by. Then set up another Zap to get the “new/updated” Google Sheets row data and import this into SharpSpring. SharpSpring doesn’t support a Lookup function yet in Zapier, so we had to use Google Sheets.
Why did we use a Google Sheet?
Because we can run any formula we want to interrogate other sheets and output the requirement we need. Cloud-based tools like Google Sheets make it super easy to integrate with other cloud-based tools.
This Google Sheets formula combines multiple values (for one field only) together, grouped by the email addresses
=ARRAYFORMULA(REGEXREPLACE(UNIQUE(ARRAY_CONSTRAIN({Data!A2:A,
SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(Data!A2:A=TRANSPOSE(Data!A2:A), TRANSPOSE(Data!B2:B)&",", )),,ROWS(Data!A2:A))))&"|", ",|", )}, COUNTA(Data!A2:A), 3)), ", ", ","))
Add pre-existing data to the sheet first
We had existing data for the specific Checkbox Group field for a number of contacts in SharpSpring already. We had to run an export in SharpSpring and insert the data first before enabling any Zaps.
Set up the action step in Zapier to Update a lead in SharpSpring
We inserted the email address and checkbox values from the Google Sheet into the SharpSpring action step.
Summary
This workaround continues to be a key asset when working with Checkbox Group items that are updated externally via Zapier.
