特定のセルの上罫線の削除をしたいが処理が遅くなってしまう

実現したいこと

これを

こうしたいです。

発生している問題・分からないこと

前の処理が完了する前に次の処理に進んでしまうため、途中でSpreadsheetApp.flush()を使っていますが処理が遅くなってしまいます。
速くする方法はありますでしょうか？

該当のソースコード

function arrangeMacro2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 適用する範囲のセルを取得
  var range = sheet.getActiveRange();//適用する範囲を取得
  var values = range.getValues();//範囲内のセルを配列に格納
  var cellFormula = range.getFormulas();//セルの数式を取得
  var formula = "=INDIRECT(ADDRESS(ROW()-1,COLUMN()))";//数式を定義
  range.setBorder(true, true, true, true, true, true);//罫線を引く

  //指定された範囲内のセルに対して処理を行う
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {

      // セルが空白だった場合、上線を削除し、上セルのコピーを入力する
      if (values[i][j] == "" || cellFormula[i][j] == formula) {
        var cell = range.getCell(i + 1, j + 1);//セルを取得
        cell.setBorder(false, true, true, true, true, true).setFormula(formula).setFontColor('#CCCCCC');
        SpreadsheetApp.flush();
      }

      // 左端の列以外で、左のセルが数式ではない時、上線と「-」を入力する
      if (j > 0 && ((values[i][j] == "-") || (values[i][j - 1] == "-") || (cellFormula[i][j] == formula && cellFormula[i][j - 1] != formula))) {
        var cell = range.getCell(i + 1, j + 1);//セルを取得
        cell.setBorder(true, true, true, true, true, true).setValue("-").setFontColor('#000000');
      }
    }
  }

}

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

特定のセルを配列に格納していき最後にまとめてgetRangeを使って処理しようとしましたが、対象となるセルがバラバラの位置にあるため上手くできませんでした。

補足

特になし

行動規範の内容に同意します

回答1件

ベストアンサー

ご提示のコードそのままでは画像のような結果にはなりませんでした。
指定範囲の上の行にあらかじめハイフンを記入しておく必要がありました。

複数のセルの枠線を短時間で消去すると画面表示に反映されないことがあるので
一度枠線を全て消去した上で必要なセルに枠線を加えるようにしたコードの例です

次のコードがご希望の動作かどうか不明ですが
実行してみると画像のような表示になります

JavaScript
1function arrangeMacro3() {
2  // 空白セルに記入する数式
3  const formula = '=INDIRECT(ADDRESS(ROW()-1,COLUMN()))';
4
5  // 適用範囲のセルの値を取得
6  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
7  const range = sheet.getActiveRange();
8  const values = range.getValues();
9
10  // 範囲内の枠線を消去し、画面に反映
11  range.setBorder(false, false, false, false, false, false);
12  SpreadsheetApp.flush();
13
14  // セル名を蓄積する配列を準備
15  const formulaList = [];
16  const filledList = []; 
17
18  // 範囲内のセルの値に応じて順次処理
19  for (let i = 0; i < values.length; i++) {
20    for (let j = 0; j < values[i].length; j++) {
21      // 値が空なら数式を入力し、セル名を蓄積する’
22      if (values[i][j] === '') {
23        // セル名を配列に蓄積
24        values[i][j] = formula;
25        formulaList.push(range.getCell(i + 1, j + 1).getA1Notation());
26      } else {
27        // 値が空でないセル名を配列に蓄積
28        filledList.push(range.getCell(i + 1, j + 1).getA1Notation());
29      }
30
31      // 範囲の左端以外で、左隣の値がハイフンのとき
32      // または上のif文で値が数式となったが左隣の値が数式ではないとき、
33      // 「-」を入力しセル名を蓄積する
34      if (j > 0 &&
35        ((values[i][j - 1] === '-') ||
36          (values[i][j] === formula && values[i][j - 1] !== formula))) {
37        // 値を設定し、セル名を配列に蓄積
38        values[i][j] = '-';
39        filledList.push(range.getCell(i + 1, j + 1).getA1Notation());
40      }
41    }
42  }
43  
44  // 処理済みの値を範囲に書き戻す
45  range.setValues(values);
46
47  // 数式のセルのフォントの色を設定
48  sheet.getRangeList(formulaList)
49    .setFontColor('#CCCCCC');
50
51  // 数式以外のセルの上辺の枠線を追加し、フォントの色を設定
52  sheet.getRangeList(filledList)
53    .setBorder(true, null, null, null, null, null)
54    .setFontColor('#000000');
55
56  // 外枠と縦の枠線を引く
57  range.setBorder(true, true, true, true, true, null);
58}