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

 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

...



Step 3: Create your Google Sheet

Create a Google Sheet  Sheet containing the customer address details. Make sure the column header names match the Name property of each control. 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 Name

Column Header

Column Header in Rule

CustomerNamecustomer namecustomername
FullAddressfull addressfulladdress
Streetstreetstreet
Citycitycity
ZipCodezip codezipcode

Copy the Spreadsheet key to use in the Business Rule

...

  • 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 updatequery 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 updatequery url will start with  '<YourServer:Port>/google/spreadsheets/'.