Google表格脚本超时。需要一种新的方式或颠倒它 [英] Google sheet script, times out. Need a new way or flip it upside down
问题描述
这里的noob是这样的,所以如果在一张纸上有不到800行的话,那么这个脚本就很好用,但是这次我有一张近1500行的脚本并且脚本超时。
noob here, so the script works great if there are less than 800 rows on a sheet, however this time I have a sheet of almost 1500 rows and the script times out.
基本上它是获取报价的快捷方式。 (这里的快速意味着5-6分钟,而不是问题)它隐藏了包含计算的列,隐藏了包含敏感信息的列和列H中没有值的行。
Basically it is a quick way to get a quote. (quick here means 5-6mins, not an issue) It hides columns with calculations, hides columns with sensitive information and rows where there was no value in column H.
什么我想知道的是,如果我可以用不同的代码做同样的事情,或者如果有人知道如何使getRange()。getValue();从表格底部开始,然后我可以依次开始两个脚本来完成工作表并生成可打印的报价。
What I want to know is, if I can do the same with a different code, or if someone knows how to make getRange().getValue(); start at the bottom of the sheet, then I could have two scripts starting one after the other to finish the sheet and produce a printable quote.
任何帮助都非常感谢。
Any help is greatly appreciated.
很多感谢
这里是脚本:
function Quote()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Quote"); `
var datarange = s.hideColumns(6);
var datarange = s.hideColumns(9);
var datarange = s.hideColumns(10);
var datarange = s.hideColumns(12);
var datarange = s.hideColumns(13);
var datarange = s.hideColumns(14);
var lastRow = s.getLastRow();
for( i=1 ; i<=lastRow ; i++) {
var status = s.getRange("H"+i).getValue();
if (status == "") {
s.hideRows(i);
}
}
}
推荐答案
您的问题是行:
s.getRange("H"+i).getValue()
这段代码从电子表格中获取数据,当在循环中使用它时,这是非常缓慢的过程。您可以使用这种方式:
This code takes data from spreadsheet, this is very slow process when you use it inside loop. You may use this conctruction:
var data = s.getDataRange().getValues();
for (var i=0; i < data.length; i++) {
var status = data[i][7]; // takes column H
// other code goes here...
}
<使用 getDataRange()
从电子表格中读取数据,并将其转换为 getValues()
。
This way you read data from the spreadsheet only once using getDataRange()
, and convert it into array with getValues()
. Then loop should work way more faster.
隐藏行时,记得加1,因为数组从0开始,heres代码用于隐藏循环内的行:
When hiding rows, remember to add 1 because array starts from 0, heres code for hiding rows inside loop:
if (status == "") {
s.hideRows(i + 1); // adding 1
}
这篇关于Google表格脚本超时。需要一种新的方式或颠倒它的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!