Query IMPORTRANGE Function to Skip Columns in Google Sheets

google sheetsgoogle-sheets-queryimportrange

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 need to be fixed and unchanging. However, I'm now getting:

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

Is it possible to edit the function above and tell it to skip Columns 4 and 5 when it's pasting the data from the source spreadsheet?

Best Answer

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.

Related Topic