Query(IMPORTRANGE) function to skip columns

Question

I'm currently using the following function to import data from one spreadsheet to another based on a value in Column K of the source spreadsheet:

=QUERY(
   IMPORTRANGE("URL","Sheet Name!A2:P1000"),
   "SELECT Col1,Col2,Col3,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 
      WHERE Col11 CONTAINS 'West'",
    1
 )

Notice I am not importing Columns 4 & 5 (D & E) because in my destination spreadsheet, those two columns are already populated and I don't want those columns to be overwritten.

However, the formula is returning:

#REF Error: Array result was not expanded because it would overwrite data in D2.

Is it possible to edit the formula so that it skips Columns 4 and 5 when it's pasting the data into the destination spreadsheet?

Answer 1

No, it's not possible to "skip columns".

Instead you could use two query functions, one to be used to get the columns A - C, the other to get the columns F and following.

First formula (A1):

=QUERY(
   IMPORTRANGE("URL","Sheet Name!A2:P"),
   "SELECT Col1,Col2,Col3
      WHERE Col11 CONTAINS 'West'",
    1
 )

Second formula (F1):

=QUERY(
   IMPORTRANGE("URL","Sheet Name!A2:P"),
   "SELECT Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 
      WHERE Col11 CONTAINS 'West'",
    1
 )

REMARKS:
Note that instead of Sheet Name!A2:P1000 was used Sheet Name!A2:P as this take the whole columns instead of only 1000 rows.

Answer 2

If you want to 'skip a column', you can use regexreplace on one column of your range; so it will have the same number of rows.

You would not be able to edit the column afterward obviously, but you can utilize some code in the final input for regexreplace function to drop-in what you need.

=arrayformula({index(_yourRange,,1),regexreplace(index(_yourRange,,1),".+","placeholder text or function")})

Answer 3

It is possible to skip columns with the query formula you originally posted. I've had success using this query =QUERY(IMPORTRANGE("URL","Sheet Name!A2:P100"),"select Col1,Col3,Col5,Col6",0)

I then modified my original spreadsheet to contain 'West' in column K and it pulled that row just fine skipping columns.

The error you posted:

#REF Error: Array result was not expanded because it would overwrite data in D2.

May have been because there was existing data in D2 and IMPORTRANGE will not overwrite existing data. You must delete the contents in cell D2 first.