Live Forms v7.0 is no longer supported. Click here for information about upgrading to our latest GA Release.

Skip to end of metadata
Go to start of metadata

The Google Connector 2.0 and 2.1 support Oauth2 - Open Standard for authorization. This requires the account holder to provide consent to the Google connector to access data on their behalf. This is a manual process and must be done for each Google account associated with the connector.

Cloud customers using the Google Connector for the first time will need to:

New in-house customers using the Google Connector for the first time will need to:

Existing in-house customers upgrading from a previous version of the Google Connector will need to:

On this page:

Installation

Installing the 2.0, 2.1 or 2.1.1 connector is a one time process for Live Forms In-house customers. The 2.1.1 connector is installed for all Live Forms Online cloud hosted customers, so you can skip the Installation step.

Follow these steps to install the Google Connector 2.0,  2.1 or 2.1.1:

  1. Make sure the version of Live Forms is compatible with the version of the Google Connector you are installing. View the compatibility matrix to check the versions. If you are not using the correct version of Live Forms, first upgrade your

    Live Forms server software.  

  2. Stop Live Forms'.

  3. If you are using the tomcat-bundle, If you are using the tomcat-bundle, the google.war file is located in <frevvo-home>/ext/connectors. Copy the war file from this location to c:\<frevvo-home>\tomcat\webapps. 

  4. If you need to download the connector separately, click  here  . Extract the google.war file to the <frevvo-home>/tomcat/webapps/ folder. 

    If you are currently using a previous version of the Google Connector and you want to upgrade to version 2.1, follow these steps if you are using the Tomcat bundle:

    1. Stop Live Forms

    2. Download the frevvo Google Connector.

    3. Extract the google.war file to replace the existing one in <frevvo-home>\tomcat\webapps.

    4. Delete the <frevvo-home>\tomcat\webapps\google directory. If this directory is not deleted then the Google Connector will run from the old directory and the newer version won't take effect.

    5. Continue with step 5.

  5. If you are running Live Forms on your local machine, you must set a domain name for your machine - otherwise you can skip this step. 

    1. Ex: For Windows edit the C:\WINDOWS\system32\drivers\etc\hosts file as an administrator and modify the 127.0.0.1 entry with a domain name:  

  6. Go to https://console.developers.google.com. Login to the google account that you are going to use with the Google connector.

  7. Create a new project – name it , you can modify the id if you like, check I agree then click Create. You will see activity window while its creating the project When complete, you should be on the project dashboard page.
  8. Click  APIs and Auth → APIs → enable the Google Drive API named Drive API. It is listed under the Google Apps APIs label. It will move to the top section of the page  when you turn it on.
  9. Click Consent screen. Select the email address you want to associate with this project. Provide a project name. Click Save.
  10. Go to Credentials → OAuth → Create new client ID
    1. The Consent screen must contain a product name and email address before continuing.
    2. Application Type = Web Application
    3. Authorized Javascript Origins = Empty
    4. Authorized Redirect URIs = http://<DOMAIN_NAME>/google/credential. Replace <DOMAIN_NAME> with your domain name. If you are using localhost, this name must match the entry in your hosts file and you must provide the port number.
    5. Click Create Client ID.
  11. Click the Download JSON file.
    1. Rename the JSON file as client_secrets.json
    2. Copy it to tomcat/lib folder
  12. Start Live Forms. Don't forget to  start the Insight server first if you are using the Live Forms Report feature.
  13. Paste this url in your browser: http://<DOMAIN_ NAME>/google/health to check the Google Connector status. Provide your domain name and the port number if you are running locally. You should see a {"status":"UP"} message.

The google.war installation is now complete.

Obtaining an Access Token

Form designers must obtain a Google access token for every Google account you wish to use with your forms/flows. For example if you connect your forms/flows to a Google account info@frevvo.com and support@frevvo.com, you will need to obtain two access tokens.

Follow these steps to use the frevvo Google connector consent UI to obtain an access token for each Google Account.

  1. Open your web browser and login to your Google Account

  2. Go to:

    1. Live Forms Online Cloud customers: https://app.frevvo.com/google/consent

    2. Live Forms In-house customers: http://<your frevvo server domain name>/google/consent
  3. Click Allow if you see the message below: This gives Live Forms access to your account even when your device is turned off.

  4. Copy the access token. THIS IS VERY IMPORTANT. The token is used as the password for Live Forms wizards and rules.

  5. Repeat steps 1-2 for all Google accounts you will be using with the frevvo Google connector.

    An access token generated for a Live Forms in-house installation running the Google connector, will not work with the Google connector running in the frevvo cloud. You must generate a separate token for each environment.

It is very important to save the token once you obtain it for a given account. The token allows the connector to access the account. There is a limit on the number of tokens that are issued per client-user combination. If the token limit is exceeded, older refresh tokens stop working.  A token can be revoked if it is not used in a 6 month period.

Connecting Forms/Flows to Google Sheets & Drive

