External fields allows you to pull in data from external data sources, such as a spreadsheet. Read the External fields guide to see how to create one.
Let's look at the examples of the Google Spreadsheet Integration we've created. This guide will take you through;
1. Example for Individual Sheet
First, create the Google Spreadsheet Fields. Integrate your Google Sheets to push the Responses to a Google Sheet.
Once you have collected your responses, create separate sheets for your calculations.
Add your calculations to your Spreadsheet. In this case, we are calculating the average results of your responses. Here's a help guide for additional functions.
This is what you'll see on your Spreadsheets.
Start creating your External Fields to calculate the average results of your responses.
Once you have set up your External fields, you can run a test to check the data connection.
Now you can go back to your PDF report and use the External fields variables you have created.
This is how *|EXTERNAL_FIELD_AVERAGETOTALCUSTOMSCORE|* will look:
2. Example for Multiple Sheets
Now let's look at a more complex example. In this case, we have created a language test that tests your knowledge before and after the language courses. For this, we will use two separate Responses: Sheet1 for before the language courses, and Sheet2 for after.
After you Integrate your Google Sheets to push the Responses to a Google Sheet, create a separate sheet for your calculations.
Add your calculations to your Spreadsheet. In this case, we are going to add calculations for Emails, Custom scores of surveys 1 and 2, and the Improvements.
The calculation for Emails (to be pushed from Sheet1, every time the survey is completed).
Sheet1!$C$2:$C$999 - Refers to the cell range you want it to choose from REF!,
Sheet1!$C$2:$C$999 - Refers to the cell values you want to return to the Calculations Sheet
The calculation for the first total custom score (to be pushed from Sheet1, each time the first survey is being completed).
Sheet1!$C$2:$AB$999 - Refers to the cell range you want it to choose from
Calculations!$A$2:$A$999 - Refers to the cell values you want to return to the Calculations Sheet
26- Refers to the hardcoded value for the Column number
The calculation for the second total custom score (to be pushed from Sheet2, each time the second survey is completed).
Sheet2!$C$2:$AB$999 - Refers to the cell range you want to choose from
$A$2:$A$999- Refers to the cell values you want to return to the Calculations
26- Refers to the hardcoded value for the Column number
The calculation for the difference between the first and second survey (to show improvement).
Make sure to apply the same Formula across all rows, as explained in the GIF below:
After you set up your Calculations, let's start setting up the External Fields.
In this example, we want to return the values from the Column D, after we have matched the filter value against the values found on Column A (Column A and D are set up by Cell range).
You can find the sheet in this example by clicking the link here.
What's next?
- Zapier integration - Zapier is a tool that enables the transfer of data from one web app to another one. By using Zapier in combination with Pointerpro, you can send the data collected within your survey to create contacts in Pointerpro or send data to apps such as Google Sheets, Mailchimp, Salesforce, Trello, and many more.
- Google Tag Manager is a tag system that Google has created that can be used for tracking and analytics on websites. It can be used to track respondents' behavior in your survey or to find out how effective an advert promoting your survey has been.
- Integromat integration - allows you to automate many processes and simplifies the transfer of information between apps, saving you bags of time. Integromat offers similar features to Zapier but is more powerful as you can set a single event to trigger a number of different actions to be taken simultaneously.