Create Ticket My Tickets Post Discussion
Welcome
Login  Sign up
  1. Knowledge Base
  2. Platform
  3. Form Connectors

Google Spreadsheet - Form Connector

TABLE OF CONTENTS

Google Spreadsheets is a popular, free platform for creating and maintaining spreadsheets. Its popularity has also resulted in Google Spreadsheets becoming a commonly accepted means of sharing data between systems. One of the easiest ways to automate your process is to connect your form data entries to a Google Spreadsheet. For example, if a timesheet form is captured, it would be helpful to have that information appear in a time-sheet spreadsheet automatically. This is where the Google Spreadsheet Connector for Forms comes in.

NOTE: Google Spreadsheets currently have a limit of 5,000,000 cells per spreadsheet.  If your expected output will grow beyond this limit, you must either create a new Spreadsheet (and update the Spreadsheet name in your connector) or consider an alternative connector option. For more information, refer to Google's help center here: https://support.google.com/drive/answer/37603

Before adding a Connector, you must ensure a valid Google connection is set up in your organization.

Adding a Google Connection

  1. To add a connection, go to the Connections page which is accessed using the left-side menu on the platform, Connected Data -> Connections. You need to be an Administrator-level user to see this.

  2. On the Connections page, click the "Add Connection" button (on the top right) and choose Google. The page will refresh, showing a new Google connection row.

  3. Click the Authorize button. This will take you to the Google accounts page where you must log in with your desired Google account. Make sure you log in with the right account - this is the account that all Google Connectors will use. Grant the access requested - this is so that our Connectors can create and update entries on your Google account.

  4. After granting access, you should be returned back to the Connections page. Your connection will be verified automatically - you will see a green tick if everything is working.

Adding a Google Connector to your Form

Once you have a valid connection, you can add as many Connectors as you like to a Form. This is done via the Connectors page. The Connectors page can be accessed from your Form design, settings or from the Screens listing if you mouse over the Form row in question. Once you are in the Connectors page, setting up a Google Spreadsheet Connector is easy:

  1. Hover over the "Add Connector" button to see a list of the available Connectors. Click the Google Spreadsheet option.

  2. This will refresh the page and display your Connector, ready for configuration. Note that at this stage nothing has been saved: you must click the Save button to save your Connector after you've finished setting it up.

  3. Enter/select the settings for the connector:
    • Using Connection (not pictured): Choose the connection to use. This is relevant if multiple Google connections are set up in Connections.
    • Spreadsheet Name: Type the name of the spreadsheet using static text and/or field data names.
    • Folder Path: Type the destination folder path where the file will be placed. Omit the top-level Shared Drive directory and include only subfolders (if any).
    • Shared/Team Drive ID: Enter the unique identifier of the Shared Drive on Google if the file being synced exists in a Shared Drive. To retrieve the ID, go to your Google Drive in a web browser and navigate to the top-level Shared Drive folder (even if the destination folder is a subfolder within the Shared Drive). Next, check the address bar of your web browser and copy the characters which follow the “/folders/” part of the URL. E.g., if the URL is https://drive.google.com/drive/folders/1234567890ABCDEFGHI, then the drive ID is 1234567890ABCDEFGHI.
      Note that this applies only to an entire Google Shared Drive, not just a single file or folder. Shared Drives are currently available to G Suite Business accounts.
    • 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.

    • Anonymize Personal Data: Check this box data to convert data from fields marked as Personal Data to a nonhuman-readable format to aid privacy. Please see this article for more on this.
    • Update Sheet Headers: Check this box to update Google Sheet headers each time the connector is processed. See more in the related section below.
    • Only Perform This Action When: Refer to "Add a Run Condition" below.
    • Run After Connector: Creates a dependency for this connector's execution based on the outcome of the selected connector. E.g. if you select Success then this connector will only run if the selected connectors outcome was successful. 
  4. At this stage, your Connector is ready to go. Hit the Save button to save your changes and activate the Connector.

Add a Run Condition

Sometimes you may have a Connector that you only want to fire in the event that the Form entry has a given answer value. For example, you may have an Audit Form which has a risk rating question with options like "Low," "Medium" and "High." If the user answers the risk rating as "High," then you want an email to be sent to a supervisor for follow-up and action.

This is where a Run Condition comes in. Run Conditions are defined by creating a true/false formula. The Connector will only run if the formula you define in the Run Condition has a true result.
So, in the example above, the Run Condition formula would be something like:

{{riskRating}} = 'High'


When you hover over the Run Condition field, you will see a hammer icon appear. Clicking the hammer will take you into the Formula Builder, which helps you to put together the formula you desire.

See the Creating a Formula page to learn more about form formula.

Update Column Headers

We highly recommend that all users enable this option to ensure that column headers are aligned with the correct columns even when form changes are made to add new fields. This is needed because of the way that column headers are added to a connected Google Sheet when the first entry is uploaded. The headers are based on whatever data names are on the form at that time. If at any point more fields are added to the form, more columns will also be added; and without this option enabled, column headers will remain static and the column headers will likely be misaligned with the existing data from previous entries (and the columns furthest to the right will have no headers at all). For this reason it's recommended to always check this box when adding a Google Sheets connector.

Please note that while this option does allow new headers to be added, it will not result in data in spreadsheet columns being rearranged to reflect any resulting ordering changes for the headers.



Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.