Live Forms supports direct connectivity with Google Sheets and Drive. The Google Connector allows you to:

  1. Save submissions to Google Sheets
  2. Read data from Google Sheets
  3. Update data in a Google Sheets
  4. Store form submissions directly into your Google Drive

Save Submissions to a Google Sheet

Live Forms provides wizards and a Google Connector that support easy direct connectivity with Google Sheets. You'll need a Google account and a Live Forms account.

Each time someone uses and submits your form, a new row is added to your Google sheet.

Follow these steps to save submissions to your Google sheet:

  1. Edit your form.
  2. Click the  what happens to my data? icon in the designer toolbar.
  3. Click on the Doc URI tab. 



  4. Select the Save Submissions to a Google Spreadsheet wizard. 



  5. This launches the Google Sheet Wizard. Enter your Google account username and your access token and click the login button
  6. A Spreadsheet dropdown appears listing all the spreadsheets in your Google account
  7. Select the Google Sheet from the list. 
  8. Select the worksheet of your Google Sheet that you want to update with new submissions.
  9. Click Finish on the Wizard 



You may see the error message "An unknown error has occurred" when selecting your Google sheet from the dropdown after you log into the wizard with your Google account and access code. To prevent this, set the Share permission for your Google Sheet to private then run the Save to Google Spreadsheet wizard. Change the share permission to public once the wizard is completed.Click here for other reasons this error may occur.

Writing Rules to Retrieve and Update Data in Google Sheets

Live Forms business rules are used to retrieve and update data in Google sheets via the Google Connector. Passing user names, passwords, sheet and worksheet names as part of a URL in Live Forms rules has been deprecated . This method will not work when the sheet or worksheet name contain certain special characters.  

If you are using the Google Connector version 2.0/2.1/2.1.1, designers are encouraged to:

  • Use http headers for authentication in Live Forms rules. 
  • Always pass Google sheet and worksheet names as query parameters.
  • Live Forms HTTP methods accept payload and header parameters as native JavaScript objects. Passing payloads and headers as a native JavaScript objects is the recommended approach and is used in the examples on this page.

Google Connector Query and Path Parameters

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.  

The Google connector supports the following URL query parameters. These parameters appear after the question mark ((question)) 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 Google connector supports the following path parameters:

  • key  - the GUID of your Google sheet. You can use the key to access 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 the same readquery as shown in the code box above using the key to identify the Google sheet instead of the ssname and wsname query parameters. Note the worksheet name and query string are passed as query parameters:

 

Let's say you have an existing form that has a dropdown control named Colors. You have a rule that populates the options of your dropdown from a Google sheet named Colorlist. In the sheet there is a column named Colors that lists color choices ( Red, Blue, Orange, Green). Your rule should look like this: 

This rule contains user and password headers to provide the <Google user name> and the <Google Account access token> for authentication. Note the headers variable (var headers = ). In your rules, replace <Google user name> with your Google User Id and the <Google Account access token> with the access token for your account.

The Google sheet and worksheet names are passed using query parameters: ssname and wsname, as this method has fewer name restrictions. In your rule, set ssname = to the name of your Google sheet and wsname=to the name of the worksheet (tab) in your Google sheet that you are trying to read from or update.

Refer to the topics below for examples. 

All references to Google Sheet columns in your rule MUST be lower case and all have all spaces removed.

Read from a Google Sheet

Live Forms forms can be initialized with data from a Google Sheet by retrieving data from the spreadsheet via a business rule. This form allows the user to select a customer from the dropdown. Once selected a business rule retrieves the address details from a Google Sheet.

This is the Google Sheet containing the customer address details.

Here is an example of a business rule that calls the Google Connector to retrieve the address details from a worksheet named Sheet1 in a Google sheet named Google Connector Address Sheet.

Let's take a look at the components of this 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. 
  • The following URL query parameters are used:
    • ssname - the rule queries a Google sheet named Google Connector Address Sheet. To connect to your Google sheet, replace Google Connector Address Sheet with the name of your Google sheet.  
    • 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. 

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 

The query string supports 'and' and 'or' logic. This query will retrieve all rows where the customer lives either in MA or CT. The rule concatenates all the addresses into the FullAddress form field. If you want to try this example with your own Google sheet, remember to change the <Google user id>, <Google Account access token>, Google sheet name (Google Connector Address Sheet) and worksheet name (Sheet1) to reflect your Google account and sheet information.

Update Google Sheet Cells

In addition to retrieving rows from a Google spreadsheet and adding rows to a Google sheet, you can also update data in an existing row. The first business rule discussed here updates a cell in a single column in a specified row and the second example shows a rule that updates cells in multiple columns in a Google sheet.

A more complex rule to generate unique sequential numbers in your forms/flows using a Google sheet is shown in the Sequential Number example.

Here is an example of the Google sheet used for the first example.

In this example, we will use this rule to call the Google Connector to update the cell in the nextid column with a value of 600 when a form named Checkbook loads..

