Installation

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

1. The Google Connector version 2.1 is compatible with  v6.1.3+. If you are not using the correct version of , first upgrade your server software.  
2. Stop .

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 .
   2. Download the frevvo Google Connector v2.1
   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  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:  

      127.0.0.1 example.com

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 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 JSON file as client_secrets.json
    2. Copy it to tomcat/lib folder
12. Start .
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. So 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 you web browser and 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
2. Click on Accept button
3. Copy the access token. THIS IS VERY IMPORTANT. The token is used as the password for  wizards and rules. 
4. Repeat steps 1-2 for all Google accounts you will be using with the frevvo Google connector.     
5. Refer to the migration topic for the next step. 

 /*member colors, results */
var x;
 
if (form.load) {
    var readquery = '/google/spreadsheets/query/u/<Google user name>/p/<Google pswd>/s/ColorList/w/Sheet1'; 
    eval('x=' + http.get(readquery));
 
    var opts = [''];
    if (x.results) {
        for (var i = 0; i < x.results.length; i++) {
            if (x.results[i].colors) {
                opts[i + 1] = x.results[i].colors;
            }
        }
    }
    Colors.options = opts;    //Colors is the name of my dropdown control
}

 /*member colors, results */
var x;
if (form.load) {
var headers = 
'{"user":"<Google user name>","password":"<Google Account access token>"}';    
 var readquery = '/google/spreadsheets/query?ssname=Colorlist&wsname=Sheet1';
    eval('x=' + http.get(readquery,headers));																					
 
    var opts = [''];
    if (x.results) {
        for (var i = 0; i < x.results.length; i++) {
            if (x.results[i].colors) {
                opts[i + 1] = x.results[i].colors;
            }
        }
    }
    Colors.options = opts;    //Colors is the name of my dropdown control
}

Connecting Forms/Flows to Google Sheets & Drive

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

 provides wizards and a Google Connector that support easy direct connectivity with Google Sheets. You'll need a Google account and a  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 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 
   
   
   
   

Writing Rules to Retrieve and Update Data in Google Sheets

 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  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, designers are encouraged to

• Use http headers for authentication in  rules. 
• Always pass Google sheet and worksheet names as query parameters. 

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 () 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: 

 /*member colors, results */
var x;
if (form.load) {
var headers = 
'{"user":"<Google user name>","password":"<Google Account access token>"}';    
 var readquery = '/google/spreadsheets/query?ssname=Colorlist&wsname=Sheet1';
    eval('x=' + http.get(readquery,headers));																					
 
    var opts = [''];
    if (x.results) {
        for (var i = 0; i < x.results.length; i++) {
            if (x.results[i].colors) {
                opts[i + 1] = x.results[i].colors;
            }
        }
    }
    Colors.options = opts;    //Colors is the name of my dropdown control
}

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. 

Read from a Google Sheet

 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.

/*member fulladdress, city,results, state, zipcode*/
var x;

var headers = 
'{"user":"<Google user id>","password":"<Google Account access token>"}';    
var readquery = '/google/spreadsheets/query?ssname=Google Connector Address Sheet&wsname=Sheet1&query=customername="' + Customer.value + '"';
  eval('x=' + http.get(readquery,headers));   

if (x.results == null) {
    FullAddress.value = 'error';
} else if (x.results.length === 0) {
    FullAddress.value = 'No Match';
} else {
    FullAddress.value = x.results[0].fulladdress;
    City.value = x.results[0].city;
    State.value = x.results[0].state;
    Zipcode.value = x.results[0].zipcode;
}    

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. 

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.

/*member fulladdress, results*/

if (form.load) {
  var x;
  var opts= [];
 
var headers = 
'{"user":"<Google user id>","password":"<Google Account Access token>"}';    
var readquery = '/google/spreadsheets/query?ssname=Google Connector Address Sheet&wsname=Sheet1&query=state=MA or state=CT';
  eval('x=' + http.get(readquery,headers));   

var fi = '';    
if (x.results == null) {
    fi = 'Error';
} else if (x.results.length === 0) {
    fi = 'No Match';
} else {
    for (var i=0; i < x.results.length; i++) {
        fi = fi + '\n' + x.results[i].fulladdress;
    }
}
FullAddress.value = fi;
} 

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..

if (form.load) {
var headers = '{"user":"<Google user id>","password":"Google Account Access token"}'; 
 
var update = '/google/spreadsheets/update?ssname=Sequential Number Generator&wsname=Sheet1&query=formname="Checkbook"';

var id = 600;
eval('x=' + http.put(update + '&updates=nextid=' + id, null, headers, false));
}

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. 

if (form.unload) { 
   var eid = EId.value; // employee id is used as the unique key in the Google Sheet row   
  var headers = 
'{"user":"<Google user id>","password":"<Google Account Access token"}';    
  var updates = '/google/spreadsheets/update?ssname=Leave Approval WS&wsname=Vacation&query=employeeid="' + eid + '"';
  var ar = ALR.value + Request.value;
  var av = ALA.value - Request.value;
       
eval('x=' + http.put(updates + '&updates=annualrequested=' + ar + ',annualavailable=' + av, null, headers, false));
}

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  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. 

 

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.