【GAS】onEdit(e)を使ってスプレッドシートの行の変動による式崩れを防ぐ

　このタイトルを見て「あるある」って思った人はスプレッドシーターの仲間と認定します（笑）。

　小遣い帳とか、在庫の受払帳みたいなものを想定していて、たとえばこんな簡単な出納帳を例に検討します。

　この状態で行を挿入したり削除したり、最大行を追加したりするとどうなるか、見ていきましょう。

行挿入

　セルE8は1個上のセルE7ではなく、挿入前の2つ上のセルE6を参照したままとなり、以降の残高の値がE7をすっ飛ばしたままとなってしまい、以降の残高の値が不正確となります。

行削除

　E7が#REF!となり、以降もその影響を受けて全部#REF!となってしまいます。参照したいセルがなくなり、数式が=#REF!+・・・となってしまったためです。

最大行追加

　試しに3行追加しましたが、当然のことながら式は自動的には入ってくれません。

onEdit(e)で残高セルを監視し、計算式の空白や#REF!が出たら対応する

　そして、現時点の私見ですが「列ごと一気に数式を入れ直す」のが少々乱暴ですが最も手っ取り早い解決策かと思います。forで回したりしてピンポイントにセルを修正でもいいんですが行数が多くなってくるとパフォーマンスが微妙かと。また、複数の異なる離れた行の削除にも対応するためというのもあります。まだいっぺんに直すほうがマシかと思います。

コード.gs

var sh = SpreadsheetApp.getActiveSheet();

function onEdit(e) {
  // 変更行
  var row = e.range.getRow();
  // 変更列
  var col = e.range.getColumn();
  // 変更行数
  var num = e.range.getValues().length-1;
  // 最終行
  var lr = sh.getLastRow();
  // 最大行
  var mr = sh.getMaxRows();
  // 行挿入or削除
  if (row > 2) {
    // 行挿入or最終行追加
    if (!sh.getRange(row,5).getFormula()) {
      sh.getRange(3,5,mr-2,1).setFormula('=R[-1]C+RC[-2]-RC[-1]');
      // 最大行追加なら罫線
      sh.getRange(lr+1,1,num+1,5).setBorder(true,true,true,true,true,true);
    // 行削除
    } else if (sh.getRange(row,5).getValue() === "#REF!") {
      sh.getRange(3,5,mr-2,1).setFormula('=R[-1]C+RC[-2]-RC[-1]');
    }
  }
}

余談

というか、自力で式を修正したほうが早くないか？ってなるんですけどね。