Let's take a look at populating a dynamic pick lists (drop downs) using a business rule. It’s a very common scenario and, with frevvo, you can use business rules to dynamically initialize the options (choices) in a pick list from a Google Sheet. We’ll use this sample Google Sheet to discuss. It has a row for each employee: First Name, Last Name, Employee Id.

On This Page:

Step 1: Obtain an access token

If you do not have an access token for your Google Account, perform this one time step.

Form designers must obtain a Google access token for every Google account you wish to use with your forms/flows. For example if you connect your forms/flows to a Google account info@frevvo.com and support@frevvo.com, you will need to obtain two access tokens.

Follow these steps to use the frevvo Google connector consent UI to obtain an access token for each Google Account.

Open your web browser and login to your Google Account
Go to:
1. Live Forms Online Cloud customers: https://app.frevvo.com/google/consent
2. Live Forms In-house customers: http://<your frevvo server domain name>/google/consent
Click Allow if you see the message below: This gives access to your account even when your device is turned off.
Copy the access token. THIS IS VERY IMPORTANT. The token is used as the password for wizards and rules.
Repeat steps 1-2 for all Google accounts you will be using with the frevvo Google connector.

An access token generated for a in-house installation running the Google connector, will not work with the Google connector running in the frevvo cloud. You must generate a separate token for each environment.

It is very important to save the token once you obtain it for a given account. The token allows the connector to access the account. There is a limit on the number of tokens that are issued per client-user combination. If the token limit is exceeded, older refresh tokens stop working. A token can be revoked if it is not used in a 6 month period.

Step 2: Add the Business Rule

Use a rule to read information from the Google Sheet and populate the employee pick list. Here’s the relevant business rule:

/*member employeeid, firstname, lastname, password, user, results*/

var x;

if (Connect.clicked) {
  var headers = {"user":"<google id>","password":"<access token>"};
  var readquery = '/google/spreadsheets/query/key/<your spreadsheet key>?wsname=<the name of your worksheet>';

    var results = http.get(readquery,headers);
    GSResults.value = results;
    eval('x=' + results);

    var opts = [''];
    if (x.results) {
        for (var i = 0; i < x.results.length; i++) {
            if (x.results[i].employeeid) {
                opts[i + 1] = x.results[i].employeeid + '=' + x.results[i].firstname + ' ' + x.results[i].lastname;
            }
        }
    }
    EId.options = opts;
}

It’s triggered by clicking on the Connect button.
We setup headers and a query using your access token and spreadsheet key (the long ID in the URL of the Google Sheet).
Run the query – perform an http.get() and eval the results.
Parse the results into an array. The array elements are “hagen=Walter Hagen” etc.
Set the options to the array.

Step 3: Try it yourself

You can try it yourself by clicking this link or clicking on the image.

Click the down arrow on the Select Employee dropdown to verify that the employee pick list is initially empty.
Click the Connect button and it will populate from this Google spreadsheet.The pick list will display the actual names (Walter Hagen, Alexa Stirling etc.) whereas the values returned upon selection will be the ids (hagen, stirling etc.) so it’s easy to perform further lookups.

Populating Dropdown Options from a Google Sheet

Here is another example of a rule that populates a dropdown control named Colors with color options from a Google Sheet.

Create a Google Sheet with a column named Colors containing a list of colors.

Create a Form with a dropdown control named Colors.
Use this rule to populate the dropdown options with the colors Red, Blue, Green and Orange. Note this rule uses http headers to provide authentication information. The Google Sheet is identified by the spreadsheet key and the worksheet name is passed as a query parameter. This is the recommended approach.
Replace <Google User ID> and <Google Account access token> with your information in the user and password headers.
Change <your spreadsheet key> and wsname= to reflect the name of the worksheet tab in your Google Sheet.

/*member colors, password, user, results */
var x;
if (form.load) {
var headers = 
{"user":"<Google user Id>","password":"<Google Account access token>"};    
 var readquery = '/google/spreadsheets/query/key/<your spreadsheet key>?wsname=<the name of the worksheet>';
    eval('x=' + http.get(readquery,headers));                                                                                   
  
    var opts = [''];
    if (x.results) {
        for (var i = 0; i < x.results.length; i++) {
            if (x.results[i].colors) {
                opts[i + 1] = x.results[i].colors;
            }
        }
    }
    Colors.options = opts;    //Colors is the name of the dropdown control
}

Create a Dynamic Pick List from a Google Sheet

Step 1: Obtain an access token

Step 2: Add the Business Rule

Step 3: Try it yourself

Populating Dropdown Options from a Google Sheet