Google Sheets Data Destination

 

About

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.  

Data destinations automatically back up data collected in the field.  They reduce the need to visit the web portal by making submitted forms available through the services you already use.  Different documents can also be sent to different services with a single form submission.  Read here for more information on data destinations.

Apps-Google-Drive-Sheets-icon.png 

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 a number of desktop or mobile device environments.  Looking for two-way integration?  Read more about how ProntoForms connects with Google services.

[ top ]

 

admin.pngnouser.pngnomobile.png

Basic Set Up

 sheets.png

  1. Create the data destination following the instructions here.
  2. Destination Basics: Name the destination.  Optionally choose to auto-link forms and documents to this destination.
  3. Add Filtering:  With a filter set up, form submissions will only be sent through the destination if the filter is met.  Read more here.
  4. Connection: Set up a Google Connection or choose an existing connection.
  5. Set up the Sheet Configuration tab as discussed below.

[ top ]

 

Sheet Configuration

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 Data Record Expression Language (DREL) to customize the spreadsheet name based on data collected forms.

spreadsheetname.png

 

 Worksheet Name

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

2015_10_14_10_47_10_Mobile_Forms_Made_Easy_Edit_Google_Sheets_Data_Destination.png

[ top ]

 

Folder

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

folder.png

  • Single folder: Enter the folder name.  For a folder named "Inspections",  enter "Inspections".
  • Multiple folders:  Use Data Record Expression Language.
    • %f - to sort submissions into folders named after the form.
    • %u - to sort submissions into folders named after the user submitting the form.
    • %a[Question Label] - to sort submissions into folders named with the answer to a chosen question.
    • Optionally add static text for formatting - eg.  "%f -- %a[Customer Name]"
  • Subfolders: Enter a forward slash (/) following the first folder, and then the name of the subfolder. 
    • %f/%a[Customer Name] - to sort submissions into folders named after the form, and subfolders named after customers.  

[ top ]

 

Metadata Columns

Optionally customize which metadata columns are included in your spreadsheet.  A number of columns are included by default, but these can be removed.  The Data Record Name and Reference Number columns are commonly added to the export to make rows of data more identifiable. 

 

  • Use the  right_arrow.jpg button to add columns to the Selected list.
  • Click on the column in the Selected list, then use the up.png and down.png arrows to reorder the columns as desired.  

metdata.png

  • The Available Metadata Columns are:

Data Record Name  

The name of the data record.

Address

The address of where the form is submitted, 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

An 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 Name

The FormSpace where the form is saved. 

Local Submitter Entry Time  

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. 

[ top ]

 

Send all Answers

  • If selected, all questions from submitted forms will be sent to the spreadsheet.  
  • If not selected, only data from the questions chosen will be included.  
  • Under Question Labels to Send (which appears when the box is unchecked), enter the question labels to include, separated by a comma, semicolon, or return.  Read more about Question Labels here.

sendallanswers.png

[ top ]

 

Link the Data Destination to Forms

In order for the destination to send any submitted form data, it must first be connected to the form.  This is done in the Data Destinations tab of the form builder.

[ top ]

 

Testing and Troubleshooting Data Destinations

Data destinations should be configured and tested carefully before using them in production. This is key to ensuring that ProntoForms data is received correctly. Read more about testing and troubleshooting data destinations

Error: Overloaded Connection 

Google Sheets does not support multiple writes to a spreadsheet at the same time. If a team has a very high data record volume, a different Data Destination type should be used. Alternative Data Destination types are available for use with ProntoForms.  

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 5 times and, for a typical submission, it takes from 4 to 10 seconds for a single Google Sheets destination to execute. When the first execution finishes, the other execution(s) will be retried. If multiple re-submissions occur at the same time, the error will occur again.

[ top ]

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments