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

问题描述

作为一名高中老师，我将所有评分记录在 Google 电子表格中.我在该电子表格中编写了可在电子表格中访问的自定义函数.一切正常.

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.

我还有一个用 google apps 脚本编写的简单(但独立)的网络应用程序，它总结了每个访问它的学生的成绩信息并将其返回到表格中.这已经完美运行了大约 8 个月.但是，学生现在在尝试检查成绩时会收到NaN"错误.NaN"仅针对使用自定义函数的单元格返回.通过简单地打开源电子表格，它可以暂时解决问题.但是在关闭电子表格后不久，Web 应用程序又开始返回NaN".

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.

推荐答案

在 Eric 的建议下，我实现了以下功能(在我的应用程序的早期运行):

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
}

它基本上会不断刷新工作表(使用 .getValues())，直到所有 #N/A 消失.它工作得非常好，但确实会增加一点延迟.

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 Script webapp 访问谷歌电子表格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！