External Fields

External fields allows you to pull in data from external data sources, such as a spreadsheet. You can read data from a data source outside the software and pull it back into Pointerpro.


   Essential         
   Professional         
   Enterprise         
   ReportR     

note
NOTE


What can this feature be used for?

  • To read data from a data source outside the software, for example, cell values from a Google Sheet
  • Use these values in variable data in the PDF report or in the formulas using all responses. For example, to calculate a score on the outcome screen, if the respondent scored 5/10 in a particular survey category, you can match that score against an external data value of 7 which is the benchmark score set for that category.
  • A more advanced use would be to use the Google sheet as a bi-directional database, whereby the same survey will also send data to the Google Sheet. This would allow the benchmark cell value referenced to be adjusted automatically as new responses are received.



This guide will teach you:

  1. Adding External Fields 
  2. Plain field
  3. Spreadsheets 
  4. API

1. Adding External Fields

Click on the 3 dots at the top right-hand corner of the screen. Select External Fields.

External fields- button

Click the left-hand button Add field and you'll see the following sections to complete:

External fields -options

  1. Name - Type the name that you want to call the field. This is the name that will be given to the variable, so choose a name that will allow you to easily recall what the field refers to.
  2. Type - Here's where you can choose the data source. You have 3 options, which we'll discuss below.
  3. Data freshness - choose how often you want to refresh the data. Bear in mind that more frequent updates may slow performance.

Now let's see how to configure the data source. You'll have different options depending on the type of data that you selected as your source: Plain, Spreadsheet and Custom API

2. Plain field

You simply need to enter a hard-coded value.

3. Spreadsheets

3.1 Filter Sheet Value

3.2 Compare Sheet value


The Google Spreadsheets type allows you to pull data from your Google Sheets, and/or into your Google Sheets. Read our Example of creating Google Spreadsheet fields to see a more detailed step-by-step guide.

External Fields- spreadsheets

1. Spreadsheet ID - The value between the "/d/" and the "/edit" in the Spreadsheet URL.External Fields- spreadsheets2. Cell - Enter the cell or range of cells where the value should be extracted from. First, enter the sheet name, followed by an exclamation point (!). Then, enter the cell names, putting a colon (:) in between your cell names. 

External Fields- spreadsheets

warning
WARNING


Don't forget to share the spreadsheet with the user sheets@sa-api-prod.iam.gserviceaccount.com



External Fields- spreadsheets

tip
TIP


You can use the Google Sheets Integration to push the responses to a Google Sheet.



External Fields- spreadsheets

  1. Filter Sheet Values - this feature will check for similar values in the column and then return a comma delimited text for any matches. Make sure to specify the range of cells for the filter.
  2. Compare Sheet Values - this feature will look for conditions in the first column and return the value of the second column if the condition is true. Possible conditions are smaller than (<), between (-), and greater than (>) on your Sheets. Make sure to specify the range of cells for the filter. 

You can enable filter sheet values or compare sheet values, although only one of these features can be enabled at once.


note
NOTE


Once you enable the filter sheet or compare sheet values, the maximum row number, you can pull data from in Google Spreadsheets is 1000.



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

External Fields-filter sheet value

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

External Fields-filter sheet value

tip
TIP


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)

External Fields-test

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

External Fields-compare sheet value

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

Compare Sheet value - enter settings

tip
TIP


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)

External Fields-test value - compare sheets

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

4. API

This uses the JSON output. Add the API endpoint and the JSON path.

External fields -custom api

Once you've set up the data configuration settings, you can run a test to check the data connection and see that the results are as expected. Make sure that the latest changes are saved.


Once you're all set, click Create field to save all your external field settings.

External fields - create fields

If you want more than one external field, click Add field and repeat the process outlined above, adding as many fields as you require.

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.