Scroll to top

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

Before

Lead with existing checkbox group values

Click to enlarge
Zapier action

Update a SharpSpring lead with Zapier

Click to enlarge
Result

Lead checkbox group is overwritten

Click to enlarge

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.

Before

Lead with existing checkbox group values

Click to enlarge
Zapier action

Update a SharpSpring lead with Zapier using a Google Sheets formula

Click to enlarge
Result

Lead checkbox group is updated

Click to enlarge

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

Click to enlarge

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.

Click to enlarge

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

Click to enlarge
Click to enlarge

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.

Click to enlarge

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.

Click to enlarge

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.

Click to enlarge

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

Click to enlarge

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.

Click to enlarge

Before

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

Click to enlarge

After

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

Click to enlarge

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.

Enquire about marketing automation

Our team of data specialists, stategists and designers are here to help your business thrive in the digital world.

Author avatar
Harry Norman
Creative Technologist and SharpSpring Technician at Merci.

Let’s collaborate

Got a project?

Merci is a innovative full service digital marketing agency.

Come and meet our teams in Bourg-Saint-Maurice or Brighton.