Our platform’s Premium BigQuery connector provides a direct link to Google’s BigQuery database service. This tool allows you to automatically insert your form entries as new rows in a target BigQuery database table.

This creates a one-way synchronization, pushing new form entries directly to your database.

Before you add the BigQuery connector, you must complete the following setup steps:

• Ensure a valid Google Cloud Platform (GCP) connection is already configured for your organization.
• Set up a few basic configurations within the GCP Console.

Adding the Connector

Follow these steps to add the connector to your specific form:

1. Navigate to App Workshop > Forms.
2. Hover over your desired form and click the Connect icon.
3. Click the Add Connector button (top-right).
4. Select Google BigQuery from the options.

Note: The page will refresh with the connector added. Changes are not live until you click Save.

Quick Access Tip

If you are already inside the Form Builder or Settings view, you don’t need to go back to the main menu. Simply click the Connectors tab located directly under the form title at the top of the screen.

Configuring the Connector

After adding the form connector, the following configuration options are available.

Project ID

Specify the Google Cloud Platform Project ID that contains your target BigQuery dataset.

Dataset Name

Specify the Dataset Name where the table is located.

Table Configuration

Specify which Form fields to populate as row values in your target dataset.
Upon your first Form entry, each selected Form field will auto-create a column of the same name if it does not already exist.

The types are: Auto Maintain, Mapped Fields, and Custom SQL Template.

Auto Maintain (add rows)

This is a recommended setting, as columns will be automatically added/altered as the Form design changes between versions. Our platform will automatically create and maintain a table in your BigQuery dataset when enabled. Due to restrictions on the BigQuery API, our connectors cannot alter the columns on a BigQuery table. As such, each new version of your Form will create a new table in BigQuery, with the form version added to the given table name as a suffix.e.g. MyExampleTableName_v1…v2…v3 etc.

Mapped Fields (update/add rows)

By default, the connector inserts new rows into the target table. If you want the connector to update existing rows, use the Key option to define a key column that will be used to find a matching row when the connector runs. If a matching row is found, that row will be updated. Otherwise, a new row will be added.

Custom SQL Template

Enter a custom SQL query to execute within a single transaction by this connector. Use our data templating syntax to inject Form field placeholders and define repeating sections. e.g.

DELETE FROM DataSetName.MyTableName 
WHERE Id = CAST({{%ENTRYGUIDFULL}} as string);
INSERT INTO DataSetName.MyTableName ( id, itemno,   formversion, myField1, myfield2,  myfieldn)
VALUES {{!REPEATSTART}} 
(  CAST({{%ENTRYGUIDFULL}} as string),  
CAST({{%ITEMNO as int64}}), 
CAST({{%FORMVERSION}} as int64), 
CAST({{myField1}} as datetime), 
CAST({{myfield2}} as string), 
CAST({{myfield2}} as int64),), 
{{!REPEATEND}};

A unique built-in function, {{%UTCNOW}}, is available to database connectors and returns the date and time the query is executed in UTC. These built-in ENTRYGUID, ENTRYGUIDFULL, and ITEMNO are always guaranteed to have a value regardless of the ‘Use NULL in Repeat Rows’ setting.

Table Name

Specify the table name that will receive entry data for this Form.
A default table name will be generated using the Form External ID if left blank.

Data Columns

Specify which Form fields to populate as row values in your target dataset.

Upon your first Form entry, each selected Form field will auto-create a column of the same name if it does not already exist.

User URL Media Fields

If enabled, media filenames will be converted to their complete URLs.

Replace Placeholders with Display Text

By default, placeholders will be replaced with the raw answer value from the form entry.

For example, date values are stored as UTC time in ISO 8601 raw format, e.g., 2015-10-23T15:05:07Z.

Select this option if you wish to replace with user-displayed text instead, e.g., 23-Oct-2015 05:05:07, assuming your time zone is UTC-10

Use NULL in Repeat Rows

If enabled, Form fields appearing in repeat sections will be set to NULL in all but the first repeat.
The following are NOT affected by this option:

• ENTRYGUID, ENTRYGUIDFULL, ITEMNO, and UTCNOW
• Fields that appear within a table or repeating page
• Formulae that reference a repeating field

Anonymize Personal Data

If enabled, data from fields marked as Personal Data will be converted to a non-human-readable format to aid privacy.

Ready to go

At this stage, your Connector is ready to go.

Hit the SAVE button to save your changes and activate the Connector.