CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulagoogle-sheets-queryimportrange
Héctor Ruíz
Héctor Ruíz

Reputation: 35

Any way to create a copy of my document of Google Sheets with query-importrange function without returns a message error?

I've made a query function with importrange function on Google Sheets. The problem is when I try to make a copy of that document for share then in the copied file returns

"No_Column: Col2".

I've no idea how to solve this issue. 

=QUERY(
  { IMPORTRANGE("url", "'Parte 1'!A1:AH");
    IMPORTRANGE("url", "'Parte 2'!A1:AH");
    IMPORTRANGE("url", "'Parte 3'!A1:AH");
    IMPORTRANGE("url", "'Parte 4'!A1:AH")
  }, "Select Col2, Col1, Col34, Col24, Col3, Col4, Col5, Col6, Col11, Col7, Col8, 
             Col9, Col10, Col12, Col13, Col14, Col15, Col20, Col21, Col22, Col23 
      Where Col10="&$C$1&" 
        And Col22 != 'Duplicado'")

Here's the formula for my query

Upvotes: 2

Views: 398

Answers (3)

Gines
Gines

Reputation: 1

I have modified the original query including a starting "iferror", and the "importrange" after the query, so you will be able to "Allow Access" to the importrange due to the original error, and then, you will see the original query:

=iferror(query(xxxxx),IMPORTRANGE("xxxxx"))

Upvotes: 0

Constantinos Samartzis
Constantinos Samartzis

Reputation: 11

This is happening because you requesting some particular columns before you give access to the new spreadsheet.

Simply go to any other empty cell (i.e. the cell under that one you mentioned above.) Write a simple IMPORTRANGE function like this:

=IMPORTRANGE("url";"'Parte 1'!A1:AH")

Then click again the cell you just filled andclick "Allow Access".

After access allowed empty the cell you just created and you will propably be able to get the first cell formula working.

Upvotes: 0

player0
player0

Reputation: 1

if you make a new copy of your spreadsheet file then links between your new spreadsheet and all importranges are not valid. you need to allow access for each importrange and only after that you can use your formula which is correctly written.

bonus fact: if importrange formula is not linked it returns #N/A error. that means that your query returns column of #N/A errors. eg it's only one single column so requesting any other column than Col1 will result in query error you getting now

Upvotes: 0

Related Questions