javascript - 如何使这个谷歌表格脚本代码更短/更快？

Question

该代码非常不言自明。只是有很多我需要独立随机化的范围。例如，范围('W1:W4')不应与范围('W5:W8')混淆，因此我不能只是随机化范围('W1:W80')。任何帮助或建议都会很棒!多谢。目前，代码的运行时间比我想要的要长一些(14 秒)。

function clickStart() {

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('T2:T21').randomize();
  spreadsheet.getRange('Y1:Y20').randomize();
  spreadsheet.getRange('Y21:Y40').randomize();
  spreadsheet.getRange('Y41:Y60').randomize();
  spreadsheet.getRange('Y61:Y80').randomize();
  spreadsheet.getRange('W1:W4').randomize();
  spreadsheet.getRange('W5:W8').randomize();
  spreadsheet.getRange('W9:W12').randomize();
  spreadsheet.getRange('W13:W16').randomize();
  spreadsheet.getRange('W17:W20').randomize();
  spreadsheet.getRange('W21:W24').randomize();
  spreadsheet.getRange('W25:W28').randomize();
  spreadsheet.getRange('W29:W32').randomize();
  spreadsheet.getRange('W33:W36').randomize();
  spreadsheet.getRange('W37:W40').randomize();
  spreadsheet.getRange('W41:W44').randomize();
  spreadsheet.getRange('W45:W48').randomize();
  spreadsheet.getRange('W49:W52').randomize();
  spreadsheet.getRange('W53:W56').randomize();
  spreadsheet.getRange('W57:W60').randomize();
  spreadsheet.getRange('W61:W64').randomize();
  spreadsheet.getRange('W65:W68').randomize();
  spreadsheet.getRange('W69:W72').randomize();
  spreadsheet.getRange('W73:W76').randomize();
  spreadsheet.getRange('W77:W80').randomize();
  spreadsheet.getRange('H10:H12').randomize();
  spreadsheet.getRange('H14:H16').randomize();
  spreadsheet.getRange('H18:H20').randomize();
  spreadsheet.getRange('H22:H24').randomize();
  spreadsheet.getRange('H26:H28').randomize();
  spreadsheet.getRange('H30:H32').randomize();
  spreadsheet.getRange('H34:H36').randomize();
  spreadsheet.getRange('H38:H40').randomize();
  spreadsheet.getRange('H42:H44').randomize();
  spreadsheet.getRange('H46:H48').randomize();
  spreadsheet.getRange('H50:H52').randomize();
  spreadsheet.getRange('H54:H56').randomize();
  spreadsheet.getRange('H58:H60').randomize();
  spreadsheet.getRange('H62:H64').randomize();
  spreadsheet.getRange('H66:H68').randomize();
  spreadsheet.getRange('H70:H72').randomize();
  spreadsheet.getRange('H74:H76').randomize();
  spreadsheet.getRange('H78:H80').randomize();
  spreadsheet.getRange('H82:H84').randomize();
  spreadsheet.getRange('H86:H88').randomize();
};

Answer 1

将 getRange 方法中放入的所有值放入一个数组中并对其进行循环。对于每个 range 调用 getRange 和 randomize 方法。

function clickStart() {

  var ranges = ['T2:T21', 'Y1:Y20', 'Y21:Y40', 'Y41:Y60']; // Add all cells to this array.
  var spreadsheet = SpreadsheetApp.getActive();
  for (let range of ranges) {
    spreadsheet.getRange(range).randomize();
  }

};

但是此时代码仍然需要很长时间并在工作时阻塞您的线程。将其包装在一个 Promise 中，该 Promise 会在循环准备好使代码异步时解析。这将确保其余代码仍然可以运行。

function clickStart() {

  return new Promise(resolve => {

    var ranges = ['T2:T21', 'Y1:Y20', 'Y21:Y40', 'Y41:Y60']; // Add all cells to this array.
    var spreadsheet = SpreadsheetApp.getActive();
    for (let range of ranges) {
      spreadsheet.getRange(range).randomize();
    }
    resolve(spreadsheet);

  });

};