Pushing Google Sheets Data to Salesforce Through FormAssembly Forms

Do you need to be able to get data collected in Google Sheets into your Salesforce instance? With FormAssembly, you can send data to Google Sheets where multiple people on your team can access it, but you can also streamline the process of sending that Google Sheets data to Salesforce, using FormAssembly, Google Sheets formulas, and prefill links. This tutorial will show you how to accomplish this.

The traditional method to get Google Sheets data into Salesforce would be to use Data Loader, but this comes with downsides. Data Loader does not allow you to push data to multiple standard and custom objects, and you cannot dedupe or perform lookups.

In this post we’ll look at a few things:

• How to utilize FormAssembly forms to send data through the Google Sheet connector to a shared Google Sheet
• Utilizing Google Sheet formulas and URLs to prefill FormAssembly forms
• Submitting data sourced from a Google Sheet into any Salesforce object and field with a Salesforce submit connector on our form

This will require the following knowledge:

• Building and prefilling FormAssembly forms through a URL
• Setting up a Salesforce Connector on your FormAssembly form
• Google Sheets and Salesforce administrative access

We will have two forms, and two Google Sheets through this use case. Our shared form and shared Google Sheet, then our transition form and transition Google Sheet.

In this use case, we’re going to essentially use formulas in our Google Sheet to build a URL that will combine the data we copy from a shared Google Sheet, placed by our shared form, into a prefilled form link. We’re building a dynamic form link in Google Sheets that leads to a FormAssembly form with a Salesforce connector on it.

It will require some initial setup in Google Sheets but once we have one row in place for our transition, we can copy it to the rest with a simple drag and drop routine method.

First, we’ll set up our shared form so we have the data we’ll be sharing pushing to our first Google Sheet, which is also shared. Well call these our “shared form” and “shared Google Sheet” during this tutorial. Check out our form example below, which asks for some basic contact information, for an idea on how to start a test.

Then configure the Google connector and share that Google Sheet you’ve either created or selected as needed with whomever you’re planning on sharing submissions to this form with. This will be our main shared data channel that all parties will pull from in future steps.

Next, confirm that our shared form is set up and the shared Google Sheet it’s pushing data to is in place and taking data by testing it.

Once we have our shared form and shared Google Sheet in place, we’ll want to create our transition form. This form will be prefilled and accessed with a URL built from our next Google Sheet, the transition Google Sheet. We will need the aliases for the fields from this transition form to build our transition Google Sheet so it must come first.

As you can partially see in the image below, this form will have the same fields as the previous form, but it has a different title, and we will set up different connectors for it.

Next, set up the Salesforce connector for this form however you would want new leads to come into your Salesforce instance, as we will use the prefilled data from the Google Sheet to populate it and send to Salesforce when we submit. In this example, we are adding a Salesforce Submit Connector to the “After Form Submitted” connector section, and configuring it to create a new lead upon form submission.

After our transition form is set up to push our shared Google Sheet data to Salesforce, we need to create the second Google Sheet that is our transition sheet. This transition sheet will be the middle-sheet between the shared Google Sheet and our second transition form that pushes the shared data to our Salesforce system. It will contain the complex formulas and mapping to prefill our transition form through a hyperlink that will submit data to Salesforce. The pictures below will show you the columns and the data values we will put within them for this example.

We’ll have a few different types of headers for our columns in this sheet, and it’s important for us to identify them correctly so the cells and data are easier to use when combining into a prefilled form URL. In the pictures below, our shared data resides in the blue columns, our columns holding and combining all the form URL and sheet information is in green, and our column holding the aliases for the form that we’re prefilling information into are in gray.

First, we’ll set up a section of column headers for our data from our shared Google Sheet.

After the column headers for the shared data are set up, we’ll place the column headers for our master prefill form URL, the form URL itself with a ‘?’ at the end to start the query of a parameter, and lastly a column for the ampersand that we can use to place the ‘&’ value into our master prefill form URL between each field we’re prefilling and parameter we’re filling it with.

Lastly, we’ll set up our columns to hold the form aliases for the fields that we’re pushing all of this data into. This will allow us to tell the master prefill column, building our URL, what fields to push the shared data to. It’s going to build us the prefill URL link with a query set to these aliases, and parameters of the shared data. You can find these aliases by following the link at the bottom of the Notifications page for your form.

Using a formula similar to the one below, you can map the other columns into the prefilled transition form column to build a full prefilled form URL. Here is an example using the field names instead of cell labels that will help clarify how the prefilled URL will be structured. Replace the values below with the respective cells in your transition sheet.

=HYPERLINK(CONCATENATE(FORMurlHEREwww.tfaforms.net/1234?,FIRSTNAME,ALIAS,AMPERSANDCOLUMN,LASTNAME,ALIAS,AMPERSANDCOLUMN,EMAIL,ALIAS,AMPERSANDCOLUMN,COMPANY,ALIAS,AMPERSANDCOLUMN,COUNTRY,ALIAS,AMPERSANDCOLUMN,ADDRESS,ALIAS,AMPERSANDCOLUMN,CITY,ALIAS,AMPERSANDCOLUMN,STATE,Alias,AMPERSANDCOLUMN,ZIP,Alias,AMPERSANDCOLUMN,PHONE,ALIAS,AMPERSANDCOLUMN,INDUSTRY,ALIAS,AMPERSANDCOLUMN,LEADSOURCE,ALIAS))

Once you have your columns properly in place and your formulas building your prefilled URL to your transition form. Give it a test with your test entry as pictured below.

Now moving forward, all you need to do is access the shared Google Sheet, copy the rows and paste them into your transition sheet. Once you have loaded the left-most columns for your recipient data from the shared sheet, you can then drag down previous rows to complete the rest of the columns that will use that copied data and produce your prefilled transition form link.

Want more Salesforce-FormAssembly tutorials? Check out this one on how to set up a multiple criteria search of Salesforce using FormAssembly.