Zapier: Automatically Collecting Challenge Responses In Google Sheets

This article will go through the process of linking challenge responses to a Google Sheet using Zapier. After completing these steps, you will be able to automatically collect all responses to any challenges in a table within Google Sheets.

Setting up the Spreadsheet

Create a new spreadsheet in Google Sheets and give it a relevant name. I'll use "Riddle Responses" for this example. Next, create column headings that represent the data you want to track. Make sure that you start from the first column in the top left. To keep things simple, for this example, I'll use Advocate First Name, Advocate Last Name, Response.  

Setting up the Zap 

• In a new tab or window, go to Zapier, and click on Make a Zap.
• On the left side, first Name your Zap and press Enter to save.
• Next, on the left side, under 1. Set up this step click on Choose App. Select Webhooks under Built-In Apps from the list that appears. Select Catch Hook in the pane that appears and click on Save + Continue.
• Click on Set Up Webhook on the left side, copy the URL to clipboard and click Continue.

• Switch back to your AdvocateHub window / tab. 
• Navigate to your Hub's Webhook configuration page: Settings > System > Webhooks.
• Click on the Add a Webhook button.
• Give your webhook a relevant name. I'll use, "Riddle Responses to Spreadsheet" for this webhook. 
• In the Webhook Provider URL field, paste the link from Zapier. Leave the Format as "Form Post"
• Scroll down to the list of events and check the box beside Stage Completed and whatever action is appropriate to your challenge e.g. Questions Answered. Then, scroll all the way down and click Save.
• Next, head back to your Zapier account and click on Edit Options on the left side under “1. Catch Hook”. Click Continue to move through this step as we won’t be editing this.
• We will now be in the Test this Step section. Go back to your hub and submit a test response to any challenge that would trigger a zap - in this example, we need to complete a challenge and answer questions as per our webhook settings in the hub. 

• Once you have done this in the hub, go back to Zapier and click on OK, I did this. It will take a few minutes for the test to work. If all is set up correctly, you will see a Test Successful green bar. Click on Continue. 

Customizing the Zap to trigger for specific Challenges

This section will walk you through creating a filter for this type of Zap. The use cases for this will allow you to keep track of specific responses based on challenge responses that are important to track.

• Click on the + symbol under Step 1 and select Filter:

• In the next window, click Save + Continue under Only continue if...

• Next, we will need to define the Filters. In the first box, click on the drop-down arrow and choose Catch Hook. Scroll down to find the Challenge Name option and click this. In the next box, click the drop-down arrow again and choose the option (Text) Contains. In the last box, copy and paste the name of the challenge from the hub. You can find the Challenge Name field by editing the challenge in the hub and the challenge name will appear in the first step. 
• Your filters should look like this:

• Click Continue to move to the next step.
• To make sure that this has been set up correctly, test this by submitting a response to this challenge in the hub. Once you have submitted a response, click on Test Filter. You should see a green status that shows that it has worked.

Connecting your Google account 

• Click on 3. Set up this step under Action on the left-hand side to see the drop-down menu:

• Click on Choose App in the drop-down menu and choose Google Sheets.
• In the next window, select Create Spreadsheet Row, then click Save + Continue.
• If you don’t already have a linked Google Sheets account, click on Connect a New Account. On the pop-up that appears, login to your Google account and then hit Accept to give Zapier access to your Google Sheets account. Click Save + Continue to move to the next step.
• Select the spreadsheet and specific worksheet you created earlier from the dropdown menu. The columns you entered should auto-populate on the page:

• For each of the fields listed (in this case, advocatefirstname, advocatelastname, response), we will need to choose a source from the hub. To do this, click on the icon to the right of the field and in the drop-down menu, choose the appropriate field that will be auto-populated based on the test response that you submitted. For example, for advocatefirstname, I will choose Contact[first Name]:

• Complete these steps for all relevant fields. For responses to questions, you will need to choose the Parameters[ Response] options and then choose which answer you wish to capture in your spreadsheet. You can always add more rows to your spreadsheet to capture answers to more than one question. Once you have done this, click on Continue.
• Let’s Test this Step! You will see a preview of the fields that will be mapped based on your test Zap. If everything looks good, click Create & Continue. If the test has been successful, you will see a green status that says Test Successful! Have a look at your Google sheet to make sure that it looks ok.
• The last step is to click on Finish and then turn on your Zap!