| Section |
|---|
| Column |
|---|
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. 
|
|
...
Use a rule to read information from the Google Sheet and populate the employee pick list. Here’s the relevant business rule:
| Note |
|---|
Escape the slash characters in your OAuth token with a backslash in the rule below like this: OAuth21\/\/<###...> |
| Code Block |
|---|
|
/*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;
}
|
- 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.
| Tip |
|---|
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.
...