Live Forms v5.3 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

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.

Watch this video for a demonstration of how Live Forms can be used to deploy forms and workflows that generate documents and automatically upload them to Google Drive.

On this page:

The information on this page refers to the Google Connector v1.4. Click here for the details about Google Connector v2.0.

Google will no longer support the Google Documents List API 3.0 after April 20, 2015. All customers must upgrade their frevvo Google Connector to version 2.0 and migrate their forms by this date . The frevvo Google Connector v1.4 and earlier will not work after this date. 

If you are using Live Forms v5.1.1+ you must upgrade to patch 9 to use the Google Connector 2.0. Follow the instructions here to upgrade the connector and here to upgrade Live Forms

Live Forms customers building applications that are high volume and address critical business processes should not use Google Sheets in place of a production quality database.

Google users with 100+ pre-existing documents in their Google Drive may have issues using the frevvo Doc Action Wizards to connect forms/flow to their Google account. The login screen in the wizard may hang or throw an unexpected error. If you experience this issue please:

  1. Update to the frevvo Google Connector v1.4
  2. Note: If you are using Live Forms Online, cloud hosted software the frevvo Google Connector is already updated to v1.4
  3. Use the form/flow Manually Set Doc URIs wizard and do NOT use the frevvo Google Sheet wizard

In the form/flow Manually Set Doc URI, do the following:

  • in the Write method, put the URL to the frevvo Google Connector and set the key in the URL to your Google Sheet, username and password. For example:

https://app.frevvo.com:443/google/spreadsheets/key/1ytf4_eeN7b77321BxLsZfOeg6oWlgAnqI4OglDFASGg/w/Sheet1?user=joe%40gmail.com&password=mypassword

The big ID after the key/ comes from the Google Spreadsheet URL that you see in the browser. It looks like this: https://docs.google.com/a/frevvo.com/spreadsheets/d/1ytf4_eeN7b77321BxLsZfOeg6oWlgAnqI4OglDFASGg/edit#gid=0

The parameters indicated in the URL: ?user=xxx&password=yyy. You should URL encode the user and password though this may not be required e.g. joe@gmail.com will work fine. You don't need to URL encode the @. Some characters must be encoded e.g. &. If you aren't sure, encode it (http://meyerweb.com/eric/tools/dencoder/).

This workaround bypasses the wizard completely and points the form to the Connector directly via a manual doc URI.

Installation 

  • If you are using Live Forms Online, the Google Connector is already setup for you.
  • If you are using Live Forms In-house, follow these instructions to install the in-house bundle

The Google connector v1.3 is bundled with Live Forms v5.3.5 but it is also available separately for download here.

If you have downloaded the Google Connector separately:

  1. Rename the download file named google-connector-1.3.war to google.war
  2. Copy google.war to c:\<frevvo-home>\tomcat\webapps
  3. 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.

Tomcat will automatically expand the war file and start the connector running. If you are not using the Live Forms tomcat-bundle or have not installed the bundle into c:\frevvo make the necessary path adjustments to step 2 above.  

Google Sheets

Live Forms forms can both write to and read from your Google sheet.

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

       

If your form contains multiple documents because one or more controls are from the palette while other controls are from one or more XSD data sources, you can write each document to a different Google Sheet. To do this use the Google Connector Url Manual Doc URI wizard to configure each Uri to a different Google Sheet or workbook within the sheet. 

See the Google Connector Spreadsheet Tutorial for step by step instructions.

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.

The form's business rule calls the Google Connector, passing a parameter named 'query'. The query paramenter selects row(s) from the spreadsheet which match values in specific columns. In this example the spreadsheet has a column named 'customer name'. We want to retrieve the spreadsheet row where customer name equals the name we select in our form's dropdown control name 'Customer'.

/*member fulladdress, city,results, state, zipcode*/
var x;
 
eval('x=' + http.get('https://app.frevvo.com:443/google/spreadsheets/query/u/google username/p/google password/s/name of Google Sheet/w/name of Google Sheet page? media=json&query=customername="' + Customer.value + '"'));
    
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;
} 

The http.get returns all the values from the matched row(s) to a varable named "x.results". The rule checks for null which means that no row matched the selected customer.

The Google connector has the following restlet parameters:

  •  /query - This tells the connector to execute the retrieve data method
  •  /u/<google username> - Your google account username.
  •  /p/<google password> - The password for this google account
  •  /s/<spreadsheet name> - The actual name of your spreadsheet.
  •  /w/<worksheet name> - The actual name of the worksheet in this spreadsheet

