I was searching for a suitable answer to this question, also, since using column letters like A, B, C is not good for flexibility and becomes a maintenance hassle. Every time your source data columns are reordered, or a new column comes in, you have to update your QUERY().
So I came up with this solution. It does require a short Apps Script function, but the benefit is that you get quite a clean, readable, maintainable query.
You use it like this:
=QUERY(A9:C13, QueryByName(A9:C9, "SELECT `Name`, `country` WHERE `age`>7"))
You use it with a name range like this:
=query(RangeDataName,QueryByName(RangeDataName, "SELECT `Contract`","RangeDataName"))
Here is a real-world use case with labels and format.
=QUERY(
Data!A1:Z,
QueryByName(
Data!A1:Z1,
"
SELECT `Epic name`,
SUM(`Time Spent (Minutes)`)/60,
SUM(`Original Estimate (Minutes)`)/60
where `Epic name` is not null and `Original Estimate (Minutes)` > 0 and `Time Spent (Minutes)` >0
group by `Epic name`
label SUM(`Time Spent (Minutes)`)/60 'Time Spent (hours)', SUM(`Original Estimate (Minutes)`)/60 'Time Estimated (hours)'
format SUM(`Time Spent (Minutes)`)/60 '0.00', SUM(`Original Estimate (Minutes)`)/60 '0.00'
"
)
)
In order to use this function, open your Google Sheet, click Tools > Script Editor and paste in the following function.
/**
* Custom sheet function for allowing the use of column headings
* instead of column labels inside the QUERY() functionx
*
* Example:
* =QUERY(A1:C13, QueryByName(A1:C1, "SELECT `name`, `country`"))
*
* The first range (in the QUERY() function) is your data source,
* and the range given in QueryByName() should be the header row
* that lists out your headings. Consider giving both ranges
* a name for easy reference.
*
* The third parameter 'firstColumnIndex', is only used if your
* source data does NOT start in column A. Otherwise you can leave
* it blank. If it starts in column B, set this to 1, if it starts
* in C, set to 2, etc.
* You can make this dynamic by setting it to COLUMN(x) where x is
* the top-left cell of your source data (or even the entire range).
*
* Function by Simon East
* <https://webapps.stackexchange.com/a/155456/20364>
*/
function QueryByName(columnHeadings, queryString, nameRange = false, firstColumnIndex = 0) {
// Lowercase all heading names so that case errors are accepted
const headings = columnHeadings[0].map(item => item.toLowerCase());
// Use a regular expression to get a list of all strings inside `backticks`
let fieldNamesInQuery = queryString.matchAll(/`([^`]+)`/g);
// Find column number by range
if (nameRange) {
firstColumnIndex = findColumnNumberByRangeName(nameRange)-1;
//let columnLetter = columnNumberToLetter(columnNumber);
}
// Loop over each one and replace it with the column letter
for (let [nameWithTicks, name] of fieldNamesInQuery) {
let columnLetter = columnNumberToLetter(headings.indexOf(name.toLowerCase()) + firstColumnIndex + 1)
if (!columnLetter)
throw new Error('The field named ' + nameWithTicks + ' was not found in the header range you specified.');
queryString = queryString.replace(nameWithTicks, columnLetter);
}
return queryString;
// Converts 1 to A, 2 to B, 27 to AA, etc...
function columnNumberToLetter(column) {
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
function findColumnNumberByRangeName(name) {
var Range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(name);
return Range.getColumn();
}
}
If you experience any bugs, feel free to post a comment here.
