I am importing ranges from other spreadsheets and filtering them using QUERY.

But I am trying to make my QUERY more resilient to changes, such as addition or removal of columns, as it can break when new columns are added or old removed. I'm using this formula

=LET(imp;IMPORTRANGE("sheet_url"; "sheet_name!A1:AA"); QUERY({imp}; "Select Col1 where Col"&MATCH(INDEX(IMPORTRANGE("sheet_url"; "GM");1); INDEX (imp;1);0)&" is not null";0))"

The formula is working but is not solving my current need. Any tip?

I am importing ranges from other spreadsheets and filtering them using QUERY.

But I am trying to make my QUERY more resilient to changes, such as addition or removal of columns, as it can break when new columns are added or old removed. I'm using this formula

=LET(imp;IMPORTRANGE("sheet_url"; "sheet_name!A1:AA"); QUERY({imp}; "Select Col1 where Col"&MATCH(INDEX(IMPORTRANGE("sheet_url"; "GM");1); INDEX (imp;1);0)&" is not null";0))"

The formula is working but is not solving my current need. Any tip?

• google-sheets

Share Improve this question Follow edited Mar 22 at 15:58 pgSystemTester 9,98722 gold badges2727 silver badges5757 bronze badges asked Mar 22 at 12:53 user30023146user30023146 1 1

• 1 1) working but is not solving my current need Please edit the question to explain exactly what is meant by this statement. Would you also read How to create a Minimal, Reproducible Example and then edit the question to include an MRE including sample data and an example of a successful outcome. 2) Query using column header as reference This part of the question title is not referenced anywhere in the body of the question. Please remove it from the title or edit the question to explain how it is relevant to your problem. – Tedinoz Commented Mar 22 at 13:21

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

To make it easier to refer to columns by name, use a named function or a lambda, like this:

=let( 
  data; importrange("..put sheet ID here.."; "sheet_name!A1:AA");
  headers; chooserows(data; 1);
  col_; lambda(colName; " Col" & match(colName; headers; 0) & " ");
  statement; "select" & col_("Sales") & "where" & col_("GM") & "is not null";
  query(data; statement; 1)
)

See named functions and lambda().