Posted in Google Apps for Business, Google Apps for Education, Google for Work, Google Spreadsheets

frevvo + Google Apps: Part 3. Dynamic Pick lists.

Does your organization uses Google Apps? You can combine frevvo + Google Apps to create some really cool and useful solutions. In Part 1 of this series, we saw how you can save submissions to a Google Sheet and in Part 2, we saw how you can use the Google Distance Matrix API to automatically calculate mileage and reimbursement amounts.

employees-sheetToday, we’ll take a look at dynamic pick lists (drop downs). 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.

First, you have to get an access token. This is a one-time step. Login to your Google account and then visit: Follow the prompts and copy the access token and save it. Detailed documentation is here.

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

var x;

if (Connect.clicked) {
var headers = ‘{“user”:””,”password”:”<access token>”}’;
var readquery = ‘/google/spreadsheets/query/key/<spreadsheetkey>/w/Employees’;
eval(‘x=’ +http.get(readquery,headers));

var opts = [”];
for (var i = 0; i < x.results.length; i++) {
opts[i + 1] = 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.
Dynamic pick list from a Google Sheet. Click the image to try it.

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.

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

Interested in learning more? Visit our website to view examples, watch videos, sign up for a free 30-day trial or contact us for more information.

Like what you see? Check out Part 4 which shows you how you can update a Google Sheet with values from a form.

2 thoughts on “frevvo + Google Apps: Part 3. Dynamic Pick lists.

Leave a Reply