Look Ups are integrations we build often. A Look Up is a set of fields in an OpenWater form that queries an outside source ( SalesForce, a Google Sheet, a CSV, etc), to autofill information for the user.

In this article, we'll go over the steps for setting up an Integrations Hub Look Up with a Google Sheet. We'll cover both creating an Autocomplete field and a Prefill field.

1. Get Started with Integrations Hub.

2. Under Connectors, click Look Up from the Integrations Hub dashboard.
​

3. You will be brought to a page with different Look Up options. Select Google Sheet.

4. The first tab is used to set up an Autocomplete field. Users will be able to start typing a value in this field and then be presented with matching data from the Google Sheet.
​
To set this up, under General Settings, add a Name (which can be anything- i.e. "Test Lookup")
​
Then add a link to your Google Sheet URL. If the Sheet has not been Shared to Everyone with the link, an error will show:
​

5. In the example Google Sheet we have 3 columns, Id, Name and Email. These are shown under Fields. To use these we'll add a Label, in this case we'll use Name and Email after copying them from under Fields. We'll set the Value to Id by clicking on the field and selecting Id from the dropdown that appears.
​

6. From there we'll configure the Filter Settings section. This is where we'll add Rules to determine which results are displayed when a value is entered into our Autocomplete Field.
​
To get started, click Add Rule, you can then click the Select Field box and in this case we're going to use the Name field, change the condition from == to Like and leave the {query} as is.

​

With this setup, when a user begins entering a Name into the autocomplete field, values that are Like that name will be displayed. {query} is the text entered by the user.
​
You can now Save and easily Test your Autocomplete field using the links above:
​

You're now ready to add the Autocomplete field to your submission form. Start by going to Round Settings > Submission Form in your program and opening the page you'd like the Autocomplete field to be on.
​
Add a new Text field and check Autocomplete from third party this will give you 2 additional options, one to require Require selection from third party and the other to Set field values from third party which will allow you to Prefill a field from your Integrations Hub, we'll go over that in detail in the next section.
​

7. To begin setting up a Prefill field, click the second tab in your Connector configuration:
​

Under General Settings, the Prefill URL is added to the submission form field you'd like to configure the Prefill on. You can add it when building your submission form under the Field Settings tab by selecting Autocomplete from third party and Set field values from third party:
​

8. The next field is Google Sheet Prefill Url, if you want to use the same sheet as your Autocomplete connector, you can leave this blank, otherwise you'll want to provide a link to the sheet you'd like to use and confirm it is shared publicly.

9. Under Prefill Settings, select Add Prefill Item. Click on Alias and select a value from the dropdown that appears, then do the same for Field Value.
​
More information on using Aliases can be found here:
​http://help.getopenwater.com/en/articles/6739168-submission-form-aliases
​
​Note: Rule must be created (see Step 6) before proceeding.
​

You can now Save and easily Test your Prefill field using the links above before adding the Prefill URL to your submission form:
​

10. In the Round Settings > Submission Form builder, once configured a Look Up field with Prefill functionally will look like this:

11. You have now completed building an Autocomplete / Look Up and Prefill field in Integrations Hub from a Google Sheet.

NOTE: The integration will re-cache data from the Google Sheet every minute.

If you need to manually re-cache the data you can use the "Refresh" button in the Integrations Hub UX

---

USING A PRIVATE SHEET:

​
​
​
​
​
​
​
​
​
​
​