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.
我有一个脚本,我在多个Spreadsheets上运行...它自动隐藏包含特定值的行。目前这个脚本设置为每天凌晨3点左右运行,以确保在处理过程中没有人处于活动状态。问题是我现在遇到的是这些表格太大而无法使用我当前的脚本,它逐行运行。脚本超时但未完成。我猜它仍然在已经隐藏的所有行上运行脚本。
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);
}
}
}
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.
我试图寻找更好的选择,并找到了人们谈论使用数组过滤器或批量运行的地方,只是不同的东西似乎没有足够的解释我转换成我正在做的工作。我知道逐行运行并不是最好的方法,特别是超过1000行并且不断增长。
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.
对于最佳情况,我希望有一个非常有效的脚本,它使用当前脚本执行的处理的一小部分。否则,如果只有一种方法可以在可见的行上运行脚本,那就差不多了。在最糟糕的情况下,如果有一种方法可以告诉它在它给出超时错误的时候从它停止的地方继续...通过放置某种类型的标签或某些东西来知道从哪里开始备份。
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".
我不认为链接表是必要的,我只需要能够在名为“SHIPPING”的工作表中隐藏AD列中数字大于0的任何行。
1 个解决方案
#1
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.
虽然你可以加快脚本本身的速度(例如通过批量连续需要隐藏的行),这些行也会超时。相反,您的脚本应该在脚本属性中记住它处理的最后一行,如果脚本超时,它将继续从该行开始。您还需要更改触发时间。让它每10分钟运行一次,但只在1)过去的凌晨3点和b)处理的最后一行还不是最后一行(完成后重置为零)时才开始处理。这应该处理巨大的床单就好了。到凌晨5点它将从凌晨3点起运行12次,因此它应该能够处理12次以上的行。注意我选择了10分钟触发器,以便先前的触发器(可以运行6分钟)不会与下一个触发器重叠。确保在工作表和脚本文件属性中设置时区,以便在检查过去的凌晨3点时是否使用时区。
#1
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.
虽然你可以加快脚本本身的速度(例如通过批量连续需要隐藏的行),这些行也会超时。相反,您的脚本应该在脚本属性中记住它处理的最后一行,如果脚本超时,它将继续从该行开始。您还需要更改触发时间。让它每10分钟运行一次,但只在1)过去的凌晨3点和b)处理的最后一行还不是最后一行(完成后重置为零)时才开始处理。这应该处理巨大的床单就好了。到凌晨5点它将从凌晨3点起运行12次,因此它应该能够处理12次以上的行。注意我选择了10分钟触发器,以便先前的触发器(可以运行6分钟)不会与下一个触发器重叠。确保在工作表和脚本文件属性中设置时区,以便在检查过去的凌晨3点时是否使用时区。