Cells containing custom functions return “NaN'

Question

As a high school teacher, I record all of my grading in a Google spreadsheet. I have written custom functions within that spreadsheet that are accessed in the spreadsheet. That all works fine.

I also have a simple (but independent) web app written in google apps script that summarizes the grade information for each student that accesses it and returns it in a table. This has operated perfectly for about 8 months. However, students now get a "NaN" error when trying to check their grades. The "NaN" is only returned for cells that use custom functions. By simply opening the source spreadsheet, it fixes the problem temporarily. But soon after closing the spreadsheet the webapp begins returning "NaN" again.

I'm assuming that it has something to do with when/how these cells are recalculated but I can't figure out how to make the cells retain their value while the spreadsheet is closed. Any help would be much appreciated.

Answer 1

With Eric's advice, I implemented the following function (which runs early on in my app):

function refreshSheet(spreadsheet, sheet) {
  var dataArrayRange = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
  var dataArray = dataArrayRange.getValues(); // necessary to refresh custom functions
  var nanFound = true;
  while(nanFound) {
    for(var i = 0; i < dataArray.length; i++) {
      if(dataArray[i].indexOf('#N/A') >= 0) {
        nanFound = true;
        dataArray = dataArrayRange.getValues();
        break;
      } // end if
      else if(i == dataArray.length - 1) nanFound = false;
    } // end for
  } // end while
}

It basically keeps refreshing the sheet (using .getValues()) until all of the #N/A's disappear. It works fabulously but does add a small lag.

Answer 2

Just to add my own comment:

I've found a similar effect where using Google App Scripts to get values from cells populated by sheet functions such as INDEX() and QUERY().

Eric's comment of using getValues() instead of getValue() may do the trick. But I haven't checked.

A link to my question is here: https://stackoverflow.com/questions/32519900/check-if-google-sheet-query-returned-rows?noredirect=1#comment53128587_32519900.