包含自定义函数的单元格返回“NaN”电子表格未打开时。 （Apps脚本Web应用程序访问谷歌电子表格） [英] Cells containing custom functions return "NaN'" when spreadsheet is not open. (Apps Script webapp accessing google spreadsheet)

问题描述

作为一名高中教师，我将所有评分记录在Google电子表格中。我在电子表格中编写了自定义函数，可以在电子表格中访问它们。这一切都工作正常。

我也有一个简单的（但独立的）在谷歌应用程序脚本编写的Web应用程序，总结每个学生访问它的等级信息并返回它在一张桌子里。这已经运行了大约8个月。但是，学生在尝试检查成绩时现在会收到NaN错误。 NaN仅返回使用自定义函数的单元格。通过简单地打开源电子表格，它可以暂时解决问题。但是在关闭电子表格后不久，Web应用程序又开始返回NaN。

我假设它与何时/如何重新计算这些单元格有关，但是我无法弄清楚如何在电子表格关闭时使单元格保持其价值。任何帮助将不胜感激。

解决方案

使用Eric的建议，我实现了以下功能（在我的应用程序中尽早运行） ：

 函数refreshSheet（电子表格，工作表）{
 var dataArrayRange = sheet.getRange（1,1，sheet。 getLastRow（），sheet.getLastColumn（））; 
 var dataArray = dataArrayRange.getValues（）; //必须刷新自定义函数
 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（）; 
休息; 
} // end if 
 else if（i == dataArray.length  -  1）nanFound = false; 
} // end for 
} // end while 
} 
  

它基本上一直刷新工作表（使用.getValues（）），直到所有的＃N / A消失。它的工作非常出色，但确实增加了一点时滞。

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.

解决方案

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.

这篇关于包含自定义函数的单元格返回“NaN”电子表格未打开时。 （Apps脚本Web应用程序访问谷歌电子表格）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！