And the following Url parameters:

  •  media=json - This must be set as shown
  •  query=<query string> - This is where you specify which rows to match based on the data in your spreadsheet.

A few important notes:

  • Notice the space in the Google spreadsheet column name 'customer name'. When you pass this to the query remove spaces as you see above query=customername. 
  • 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 
  • No matter what the case is of the Google SS column name the query string column name MUST be lower case and all spaces removed.
  • Substitue the Google username, password, sheet name and sheet page name with your information
  • Here is an example of the url used in the rule shown above with the Google username, password, sheet and worksheet names provided:

https://app.frevvo.com:443/google/spreadsheets/query/u/qa@frevvoedutesting.com/p/f-r-evv0/s/Disclosure Addresses/w/Address Name? media=json&query=customername="' + Customer.value + '"'));

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.

/*member fulladdress, results*/

if (form.load) {
  var x;
  var opts= [];
 
  eval('x=' + http.get('https://app.frevvo.com:443/google/spreadsheets/query/u/google username/p/google password/s/name of Google Sheet/w/name of Goggle Sheet worksheet?media=json&query=state="MA"or state="CT"'));    


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;
} 

You can test your queries directly in the web browser. Note that characters such as the equals and double quotes must be URL encoded as shown in the examples below where you see %22 for quote and %3D for =

https://app.frevvo.com:443/google/spreadsheets/query/u/your Google username/p/your Google password/s/your Google sheet name/w/the name of the worksheet? media=json&query=customername=%3DHMF

https://app.frevvo.com:443/google/spreadsheets/query/u/your Google username/p/your Google password/s/your Google Sheet name/w/name of worksheet?media=json&query=name%3D%22AAA Insurance%22

Here is an example of a URL to read from a Google sheet named Disclosure Addresses, a worksheet in the Disclosure Addresses sheet named Address Info, a query named customername where the value is HMF:

https://app.frevvo.com:443/google/spreadsheets/query/u/qa@frevvoedutesting.com/p/f-r-evv0/s/Disclosure Addresses/w/Address Info?media=json&query=customername=HMF

Notice the URl example above uses https and provides a port number of 443. The query will also work with http but you must remove the port number 443 from the URL.

See this example of a rule to populate the dropdown options from a Google Sheet.

Update Google Sheet Cells

In addition to retrieving rows from a Google spreadsheet and adding rows to a spreadsheet, you can also update an data in an existing row. See the business rule example Sequential Numbers.

The business rule calls the Google Connector, passing a parameter named 'query'. The query paramenter 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'. The paramenter named 'update' 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 33 when the form loads.

if (form.load) {
var updatemethod = '/google/spreadsheets/update/u/google username/p/google password/s/name of Google Sheet/w/name of Google Sheet worksheet?media=json&query=formname="Checkbook"';
var id = 33;
eval('x=' + http.put(updatemethod + '&updates=nextid=' + id));
}

The Google connector has the following restlet parameters:

  •  /update - This tells the connector to execute the update data method
  •  /u/<google username> - Your google account username
  •  /p/<google password> - The password for this google account
  •  /s/<spreadsheet name> - The actual name of your spreadsheet.
  • /key<spreadsheet key> - The GUID of your spreadsheet
  •  /w/<worksheet name> - The actual name of the worksheet in this spreadsheet

And the following Url parameters:

  •  media=json - This must be set as shown
  •  query=<query string> - This is where you specify which rows to match based on the data in your spreadsheet.
  •  update=<update string> - This is where you specify which cell(s) to update in the matched row(s)

Here's a second Update example. This updates two columns.

if (form.unload) { 
   var eid = EId.value; // employee id is used as the unique key in the Google Sheet row
   var updatequery = '/google/spreadsheets/update/u/joe@gmail.com/p/xyz/key/google sheet key/w/2015?media=json&query=employeeID="' + eid + '"';
   var ar = APR.value + Request.value;
   var av = APA.value - Request.value; 
   var updateparams = '&updates=AnnualRequested=' + ar + ',AnnualAvailable=' + av;
    
   eval('x=' + http.put(updatequery + updateparams)); 
}

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

 

  • Do not use spaces or special characters when creating submission folder names.
  • If you configure the Submission folder field with a template then enter a value with a special character, ex:test@frevvo, the folder that is created will have the @ encoded.

Format & Email data using a Google Document

The Google Document wizard is deprecated. Use the standard Email wizard which now supports HTML and CSS formatting plus form field templates to pull data entered into the form into your email message subject and body.

Google Connector Known Issues

Please report any issues or feedback to us here.

TicketDescriptionWork-around
#16848

Google 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. A checked Boolean control value is 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