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

1.1 Create Google Spreadsheet Fields

1.2 Filter Sheet value

1.3 Compare Sheet value


1.1 Create Google Spreadsheet Fields

Integrate your Google Sheets to push the Responses to a Google Sheet.

Google Sheet responses



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

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.

calculations for spreadsheet


This is what you'll see on your Spreadsheets.


average result on spreadsheet



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

external fields average custom score



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

Test run on external fields



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

external fields variable in pdf


This is how *|EXTERNAL_FIELD_AVERAGETOTALCUSTOMSCORE|* will look;

example gauge chart for external fields


1.2 Filter Sheet Value

Filter Sheet Values allows you to check for a specific value in the range of cells you choose. Let's see an example;


Pick the range of cells you want to filter from

Google sheets field -  excel two columns


Enter your Cell values and the Filter value you want to filter.

Filter Sheet values example

tipTip: This feature is best set as a variable (for example, *|contact_email|* or *|f1_result|*), however it is possible to set it as a hardcoded value.


When you test your value, it should return the number of the last column you chose (in this case J)

test value


1.3 Compare Sheet value

Compare Sheet value allows you to look for conditions in the first column and return the condition in the second column. Possible conditions are smaller than (<), between (-), and greater than (>) on your Sheets. Let's see an example;


Pick the cell values you want to filter. (In this case; the column C values, will return a column D text)

excel column - compare sheet value


Enter your Cell values and the Filter value you want to filter.

Compare Sheet value - enter settings

tipTip: This feature is best set as a variable (for example, *|contact_email|* or *|f1_result|*), however it is possible to set it as a hardcoded value.


When you test your value, it should return the values of the column you chose (in this case text Medium score)

test value - compare sheets


You can go back to your PDF report and use the External fields values.


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

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

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

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

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

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

Sheet 2 - total custom score example



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

improvement calculation



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

GIF - formulas for all cells



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

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

cell D values


You can read our guide on External Fields to learn more about it. Good luck!


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.