时间戳

首页 > 解决方案 > 从不同工作表获取数据的 Appscript 功能很慢，我该如何优化它？

arrays - 从不同工作表获取数据的 Appscript 功能很慢，我该如何优化它？

问题描述

我是 JS 和编程的新手。我正在运行一个包含很多工作表的项目电子表格，并试图在一张主工作表中收集信息。问题是，遍历 100 张纸并获取值会使函数太慢并且需要大约 40 秒的运行时间。

我查看了日志，发现 .getValue 要求非常高，每次迭代需要 0.2 秒。我已阅读有关批处理功能的信息，但知道如何执行此操作...

function onEdit(e) {

//declaring variables
var sheetnames = new Array();
var budgetSums = new Array();
var projectState = new Array();
var workingHours = new Array();
var projectLink = new Array();
var sheetLink = new Array();

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var masterSheet = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Mastersheet");

//get information and cell values from diffentent sheets
for (var i=0 ; i<sheets.length ; i++) {
sheetnames.push( [ sheets[i].getName() ] ); 
budgetSums.push( [ sheets[i].getRange('F1').getValue() ] );
projectState.push( [ sheets[i].getRange('K1').getValue() ] );
workingHours.push( [ sheets[i].getRange('K2').getValue() ] );
sheetLink.push( [ sheets[i].getSheetId() ] )
}


//set cell values in the master sheet
for (var i=0; i<sheetnames.length; i++){
masterSheet.getRange(i +1, 1).setValue(sheetnames[i]); 
masterSheet.getRange(i +1, 2).setValue(budgetSums[i]); 
masterSheet.getRange(i +1, 3).setValue(projectState[i]); 
masterSheet.getRange(i +1, 4).setValue(workingHours[i]); 

}
for (var i=0; i<sheetnames.length; i++){
masterSheet.getRange(i +1, 5).setValue('=HYPERLINK("#gid=' + sheetLink[i] 
+ '";"' + sheetnames[i] + '")' ); 
}
}

该代码正在运行，但我不知道如何优化它。也许我需要一种不同的方法来解决这个问题？

标签： arraysgoogle-apps-scriptgoogle-sheets

解决方案

我相信你可以采取这两个部分：

for (var i=0 ; i<sheets.length ; i++) {
  sheetnames.push( [ sheets[i].getName() ] ); 
  budgetSums.push( [ sheets[i].getRange('F1').getValue() ] );
  projectState.push( [ sheets[i].getRange('K1').getValue() ] );
  workingHours.push( [ sheets[i].getRange('K2').getValue() ] );
  sheetLink.push( [ sheets[i].getSheetId() ] )
}


//set cell values in the master sheet
for (var i=0; i<sheetnames.length; i++){
  masterSheet.getRange(i +1, 1).setValue(sheetnames[i]); 
  masterSheet.getRange(i +1, 2).setValue(budgetSums[i]); 
  masterSheet.getRange(i +1, 3).setValue(projectState[i]); 
  masterSheet.getRange(i +1, 4).setValue(workingHours[i]); 

}

并像这样重写它们：

for (var i=0 ; i<sheets.length ; i++) { 
  masterSheet.getRange(i+1,1,1,4).setValues([[sheets[i].getName(),sheets[i].getRange('F1').getValue(),sheets[i].getRange('K1').getValue(),getRange('K2').getValue()]]);
  sheetLink.push( [sheets[i].getSheetId()])
}

我还没有测试过这段代码，所以它可能会使用一些调整来让它工作。

如果您可以重组用户界面，以便将它们全部集中在一个 getValues() 中，那将是很棒的，但您必须考虑这可能会如何影响您的用户。当然，一种可能性是使用对话框进行数据输入，然后不管你如何存储数据。但是使用电子表格进行数据输入比为所有数据输入构建自定义对话框要简单得多。

多看几眼之后，我认为您可能会一直这样做：

for (var i=0 ; i<sheets.length ; i++) { 
  masterSheet.getRange(i+1,1,1,5).setValues([[sheets[i].getName(),sheets[i].getRange('F1').getValue(),sheets[i].getRange('K1').getValue(),getRange('K2').getValue(), '=HYPERLINK("#gid=' + sheets[i].getSheetId() + '";"' + sheetnames[i] + '")']]);
}

如果您可以为我们设置一个示例电子表格，我很乐意尝试让它工作。不要将我们与具有私人信息的东西联系起来，否则您将无法承受损失。

我认为我不想在 onEdit(e) 触发器中运行它。请记住，简单的触发器需要完成 30 秒。

推荐阅读