Scroll to top

Naming opportunity with incrementing number from a SharpSpring form

If you require opportunities to be created from a specific form, that are then inserted into a specific pipeline and named with an incrementing number, you’re at the right place. This technique uses Zapier and Google Sheets.

This task was presented to me from a client who required a 3 digit incrementing number to be added to the start of every opportunity name in a specific pipeline, for example: [001] Company Name, [002] Company Name, [003] Company Name

Step 1: Create a Google Sheet

Assuming you already have a SharpSpring form, the next thing to do is create a new Google Sheet. The reason we use a spreadsheet is because we can take a ROW number and use this to create the 3 digit ID required for the opportunity name.

Each time the SharpSpring form is completed, a new row is added with some data.

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.

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: Company name

2 – Trigger sheet

COLA: ID

COLB: Email

COLC: Company name

Click to enlarge

Add some formulas to the trigger sheet

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

=ARRAYFORMULA(data!A2:B)

This will copy the the row from the [data] sheet and insert them into [trigger] sheet.

Click to enlarge

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

=ARRAYFORMULA(IF(B2:B="",,TEXT(MINUS(ROW(data!A1:A),1), "000")))

This will populate column A with our ID number formatted in 3 digits, but only when the next 2 cells in COLA and COLB are populated.

Click to enlarge

Step 2: Setup the automation

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

Basically, anytime a SharpSpring form is completed, a Visual Workflow in SharpSpring posts back the lead info (the lead who submitted the form) to a webhook URL – we’ll set this up later.

Make a copy of my Zap in your own Zapier account

You can copy my Zap by clicking the button below. You will just have to configure each step assigning the Google Sheet and it’s columns.

You will also have to ‘Create a SharpSpring Visual Workflow” manually.

Create your own – set up the Catch Hook trigger in Zapier

The App is called “Webhook by Zapier”.

The CATCH Hook makes the individual fields of a lead available in Zapier, which we can pick and use in the next steps of the Zap.

Copy the Webhook URL – we need this in the next step.

Click to enlarge

Create a SharpSpring Visual Workflow

In SharpSpring, create a Visual Workflow that triggers each time the [specific form] is completed.

The action is post back lead information to the URL [Webhook URL].

Click to enlarge

Send test data to Zapier by completing the SharpSpring form

Complete the SharpSpring form and head back to Zapier to see the new request.

Click to enlarge

Set up the Google Sheet step to add a new row for each webhook request

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

Follow the steps and hit [test and review].

Head back to your Google Sheet, data sheet and see the new row added with an email address and company name.

Click to enlarge

Add a lookup step to get row data from your Google Sheet

We get the row by searching for an [email address] in the email column of our [trigger] sheet.

We insert the [email address] from the original webhook trigger.

I have also added a delay step in below the lookup of 2 minutes. This allows the formula in Google Sheets to run, otherwise the speed of the Zap might be too fast for the formula to finish in time.

Click to enlarge

Test and review, and you’ll see the output produce the 3 required columns plus some others: ID, Email, Company

Click to enlarge

Step 3: Finish the automation

The final step of the Zap is to create an opportunity in your specific pipeline stage. We can define the exact stage and other details here.

We take the output fields from the lookup step previously and use these to craft our opportunity name. You can insert text and fields to create your format: [{ID}] – {company}

It starts with [000] because this is the first test and setup run. The next run will be a real form completion, and it will output [001], and so on..

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.