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

Installation 

The Google connector v1.3 is bundled with  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  tomcat-bundle or have not installed the bundle into c:\frevvo make the necessary path adjustments to step 2 above.  

Google Sheets

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

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

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.

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.

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

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.