Google Sheets query with column names instead of letters/index

Problem

A normal query like this is fragile and opaque, if we insert a column we have to update all our references, and without column names it's really hard to tell what it's doing.

=QUERY('My Data'!A4:AAC, "
select Col5, sum(Col4), sum(Col10)
where Col3 = FALSE
and Col5 is not null
and Col2 <> 'Head Office'
group by Col5
order by Col5 desc
")

Solution

We define our query as a template and reference the target columns by header name.

The goal is to be able to define our query like this:

=SUPERQUERY('My Data'!A4:AAC, "
select {{Film Week Start}}, sum({{Computed Cost}}), sum({{TotalTime}})
where {{Ignore}} = FALSE
and {{Film Week Start}} is not null
and {{EmployeeType}} <> 'Head Office'
group by {{Film Week Start}}
order by {{Film Week Start}} desc
")

Here's an implementation of our SUPERQUERY named function taking query_template and data_range parameters:

=LET(
  start_delimiter,
  "{{",
  end_delimiter,
  "}}",
  header_row_range,
  OFFSET(data_range, 0, 0, 1),
  header_names,
  ARRAYFORMULA(UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN("❄", TRUE, IFERROR(REGEXEXTRACT(SPLIT(query_template, start_delimiter), "(.*?)" & end_delimiter))), "❄")))),
  placeholders,
  ARRAYFORMULA(start_delimiter & header_names & end_delimiter),
  col_strings,
  ARRAYFORMULA("Col" & MATCH(TRIM(header_names), header_row_range, 0)),
  final_query_string,
  REDUCE(
    query_template,
    SEQUENCE(ROWS(placeholders)),
    LAMBDA(current_query, i,
      SUBSTITUTE(
        current_query,
        INDEX(placeholders, i),
        INDEX(col_strings, i)
      )
    )
  ),
  QUERY(data_range, final_query_string)
)

johtso/superquery.md

Problem

Solution