I'm using various import functions to pull stock data from several places. I'm using
importxml, and even some scripting with
Out of the blue, all my imports are now failing with various errors that make no sense to me (especially as the formulas were all working perfectly for days, until they stopped... ). The errors I'm getting are:
- This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()
- Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col2
- Function INDEX parameter 2 value is 3. Valid values are between 0 and 1 inclusive.
All 3 are incorrect. For #1 (using
importXML), there is absolutely no reference to any cell using
randbetween(). For #2 (using
query(importhtml()) ), there most definitely is a Col2 in the returned data (if it were being returned). And for #3 (using
Index does support values beyond 0 and 1.
The one factor I have figured out is that the imports are failing when the cell they are referencing, is pulling data from another sheet. For instance this formula:
Pulls in this data:
ABX160902P00021000 BBBY160902P00042000 COH160916P00040000 CSCO160902P00031000 CXW160916P00026000 GDX160916P00030000 POT160916P00015000 PVG160916P00010000 WFM160902P00030000
All import formulas that reference those strings, fail. However if I copy that data and paste it in again as values, then all the import formulas begin working fine. I can also manually type in other symbols and they will always work fine, for every import method. I can also type formulas that result in the same data (such as
=lower("WFM160902P00030000")) and they will also work fine, so it's not merely a problem with referencing a formula.
As mentioned previously, this was working perfectly fine all weekend. Suddenly today, this issue is happening and I'm at a loss as to really explain what's going on, other than it seems related to referencing data pulled from another sheet.
Also I have a spreadsheet to demonstrate this issue, but when I make a new sheet for this issue, it doesn't fail in exactly the same way. It does fail however so, maybe this will be helpful. See the "ImportMethods" sheet.
Here is an example of what the public sheet looks like currently:
And here is what my private sheet looks like, with the exact same formulas doing the exact same things:
As you can see, even my private sheet is failing sometimes with hard values, but always with data from another sheet.