Example of External Fields - Google Spreadsheet

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.


   Essential         
   Professional         
   Enterprise         
   ReportR     

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
  2. Example for multiple sheets

1. Example for Individual Sheet

First, create the Google Spreadsheet Fields. Integrate your Google Sheets to push the Responses to a Google Sheet.

Example of External Fields - Google Spreadsheet-Google Sheet responses

Once you have collected your responses, create separate sheets for your calculations.

Example of External Fields - Google Spreadsheet-Create new sheet

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.

Example of External Fields - Google Spreadsheet-calculations for spreadsheet

This is what you'll see on your Spreadsheets.

Example of External Fields - Google Spreadsheetaverage result on spreadsheet

Start creating your External Fields to calculate the average results of your responses.

Example of External Fields - Google Spreadsheet-external fields average custom score

Once you have set up your External fields, you can run a test to check the data connection.

Example of External Fields - Google Spreadsheet-Test run on external fields

Now you can go back to your PDF report and use the External fields variables you have created.

Example of External Fields - Google Spreadsheet-external fields variable in pdf

This is how *|EXTERNAL_FIELD_AVERAGETOTALCUSTOMSCORE|* will look:

Example of External Fields - Google Spreadsheet-example gauge chart for external fields

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.

Google Spreadsheets - example of responses

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).

Example of External Fields - Google Spreadsheet-calculation for email

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

Example of External Fields - Google Spreadsheet-sheet 1 email examples

The calculation for the first total custom score (to be pushed from Sheet1, each time the first survey is being completed).

Example of External Fields - Google Spreadsheet-custom score calculation for sheets

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

Example of External Fields - Google Spreadsheet-Sheet 1 - total custom score example

The calculation for the second total custom score (to be pushed from Sheet2, each time the second survey is completed).

Example of External Fields - Google Spreadsheet-calculation for second custom score

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

Example of External Fields - Google Spreadsheet-Sheet 2 - total custom score example

The calculation for the difference between the first and second survey (to show improvement).

Example of External Fields - Google Spreadsheet-improvement calculation

Make sure to apply the same Formula across all rows, as explained in the GIF below:

Example of External Fields - Google Spreadsheet-GIF - formulas for all cells

After you set up your Calculations, let's start setting up the External Fields

Example of External Fields - Google Spreadsheet-External Fields - example 2

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).

Example of External Fields - Google Spreadsheet-cell D values

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.
S
Support is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.