Muchonovski got it wrong

なにが なんとも。どれが どうでも。

#REF!に悩まされない人生を生きる

Google SpreadsheetでシートAに入力した情報を他のシートで参照して自動処理するの、ちょう便利ですよね。ただ厄介な部分が、Excelと同じように発生する#REF!問題です。ご存知のように、Google Spreadsheet(や、そのネタ元となったExcel)は、任意のレンジ(セルやセル範囲)をカット・コピー・ペーストする際、別セルの数式内からそのレンジを相対参照している場合は、相対参照の位置関係が維持されるよう、そのレンジ表記も連動して書き換えてくれます。たとえば"=ROW(A1)"という数式が入ってるセルを右下のセルにコピーしたら"=ROW(B2)"になる、とかね。別シートを参照する数式でも同じように処理されるので、シートAを編集すれば、シートAのレンジを参照しているシートZの数式もその編集に追従してくれる。

これ表計算ソフトには不可欠な機能なんですが、一方この仕様によりしばしば発生するのが「セルやレンジ単位で削除コマンドを実行したときに、他のセルの数式が壊れることがある」という問題です。もうちょっと具体的に言うと、あるセルやレンジの内容を別セルQの数式内から参照してるときに元のセルやレンジを削除すると、「参照先が存在しない」というエラーが発生して、そのレンジ表記が"#REF!"に書き換えられてしまい、数式が壊れます(#REF!問題)。

自分だけが編集してるspreadsheetの場合は、この問題が起きたときも比較的対処がしやすいのですが、Google Spreadsheetは人と共有したときに威力を発揮するので、入力用シートはいろんな人に編集を許すこともあります(form経由で情報追記だけを許可する方法もあるけど、複数情報の追記とか行の入れ替えとか再編集とか不便なんですよね)。そうすると、裏側にある処理用シートなどで、この#REF!問題がバンバン発生する。「なんかうまく動かないんだけど」と言われて見に行くと、たいてい入力用シートでのセル削除に伴って別シートの数式が壊れてるわけです。これを運用ルールで「削除コマンドは仕様厳禁! 面倒でもコピペの繰り返しだけで編集すれば問題は起こらないよ」とカバーする方法もありますが、こんな説明が通じる人はそんなに多くないし、そもそも解決策として美しくない。

というわけで、複数の人に編集権限を与えるシートの内容に対し、別シートで後処理を行いたいときは:

  1. 編集用のシートAと後処理用のシートZの間に中間処理用のシートBを挟む
  2. シートAが編集されるたびに、GASのonEdit()でその内容全体をシートBにコピーする
  3. シートZからはこの中間処理用のシートBを参照させる

という方法がおすすめです。具体的には、script.gs にこんなかんじのGASのscriptを仕掛けます:

function onEdit(event) {
  var originSheet = '編集用シート';
  var destinSheet = '中間処理シート';
  var copyRange  = 'A1:Z1000';
  copySheet( originSheet , destinSheet , copyRange );
}

function copySheet( originSheet , destinSheet , copyRange ) {

  var workingSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet_copyFrom = workingSheet.getSheetByName( originSheet );
  var sheet_copyTo   = workingSheet.getSheetByName( destinSheet );
 
  var copyValue = sheet_copyFrom.getRange(copyRange).getValues();
  sheet_copyTo.getRange(copyRange).setValues(copyValue);
}

こうすることで、シートAには別セルから数式で参照されているレンジが一切なくなるので、シートAを(行・列の削除を含め)どんなに編集しても、他のシートでの#REF!問題を誘発しなくなります(GASに記述されたレンジ表記はセル内数式のレンジ表記と違って、ユーザーの編集作業に自動追従しないからです)。シートAの内容はGASにより毎回シートBにコピーされ、このシートB上のレンジは他のシートのセル内数式で非破壊的かつ安全に参照できます。安心安心。このやり方の唯一の難点は、編集動作がほんのちょっとだけ遅くなることでしょうか。

※もちろん、Excelでもマクロで同じ逃げ方ができます。使用する機会は稀だと思いますが…。