Query Parameters in Google Sheets Add-on

When you need to run the same query often, but with some different values (for example, build the same report but for different time periods), you can use queries with parameters.

 

ExpandedToggleIcon        Using Parameters

A parameter is a variable that you can use in query filters or in the SQL code of a query instead of a constant value. After you configure your query, you will be able to set new parameter values without modifying the query itself.

Parameters are especially useful, if you save the query with parameters in the Query Gallery, and then reuse it in this or another workbook. Skyvia Query add-on allows you to set parameter values directly from the gallery without even opening the query in the editor.

query-add-on-gallery-query

 

ExpandedToggleIcon        Parameter Names

When you specify parameter name in a filter condition, in visual query builder, you must specify the name, consisting of alphanumeric characters or underscore. Skyvia will automatically add the necessary prefix to it.

When you use a parameter in the SQL code, you should prefix the alphanumeric name with the colon ":" character for MySQL or with the "@" character for all other data sources.

note Note

You can use the colon ":" character for other sources too, if necessary. Additionally, for most data sources you can use unnamed parameters in the SQL statements, that are added as the "?" character.

 

ExpandedToggleIcon        Setting Parameter Values

Parameter values can be set and modified whenever necessary without changing the whole query. You can set parameters for a query either in the Query editor or in Query Gallery (if you saved your query with parameters in it).

To specify values for query parameters in the Query editor, click the Parameters button. Skyvia Query will detect all the parameters, used in the query, and display their list, allowing you to select a data type and specify a value for each of them. After this, you can either apply parameter changes or cancel them, using the Apply or Cancel buttons respectively.

query-add-on-parameters

In Query Gallery, the parameters are displayed when click a query with parameters. You can quickly configure their types and values before running the query, without even opening it in the Query editor.