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

frevvo supports direct connectivity with Google Documents and Spreadsheets. The Google Connector allows you to:

  1. Save submissions to Google Spreadsheets
  2. Read data from Google Spreadsheets
  3. Update data in a Google Spreadsheet 
  4. Store form submissions directly into your Google Apps account.
  5. Format emails or confirmation receipts using an online Google Document; and store form submissions directly into your Google Apps account.

On this page:

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

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

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

Installation 

  • If you are using frevvo Online, the Google Connector is already setup for you.
  • If you are using frevvo In-house, please read the installation instructions.

If you are using frevvo In-house and have downloaded the Google Connector:

  1.  rename the download file named connector-google-apps.zip 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 frevvo tomcat-bundle or have not installed the bundle into c:\frevvo make the necessary path adjustments to step 2 above.  

Google Spreadsheets

frevvo forms can both write to and read from your Google spreadsheets.

Save Submissions to a Spreadsheet 

frevvo provides wizards and a Google Connector that support easy direct connectivity with Google Spreadsheets. You'll need a Google Docs account and a frevvo account.

To save submissions to your Google spreadsheet, edit your form and use the Doc Action Wizard to configure your form to save your submissions into a Google spreadsheet. Each time someone uses and submits your form, a new row is added to your Google spreadsheet.

Google will not automatically add a row to your spreadsheet. Make sure your spreadsheet has plenty of empty rows at the bottom for all your expected submissions. See Google Connector Known Issues.

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 Spreadsheet. To do this use the Google Connector Url Manual Doc URI wizard to configure each Uri to a different spreadsheet or workbook within the spreadsheet. 

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

Read from a Spreadsheet 

frevvo forms can be initialized with data from a Google Spreadsheet by retrieving data from the Google 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 Spreadsheet.

This is the Google spreadsheet 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'.

eval('x=' + http.get('http://www.frevvo.com/google/spreadsheets/query/u/<google username>/p/<google password>
/s/DisclosureAddresses/w/ByName?media=json&query=customername="' +
Customer.value + '"'));


    if (x.results == null) {
        RequestID.value = 'error';
    } else if (x.results.length == 0) {
        RequestID.value = 'No Match';
    } else {
        RequestID.value = x.results[0].nextid;
}
if (x.results == null) {
    FullAddress.value = 'error';
} else if (x.results.length == 0) {
    FullAddress.value = 'No Match';
} else {
    FullAddress.value = x.results[0].address;
    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. 
  • 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.

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 (form.load) {
eval('x=' + http.get('http://www.frevvo.com/google/spreadsheets/query/u/<user>/p/<password>
    /s/DisclosureAddresses/w/ByName?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].address;
    }
}
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 =

http://www.frevvo.com/google/spreadsheets/query/u/<user>/p/<password>
    /s/DisclosureAddresses/w/ByName?media=json&query=name%3DHMF

http://www.frevvo.com/google/spreadsheets/query/u/<user>/p/<password>
    /s/DisclosureAddresses/w/ByName?media=json
    &query=name%3D%22AAA Insurance%22

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

var updatemethod = 'http://www.frevvo.com/google/spreadsheets/
update/u/<google username>/p/<google password>/
s/SequentialNumberGenerator/w/Sheet1?media=json&query=formname="Checkbook"';
var id = 33;
eval('x=' + http.get(updatemethod + '&updates=nextid=' + id + '&_method=put'));

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.
  •  /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)
  •  method=put - A hard coded Url parameter that must exist in the Url string

Google Apps

Form submissions can be saved to your Google Apps Premier account. Note that Google Apps for Education account are currently not supported.

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

Create a new Google Apps Collection

Use the Google Documents wizard

  1. Click the Doc Action button 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 (Collections) 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 Apps account. 

 

Do not use spaces or special characters when creating submission folder names.

Google Documents

The standard email wizard now supports HTML and CSS formatting plus form fields templates to pull an data entered into the form into your email message subject and body.

The Google Documents Connector can be used to format emailed form submissions and to echo a formated document back to the page that submitted the form. Here is a sample email formated using the Google Document template shown below.

To use the Google Connector, edit your form and use the Form Action Wizard to echo a formatted document back to the page and/or use Doc Action Wizard to format an email message.

Google Document Template Syntax

This feature is currently in alpha release. The syntax may change. The Google Document Connector uses a template syntax to insert values from a submitted form into the document. The syntax is ${form.[Name]}. Where [Name] is the name you gave to the particular form field in the frevvo Form Designer. Read more about naming form fields.

Here is a Google document using the template syntax:

Repeats

Repeat controls have a special syntax. Assume that your form contains a repeating section named Expense. First you must add a Repeat As tag to the document. ${repeat form.[Name] as [alias]}. For example: ${repeat form.Expense as exp}. Then use the alias to reference the controls inside the repeat. The Connector's template processor will add a row to the table for each repeating item in the form submission.

Sections

Form controls nested inside sections must be referenced using a nesting notation. If two text controls named Firstname and LastName are nested inside of a section named PersonalInfo then the templates in your google document must be written as:

${form.PersonalInfo.FirstName}  ${form.PersonalInfo.LastName}

If you add another section to your form and name it MedicalHistory and drag/drop PersonalInfo into that section, then the templates must be updated and written as:

${form.MedicalHistory.PersonalInfo.FirstName} ${form.MedicalHistory.PersonalInfo.LastName}

Limitations

The connector currently supports only form controls from palette. It does not yet support form controls from XSD data sources.

Templates are case sensitive. If your form field is named '''Hours''' then your template must be written as ${form.Hours}. Using lower case as in ${form.hours} will result in no field data in the document.

  1. Do not try to change the font size, color, etc of the template strings in your document. See Google Connector Known Issues.

Google Connector Known Issues

The frevvo Google Connector is an alpha release. These are the known issues we are working to solve. Please report any issues or feedback to us here.

TicketDescriptionWork-around
3931Google Connector fails to replace templates with form field valuesText formatting of the templates can possibly corrupt the underlying html document structure. If your document fails to produce results for certain templates, delete the template and re-add it to your document. Copy and pasting template tags can have the same effect. Both problems are due to the fact that the Google Docs is inserting html font tags into the middle of your templates. You can also fix this by cleaning up the document in the html view. But better to avoid the problem initially.
7710Google Connector fails to add row to google spreadsheetThe Connector will fail to add a submission to your google spreadsheet if the spreadsheet has no empty rows at the bottom. Make sure your spreadsheet has plenty of empty rows at the bottom for all expected form submissions.

 

 

 

 

 

 

 

  • No labels