Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

...


Section


Column

 forms frevvo forms can be initialized with data from a Google Sheet by retrieving the data from the spreadsheet using a business rule. Rule syntax and best practices to follow are discussed in the Writing Rules to Retrieve and Update Data in a Google Sheet topic.

The example discussed below uses a form that has a dropdown control to select a customer from a list. Once selected, a business rule queries a Google Sheet and retrieves the full address details for the selected customer. City, State, and Zip Code controls are also populated with the customer information.


Column
width350px

On This Page:

Table of Contents
maxLevel2


...

If you do not have an access token for the Google Account where you are going to create your spreadsheet, perform this one-time step.

Insert excerpt
frevvo103:Obtain an Access Tokenfrevvo103:
Obtain an Access Token
nopaneltrue

...

Create a simple example contact form with a few fields. Note the Select Customer control is a dropdown. If you need help creating a form, check out the Quick Demo and Form Designer tutorial videos on our website.

Image Modified

Step 3: Create your Google Sheet

Create a Google Sheet containing the customer address details. The column name on a sheet must match the control name. The matching is case-insensitive and any spaces in the column name is ignored. When you reference the column name in the rule, use only lower case and no spaces.

Control NameColumn HeaderColumn Header in Rule
CustomerNamecustomer namecustomername
FullAddressfull addressfulladdress
Streetstreetstreet
Citycitycity
ZipCodezip codezipcode

...

It is best practice to use the spreadsheet key in your business rule to specify the Google sheet. This method is recommended because each spreadsheet has it's its own unique key. Using the key instead of the spreadsheet/worksheet name avoids issues that may occur if you have more than one spreadsheet with the same name.

...

Note

A few important notes:

  • The Google spreadsheet column name is 'customer name'. When you pass this to the query, remove spaces as you see above query=customername. Any references to Google Sheet columns in your rule MUST be lower case and all have all spaces removed.
  • If you have spaces in your worksheet or Google Sheet name, do not remove them. 
  • Notice the double quote characters in the query string added around Customer.value. This is important in cases where your data contains spaces, you need to surround them with double quotes.
  • You will always get back an array even for 1 result.
  • If there are no results, the returned JSON Object x.results will be null 

...

In this example we have a checkbook form where we want to initialize a field named CheckNum with the next sequential check book checkbook number when the form loads. Here is our Google spreadsheet:

...

Here is the rule that reads the next sequential number from the spreadsheet and updates plus 1. Note this rule uses http headers to provide authentication information, access the Google sheet using the key, and passes the worksheet name as a query parameter. This is the recommended approach.

...