Windy.Query

Antonio Nakić-Alfirević · Featured, Functions
A package with a single function that allows running SQL queries against workbook tables.

$39.99

Buy Extension
Instructions
This package contains a single Excel function called “Windy.Query” that supports running SQL queries against workbook tables. This function is similar to the Query function in Google Sheets, but it supports the full SQL syntax and can run queries that reference multiple tables from the workbook.
SQL Query function for Excel
SQL Query function for Excel
The Query function allows running a SQL query in Excel that can work with Excel tables as if they were regular database tables. For the results to automatically spill, Office365 version of Excel is required as it supports the required dynamic arrays feature.
Duration: 125s
Play video

Example:

The following formula joins two tables and returns the result, including the headers. Whenever the person and department tables change, the results will also update automatically.

=Windy.Query("select * from department d inner join person p on d.ID = p.DptId", true, true)

Summary:

The function accepts a string with a SQL query that can work with tables in the workbook as if they were tables in a database. The function internally uses SQLite to process the data and queries, so you can perform joins, create common table expressions or use any other operations that SQLite supports. Single-cell named ranges can be used as variables (e.g. @myVar).

The function supports automatically updating results when the source tables and variables change. 

Query execution is synchronous and will not block the user interface of Excel.

Parameters:

  • query - the SQL query to execute
  • showHeaders- if true, the function will return headers as the first row of the result
  • autoRefresh - if true, the function will listen for changes in the referenced workbook tables and named ranges, and will update the results as needed

Remarks:

  • The returned results are tabular (2D array), so the dynamic arrays feature (introduced in Excel 365) is required to allow the function to spill results. Older versions of Excel can still use the function, but it must be invoked as an array formula, and the results will not automatically spill.
  • By default, updates are throttled to occur no more than once every 2s. This is controlled by the RTDThrottleInterval setting in Excel. This setting has no native UI in Excel, but it can be changed in VBA by entering e.g. Application.RTD.ThrottleInterval=20 in the immediate window. The interval is set in milliseconds.

Requirements:

  • Windows 7 or higher
  • Excel Office365
  • QueryStorm Runtime