Introduction
Lookup is a property that uses a Google Spreadsheet as data source. This means that you can define unlimited values for your property.
Another great feature included in this property is the possibility to create hierarchical values (one level per column from the Google Spreadsheet).
You can create lookup properties in several Google Drive folders and/or Google Team Drives using the same Google Spreadsheet to get the values. (Properties will be different)
Lookup Configuration
In the following video you can find a brief example about how to use and configure a Lookup property.
The following paragraph contains a more detailed explanation about how to configure a lookup property.
To configure your Lookup property, firstly you need to create a Google Spreadsheet with all the values you would like to use.
Each row of the Spreadsheet will be a selectable combination. For example, imagine that we have a Google Spreadsheet with 3 columns (Region, Country and City). If we want to make only cities selectables we need to create combinations like:
| Region | Country | City |
| Europe | Spain | Madrid |
| Europe | Spain | Barcelona |
If you want to make countries selectables you need to add a row for each country, like this:
| Region | Country | City |
| Europe | Spain | |
| Europe | Spain | Madrid |
| Europe | Spain | Barcelona |
In this example you will be able to select Spain, Madrid or Barcelona.
IMPORTANT: First row is mandatory. Reserve the first row to indicate which values will be stacked on each column. If you don’t know how to tag your columns just write column1, column2….columnN to differentiate them.
Once you have your Spreadsheet, go to Metadrive and create a lookup property:
- Select your Google Spreadsheet using the Spreadsheet picker.
IMPORTANT: Ensure all editors using Metadrive folder have access to the data source (Spreadsheet). If someone hasn’t permissions to see the Spreadsheet, he/she will not be able to edit or filter the lookup property.
- Specify the range of your data. Use A1 Notation, you can write Sheet name!First Cell:Last Cell or directly First Cell:Last Cell (first sheet by default)
What is A1 Notation?
This is a string like Sheet1!A1:B2, that refers to a group of cells in the spreadsheet, and is typically used in formulas. For example, valid ranges are:
- Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.
- Sheet1!A:A refers to all the cells in the first column of Sheet1.
- Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.
- Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.
- A1:B2 refers to the first two cells in the top two rows of the first visible sheet.
- Sheet1 refers to all the cells in Sheet1.
- And select how you would like to see your property. You have 2 options: List or Tree.
List selectors are dependant, if a value is selected, only possible values will appear on the next selector. Pay attention that some intermediate nodes can be selectable and some others not and this will cause that not all the selectors will be used.
List
Tree
Finally save your configuration and it’s done!
Limitations
There is a limitation on the number of characters for a combination. This means, you can have unlimited combinations (rows) but the sum of all the characters combined cannot be greater than 100.
For example, if you have a row like this:
| Column 1 | Column 2 | Column 3 | ………………… | Column N |
| Value 1 | Value 2 | Value 3 | ………………… | Value N |
And the sum of Value 1, Value 2, Value 3…. Value N is greater than 100, this row will not appear on the lookup property.
This happens due to Google Drive API limitations.