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.

Today, 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: https://app.frevvo.com/google/consent. 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:

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.

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.