gscript "getValue()" returns "#VALUE!" error

Question

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.

Answer 1

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. The milliseconds value isn't deterministic and will strongly depend on how complex is the spreadsheet. You could try to figured out the most convenient milliseconds value by trial and error.

Bear in mind that the maximum execution time for a custom function is 30 seconds.

Answer 2

When getting #VALUE! error in script calling getValue() on a cell that refers to something like value("$3,212.45"), the problem can be a mis-matched locale.

To fix this,

1. we need this in the script:

   var spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); spreadSheet.setSpreadsheetLocale('en-US');

and

2. set the locale to the United States in the spreadsheet under File > Spreadsheet Settings.

Just using the U.S. as an example. It works with any locale. Just make sure the country AND language match.

This caused the locale to match and currency to get parsed OK. Now this script:

var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
spreadSheet.setSpreadsheetLocale('en-US');
var analysisSheet = spreadSheet.getSheetByName("Analysis");
var roi = analysisSheet.getRange("ReturnOnInvestment").getValue();

returns the value of the cell and not the #VALUE! error.