Data reduction via IMPORTRANGE

1
votes

I am trying to do some data reduction in my Google Sheets by using the following IMPORTRANGE formula:

=query(importrange("https://docs.google.com/a/ap.averydennison.com/spreadsheets/d/1xz1lXY-w5Ii_aWqVAhHgRCmeoes9ltSUtibE4kzhMHA/edit#gid=2051232966","SF_Flex_Rel!a:l"), 
 "select * where Col1 = '"&text(B1,"###")&"'",1)

The 'source' sheet has a whole lot of sales data records. What I am trying to do in the new sheet via this formula is only bring in the sales records from the source sheet that match the customer number specified in cell B1.

It seems to work OK if I limit the IMPORTRANGE to only query about 10,000 rows. Once I go over around 20,000 rows the screen will briefly flash up the records, then a small progress bar shows in the top right corner of the sheet and the records disappear. The cell with the formula just shows #ERROR! with no other comments to tell me why.

Is there something wrong with my formula syntax?

Is there a better way to achieve this data reduction?

Is there some undocumented data limitation on IMPORTRANGE function (I am using 'new' Google Sheets)?

2
google-sheetsgoogle-sheets-formulagoogle-sheets-queryimportrange
Hello, I have the same problem (4 columns, it stops at around 50,000 rows - full formula works only during 3 seconds when I open the ss) if I reduce the columns size to "Sheet1!A:B" (instead of "Sheet1!A:D") it works. If I change the "select *" with "select Col1, Col2, Col3, Col4" I have the following error: "'Error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMNA" ! One thing that I have noticed is that the column D contains many time the same numbers (from 1 to 200). I have tried to move the D column in C and I have the same error. So it is possibly a Google bug ? – miodf

2 Answers

0
votes

try like my example :

  =QUERY(                                                // data
              IMPORTRANGE(
                "Spreadsheet Key",    // spreadsheet key 
                "DATA!A:C"                                         // datarange
              ), 
              "SELECT Col1 WHERE Col2=" & "'" & B2 & "'"           // query
            )
0
votes

I had the same problem. This answer helped me find a workaround : https://productforums.google.com/forum/#!topic/docs/RxVUFGWQ2Y4

In my example :

1) In the spreadsheet where the data is I have added a few empty columns (E to H) in order to display 4 columns of data in 5 maximum rows. =Query(Sheet1!A:D,"select * Where A contains 'KEYWORD' limit 5",1)

2) Then in the other spreadsheet: =ImportRange("https://docs.google.com/spreadsheets/d/ss_key_here/", "'Sheet1'!E1:H5")