Let's take a look at the components of this 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. 
  • The rule uses the following URL query parameters:
    • ssname - the rule queries a Google sheet named Sequential Number Generator. To connect to your Google sheet, replace Sequential Number Generator with the name of your Google sheet.  
    • wsname  - the rule queries the worksheet (tab) of the Sequential Number Generator 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 'form name'. We want to retrieve the spreadsheet row where form name equals the name 'Checkbook'.
    • '&update' in the eval statement indicates which column(s) to update in the matched row(s). In this example the spreadsheet has a column named 'nextid'. We want to set the value of nextid to the new value of 600 when the form loads.

Notice the two parameters for the http put method. These must be added to the eval statement.

  • null = <payload> - set this to null when updating a spreadsheet.
  • false = <multi-part> - set this to false when updating a spreadsheet.

Update Multiple Columns in a Google sheet

You may want to update multiple columns in a Google sheet using a single business rule. For example, let' say you have a Google sheet that lists employee ids, annual leave requested and annual leave available.

Your form uses a business rule to populate the Employee Id field named EId with the logged in user's id. A second rule reads the current Annual Leave Requested and Annual Leave Available values for that employee from the Google sheet when the form loads. The image shows the form when user f_tom logs in, and accesses the form to request 3 vacation days.

When Tom submits the form, you want to add 3 days to the existing Annual Leave Requested value of 4 and subtract 3 from the current Annual Leave Available value of 16 in your Google sheet. Here is an example of a rule to update both columns. If you want to try this with your Google sheet, substitute <Google user id> with your Google account user name, <Google Account Access token> with the access token for your account in the headers and change the ssname and wsname query parameter values to reflect the name of your Google sheet and the name of the worksheet (tab) in your Google Sheet. 

The Google sheet now reflects 7 days of requested leave with 13 days available for user Tom.

Refer to this topic for information about query and path parameters.

Cells with Formulas

If your Google Sheet cell contains a formula, that formula will be removed when the cell is updated with the form data. If you do not want this to happen then do not update that particular cell in the row.

Special Characters in Google Sheet and Worksheet Names in Rules

Google supports special characters in Google Sheet and Worksheet Names. If you are using Live Forms v6.1.3, and Google Connector version 2.1, your rules must pass the sheet and worksheet names as query parameters, to avoid any issues with special characters. 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.

Google Drive

Form submissions can be saved to your Google Drive.

The first step is to create a folder in your Google account. This will be the location where your form submissions will be stored. For example, create a folder called Leave Approvals.

Use the Google Documents wizard

Use the Google Documents wizard:

  1. Click the  what happens to my data icon in the toolbar at the top of the form.
  2. In the wizard that pops up, select the Save to Google Documents button.
  3. Enter your login credentials. Enter the access token for your account.
  4. After your login has succeeded, the wizard will show the list of Folders available. Sub-folders are not listed in the folder list.
  5. Select the folder you created earlier (Leave Approvals).
  6. Choose a name for the Submission Folder. This name should be unique for each submission. The best approach is to choose a combination of controls in the form that will generate a unique name. For example, if your form contains controls named EFulName and StartDate for employee's full name and start date of the leave, then choose: {EFullName}_{StartDate}.
  7. Click the Finish button.
  8. Your form is now configured to save submissions to your Google Drive.

Credentials, Folder, Submission Folder

If you want to save a pdf of your form/flow to the Google folder, make sure you have Save PDF checked. An optional field that is used to name the pdfs displays. You can use templates in this field. For example, if you have a control called EmployeeName in your form, you can type {EmployeeName} into the PDF Name field and the pdf's in the Google folder will be named {EmployeeName}.pdf 

  • If you leave the PDF Name property blank, Live Forms uses the flow/form name as the pdf file name. The pdf may not generate if the Form Name contains special characters. To avoid any issues, name your form/flows using characters that are suitable for filenames and explicitly set the pdf name in the form/flow using the same standard. Following the POSIX filename standard (A–Z a–z 0–9 . _ - ) will ensure it works. 
  • If a form does not reach the specified folder in your Google Drive successfully, Live Forms notifies the tenant admin of the failure via an email. If a flow does not reach the specified folder in your Google drive successfully, the flow administrator(s) - if configured - and the tenant admin receive the email.
      

     

Google Connector Known Issues

Please report any issues or feedback to us here. See the list below for some known issues with the Google Connector.

TicketDescriptionWork-around
#16848Google Spreadsheet - Boolean control : Fetching values true/false should not be case sensitive

Google sheets automatically converts true, True, false, and False values to upper case. Click here for more information. Let's say you have a checked Boolean control value stored in your spreadsheet  as TRUE.  This might cause an issue when reading from Google spreadsheet, and setting the value of the Boolean checkbox. Implement one of the following as a workaround:

  • Convert to lowercase before setting the value of the T/F control
  • Format the cells in your spreadsheet as Plain Text by clicking the 123 button and selecting Plain Text.

  • Enter the text as: 'true - (the ' prevents Google sheets from changing the value to upper case).

#17952Some number values update to date values in Google SheetsIn some cases, Google Sheets may change valid number values to dates values. The best way to handle this situation is to write scripts to clean up the bad data in your Google Sheets.

 

 

 

 

 

 

 

  • No labels