Sheets are used to store the data in Studio. To use this data on any screen in your app, or use it in workflows, you need to fetch this data from sheet using a special function known as LOOKUP. LOOKUP is used to retrieve data from any sheet.
To fetch data from any sheet and bind it to a control, you need to write the LOOKUP() formula in the control's formula box in the following format -
Where, Sheetname is the sheet's name from where you want to fetch the data; columnname is the column whose data you want to fetch. If there are more than one columns to be fetched from sheets, then list all the columns in the [ ] brackets separated by comma, e.g. [Sheetname.columnname1,Sheetname.columnname2]
filter is used to filter results in the sheet. Filter has three components, i.e. Column to filter, filter operator & filter criteria. It has the following format - Sheetname.columnname1="Studio"
sorting is used for sorting the result set on the basis of the column specified in either ascending ("ASC") or descending ("DESC") order;
order is the order in which you want to sort the data fetched from the sheet. It can have value either ASC or DESC which needs to be put within double quotes "".
Lets take an example to understand different ways in which you can use LOOKUP() to fetch data from sheets. Lets try to fetch data from a sheet called as BaseSheet. You can find this sheet under Home>Sheets>Global. The following image depicts a snapshot of this Sheet.
Fetching a single value data from Sheet
To fetch a single unit of data in a control, for example say first name from the above BaseSheet where email address is "firstname.lastname@example.org", then the formula will be like -
You should put this formula in the formula box of any control where you want to fetch the first name. Lets say if you want to fetch the data in a text control, then in the formula box, paste the above formula and click on Save and Validate button (as shown in the following image)
Once you see an expected result in formula box, close the formula box and see the result in preview by clicking on preview button. The preview should look like this -
Fetching a list data from Sheet
To fetch a list of data from Sheets, you should bind it to a list type control viz. dropdown, radio, checkbox, multiselect, etc. Lets see this in an example where we would fetch all the departments from the BaseSheet that has a job with title "Staff Accountant IV". The result set of this query would be a list of department that should ideally be mapped to a drop down in order to see it appropriately. Lets bind the following formula to a dropdown control which will fetch all the departments having jobtitle as "Staff Accountant IV" -
LOOKUP([BaseSheet.department],BaseSheet.jobtitle="Staff Accountant IV")
Once you have typed the formula in the formula box of dropdown control, click on Save and Validate button and then click on preview to see the output (as shown in the below image)
In the above image, you can see department "Product Management" is repeated twice. This is because LOOKUP() will always fetch the data as-is from the sheet. In other words, there are two records in the BaseSheet that has department as "Product Management" and jobtitle as "Staff Accountant IV" and hence you can see two Product Management options in the list. To work around this feature, you need to use UNIQUE() formula on LOOKUP() to fetch unique values of department. This can be written as follows -
UNIQUE(LOOKUP([BaseSheet.department],BaseSheet.jobtitle="Staff Accountant IV"))
Fetching multiple column data or tabular list from Sheet
In the above example, we saw how to fetch a single column data. But you will come across situations where you need to fetch multiple column data or data having multiple rows & columns; in such cases you can use all the relevant columns in the lookup formula separated by comma and placed within the [ ]. For example, if you want to fetch first name, department & jobtitle of a user from BaseSheet whose jobtitle is "Staff Accountant IV", then the formula will be like -
LOOKUP([BaseSheet.name.first_name,BaseSheet.department,BaseSheet.jobtitle],BaseSheet.jobtitle="Staff Accountant IV")
To see the output of this formula, paste this formula in a special control called as Sheet Lookup and click on Save and Validate button and preview the app (as shown in the following image).