Cells containing custom functions return "NaN" when the spreadsheet is not open. (Script webapp applications access to Google spreadsheet)

Question

As a high school teacher, I record all my grades in a Google spreadsheet. I wrote custom functions inside this spreadsheet that are available in a spreadsheet. Everything works perfectly.

I also have a simple (but independent) web application written in a Google script application that summarizes class information for each student who accesses it and returns it in a table. This works great for about 8 months. However, students now get a "NaN" error when trying to check their grades. "NaN" is returned only for cells that use custom functions. Just by opening the source table, it temporarily fixes the problem. But shortly after closing the table, webapp will return "NaN" again.

I suppose this has something to do with when / how these cells are recounted, but I cannot figure out how to get the cells to keep their value while the table is closed. Any help would be greatly appreciated.

Answer 1

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

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 } 

Basically, he keeps updating the sheet (using .getValues ​​()) until all # N / A disappears. It works fabulously, but adds a slight lag.

Answer 2

Just add your comment:

I found a similar effect when using Google application scripts to get values ​​from cells filled with worksheet functions like INDEX () and QUERY ().

Eric's comment on using getValues ​​() instead of getValue () can do the trick. But I did not check.

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