Having a form in your website or sending it to your prospects by e-mail is a very good way to start communicating with them and also get some extra information from the form. There are two ways to achieve this, the easiest is through zapier integration and the second one which would involve a bit of technical and programming skills through webhooks. In case you or anybody in your team is not tech savvy enough, we strongly recommend you to consider zapier first since they also have a free plan for less than a 100 zaps per month.

The first thing you need is of course your Google form as well as the Google Spreadsheet where you are getting the responses.

In the google spreadsheet go to Tools > Script editor:

Now you need to write a simple script composed mainly of two parts. In the first part of the script you need to define:

  1. First you need to name the script, for this example we'll go for the default one "mainscript". Specify the spreadsheet where the information is going to be taken from. To achieve this you need the open by ID command, this way SpreadsheetApp.openById('spreadsheetId') and replace the spreadsheetId for the actual ID you find in the URL of the spreadsheet (the ID is not the whole spreadsheet URL but the value between '/d/' and '/edit').

2. The sheet in the spreadsheet needs to be activated by the command activate command as follows SpreadsheetApp.getActiveSpreadsheet().

3. The range where to look for the information with the following command sheet.getDataRange() (please keep in mind in the previous command, sheet stands for a variable defining the active spreadsheet from the previous point).

4. Retrieve the values allocated in a certain range with the command rangeName.getValues() (Please keep in mind that rangeName is a variable assigned after executing the previous command).

5. The next step is to iterate through the values in the range with the following command for (var row = 0; row < data.length; row++) {. While doing so, you can assign the values to variables that will be used later on as payload like this for example var Email = (data[row][2]). This last command assigns a value taken from a cell (in this case the row 0 which would correspond to the column A in the spreadsheet, to a variable that we are naming Email in this case. You need to do the same for all the data you need in the CRM, for example if you need all the values from column A to D, you'd need to define all the variables as row[0] to row[4].

In the second part of the script, you need to:

  1. Define the information that you need to pass to the system and include it into the request. The best way to do it is to create a variable that you can call for example data and include all the variables created in the first part of the script.
  2. You need to make an HTTP request to the Webhook URL that is generated at the beginning as the trigger, specifying the kind request and the data to be included in it.

Once your script is ready, you need to set up a trigger for it. You can do it from Edit > Current project's triggers

Click on Add trigger

Select the function you have just created (the default one is mainscript) and Select the event type as on form submit

And that should do it! All the information included in the form can be used and included in the record:

Haven’t found the answers you’re looking for? Ask our User Community.

Did this answer your question?