google-apps-script - 简单的 Google 表格脚本现在速度慢且不可靠

Question

我编写了以下脚本，将一个菜单项和几个自定义排序和颜色函数添加到我的一个 Google 电子表格中。该电子表格只有大约 40 行和 10 列。该脚本过去运行时间很短，但现在运行三个主要功能之一大约需要 25 秒。此外，当脚本完成时，我在刷新浏览器之前看不到排序和行着色的结果。谁能指出我正在发生的事情的正确方向？

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Pastoral Care", functionName: "customSort1"},{name: "Read Aloud Prayers", functionName: "customSort2"},{name: "Priests' Notes", functionName: "customSort3"} ];
  ss.addMenu("Layout", menuEntries);
}

function customSort1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  sheet.sort(2, true).sort(3, true).sort(1, true);

  var endRow = sheet.getLastRow();
  var endColumn = sheet.getLastColumn();

  for (var r = 2; r <= endRow; r++) {
    colorRowPastoralCare(r, endColumn);
  }
}

function customSort2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  sheet.sort(2, true).sort(3, true).sort(5, false);

  var endRow = sheet.getLastRow();
  var endColumn = sheet.getLastColumn();

  for (var r = 2; r <= endRow; r++) {
    colorRowReadAloudPrayers(r, endColumn);
  }
}

function customSort3() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  sheet.sort(2, true).sort(3, true).sort(6, false);

  var endRow = sheet.getLastRow();
  var endColumn = sheet.getLastColumn();

  for (var r = 2; r <= endRow; r++) {
    colorRowPriestsNotes(r, endColumn);
  }
}

function colorRowPastoralCare(r, endColumn){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, endColumn);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[0] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[0] === "1 Acute"){
    dataRange.setBackgroundRGB(255, 211, 216);
  }else if(row[0] === "2 Recovering"){
    dataRange.setBackgroundRGB(255, 236, 218);
  }else if(row[0] === "3 Long-term"){
    dataRange.setBackgroundRGB(255, 254, 220);
  }else if(row[0] === "4 Prayer only"){
    dataRange.setBackgroundRGB(213, 255, 227);
  }else if(row[0] === "5 Homebound"){
    dataRange.setBackgroundRGB(215, 238, 253);
  }
  SpreadsheetApp.flush(); 
}

function colorRowReadAloudPrayers(r, endColumn){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, endColumn);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[4] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[4] === "Y"){
    dataRange.setBackgroundRGB(213, 255, 227);
  }else if(row[4] === "N"){
    dataRange.setBackgroundRGB(215, 238, 253);
  }
  SpreadsheetApp.flush(); 
}

function colorRowPriestsNotes(r, endColumn){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, endColumn);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[5] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[5] === "Y"){
    dataRange.setBackgroundRGB(213, 255, 227);
  }else if(row[5] === "N"){
    dataRange.setBackgroundRGB(215, 238, 253);
  }
  SpreadsheetApp.flush(); 
}

标签： google-apps-scriptgoogle-sheets

Answer 1

我的猜测是你想做这两种类型中的一种。我猜你真的想要第一个。上升或下降的选择可能与我所拥有的不同。

function sorttest1() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  rg.sort([{column:1,ascending:true},{column:2,ascending:false},{column:3,ascending:false}]);
}

function sorttest2() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  rg.sort({column:1,ascending:true}).sort({column:2,ascending:false}).sort({column:3,ascending:false});
}