My spreadsheet displays a number in the
ReturnOnInvestment range, not the
#VALUE! error. I checked
analysisSheet.getRange("ReturnOnInvestment").getA1Notation() to make sure I'm not confused and looking at the wrong cell. (as it happens,
#VALUE! does not appear anywhere on my sheet. Also,
getFormula() works fine.
But this code returns the
#VALUE! error into
var spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); var listingsSheet = spreadSheet.getSheetByName("Listings"); var analysisSheet = spreadSheet.getSheetByName("Analysis"); var roi = analysisSheet.getRange("ReturnOnInvestment").getValue();
ReturnOnInvestment contains a formula, a simple fraction. If I replace it with a hard-coded number, it works as expected.
Do you have any ideas as to why this might be happening?
Update, more information
I have created a very simple situation which replicates the issue. When I use
importxml(myUrl, mypath) by itself, it works fine. This function returns text. The text is like
$3,212.45. However, when I do
value(importxml(myUrl, mypath)) it works fine on the spreadsheet, but crashes in script.
If I do
=value(SUBSTITUTE(importxml(MyUrl,MyXPath,"$","")) it also works fine. So my script doesn't like the dollar sign! So now, worst case, I have a work around, but gee I really don't want to have to change 100s of formulas and then remember I have to keep doing that.
I fixed that smaller simpler example by setting the Locale of my spreadsheet to the U.S. But my original formula is still exhibiting the error.
It's almost as if the script doesn't use my international settings or something.