You shouldn’t buy this!

In 2021, Gravity Wiz contracted me to fork this plugin. Since then, they’ve launched Gravity Perks Google Sheets, and you should buy that instead. It’s way better, and I helped build it. Check out the software I offer now or hire me to build the plugin you need at https://breakfastco.xyz/ I am listed on the Gravity Wiz Recommended Developers page.

Thanks,
Corey Salzano

Convert alphabetical Google Sheet column names to numbers

Google Sheets QUERY statements typically work with A, B, and C column names just fine.

=QUERY(A1:B326,"SELECT SUM(A) WHERE B = 'Active'")

Here’s another QUERY that SELECTs data from another tab in the same sheet with a GROUP BY clause and column labels.

=QUERY(Sites!C2:C178,"SELECT C, COUNT( C ) WHERE C != '' GROUP BY C ORDER BY COUNT( C ) DESC label C 'Install', Count( C ) 'Count'")

However, QUERY requires column names Col1, Col2, Col3 instead of A, B, and C when querying data from a different Sheet.

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1KYK-Op3XKNElfwD0cz0SmBVA1y8NgnCr4bU2u6NJuN0/edit#gid=0", "Sheet2!A1:B326"),"SELECT SUM(Col1) WHERE Col2 = 'Active'")

I find it difficult to remember the letter O is the 15th letter of the English alphabet. There is no chance that I am going to memorize that column BH is the 60th.

Column name converter

Converts A to 1 and BH to 60



What’s going on in that second query?

QUERY can pull data from a Google Sheet at a different URL if a call to IMPORTRANGE is passed into the first parameter of QUERY.

Referencing tabs with spaces in their names

Use single quotes around a tab name that contains spaces, like ‘Raw Data’.

=QUERY('Raw Data'!A2:E700,"SELECT D,E,B WHERE A = 'received'",1)