Problem
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 roi
.
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.
Best Answer
Most of the Google Sheets formulas are calculated on the client-side (the user computer) while some formulas like
IMPORTXML
and Google Apps Script are ran in the server-side (Google's data-centers). Errors like#VALUE
could occurs because the script is reading a value from the spreadsheet before the recalculation (including the transportation time and UI refresh) is finished.One alternative is to add
Utilities.sleep(milliseconds)
to the script before reading values from the spreadsheet. Themilliseconds
value isn't deterministic and will strongly depend on how complex is the spreadsheet. You could try to figured out the most convenientmilliseconds
value by trial and error.Bear in mind that the maximum execution time for a custom function is 30 seconds.