DocuPhase Forms latest - This documentation is for DocuPhase Forms v11.3. Not for you? Earlier documentation is available too.

Google Connector

Does your organization use Google Apps? Combining Google Sheets/Drive and DocuPhase Forms provides interesting and useful ways to solve real business problems. The ability to read and write workflow data from/to a Google Sheet and post documents to Google Drive makes completing routine, day-to-day business tasks easier. DocuPhase Forms provides a Google Connector that supports direct connectivity between DocuPhase Forms and Google Sheets/Drive.

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

Screen Shot 06-25-24 at 03.23 PM.PNG

 

Google Connector Best Practice

Create a Google Shared Drive that hosts all folders and sheets you plan to use with DocuPhase Forms. This helps group all documents and sheets that are maintained by the application. Each folder and sheet must be shared with the service account (see Admin: Manage Connectors.) You may also share the folders and sheets with any other users in your organization who need access to them.

To learn more about Google Shared Drives, view this Google article.

  • Do not add the service account as a Shared Drive user; rather, click the Share icon for each folder and/or sheet and add that user as a Contributor or Content Manager on it.

  • We do not recommend sharing parent folders from a user’s ‘My Drive’ because any file/folder created by the connector will be owned by the service account and cannot be deleted easily (e.g. to preserve storage space). There is no direct drive access for the service account.

  • Sheets shared with the service account cannot be located inside a folder.

Connect a Form/Workflow to Google

Your Tenant Admin must configure the Google Connector in the Admin: Manage Connectors page before you can perform the following steps in the Form/Workflow Document Actions tab.

Add a Document Action

  1. You can add a Document Action at the end of your form/workflow or after any workflow step.

    1. Document Action: Go to your form/workflow's Settings tab in the Guided Designer Navigation bar. Click the Document Actions tab.

    2. Activity Document Action: Click the + icon after any workflow step, then click Add Document Action.

  2. You will automatically be on the Send Documents sub-tab. Click ADD DOCUMENT ACTION and select Send to External System.

    Screen Shot 06-07-24 at 05.29 PM.PNG

     

  3. Select Send to Google Drive or Send to Google Sheets.

    Depending on your selection, see the the sections below for wizard details.

Save Documents to Google Drive

  1. Select a Folder from the available list of option. Only folders that are shared with your Service Account are visible. See Google Connector Best Practice above.

  2. Select a Sub Folder.

    1. You can enter a static name, like “Purchase Orders” or use a template like {Department}, or any combination.

      1. If you use a template, and it resolves to null, the submission’s unique id will be used as the folder name instead.

    2. If the Sub Folder does not yet exist at the time of submission, it will be created.

    3. If the Sub Folder already exists at the time of submissions, the document(s) will be added to it.

  3. Select the document(s) to send.

    1. Send Snapshot - choose your format if you would like to send a snapshot of all the printable forms and fields in your submission.

    2. Check Data if you would like to send the submission.xml document (uncommon).

    3. Check Attachments if you would like to send files uploaded by the user via upload controls.

    4. Choose whether to send Generated (Mapped) PDFs. You may choose all, none, or select specific mapped PDF(s).

 

Note that when the file is saved to Google Drive, it will be created by (and last modified by) the service account.

Save Data to Google Sheets

  1. Select the Spreadsheet from list. Only sheets that a) shared with the service account and b) not in a folder will be shown. See Google Connector Best Practice above.

  2. Select the Worksheet (this will list the tabs on your sheet).

  3. Map columns.

    1. Each available column will appear in the Column dropdown. Click the blue Add icon to map more columns. Once a column is selected, it is not available to be selected again. Once all available columns are mapped, the “Add” button becomes disabled.

    2. You may enter static text or a template(s) in the corresponding Value field. The column name does not need to match the control name.

Mapping Multi-Value Controls

You can map multi-value controls such as controls inside repeats, table columns, and checkboxes to the same OR separate Google sheet rows.

To map the value to a single row, you can use any regular separator, such as a comma, in your repeat template syntax. This results in a single, comma-separated string value in the target cell. For example, you can map the Items column in a table to a text column named with the syntax {,|Items}. This results in a single value sent to the target Google cell as a comma-separated string, e.g "Widgets, Wingdings, Whatzits".

