Google Sheets Data Destination

This topic describes how to configure a Google Sheets Data DestinationClosed 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:



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 (DRELClosed 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.
    This is the folder your documents will be sent to. If the folder doesn't exist yet, it will be created.
    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.
    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.