Cells containing custom functions return “NaN'” when spreadsheet is not open. (Apps Script webapp accessing google spreadsheet)

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.

It seems that this may be a manifestation of Issue 1131: Custom functions often return #N/A. I suggest adding your reproduction scenario to that issue, or creating a new one. — Mogsdad
Eric added comments to another question that may be a viable workaround for you. If your script doesn't already do so, try doing a .getValues() over the problem range to trigger a recalc. (And if you already do that... check if you encounter any NaN and do it again!) — Mogsdad

Scott S. Scott S. · Accepted Answer · 2013-06-28T18:40:13

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.

Cells containing custom functions return “NaN'” when spreadsheet is not open. (Apps Script webapp accessing google spreadsheet)

2 Answers