Update a SharpSpring checkbox lead field without overwriting existing values

The checkbox lead field in my opinion is one of the most useful fields when it comes to segmentation.

However when using a Zapier to apply a new value to a SharpSpring checkbox group field, it overwrites the existing values with the new incoming data.

This technique uses Zapier and Google Sheets.

The problem

List segmentation and SharpSpring dynamic emails won’t work

If you have a rules-based list that only adds a contact to that list when criteria is met, then the example demonstrated below will remove them out of that list because the criteria is not met by the lead. This can disrupt audience segmentation and could also send the default option of dynamic email content to leads.

Example of SharpSpring checkbox values being overwritten using Zapier

The solution

The only way to add an additional value to a checkbox group field in SharpSpring without overwriting the existing values, is to comma separate all previous values with the new value as a single string – using that string as the input variable in Zapier.

We’re still « overwriting » the checkbox group field with values, but we’re now overwriting it with a merge of the existing values and new values.

We use a Google Sheets formula to group  comma separate the values into a single cell. We then run another Zap to get the « new/updated » Google Sheets row and import this into SharpSpring.

Step 1: Create a Google Sheet

We use a spreadsheet because we can run a formula to list all unique emails into a column, and apply all comma separated multiple values in another column.

Use the « when a row is updated » as a trigger in Zapier, so each time a new value is added, the row updates and now the column data is made available is Zapier.

The technique below only updates 1 SharpSpring checkbox-group custom field

Create your own Google Sheet

Make 2 sheets in a single spreadsheet and add the specific column headers – copy what I have done below:

1 – Data sheet

COLA: Email

COLB: Checkbox Values

2 – Trigger sheet

COLA: Email

COLB: Checkbox Values

Make a copy of my Google Sheet

This will open a new tab where you can save a copy of my sheet in your Google Drive.

Add some formulas to the trigger sheet

Copy the following formula and insert this into cell A2 on the trigger sheet.

=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)), ", ", ","))

This formula will copy data from the [data] sheet and insert it into the [trigger] sheet.

Step 2: Setup the automation

Set up a new trigger step in Zapier

Automation requires a trigger to start, and the trigger used in this automation is “when a new CATCH hook has received data“.

Add your own trigger step:

This could be a CATCH Hook, a new WooCommerce Order or a SharpSpring form for example.

You can copy my Zap by clicking the button below.

Set up the Google Sheet action step to add a new row

Locate the [data] Google Sheet from your spreadsheet in your drive. The available columns will appear and you can choose what data you want to add.

Map the fields from your trigger step and insert them into the Google Sheet fields, then hit [test and review].

Go to your Google Sheet

See how the [Email] and [Value] are inserted into a new row on the [data] sheet.

You’ll notice there’s already some data in ROW 1. This is an example of this process having already happened. If you have a data sheet, you should insert your data into the [data] sheet and comma separate your values into COLB. Do this before enabling your Zap, otherwise each row of the [trigger] sheet will run in Zapier.

Check out the trigger sheet

See how the [Checkbox Values] column have grouped the individual rows in [data] by [email] into a single cell. Pretty neat don’t you think.

Step 3: Finish the automation

This is the final step to update a lead in SharpSpring with multiple checkbox values.

Set up another trigger step in Zapier

This step triggers when a row in the [trigger] sheet is added or updated. « Updated » is generally what we’ll be using to update a checkbox group with new values.

You can copy my Zap by clicking the button below.

Set up the action step in Zapier to Update a lead in SharpSpring

Insert the {Checkbox Values} data from Google Sheets

Once you’ve inserted the email address and checkbox values from Google Sheets into the Zapier step, hit [test and run].

This will send the field to SharpSpring and update the lead.

Before

A single value is set in the checkbox group custom field for the lead in SharpSpring.

After

The checkbox field is updated with both Value 01 & Value 02. Job done!

Summary

By utilising the power of other cloud-based apps and connecting SharpSpring to these apps via Zapier, we can create complex automations that fire in seconds – this simply isn’t possible to be done by a human at the same speed.