谷歌电子表格脚本非常缓慢

Question

我有一个每天都要运行的脚本，但它运行得非常慢，而且我无法找出是什么导致了它的速度缓慢。有没有人能提出建议？我最初编写了一个函数来处理一个工作表，随后添加了一个循环调用每个工作表的函数。

function addAllLog() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  // build array of all sheets
  for (var i in allSheets) { 
    if (i == 0) { continue;}  // skip first sheet

    var tmpName = allSheets[i].getName();
    if (tmpName == "Print Sheet")  break;  //stop at this tab

    var tmpName = ss.getSheetByName(allSheets[i].getName());
    ss.setActiveSheet(ss.getSheetByName(allSheets[i].getName()));

    addLog();
  }
}

function addLog () {
    var sheet = SpreadsheetApp.getActiveSheet();
    var numLastRow = sheet.getLastRow();
    var numThisRow = numLastRow+1;
    today = new Date();
    today.setDate(today.getDate()-1);
    today = Utilities.formatDate(today,"EDT","MM/DD/YYYY");

    if (sheet.getMaxRows() == numLastRow) sheet.insertRowAfter(numLastRow);

    var range = sheet.getRange(numLastRow,2,1,16); // last row, column b, 1 row of 16 columns
    var data = range.getValues();

    range.copyTo(sheet.getRange(numLastRow+1, 2,1,16)); //you will need to define the size of the copied data see getRange()

    sheet.getRange(numThisRow, 1).setValue(today);
    sheet.getRange(numThisRow, 4).setValue('Log');
    sheet.getRange(numThisRow, 5).setValue('');  //erase copied value if there was anything there
    sheet.getRange(numThisRow, 7).setValue('');  //erase copied value if there was anything there

    sheet.getRange(numThisRow, 6).setValue('=GoogleFinance(C' + numThisRow + ',"price")');
    sheet.getRange(numThisRow, 6).setValues(sheet.getRange(numThisRow,6).getValues());

}

任何有关加速它的建议都将不胜感激！

- Cactus Dude

Answer 1

每次您直接在电子表格上设置一个范围的值，至少需要800毫秒才能完成。因此，解决这个问题的最佳方法是创建一个与范围具有相同宽度和高度的Object[][]，并使用sheet.getRange(range).setValues(Object[][])进行设置。

在这篇文章中，Apps Script最佳实践|Apps Script，Google解释了其中的区别。

另外，一个有趣的解决方案是在内存中创建一个电子表格抽象。我根据这种方法制作了自己的MemsheetApp，它运行得非常好。如果您想改进它，我会很乐意接收拉取请求。

- Tito Leiva

Answer 2

此版本不要求您激活每个工作表，因此可能需要少许时间。

function addAllLog() {
  var ss=SpreadsheetApp.getActive();
  var allSheets = ss.getSheets();
  for (var i=1;i<allSheets.length;i++){ 
    var shtname = allSheets[i].getName();
    if (shtname == "Print Sheet"){
      break;  //stop at this tab
    }else{
      addLog(shtname);
    }
  }
}

function addLog (shtname) {
  var sheet = SpreadsheetApp.getSheetByName(shtname);
  var numLastRow = sheet.getLastRow();
  var numThisRow = numLastRow+1;
  today = new Date();
  today.setDate(today.getDate()-1);
  today = Utilities.formatDate(today,"EDT","MM/DD/YYYY");
  if (sheet.getMaxRows() == numLastRow) sheet.insertRowAfter(numLastRow);
  var range = sheet.getRange(numLastRow,2,1,16); 
  range.copyTo(sheet.getRange(numLastRow+1, 2,1,16)); 
  sheet.getRange(numThisRow, 1).setValue(today);
  sheet.getRange(numThisRow, 4).setValue('Log');
  sheet.getRange(numThisRow, 5).setValue('');
  sheet.getRange(numThisRow, 7).setValue('');
  sheet.getRange(numThisRow, 6).setFormula('=GoogleFinance(C' + numThisRow + ',"price")');
}

- Cooper

---

我尝试使用数组，但是许多单元格中都有公式。当我执行var data = range.getValues();时，它只获取实际值，因此其中没有公式。我删除了大部分的setValues，但速度似乎没有受到影响。我将数据移动到函数外面，作为全局变量，但也没有影响（虽然我并不认为会有影响）。我只是不明白为什么复制该行并更改几个单元格需要30秒钟每张表格。由于时间太长，我无法将其设置为自动触发器。 - Cactus Dude

我做了一些小的修改，可能会稍微加快速度。你不想把更新的值放在文件顶部吗？ - Cooper

我对代码进行了修改，删除了一个无用的行。我把最新的数据放在了顶部，但实际上并不喜欢这种方式，所以又回到了之前的样子。看起来最耗时的那一行是 range.copyTo 的代码。必须有其他方法来复制公式和数据。 - Cactus Dude

如果您想要复制公式和数据，copyTo 是最好的方法。 - Cooper

Answer 3

看起来是谷歌的问题。在我的测试中，即使是SpreadsheetApp.getSheetByName()也会使脚本减速一分钟左右。

- sarimarton