Versions Compared

Old Version 1

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Megan Ellis

Saved on

Key

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


Section


Column

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


...

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

...

  • The Google user id and access token for the password are supplied via user and password headers. Note the line beginning with var headers =. If you want to try this example with your own Google sheet, change <Google user id> to your Google account user name and <Google Account access token> to the access token for your account. 
  • On Premise customers need to also specify the server and port where their Google Connector is hosted. The query url will start with  '<YourServer:Port>/google/spreadsheets/'.
  • The path parameter, /key, is used to provide the spreadsheet key to identify the spreadsheet. 
  • The following URL query parameters are used:
    • wsname  - the rule queries the worksheet (tab) of the Google Connector Address sheet named Sheet1. To connect to your Google sheet, change Sheet1 to reflect the name of the worksheet (tab) in your Google Sheet.
    • query=<query string> - This is where you specify which rows to match based on the data in your spreadsheet. The query parameter selects row(s) from the spreadsheet which match values in specific columns. In this example the spreadsheet has a column named 'customer name'. The query parameter retrieves the spreadsheet row where customer name equals the name we select in our form's dropdown control name 'Customer'.  
  • The http.get returns all the values from the matched row(s) to a variable named "x.results". The rule checks for null which means that no row matched the selected customer.

...

The rule reads the number in the nextid column of the spreadsheet and populates the Checknum field in the form when the form loads. It then adds 1 and updates the nextid column in the spreadsheet. The Checknum field for the next user to load the form is populated with the incremented value.

Remember that On Premise customers need to also specify the server and port where their Google Connector is hosted. The query url will start with  '<YourServer:Port>/google/spreadsheets/'.