Examples

Overview

This guide provides examples for using the =FLEX() function in your spreadsheet. See examples on how to load a data set, select the columns you want to view, filter results, and other common operations.

To try these examples, you’ll need a Flex.io account and either the Excel add-on or Google Sheets add-on.

Contents

1. Overview of the Flex function
2. Loading a data set
3. Showing and hiding column headings
4. Selecting columns
5. Using filter criteria
6. Sorting results

Overview of the Flex function

The most common form of the =FLEX() function is:

=FLEX( [function-id], [columns], [filter criteria], [configuration] )

The function parameters are used as follows:

The examples below show how to use each of these four common parameters.

Loading a data set

To load a data set, you use the =FLEX() function to select the records you want from one of the integrations you’ve installed. We can try this using two sample data sets that come pre-loaded and ready to use with Flex.io:

• Sample contacts, simliar to what you’d find in a typical contact list
• U.S. Zip Code statistics, including the 2010 population density for these Zip Codes

To load the sample contacts, copy and paste the following formula into any cell in a blank sheet:

=FLEX("flex-sample-contacts")

To load the Zip Code statistics, use this formula instead:

=FLEX("flex-zipcode-stats")

Showing and hiding column headings

Column headers are displayed by default. If you want to hide the colum headers, add headers=false to the configuration parameter.

=FLEX("flex-sample-contacts","","","headers=false")

If you want hide column headers and also limit the results the function returns, you can combine headers=false with a limit= setting by separating them with an & symbol.

=FLEX("flex-sample-contacts","","","headers=false&limit=2")

Selecting columns

Use the columns parameter to select the columns you want to load. You can either specify the column names explicitly, or use a range of cells to get the column names from your spreadsheet.

Select a set of columns

You can pick any column order and display columns multiple times. Here, we select four columns by specifying the column names in the second parameter.

=FLEX("flex-sample-contacts","id, first_name, last_name, email","","limit=2")

Select columns specified in a list

This example gets the column names from a list in the range B3:B7.

=FLEX("flex-sample-contacts",B3:B7,"","limit=4")

Select columns based on a set of headings

You can select columns without editing your formula. If you put this formula in B3 of your sheet, it will select the columns you type in row above it.

=FLEX("flex-sample-contacts",B2:E2,"","headers=false")

Using filter criteria

You can use the filter criteria parameter to apply a set of query criteria to the data you load. The =FLEX() function uses the Lucene Query Syntax for the filter criteria, so you can use keyword and wildcard matching, select a range of values, exclude certain values from results, or apply any other kind of criteria the Lucene Query Syntax supports.

Select records that contain the value “Rogers”

Here the filter criteria is simply the value “Rogers”. Since this example does not include a column name in the filter criteria, the Flex function searches for “Rogers” across all columns.

=FLEX("flex-sample-contacts","","Rogers")

Select records where the last name is “Rogers”

By adding last_name: to the filter criteria, the Flex function only searches for “Rogers” in the last_name column. If you change the filter criteria to city:Rogers here, you’ll select the record for Joesph Cox instead of Tracy Rogers.

=FLEX("flex-sample-contacts","","last_name:Rogers")

Select records where the Zip Code is either 60605 or 60606

You can filter on any number of columns. When you add multiple filter criteria, separate each with a space.

=FLEX("flex-sample-contacts","","zipcode:60605 zipcode:60606")

Select records where the email address does not contain “gmail.com”

You can exclude certain records from the results using a minus symbol (-) at the start of one or more of the criteria.

=FLEX("flex-sample-contacts","","zipcode:60605 zipcode:60606 -email:gmail.com")

Select records where the state is “IL” and the city is “Mattoon”

By adding a plus symbol (+) at the start of one or more of the criteria, you can limit the results to only records that meet these conditions.

=FLEX("flex-sample-contacts","","+city:Mattoon +state:IL")

Sorting results

After selecting a set of data, you can sort the results by adding a =SORT() function to the formula.

Sort records by last name

This example sorts the results based on the third column, which is last_name in the sample contacts data set.

=SORT(FLEX("flex-sample-contacts","","city:Chicago","headers=false"),3)

Sort records descending by birthday

If you put this formula in B3 of your sheet, it will get column names from the row above (in range B2:J2), and then sort the results by the seventh column.

=SORT(FLEX("flex-sample-contacts",B2:J2,"+state:NY +email:gmail.com","headers=false"),7,-1)