-1

I'm using various import functions to pull stock data from several places. I'm using importhtml, importxml, and even some scripting with UrlFetchApp.

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:

  1. This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()
  2. Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col2
  3. Function INDEX parameter 2 value is 3. Valid values are between 0 and 1 inclusive.

All 3 are incorrect. For #1 (using UrlFetchApp or importXML), there is absolutely no reference to any cell using now(), rand(), or 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(importhtml()) ), 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:

=sort(UNIQUE({TR_OPRA;OPRA}),1,true)

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:

http://ctrlv.in/829870

And here is what my private sheet looks like, with the exact same formulas doing the exact same things:

http://ctrlv.in/829874

As you can see, even my private sheet is failing sometimes with hard values, but always with data from another sheet.

1

ARRRGH! It was user error. I did in fact have a now() embedded many levels deep in a nested formula that was a leftover from an experiment I did recently, and totally forgot about. Removed it and presto... all is well. Thanks to all who tried to lend a hand on this one!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.