Using column names in IMPORTRANGE QUERY

Question

I have used a script that allows me to substitute column names for Col# format in QUERY of a sheet in the same Google workbook but when I call the QUERY from a different Google Sheets spreadsheet with IMPORTRANGE, I get this error:

Range not found (line 11, file "SQLfunction")

My calling cell looks like this:

=QUERY(
  IMPORTRANGE("long-hairy-key","MASTER!A:BN"),
  IMPORTRANGE("long-hairy-key",SQL("MASTER!A:BN",STATS!D33,true)),
  1
 )

STATS!D33 is this:

select TRACT,PARID,LINE,OWN1,T_STATUS,ExhibitDate,FEET2,NewEase,TWSacres,ExistEase,PrevDistTWS,ATWSacres,ARdistance,StagingArea,Damages,UpdateReason,ACQ_STATUS where Col51 <>'' ORDER BY LINE ASC, TRACT ASC Label OWN1 'Landowner'

and the SQL function is from here.

Can someone recommend how I might adjust the script to accommodate IMPORTRANGE?

Answer 1

The referred SQL() custom function is not able to access an external spreadsheet. You have to adapt the SQL() custom function to make it able to access an external spreadsheet or to redesign your formula.

If you go for redesigning the formula, one approach is to move the import of the external data out of the "main formula". To do this,

1. First, get the external range. Add a new sheet (assume that it's name is Sheet1) and add the following formula to A1 =IMPORTRANGE("long-hairy-key","MASTER!A:BN")
2. Second, replace the "main formula" by other like the next one:

=QUERY(
   Shee1!A:BN,
   SQL("Sheet1!A:BN",STATS!D33,true)),
   1
 )

Answer 2

You may generate sql statement with formulas.

Step1. Use importrange to get headers only.

Paste this formula in Sheet1 A1:

=transpose(IMPORTRANGE("long-hairy-key","MASTER!A1:BN1"))

Need to transpose the data to make vertical list of field names.

Step2. Add column numbers.

Paste this formula in B1:

=ARRAYFORMULA(ROW(OFFSET(A1,,,counta(A:A))))

In this step you will get table, which looks like this:

  A               B
1 FieldName1      1
2 FieldName2      2
3 FieldName3      3
4 FieldName4      4
5 FieldName5      5

Step3. Combine the SQL statement.

Use column C to indicate, which columns are to be selected in your query:

  A               B    C
1 FieldName1      1    1
2 FieldName2      2
3 FieldName3      3    1
4 FieldName4      4
5 FieldName5      5    1

And use this formula in free cell (F1):

="select Col"&JOIN(", Col",FILTER(B:B,C:C=1))

In my case, this formula gives string select Col1, Col3, Col5, and this can be used in query formula:

=query(IMPORTRANGE("long-hairy-key","MASTER!A:BN"), F1)

Use additional columns to make your SQL smarter, add where clause, order by, label and so on.

---

Update

So this step-by-step solution doesn't solve the problem with rearranged columns.

The trick is to use column names list and save it as values:

1. paste the formula from step1: =transpose(IMPORTRANGE("long-hairy-key","MASTER!A1:BN1"))
2. copy it → go to paste spacial → values

Then you need to go through steps 1 and 2 in order to make the list of current firld numbers.

And then you'll have actually two tables. The first one will hold actual field list and field numbers, generated by formulas from step1 and step2. Here's Table1:

  A               B
1 FieldName1      1
2 FieldName2      2
3 FieldName3      3
4 FieldName4      4
5 FieldName5      5

And the second table will have SQL settings. Here's Table2::

  A               B    C
1 FieldName1      1    1
2 FieldName3      3
3 FieldName2      2    1
4 FieldName4      4
5 FieldName5      5    1

Field names in it must be entered as values. Column B will contain vlookup function:

= vlookup(A1, table1!A:B, 2, 0)

or even ArrayFormula:

= arrayformula(vlookup(offset(A1,,,counta(A1:A)), table1!A:B, 2, 0))

That's all you need to start. Then just make your query smarter.