2023 W8: Stairway to Query heaven
Every week I post about one thing that happened at Rows. We're building in public!
---
Last week we shipped a bunch of stuff (as usual), and the highlight was our new awesome Grid - smooth pixel scrolling, freeze panes that work super well on our page layout, and responsive Charts that adapt to your mobile devices.
Among the smaller upgrades, was a bunch of UX improvements: We launched a bigger query editor inside our Wizard, to power our BigQuery and our QUERY function (yes, the same as Excel and GSheets).
As I was playing with it, I became aware of the many layers of querying that exist on spreadsheets. And that's what I'll talk about. Queries. This will not be a (too) technical post, just a broader perspective focusing on the layers of Queries:
Queries that you execute via spreadsheet functions:
Basic queries that execute maths jobs over ranges;
Intermediate queries, finding values in a database-like fashion;
Advanced queries, that return several rows of data;
Full queries, with enormous flexibility and power;
Natural language (AI) queries, that take your language to solve jobs.
Queries that you execute in a Visual Wizard.
Query Who
Queries are questions or requests.
They are a strong part of the engineering lingo, and a huge part of the data science lingo, much thanks to SQL, an international standard that establishes a language for asking questions to tables of data.
Select Names Where Age > 30 And Role = 'Engineer'
- This is a query.
Query What
There are many ways to do queries in spreadsheets, and Rows as other spreadsheets started by solving the simple cases, then moved on to more powerful ones.
As we progressed, we felt the need to develop visual interfaces to help users, and developed our Wizard.
We can box different queries as follows:
basic: these queries return values for certain conditions, and they always return a single value. COUNTIFS is a great example, as it returns the count of records that match certain criterion. SUMIFS is another simple way to do queries.
intermediate: these queries search values within a table, and operate almost like searches in databases, by matching rows and columns. VLOOKUP and INDEX+MATCH are two ways of doing it. There's an old spreadsheet question that asks "what's the better function, VLOOKUP(), INDEX() + MATCH(), or XLOOKUP()?". The answer is none, and that's why we released the awesome XYLOOKUP()
advanced: these queries return more than 1 value, for example a range with 6 rows and 2 columns of data. FILTER and UNIQUE work this way.
full query: if you want the full power of a query language to ask questions about your spreadsheet data, you can use QUERY. This function uses the SQL syntax to solve your problems, complex as they may be! Learn more about it here. (Btw, The function QUERY_BIGQUERY() does a similar job, but for your projects inside Google BigQuery tables..). We also have the function PARSE, that uses the jsonpath schema to parse through ranges or cells with Json content. it's quite powerful too.
natural language: with the debut of natural language models, we can now write human requests and get answers even without knowing complex spreadsheet formulas. We aren't yet ready for prime time, but it's getting damn close! Use our function ASK_OPENAI() for that!
I recorded a video going through all types of Queries solving problems over a simple Table.
(Unfortunately I'm a bit under the weather so the voice is rough. Apologies in advance!)
Query How (the heaven part).
As you probably already know (we tend to speak about it a lot), we expose all kinds of functionality via Wizards.
In the case, there's many benefits to using the Wizard: clearly readable info, and more space for your queries.
I think this is an obvious step for every spreadsheet out there! Can't wait to be ripped off 🙄.
-
Long term, I believe we will see 3 trends:
natural language will progressively take over many query tasks;
for critical and formal jobs, some of those AI tasks will translated from natural language into a deterministic formulas, and explained back in unambiguous human language.
more visual Wizards, to act as the stress-free interface between humans and machine.
Can't wait to show you what we have ahead. Meanwhile, Query away!
H
(see you next week!)
