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:


Rule syntax and best practices to follow are discussed in the Writing Rules to Retrieve and Update Data in a Google Sheet topic.

Step 1: Obtain an access token

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

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:

You need to escape the slash characters in your OAuth token with a backslash in the rule below as follows: 

OAuth21\/\/<###...>



/*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.push(x.results[i].employeeid + '=' + x.results[i].firstname + ' ' + x.results[i].lastname);
            }
        }
    }
    EId.options = opts;
}
  1. It’s triggered by clicking on the Connect button.
  2. We setup headers and a query using your access token and spreadsheet key (the long ID in the URL of the Google Sheet).
  3. Run the query – perform an http.get() and eval the results.
  4. Parse the results into an array. The array elements are “hagen=Walter Hagen” etc.
  5. Set the options to the array.

The column name on a Google sheet must match the control name. The matching is case-insensitive and any spaces in the column name are ignored. A control named "FirstName" matches a column header "first name." However, references to Google Sheet columns in your rule must be lower case and cannot contain spaces. The correct reference for this example is "firstname."

Step 3: Try it yourself

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

  1. Click the down arrow on the Select Employee dropdown to verify that the employee pick list is initially empty.



  2. 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 Checkbox or Radio Options from a Google Sheet

Here is another example of a simple rule that populates a Checkbox or Radio control named Colors with color options from a 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] = x.results[i].colors;
       }
     }
   }
    Colors.options = opts;    //Colors is the name of the Checkbox/Radio control
}