I have a 200k cell database in New Google sheets. I'm running QUERY() in the same spreadsheet but in another sheet.
The results I get from QUERY() is perfectly fine. The problem is: when I try to use the resulting queried data in another sheet, the resulting data is not what I see from the QUERY(). I only see a portion of it. This happens both when I use importrange() and when I use Google app script code to replace importrange.
When using code and I .getValues() the range, the data fetched is already not what it looks like in the QUERY function on the original sheet. So that's why I'm saying the actual result from QUERY is different from what I see.
The QUERY result from what I see is correct. The data set from importrange() or .getValues() is incorrect. This just happened suddenly. Before everything was working fine. It started this Friday (March 21, 2014) night EST.
Wondering if anybody else is having this same problem.
Edit: I've also tried the myimportrange() custom code provided by ahab as a work around. I get the same result as importrange(). I've exhausted what I can try I think. Might be a Google bug?
Edit 3/24/14: It seems to only a problem on formulas. I have a different part of the spreadsheet that was also not importing all of the values, but for that portion i pasted values because it will not change any more, and the problem resolved itself.
It seems like the formula calculates and I can see it but Google's server has a different number for that cell.
edit 3/24/14 10am EST: Things all came back without me doing anything. I think it was a Google issue. They must have turned something off over the weekend and turned it back on when the engineer got into the office today. Should I just delete this question? Moderator?
edit 3/28/14 10pm EST: it's happening again. This time the QUERY() function not producing the correct results.
edit 4/2/14: This problem carries over to other formulas (simple formulas)
edit 4/7/14: This seems like a recurring problem over the weekends (US time zone)
edit 4/23/14: latest cycle of importrange down started Good Friday, importrange works again now.
edit: I have a more accurate description of the error:
So let's say i have a spreadsheet foo. In cell A1, sheet1 in spreadsheet foo, is a formula that returns value X. value X is the correct value, but since value X depends on all these different calculations (such as a query, which is blank at load) from inside the spreadsheet, when you first open/load the spreadsheet, the initial value is Y. Only after about 30 seconds to 1 minute does the value (after calculating what looks like 3-4 times) settle correctly to value X. Every time i hit reload on the spreadsheet, the value gets reset to value Y, and it takes 30 seconds to 1 minute to arrive at the correct value X.
Now i have another spreadsheet bar. In bar, i'm importing the range sheet1!A1 from spreadsheet foo. However, value Y shows up, even though value X is displayed in foo. So bar is only loading the initial value but not the calculated and most recent value X.
This phenomena has been ongoing since I started using the new sheets. Sometimes it imports correctly and imports X from foo, other times it imports Y from foo. And i've done absolutely nothing. It seems to be unstable. Sometimes it is able to import the correct value, sometimes not.
