Update a Data Source Using Data from Submitted Forms (Google Sheets)

 

About

The Google Sheets Data Destination and Google Sheets Data Source features can be combined and used to update a data source when a mobile form is submitted.  

This functionality is intended for use as just one part of your ProntoForms configuration. Multiple Data Destinations of various types can be linked to a single form. While you may use the configuration discussed below to automatically add data from a couple of fields to your data sources, additional data destinations can still be configured to perform other functions, such as emailing a PDF copy of the full data record to a customer, or storing a copy of all submitted answers in Google Drive.

Read more about the various ways ProntoForms can interact with Google Sheets and other Google Apps.

[ top ] 

 

Sample Use Case

A common form is the "Site Visit" form, where site information is pulled into a ProntoForm from a Google Sheets Data Source named "Site Info", and then referenced in a mobile form through a dropdown question. Mobile workers select the site name from the list, and then the other fields are automatically filled in with the specifics of the site.

Occasionally, mobile workers visit a new site. An administrator does not always have time to enter the new site information into the data source spreadsheet before the mobile worker gets there. 

The concept behind this configuration is that mobile users should be able to:

  • manually enter new site information
  • have that site information automatically added to the "Site Info" data source for future use.

This article will run through a sample configuration for the use case described above using a V1 (form builder enabled) form.

 

[ top ] 

 

Configure the Google Sheets Data Source

Below is the spreadsheet named "Site Info" as viewed in Google Sheets.  The columns shown will be available for use in the "Site Visit" form.

Data_Source1.png

This article discusses this specific configuration for a Google Sheets Data Source. For more information on the standard Google Sheets Data Source, read this article.

When configuring the Google Sheets Data Source in the Google Connection tab, complete the "Spreadsheet Path" with the path to the file.  If the Sheet is not in a folder (sheets can only be sorted into folders via Google Drive, not Google Sheets), simply enter the spreadsheet name exactly as it appears in Google Sheets, in this case, "Site Info".

 

[ top ] 

 

Configure the Mobile Form

Page 1:

  • In the form pictured below, Page 1 consists of:
    • A dropdown question referencing the "sitename" column of the data source.  This question can auto-populate the questions on Page 2 if an answer is selected; the "Site Information" fields will only update the data source if the following question is answered "Yes."
    • A dropdown question with answers "Defined Here" of "Yes" and "No."  Users should select "Yes" if they are visiting a new site that is not on the list.  We will use this question in a filter rule in the data destination.

form.png

Page 2: 

  • This page consists of simple textboxes to collect site information.  As mentioned above, these could be populated by Question 1, Page 1 if the site already exists.
  • Note that the Question Labels of the "New Site" information should exactly match the header text of the columns in your spreadsheet (not the column headers as shown in the data source) you would like this data to populate.  

form3.png

[ top ] 

 

Configure the Google Sheets Data Destination

Read for full details on creating and configuring a Google Sheets Data Destination; this article only contains details specific to this configuration.

Name

Since this data destination will only be used to update the "Site Info" data source, name it something that makes this clear, like "Add New Site Information."

Filtering

Configure a custom filter rule so that the data destination only executes if the question "New Site?" is answered "Yes".

  1. Enter the reference to the question, using the question label ("%a[New Site?]") under Input Expression.
  2. Enter the exact string to match ("Yes") under Filter Expression.

DDfilter.png

 

Google Sheets Configuration

  1. Enter "Site Info" (or the name of your Google Sheets spreadsheet) as the spreadsheet name.
  2. Do not select any Metadata columns.
  3. De-select "Send All Answers."
  4. Under "Question Labels to Send," list the question labels for the answers that contain the new "Site Info."  As noted in "Configuring the Mobile Form", these should exactly match the existing Column headers in the spreadsheet (not the imported columns as shown in the data source -- i.e., the label should be "Site Name", not "sitename").  Otherwise, the data will be added to new columns in the spreadsheet, and won't populate in the form the data source is linked to

ddsendanswers.png

A note on metadata columns: The "Data Record ID" column is unavoidably added to the spreadsheet the first time it is updated through the data destination, but this will not affect the functioning of the data source.  This information could be useful in tracking down the source of newly-added rows, in the event that there was en error in entry.  Feel free to include other metadata columns, like User Name, to make it easier to identify which users added which Site Info rows.

[ top ] 

 

Submit a Data Record

As with all form and data destination/source changes, it is important to test the new functionality before deploying forms to mobile users. 

The user below has answered "Yes" to the "New Site?" question, which will  trigger the "Add New Site Information" Data Destination to execute when the form is submitted, and push the new Site Information to the Google Sheets spreadsheet.

 

The mobile user submits the form.  Within moments, the Google Sheets spreadsheet "Site Info" will be updated, with the new site information added as a new row.  When the Data Source performs its next scheduled fetch, this new row will be included in mobile workers' options for the "Select a site:" question. 

 [ top ] 

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

Comments