Google Sheets Data Destination

This topic describes how to configure a Google Sheets Data Destination A Data Destination specifies where to send data from a submitted form. You can use Data Destinations to automate data sharing and storage, routing data to a specific service (such as email or cloud storage) in several different formats., as well as the expected behavior of the Data Destination.

Supported on all tiers:

Contents

• Google Sheets Data Destination
• Introduction to Google Sheets Data Destinations
• Configure a Google Sheets Data Destination
• Expected results
• Testing and troubleshooting Data Destinations

Introduction to Google Sheets Data Destinations

The Google Sheets Data Destination sends data from submitted forms to a Google Sheets spreadsheet. Use form data to create a new spreadsheet or add information to an existing spreadsheet and keep track of related submissions in a single file. Create custom spreadsheet and folder names based on data in the form to logically organize and manage files.

Tip:To set up a Data Destination, follow the instructions in the topic Creating and Managing Data Destinations.

Data Destinations automatically back up data collected in the field. They reduce the need to visit the Web Portal by making the information in submitted forms available through the services you already use. Different documents can also be sent to different services with a single Form Submission.

Info:The topic Data Destinations Overview describes Data Destinations in more detail.

Google Sheets allows users to create, share, and store spreadsheets in the cloud. Google Sheets is directly integrated with Google Drive, making stored spreadsheets available on several desktop or mobile device environments. ProntoForms supports Google Sheets stored in your personal My Drive or in your company’s Shared drives.

Info:The topic ProntoForms and Google: Two-Way Integration describes the ProntoForms and Google integration in more detail.

Configure a Google Sheets Data Destination

1. Go to Manage Forms > Data Destinations > CREATE DATA DESTINATION.

2. Select Google Sheets.

3. Configure the Data Destination as specified in the topic Creating and Managing Data Destinations.

4. In the Sheet Configuration tab, configure the following information:

   Drive type

   Is your spreadsheet stored in a Shared drive?

   • If yes, select Shared Drive, and then enter the name of the Shared drive.

   • If no, select My Drive.

   Note:If you don’t include the name of your Shared drive, the system defaults to your personal My Drive.

   Spreadsheet Name

   This is the name of the spreadsheet your data will be sent to. If it exists already, your data will be added as a new row. If it doesn't exist yet, it will be created. Use the Data Reference Expression Language (DREL Data Reference Expression Language (DREL) is used to get form data and metadata and add it to a string, such as dates, usernames, or answers to questions in forms.) to customize the spreadsheet name based on data collected in forms.

   

   Worksheet Name

   This is the name of the worksheet within the spreadsheet your data will be sent to. The Worksheet Name is static and does not process DREL.

   

   Folder

   This is the folder your documents will be sent to. If the folder doesn't exist yet, it will be created.

   

   • For a single folder, enter the folder name.

     Example: For a folder named Inspections, enter Inspections.

   • For multiple folders, use the following Data Reference Expression Language expressions:

   • %f to sort submissions into folders by form name.

   • %u to sort submissions into folders by user name.

   • %a[Unique ID A Unique ID refers to the specific identifier of a question, form page, form section, or Data Destination. Unique IDs are used as reference points when pulling data for conditional logic, Analytics projects, Data Destinations, and Documents.] to sort submissions into folders named with the answer to a specific question. Read our documentation on Unique IDs.

   • Optionally add static text for formatting

     Example: %f for customer %a[CustomerName] returns Building inspection for customer Hansen Co.

   • For a subfolder, enter a forward slash (/) following the first folder, and then the name of the subfolder.

     Example: Use %f/%a[Customer Name] to sort submissions into folders by form name, and subfolders by customer name.

   Metadata columns

   Optionally customize which metadata columns are included in your spreadsheet. The Data Record Name and Reference Number columns are commonly added to the export to make rows of data more identifiable. The system generates a Data Record ID column by default.

   • Use the Move Right arrows  to add columns to the Selected Metadata Columns list.

   • Click on the column in the Selected Metadata Columns list, and then use the Up  and Down  arrows to reorder the columns as desired.

     

   • The Available Metadata Columns are:

     Data Record Name	The name of the Form Submission.
     Address	The address where the form is submitted from, based on geo location data.
     User Name	This is the name and username of the user submitting the form.
     Form Version	The version of the form being filled out.
     Device OS	The operating system of the device the form is filled out on.
     Reference Number	A unique reference number for the form.
     Server Receive Time	The time the data record is received by the ProntoForms server.
     Device OS Version	The operating system on the device the form is filled out on.
     FormSpace FormSpaces are where forms are stored and organized in the ProntoForms Web Portal. A ProntoForms Team may have multiple FormSpaces, depending on their needs. Admins can set FormSpace permissions to control which users have access to the forms in that FormSpace. Name	The FormSpace where the form is saved.
     Local Submitter Entry Time	The local time when the form is submitted.
     Mobile Application version	The version of the ProntoForms app when the form was filled out.
     Device Entry Time	What time the device accessed the form.
     Form Name	The name of the form.
     Location	The geo location collected when the form is submitted.

   Send All Answers

   • If selected, all questions from submitted forms will be sent to the spreadsheet.

   • If not selected, only data from the specified questions will be included.

   • Under Question Labels to Send (which appears when the Send All Answers box is not selected), enter the question labels to include, separated by a comma, semicolon, or line break.

     Info:The topic Overview: Unique IDs describes Question Labels in more detail.

     

5. Select Create.

   Result: The system saves your new Data Destination.

Expected results

Once the Data Destination processes a form submission, the relevant Google Sheet shows the new entry with the following characteristics:

• The new entry is in the specified drive, folder, and Sheet. If the folder or Sheet don’t exist, the system creates a new one.

• The entry includes Metadata columns as the first columns in the sheet, and the Answer columns following them. Answer columns appear in the same order as they appear in the form, and the column headers are the Question Unique IDs.

  Note:If you change a question Unique ID, the system adds a new column for this question.

• Answers not collected in a repeatable section appear as a single row.

• Answers collected in a repeatable section generate a new row for each answer. The system repeats other information, such as metadata, on every row.

  Example: The following table shows a single Form Submission in which Part, Quantity, and Price are questions within a repeatable section. In this case, the section has three entries.

  Data Record ID	Customer	PO	Part	Quantity	Price
  123456789	Company ABC	123	Compressor	1	$250.00
  123456789	Company ABC	123	Control Board	1	$100.00
  123456789	Company ABC	123	Switch	3	$25.00

Testing and troubleshooting Data Destinations

Data Destinations should be configured and tested carefully before using them in production. This is key to ensure that ProntoForms data is received correctly.

Info:The topic Troubleshooting Data Destinations describes how to troubleshoot a Data Destination in more detail.

Error: Overloaded Connection

Note:Google Sheets does not support multiple edits to a spreadsheet at the same time. If your team has a very high volume of Form Submissions, use a different Data Destination type.

If there are two or more data records submitted to a Google Sheets destination at or around the same time, one of them will receive the error message, "Connection to Google Sheets is overloaded, please try again later".

The destination will try to resubmit up to five times and, for a typical submission, it takes from four to ten seconds for a single Google Sheets destination to execute. When the first execution finishes, the other executions will be retried. If multiple re-submissions occur at the same time, the error will occur again.

Using Shared Google Sheets as Destinations

Note:Google Drive no longer supports adding shared files to your own Google Drive Account. If you previously configured a Data Destination with a shared Google Sheet owned by another user, you might notice that the Data Destination now creates a new file stored in your own account. We recommend that you use Google Sheets stored in Shared drives instead.