- Set Up the Data Source
- Data Retrieval and Format
The Google Sheets Data Source retrieves data from a Google Sheets spreadsheet. This data source can be used in conjunction with the Google Sheets Data Destination to update spreadsheets referenced by the data source. This data can then be used to populate a list of options in dropdown or radiobutton questions.
Data sources are usually set up to fetch data on a specified schedule. This allows teams to maintain and update their data through the external service of their choice, minimizing the need to access the web portal.
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.
- Create the data source as described here.
- Select Google Sheets as the type.
- Name the data source and optionally enter a Description.
- Set up the data source using the information below.
Please note the following when setting up your spreadsheets for use with the data source:
- The first line in your spreadsheet will be read as a header row.
- Any columns not containing a header value will have a unique header name automatically generated.
- The first blank line in your spreadsheet will be treated as the end of input. If you are missing data from your Data Source, verify there are no empty lines in your worksheet.
- Although a Google Sheet can contain multiple worksheets, only one worksheet can be referenced by a single data source; select the desired worksheet in the Google Connection tab.
If this option is selected, users will get a notification from the ProntoForms app after a fetch. It will prompt users to reconcile (send/receive or refresh). This setting is recommended if it is important that users have the most up-to-date data.
- With "push updates" and an automatic fetch set up, some data sources send a notification for each fetch, whether or not the data source has new data.
- If the fetch schedule is hourly, users may get notifications hourly even if the data has not changed.
- Some users may find these notifications annoying or confusing. Consider the fetch frequency carefully if notifications are enabled.
Automatically fetch new data on a schedule:
- If checked, data will be retrieved from Google Sheets at the Fetch Frequency interval.
- Make sure that the team has a Problem Contact Email Address. If a fetch is unsuccessful for any reason, this address will receive an email with an error message and details. Without this email, it may not be immediately clear that a fetch has failed.
- If unchecked, the data source will not be updated automatically. To retrieve new data from Google Sheets, the data source will need to be manually updated through the "Fetch New Data" button in the web portal.
Choose Hourly, Daily, Weekly or Monthly. This is how often the data source will fetch new data from Google Sheets.
This applies to all fetch frequency options except “Hourly.” Data will be fetched at midnight in the chosen time zone.
Configure a Google Connection or use an existing connection.
Enter the path to the file containing the data. File path is found in Google Drive, just below the Search bar.
- Path includes name of the Google Sheet, without file extension:
- Correct case should be used when referencing both path and worksheet.
- The spreadsheet may contain more than one Worksheet. If this is the case, enter the worksheet name. If nothing is entered in the Worksheet Name box, the first Worksheet in the spreadsheet will be used.
- If left unchecked, the data source will wait until the specified time and interval to retrieve data for the first time.
- If checked, the first data retrieval will occur immediately after creating the data source.