How-to Automate a Google Sheets Lead Capture Form

One of the questions we often receive is how to automate lead capture using RPR Reports. Essentially, how can an agent create a process that uses an RPR report to generate leads? Although RPR doesn’t have a system to do this out of the box, with a little creativity, it’s possible with Google Forms, your own Gmail and Zapier (yes, the free plan).

This article will walk users through the step-by-step of creating a report, building a form with Google, and automating the follow-up with Zapier and Gmail. There will be a manual step of copying the report URL to Zapier, but the report can be used for multiple submissions. Let us explain…

Imagine offering your website visitors a branded Market Activity Report that showcases recent neighborhood activity like new homes, price changes, and open houses. Or perhaps offering visitors a detailed School Report for a local school. Or maybe even a Neighborhood Report highlighting the local area. It’s all possible with just a small amount of effort on your end.

For this example, let’s use the offer of a Market Activity Report that showcases neighborhood activity like new homes, price changes, and open houses. This strategy works well, given that one report can work for multiple people. The key is to create a new report each week, so the information is fresh.

This tutorial is divided into 3 parts:

1. Creating the Market Activity Report in RPR
2. Creating a Form with Google Forms
3. Connecting your Form to Zapier

Creating the Market Activity Report in RPR

1. Visit narrpr.com.
2. Select Research, select Residential Market Activity Search.
3. Enter a location for your search.
4. Select Change Type to include specific property status. Also set how far back to look for changes.
5. Select Filters to add additional search criteria.
6. Select Update and Search.
7. Select the shaded geography outline.
8. Click Create Market Activity Report.
9. From the Reports page, include or omit items from the report.
   1. Start with selecting the report elements.
   2. Confirm how far back the report should showcase activity changes.
   3. Select which property status to include.
   4. And what Property Types to include.
   5. Select how many properties the report should have per status.

Creating a Form with Google Forms

The first step will be to create a new form and then add questions.

Create Form

1. Visit forms.google.com, and click Blank.
2. Next, give the form a name. Click Untitled form and type a new name.
3. Directly under title, add the form description.
4. Select the color palette icon in the upper right side of the screen and set your options, including the header image, theme color, background color, and font style.

Add Questions

1. The first question is already added, select Untitled Question and type the question.
2. Change the question type by selecting the dropdown to the right.
3. Add response options (if applicable).
4. Toggle the Required switch to make a question mandatory.
5. To add more answers, choose Add Another.
6. To add a new question, click the + on the right.

Confirmation Message

1. Click Settings > Presentation.
2. Under Confirmation message, enter your text and click Save.

Send Results to a Spreadsheet

1. With your form open, at the top center, select Responses.
2. Now click on the three vertical dots.
3. Click Select response destination.
4. Select Create a new spreadsheet.
5. If you want to change the spreadsheet name, this is a great time. Highlight the current name and type over it. Remember this name, we will access the file in a later step.
6. Click Create.

Setting up the Trigger

1. Select the Make a Zap button.
2. Using the search bar at the top, type Sheets.
3. Select Google Sheets.
4. Select New Spreadsheet Row.
5. Advance to the next screen by selecting Save + Continue.

Now select Connect an Account.

1. A window from Google will appear. Log in with your Google username and password to authenticate/authorize the connection between that Google Sheets account and Zapier. Note: If you are already logged in to your Google account on your browser, you will be asked to approve access to Zapier.
2. Accept access to Zapier on Google Sheets.
3. After approving access, Google Sheets will now be connected.
4. Choose Save + Continue.
5. Now use the Spreadsheets dropdown to find the spreadsheet from step 4 of Send Results to a Spreadsheet above.
6. From the Worksheet dropdown select the worksheet. (The tab name in spreadsheet.)
7. Setting up the Action
8. Now search for Gmail.
9. Choose Send Email.
10. Advance by selecting Save + Continue.
11. Because you already authorized Google with Zapier, your Gmail account should be linked. If so, continue on to Step 7. If not go to the next step.
12. Select Connect an Account.
13. A window from Google will appear. Login with the Google username and password needed to authorize your Google email connection.
14. Accept access to Zapier on Gmail.
15. Select Save + Continue.

Setting up the Gmail Email

1. To: Here you will use the dropdown to select the column that is holding the email address from your form.
2. CC: If you want to have an email address copied on the email, enter it here.
3. BCC: If you want to have an email address blind copied on the email, enter it here.
4. From: Enter your Gmail email address.
5. From Name: Enter your name.
6. Reply To: Enter your Gmail address.
7. Subject: Enter your subject line.
8. Body Type: Choose the type of email – Plain, HTML.
9. Body: Write the template email here. Use the button on the right to add merge fields from your spreadsheet as needed.
10. Label/Mailbox:
11. Attachments: Enter the URL for your RPR Report Here.
12. Choose Continue.
13. Now select “Test this step” to test email.

TIP: Update your report weekly to make sure you have fresh data. Then update the URL in step 11 above with the new report.