0

I'm using the following formula in a spreadsheet: 

=query(importrange("https://docs.google.com/spreadsheets/...?usp=sharing","Gmap List!A:G"), "select Col1,Col2,Col3,Col4,Col5,Col6,Col7 where Col7 = 'Deer Valley'",1)

It produces the following result:

example output

This is the mystery. Those cells should have the Zip Code numbers 85029 in both cells.

The simple use of the above IMPORTRANGE shows the number 85029 in both of the cells referenced by the query.

Any idea of the cause of this?

The source spreadsheet generates the contents of the cells that show up empty exactly the same as the cells that have numbers in them.

Community
  • 1
  • 1
Dave Bird
  • 3
  • 5

2 Answers2

0

This problem might be better described by a better subject title. Something like "Query Search returns incomplete resultset." But, no matter at this point since I have fixed it.

I don't know why the resultset is incomplete, but here is at least part of the issue, and what I did to fix it.

Some of the cells of the resultset which were empty were sourced from cells which were derived from cells (say Members!F3) containing the form 85029S, 5 digits followed by a single alpha. The source cells contained the formula for example, =Left(Members!F3,5), which yielded 85029, but resulted in the incomplete resultset. This was fixed by changing the formula to =value(Left(Members!F3,5)). Why this works, I have no clue.

The above is a definite simplification of what is going on. Not all incomplete resultsets come from the form "5 digits followed by an alpha". But, all incomplete runsets do seem to come from a computation with an additional level similar to =indirect("Zip Corrections!C" & match(F3,'Zip Corrections'!B:B,0)), and the fix works for all conditions.

If anyone looks at this and wants to dig into it, I'm reasonably sure they will want to look at the actual spreadsheet which shows the problem. This, I can't share because of privacy issues. Plus, I am not keen on making an example spreadsheet to show the issue, since I've solved the problem now, and that's potentially a lot more work.

That's the best I can do at this point.

Dave

Dave Bird
  • 3
  • 5
0

A possible solution may be using '=' instead of 'contains':

=query(importrange("https://docs.google.com/spreadsheets/...?usp=sharing","Gmap List!A:G"), "select Col1,Col2,Col3,Col4,Col5,Col6,Col7 where Col7 contains 'Deer Valley'",1)

I've also found that empty cells within the importrange range caused problems; filling them with zeros fixed the problem.

Grubbmeister
  • 857
  • 8
  • 25