在大型列表中查找和更新数据的Google表格脚本非常慢

Question

希望有人能让我走上正确的道路。(我认为这归结为我不了解如何使用客户端数组？

我拼凑在一起的脚本在客户端列表中查找匹配的名称，然后根据我在另一个选项卡上输入的数据更新一堆列。最后，我希望能够完全从另一张纸上执行此操作，但现在需要 3-4 分钟才能遍历整个列表。我怀疑我需要做的是获取整个数据集，对其进行修改，然后设置新值，但我不确定我是否正确理解了这个问题。

function UpdateEntry() {
var client_name=SpreadsheetApp.getActive().getSheetByName("Rolodex!C2").getValue()
var addr=SpreadsheetApp.getActive().getSheetByName("Rolodex!C4").getValue()
var client_phone=SpreadsheetApp.getActive().getRange("Rolodex!E4").getValue()
var client_fax=SpreadsheetApp.getActive().getRange("Rolodex!E6").getValue()
var client_WWW=SpreadsheetApp.getActive().getRange("Rolodex!E7").getValue()

var data=SpreadsheetApp.getActive().getSheetByName("Rolodex").getRange("C4").getValue()
var key=SpreadsheetApp.getActive().getSheetByName("Rolodex").getRange("C2").getValue()
var database=SpreadsheetApp.getActive().getSheetByName("Database")
var datalength=database.getDataRange().getValues().length

database.getDataRange().getValues()

for (var i = 1;[i]<=datalength ; i++){
Logger.log('i: ' + i)
if(database.getRange([i],[1]).getValue() == client_name){
database.getRange([i],2).setValue(addr)
database.getRange([i],3).setValue(client_phone)
database.getRange([i],4).setValue(client_fax)
database.getRange([i],5).setValue(client_WWW)
}
}
}

Answer 1

我相信你目前的情况和目标如下。

• 您的脚本工作正常。但工艺成本很高。
• 您希望降低脚本的处理成本。

修改点：

• getValue()是类范围的方法。但在var client_name=SpreadsheetApp.getActive().getSheetByName("Rolodex!C2").getValue()，getValue被用作类表的方法。在这种情况下，我认为脚本的第一行发生了错误。
  • 从你的问题来看，我认为getSheetByName可能getRange.
• SpreadsheetApp.getActive()可以通过声明一次来使用。
• client_name,addr,client_phone,client_fax,client_WWW的值可以通过一个getValues检索。
• 不使用data和key。
• 不使用database.getDataRange().getValues()线。
• 循环中使用getValue和setValue。
  • 在这种情况下，首先使用getValues从工作表中检索值。并且，创建用于放入工作表的值，然后，使用setValues将创建的值放入工作表。
  • 这里，作为重要的一点，当使用setValues时，所有列长度都需要相同的长度。请小心这一点。

当上述几点反映到您的脚本中时，它将变为如下。

修改后的脚本：

function UpdateEntry() {
var ss = SpreadsheetApp.getActive();
// 1. Retrieve values of client_name, addr, client_phone, client_fax, client_WWW from Rolodex sheet.
var sheet = ss.getSheetByName("Rolodex");
var [[client_name],,[addr,,client_phone],,[,,client_fax],[,,client_WWW]] = ss.getRange("Rolodex!C2:E7").getValues();
var ar = [client_name, addr, client_phone, client_fax, client_WWW];
var arLength = ar.length;
// 2. Retrieve values from database sheet.
var database = ss.getSheetByName("Database");
var values = database.getDataRange().getValues();
var columnLength = values[0].length;
// 3. Create values for putting to Database sheet using the values retrieved from Rolodex sheet.
var res;
if (columnLength > arLength) {
res = values.map(r => r[0] == client_name ? ar.concat(r.slice(arLength)) : r);
} else {
res = values.map(r => r[0] == client_name ? ar : r.concat(Array(arLength - columnLength).fill("")));
}

// 4. Put the values to Database sheet.
database.getRange(1, 1, res.length, res[0].length).setValues(res);
}

注意：

• 使用此修改后的脚本时，请在脚本编辑器中启用 V8 运行时。

• 如果上述修改后的脚本不是您期望的结果，则当前脚本可能不是您期望的结果。如果我的理解是正确的，你能提供你期望的示例输入和输出吗？通过这一点，我想确认一下。

引用：

• 解构赋值
• 地图()
• 条件(三元)运算符
• getValues()
• 设置值(值)
• google-apps-script
  • 我认为上面页面中的链接可能很有用。