Google脚本似乎正在缓存单元格值-有什么避免的方法吗? [英] Google Scripts seems to be Caching Cell Values - Any way to avoid?

问题描述

您好，堆栈霸主！

我目前正在尝试整理一个小的编码项目.很简单，但是我却遇到了困难.

目标是每天一次根据这些单元格之一清除三个单元格.

基本上-G9包含我希望清除单元格的时间.一旦它超过了G9中指定的时间，我希望清除单元格D6，D9和G9.

我已经建立了一种使用IF语句的方法，无论是在工作表方面还是在脚本方面.在工作表本身上，我在单元格K12中有一个1/0切换.如果G9中的值大于现在的时间(存储在K11中)，则不应删除它，因此将其设置为"1".一旦G9中的值大于现在存储在K11中的时间，它将值设置为"0"，这意味着应将其删除.

我有Google脚本来检查单元格K12的值，并在K12 = 0的情况下删除我指定的范围，就像这样:

  function ClearCells(){var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AutoDeleteCellsOnTimeExpired');var Check = s.getRange("K12").getDisplayValue();如果(检查=="0"){s.getRange('D6').clearContent();s.getRange('D9:G9').clearContent();}} 

然后，我在脚本"功能上设置了一个切换开关，以每分钟运行一次脚本.问题是，即使K12中的值从"1"更改为"0"，脚本也不会清除单元格.执行记录显示代码执行正常.

这是关键所在...如果值是0，首先(我已经在G9中设置了一个值，现在已经晚了)，脚本会自动按预期运行.看一看之后，似乎Google Sheets假定是因为对该表没有进行 USER 编辑(电子表格设置被设置为每分钟重新计算一次NOW公式)，它认为值不能不同，也不必费心重新检查K12中的值.如果我在电子表格上编辑另一个单元格(甚至完全不相关)，则Google Scripts会再次按预期检查该单元格的值.

这对我来说是因为它正在缓存K12的值，而不希望它在没有用户输入的情况下发生更改(这是错误的，因为它基于自动= NOW更新来更新自身)?

如果我错了，请告诉我！否则...假设这样做，如何停止Google表格?我试过添加flush()命令，但是什么也没做.

可以在此处找到电子表格的链接(这是一次性版本，仅显示需要查看的信息-编辑和调整):解决方案

您的假设

这对我来说是因为它正在缓存K12的值

是正确的.这些值被缓存，以便更有效，更快速地检索数据.刷新缓存的最简单方法是使虚拟 setValue() 或clearContent()调用，然后再使用getValue()检索值.像这样

  function ClearCells(){var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AutoDeleteCellsOnTimeExpired');s.getRange("A1").setValue("DummyValue")//虚拟调用Logger.log("SetValue")s.getRange("A1").clearContent()//删除虚拟值//您可以只使用clearContent()或setValue()和clearContent()调用//为了达到相同的结果var Check = s.getRange("K12").getValue();Logger.log(检查)如果(检查== 0){s.getRange('D6').clearContent();s.getRange('D9:G9').clearContent();}Logger.log(新版本")} 

或者，您可以直接在google app脚本中检查到当前时间的时间值，并执行所需的功能，如下所示:

  function ClearCells2(){var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AutoDeleteCellsOnTimeExpired');var curTime =新的Date()var expTime = s.getRange("G9").getValue().split(:")Logger.log(curTime.getHours()+:" + curTime.getMinutes())Logger.log(expTime [0] +:" + expTime [1])如果(curTime.getHours()> = expTime [0]&& curTime.getMinutes()> expTime [1]){Logger.log(清除")s.getRange('D6').clearContent();s.getRange('D9:G9').clearContent();}} 

参考文献:
Date()， date.getHours()， date.getMinutes()

Hello Stack Overlords!

I'm currently trying to put together a little coding project. Something simple, but I'm having a rather difficult time.

The objective, clear out three cells once the time of day, based on one of those cells, passes.

Essentially - G9 contains the the time I want the cells to be cleared. Once it has gone past the time of day specified in G9, I'd like cells D6, D9 and G9 cleared.

I've set up a way of doing this using an IF statement, both from the sheets side, and scripts side. On the sheet itself, I have a 1/0 toggle in cell K12. If the value in G9 is greater than the time now (stored in K11), it shouldn't be deleted, so is set as "1". As soon as the value in G9 is greater than the time now stored in K11, it set's the value to "0", meaning it should be deleted.

I've got Google Scripts to check the value of cell K12, and to delete my specified ranges if K12 = 0, as so:

function ClearCells() {

var s =    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AutoDeleteCellsOnTimeExpired');
var Check = s.getRange("K12").getDisplayValue();

if (Check == "0"){
  s.getRange('D6').clearContent();
  s.getRange('D9:G9').clearContent();}
}

I've then set a toggle on the Scripts function, to run the script once a minute. The issue is, the script will not clear the cells, even after the value in K12 has changed from "1" to "0". The execution transcript shows the code executes fine.

Here's the clinch... If the value is 0, to begin with (I set a value in G9 already later than now), the script runs fine automatically, as expected. After taking a look, it seems that Google Sheets assumes because there hasn't been a USER edit to the sheet (the spreadsheet settings are set to re-calculate NOW formulas once a minute), it thinks the value can't be different and doesn't bother to re-check the value in K12. If I edit another cell on the spreadsheet (even totally unrelated), Google Scripts then checks the cell value again as expected.

This indicates to me it's because it's caching the value of K12, not expecting it to change without user input (which is wrong because it updates itself based on an automatic =NOW update)?

If I'm wrong, please let me know! Otherwise... how can I stop Google Sheets assuming this? I've tried adding a flush() command, but that did nothing.

Link to the spreadsheet can be found here (this is a disposable version, only showing the need-to-see info - edit and tweak away): https://docs.google.com/spreadsheets/d/1AGOLuvGCNWJk1Ft9T2ifjcf9CzCXNHysLiSvsUwxZW4/edit?usp=sharing

Many thanks for your time!

解决方案

Your assumption

This indicates to me it's because it's caching the value of K12

is correct. The values are cached as to retrieve the data much more efficiently and quickly. The easiest way to refresh the cache is by making a dummy setValue() or clearContent() calls, before you retrieve the value using getValue(). Like so

function ClearCells() {

    var s =    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AutoDeleteCellsOnTimeExpired');
    s.getRange("A1").setValue("DummyValue")  //Dummy Call
    Logger.log("SetValue")
    s.getRange("A1").clearContent()  //Delete Dummy Value
    // You can use either just clearContent() or setValue() and clearContent() calls
    // To achieve the same result
    var Check = s.getRange("K12").getValue();
    Logger.log(Check)
    if (Check == 0){
      s.getRange('D6').clearContent();
      s.getRange('D9:G9').clearContent();
    }
    Logger.log("New Version")
}

Alternatively, you can check the time value to the current time in the google app script directly and perform the required function, like so:

function ClearCells2() {

  var s =    SpreadsheetApp.getActiveSpreadsheet()
             .getSheetByName('AutoDeleteCellsOnTimeExpired');
  var curTime = new Date()
  var expTime = s.getRange("G9").getValue().split(":")
  Logger.log(curTime.getHours() +":"+curTime.getMinutes())
  Logger.log(expTime[0]+":"+expTime[1])

  if (curTime.getHours() >= expTime[0] && curTime.getMinutes() > expTime[1]){
    Logger.log("Clear")
    s.getRange('D6').clearContent();
    s.getRange('D9:G9').clearContent();
  }

}

References:
Date(),date.getHours(),date.getMinutes()

这篇关于Google脚本似乎正在缓存单元格值-有什么避免的方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！