0

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.

7
  • 1
    When getDisplayValue() and getFormula() are used instead of getValue() and also the a1Notation is directly used instead of ReturnOnInvestment (is this the named range?), what result will you get? If those are also the same issue, in order to confirm your situation, can you provide a sample Spreadsheet for replicating your issue? Of course, please remove your personal information.
    – Tanaike
    Oct 24, 2019 at 23:24
  • @Tanaike, getdisplayvalue, getformula, and geta1notation all work fine. I'm adding some more info onto my question so check that out in a few minutes.
    – toddmo
    Oct 24, 2019 at 23:56
  • Please add a minimal and complete example the spreadsheet and code, i.e., create a demos spreadsheet including only the formula, your code should include at least a complete function declaration, then add instructions about how to use your script. Is it a custom function or is it script that is called by trigger? Oct 25, 2019 at 0:06
  • @Rubén I'll have to winnow down exactly what the issue is. ATM I'm trying to see if i can set the Locale for my script, so it matches the spreadsheet locale.
    – toddmo
    Oct 25, 2019 at 0:14
  • 1
    Thank you for replying. I noticed that an answer has already been posted. I think that it will resolve your issue.
    – Tanaike
    Oct 25, 2019 at 1:34

2 Answers 2

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.

1
  • Thank you. It was fixed by setting the locale in script and the spreadsheet properties: var spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); spreadSheet.setSpreadsheetLocale('en-US');
    – toddmo
    Oct 25, 2019 at 18:07
1

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

  1. 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.

1
  • Thank you for adding you solution as an answer. I still think that using setSpreadsheetLocale in the way you are doing is helping not because it is changing the spreadsheet locale but because it's a very slow method and it introduces a "pause" before executing the next code line. Anyway, from a pragmatic perspective, as this solution works for you, it's fine. Oct 27, 2019 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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