You can also map a repeat control to separate Google sheet rows. In this case, you must use a special syntax to send separate values. Use the repeat separator 'json'. Example: {json|Items}

In the example below, Color Choices will map to a single cell as a comma-separated list, while Item will map to separate rows.

 

 

 

 

 

Business Rules

You may also use business rules to read and update data from Google Sheet. For example, you may wish to maintain a matrix of approvers in a Google Sheet, which DocuPhase Forms reads from and uses for step assignments. The approval matrix sheet can easily be updated by your business users, and DocuPhase Forms instantly reflects the change.

Google Sheets should not be used in place of a production quality database. Reading/updating large datasets can impact performance, and because Google is a 3rd party, DocuPhase cannot assist with potential service interruptions.

Google Sheets Rule Functions

There are two basic functions you can call in business rules.

  1. To read from a sheet, use frevvo.getGoogleSheetRows('<sheet key>','<tab name>','<query>').

  2. To update a sheet, use frevvo.updateGoogleSheetRows('<sheet key>','<tab name>','<query>', ‘update values in JSON format’).

    1. JSON format is {column:value,column2:value2}

    2. You may wish to set up the JSON parameter string in a variable and then pass the variable in your function.

The query is optional (replace with the word null if not using), and you can add multiple queries using ‘and’ or ‘or’ between them inside the same quote set.

The sheet key is the GUID of your Google sheet. Use the key to specify your Google sheet 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 

The query is typically structured as “<ColumnHeader>='value'>”. Your value can come from a control in your form.

Create a Dynamic Dropdown List from a Google Sheet

Let's take a look at populating dynamic pick lists (drop downs) from a Google Sheet using a business rule. We’ll use this sample Google Sheet to discuss. It has a row for each employee: First Name, Last Name, Employee Id.

For this example, we have a form with three controls: a Trigger control named ‘Connect’, a Dropdown control named ‘EId’ and a TextArea control named ‘GSResults’. The goal is to populate the options in the Dropdown so that the user sees a list of employee names, and when they select one, the value selected is that employee’s corresponding Employee ID.

Add a rule to read information from the Google Sheet and populate the employee pick list. Here’s an example:

/*member employeeid, firstname, lastname, results*/ var x; //Condition (when to run the rule) if (Connect.clicked) { // declare and set a variable (x) to the result of the special read GS function var x = frevvo.getGoogleSheetRows('10RkDKC8dXpUxtsPQcxK-mhH4rBIxZthl6Arqx8kBmBY', 'Employees', null); // Populate the results in a hidden text area GSResults.value = JSON.stringify(x); // declare an array variable (opts) var opts = ['']; // if there were results from GS, loop through them and push each row into the array, in the option syntax format if (x) { for (var i = 0; i < x.results.length; i++) { if (x.results[i].employeeid) { opts.push(x.results[i].employeeid + '=' + x.results[i].firstname + ' ' + x.results[i].lastname); } } } // Set the dropdown options to the array created above EId.options = opts; }
  1. This rule is triggered by clicking on the Connect button (a Trigger control.) You could also run this rule on form.load.

  2. The parameters for the function frevvo.getGoogleSheetRows are a) the spreadsheet key (the long ID in the URL of the Google Sheet) and b) the tab name, ‘Employees’, and c) null (meaning we are not using a query in this example). Remember to replace the examples with the key and tab name of your sheet.

  3. Parse the results into an array. The array elements are “hagen=Walter Hagen” etc.

  4. Set the options to the array.

For this example, we also populate the TextArea ‘GS Results’ with the full result of the function, which is all data on the specified tab in JSON format. One common practice is to do this operation on form.load, and then populate dropdowns (or prefill other control values) based on the data in your form, rather than making multiple calls to the Google Sheet. This can improve performance and prevent errors if your Google Sheet data is changed before the workflow is fully submitted.

This same model will work for populating options for Radio and Checkbox controls as well.

Prefill Control Values from a Google Sheet

You can initialize your form with data from a Google Sheet using a business rule. The example discussed below uses a form that has a dropdown control to select a customer from a list. Once selected, a business rule queries a Google Sheet and retrieves the full address details for the selected customer. City, State, and Zip Code controls are also populated with the customer information.

Add a rule to read information from a specific row of the google sheet and prefill your form fields. Here is an example:

