Writing Rules to Retrieve and Update Data in a Google Sheet
frevvo business rules are used to retrieve and update data in Google sheets via the Google Connector. One advantage to initializing your form/workflow from a Google sheet is to provide business users, who may not be familiar with frevvo, an opportunity to modify the Google sheet to make changes. Once modified, the changes in the Google sheet are reflected in the form/workflow without further action.
Designers are encouraged to:
- Use http headers for authentication in frevvo rules.
- You can identify your Google sheet/worksheet, using the ssname and wsname query parameters.
- frevvo recommends using the spreadsheet key instead of spreadsheet name to identify your Google Sheet
- Pass payloads and headers as native JavaScript objects.
Google Connector Query and Path Parameters
If you want to retrieve or update data from a Google sheet, you must write a business rule which contains a query.
Queries to a Google sheet can include query parameters or parameters that are part of the path in the URL or a combination of both.
Note for On Premise Customers
On Premise customers need to also specify the server and port where their Google Connector is hosted. The query url's below will start with '<YourServer:Port>/google/spreadsheets/. . . '.
Queries using the Spreadsheet key
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 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.
The Google connector supports the use of the key as a path parameter:
- key - the GUID of your Google sheet. Use the key to specify your Google sheet instead of the sheet name (ssname) in your rule.
- To find the Google sheet key,
- Open your Google sheet.
- The key is the long string of characters between the d/ and the /edit in the URL in the address bar.
Ex: https://docs.google.com/spreadsheets/d/1FXYb7PJpozlFcJol12YNEwhFFwa-tvpO6OjU1wzxDTI/edit?usp=sharing
Here is an example of a query using the key to identify the Google sheet. Note the worksheet name and query string are passed as query parameters:
The query=<query string> parameter is where you specify which rows to match based on the data in your spreadsheet.
Variables in rules must be unique. Having duplicate variables in the same rule will cause the rule to fail but you may not get an error message.
Queries using the Spreadsheet and Worksheet Name
Queries can also be written using the spreadsheet/worksheet name. This approach is not recommended because the rules will fail if you have more than one spreadsheet with the same name.
The Google connector supports the following URL query parameters. These parameters appear after the question mark (
) and are separated by an ampersand (&) in the URL:
- ssname - This is the name of your Google sheet.
- wsname - This is the name of the worksheet in your Google Sheet.
- query=<query string> - This is where you specify which rows to match based on the data in your spreadsheet.
The image shows a query that reads from a Google sheet named Google Connector Address Sheet and a worksheet (tab) named Sheet1.
The column name on a Google sheet must match the control name. The matching is case-insensitive and any spaces in the column name are ignored. A control named "FirstName" matches a column header "first name." However, references to Google Sheet columns in your rule must be lower case and cannot contain spaces. The correct reference for this example is "firstname."
| Control Name | Column Header | Column Header in Rule |
|---|---|---|
| FirstName | first name | firstname |
Special Characters in Google Sheet and Worksheet Names in Rules
Google supports special characters in Google Sheet and Worksheet Names. Special characters {}, #, + ,& are not supported in rules but they will work in the Save Submissions to a Google Spreadsheet wizard.
Avoid leading/trailing spaces in spreadsheet or worksheet names when using rules to read/update Google sheets. Spaces in the Google Sheet and worksheet names should be encoded with the + (plus) sign. For example, a worksheet named Employee Information should be encoded as Employee+Information.
Refer to Create a Dynamic Pick List from a Google Sheet, Update a Google Sheet and Reading from a Google Sheet for examples.