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.

Open

 

On this page

• 1 Google Connector Best Practice
• 2 Connect a Form/Workflow to Google
  • 2.1 Add a Document Action
    • 2.1.1 Save Documents to Google Drive
    • 2.1.2 Save Data to Google Sheets
      • 2.1.2.1 Mapping Multi-Value Controls
• 3 Business Rules
  • 3.1 Google Sheets Rule Functions
  • 3.2 Create a Dynamic Dropdown List from a Google Sheet
  • 3.3 Prefill Control Values from a Google Sheet
    • 3.3.1 Multiple Queries
  • 3.4 Update a Google Sheet
• 4 Troubleshooting
  • 4.1 Doc Action Failure Notifications
  • 4.2 Folder/Sheet Not Available in Doc Action Wizard

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.

• 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

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.
   
   

   Open

   

    

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:

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:

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.

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.

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.