/*member fulladdress, city,results, state, zipcode*/ //declare and set a variable (x) to the result of the special read GS function var x = frevvo.getGoogleSheetRows('1Ak9v1SJNAjQu_4lxWv909quyH4bpmbuF2IxjgJM7VBw', 'AddressInfo', "customername='" + SelectCustomer.value + "'"); //error handling - display a message if there is any problem getting results if (x.results == null) { FullAddress.value = 'error'; } else if (x.results.length === 0) { FullAddress.value = 'No Match'; } else { //as long as there are results, display them in the corresponding form fields. 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 parameters for the function frevvo.getGoogleSheetRows are a) the spreadsheet key (the long ID in the URL of the Google Sheet) and b) the tab name, ‘Employees’, and c) the query to the row of the selected customer: "customername='" + SelectCustomer.value + "'". Note that the column name is converted to all lower case without spaces. If you want to use spaces, you must enclose the column name in single quotes here. We are also passing in the value of “SelectCustomer”, so that whenever the user changes their selection, the address shown is updated to reflect it.

Multiple Queries

The query string supports ‘and’ and ‘or’ logic. Simply separate your queries in the third parameter by the word ‘and’ or ‘or’. This query will retrieve all rows where the customer lives either in MA or CT by setting the query to "state='MA' or state='CT'". The rule concatenates all the addresses into a text area control named FullAddress when the form loads.

/*member fulladdress, results, password, user*/ if (form.load) { // Note the 3rd query includes 'or' var x = frevvo.getGoogleSheetRows('1Ak9v1SJNAjQu_4lxWv909quyH4bpmbuF2IxjgJM7VBw', 'AddressInfo', "state='MA' or state='CT'"); var opts= []; 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 a Google Sheet

Let's take a look at how you can update existing values in a sheet. It’s another common scenario that can be used for a variety of tasks (e.g. limiting the number of submissions for a particular form or creating a sequentially increasing counter). We’ll use this sample Google Sheet to discuss. It has a row for each employee: Employee Id, Location and Extension. The form has three corresponding controls. When Update Google Sheet is clicked, we want to update the location and extension for the selected employee.

Troubleshooting

Doc Action Failure Notifications

If the submission doesn't reach Google, all tenant administrators or workflow admins (if configured) will receive a Doc Post Failure notification email reporting information about the error. The email subject contains the server name where the form/workflow is hosted, and the form/flow name, project name and owner are listed in the email body. The URL shown in the email will tell you whether the failure occurred in regards to a Sheet (look for the word “spreadsheets” in the URL) or Drive (look for the word “documents” in the URL).

Here are some examples:

There are a number of reasons why your data or documents may not post successfully to Google Sheets/Google Drive. Here are some common reasons and troubleshooting actions you can take.

Possible Reason for Doc Post Failure

Try this…

Possible Reason for Doc Post Failure

Try this…

Connection Timeout

  • Check if the submission documents/data are actually present or not in Google Drive/Sheet. Sometimes, the submission goes through successfully, but DocuPhase Forms does not hear back from Google in time and after a certain timeout period throws this error.

  • Resubmit (see this documentation on how to resubmit forms/workflows).

Google Sheets

Workbook or Sheet deleted/renamed

First Column header deleted/renamed 

  • Check your Google Sheet to see if the workbook, sheet, or first column header was altered. This can also happen if the sheet is sorted without excluding column headers, so that the headers end up on some other row.

  • If needed, update the Doc Actions to reflect the new name.

  • Resubmit failed submissions.

Google Sheet has a column or sheet set as a Protected Range

Google Sheets has reached the limit of 5000000 cells

  • Reduce the size of your spreadsheet. A few methods are described in this blog article.

Google Sheets or Google Drive API is not enabled

Google Drive folder deleted/renamed

  • Check your Google Drive to see if the folder was altered.

  • If needed, update the Doc Actions to reflect the new folder name.

  • Resubmit failed submissions.

Folder/Sheet Not Available in Doc Action Wizard

This is typically a permission issue.

  • Ensure that the folder or sheet is shared with the Service Account.

  • Do not add the Service Account as a user on the Shared Drive.

  • Make sure the Sheet is at the top level in the Shared Drive, not inside a folder.