Example of Creating 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.


Let's look at the example of the Google Spreadsheet Field we've created.


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



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



This is what you'll see on your Spreadsheets.

excel view of average result


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

external fields variable in pdf


2 Filter Sheet Values and Compare Sheet Values

2.1 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


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

test value


2.2 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


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

test value - compare sheets


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.


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



V
Vincent 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.