如何在Google表格中增强自动隐藏脚本，以免超时 [英] How can I enhance my auto hide script in Google Sheets so it wont time out

问题描述

我有一个脚本可以在多个Spreadsheets上运行......它会自动隐藏包含特定值的行。目前，该脚本设置为在凌晨3点左右每天运行一次，以确保在处理过程中没有人处于活动状态。问题是我现在正在运行是这些表变得太大，不能使用我的当前脚本，它逐行运行。脚本超时并且没有完成。我猜测它仍然在所有已经隐藏的行上运行脚本。

I have a script that I run on multiple Spreadsheets... it auto hides rows that contain a certain value. Currently this script it setup to run daily around 3:00 am, to ensure no one is active in it while it processes. The issue is I am now running into is these sheets are getting too large to use my current script, which runs line by line. The script times out and doesn't finish. I'm guessing it still runs the script on all the lines that are already hidden.

这是我当前的脚本，它非常基本：

Here is my current script, which is pretty basic:

function autoHide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("SHIPPING");

      //get data from column 
  var data = sheet.getRange('AD:AD').getValues();

      //iterate over all rows
  for(var i=0; i< data.length; i++){
      //compare first character, if greater than 0, then hide row
    if(data[i][0] > 0){
      sheet.hideRows(i+1);

    }
  }
}

我有尝试寻找更好的选择，并发现人们在讨论使用数组过滤器或分批运行时，只是不同的东西，似乎没有足够的解释来转化为我正在处理的内容。我知道，逐行运行这种方法并不是最好的方法，尤其是超过1000行并且不断增长。

I have tried searching for better options, and found where people were talking about using array filters, or running in batches, just different things that didn't seem to be explained enough for me to translate to what I was working on. I know running this line by line isn't the best way, especially with over a 1,000 rows and growing.

对于最好的情况，我希望有一个非常高效的脚本，它使用当前脚本处理的一小部分。否则，如果只有一种方法可以在可见的行上运行脚本，那几乎是一样的。最糟糕的情况是，如果只有一种方法可以告诉它在发生超时错误时停止它的位置......通过放置某种类型的标签或其他信息来知道从哪里开始备份。

For a best case scenario, I would like to have a very efficient script that uses fractions of the processing my current script does. Otherwise, if there was just a way to run the script on the rows that are visible, that would be almost as good. Worst case scenario, if there is just a way to tell it to pick up where it left off when it gave a time out error... by placing some type of tag or something to know where to start back up.

我不认为链接工作表是必要的，我只需要能够在名为SHIPPING的工作表中隐藏AD列中具有大于0的数字的任何行。 。

I don't think linking a sheet is necessary, I just need to be able to hide any row that has a number greater than 0 in column AD, on a sheet called "SHIPPING".

推荐答案

虽然您可以加速脚本本身（例如，通过批量连续的行需要隐藏）甚至超时。

相反，您的脚本应该在脚本属性中记住它处理的最后一行，如果脚本超时，它将继续从该行开始。

您会还需要更改触发时间。让它每10分钟运行一次，但是只有在1）它的过去3am和b）最后一行不是最后一行时才会开始处理（当你完成时你重置为零）。
这应该处理巨大的床单很好。到凌晨5点，它将从凌晨3点开始运行12次，所以它应该能够处理12倍多的行。

注意我选择了10分钟的触发器，以便以前的触发器（可以运行6分钟）下一个触发器。

确保在工作表和脚本文件属性中设置时区，以便在检查其过去3am时是否已使用时区。

While you could speed up the script itself (for example by batching consecutive rows that need to be hidden) that will also evenctually time out.
Instead, your script should remember in script properties the last row it processed, ao that if the script times out it will continue starting from that row.
You will also need to change the trigger times. Make it run every 10 minutes but only start processing if 1) its past 3am and b) the last row processed is not yet the very last row (which you reset to zero when finished).
this should handle huge sheets just fine. by 5am it would have run 12 times since 3am so it should be able to process 12 times more rows.
Note I chose 10min trigger so that a previous trigger (which could run for 6 minutes) wont ever overlap the next trigger.
Do make sure to set your timezone in the sheet and script file properties so that you use your timezones when checking if its past 3am already.

这篇关于如何在Google表格中增强自动隐藏脚本，以免超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！