この広告は、90日以上更新していないブログに表示しています。SpreadsheetでシートAに入力した情報を他のシートで参照して自動処理するの、ちょう便利ですよね。厄介な部分が、Excelと同じように発生する#REF!問題です。Spreadsheet(や、そのネタ元となったExcel)は、任意のレンジ(セルやセル範囲)をカット・コピー・ペーストする際、別セルの数式内からそのレンジを相対参照している場合は、相対参照の位置関係が維持されるよう、そのレンジ表記も連動して書き換えてくれます。"=ROW(A1)"という数式が入ってるセルを右下のセルにコピーしたら"=ROW(B2)"になる、とかね。別シートを参照する数式でも同じように処理されるので、シートAを編集すれば、シートAのレンジを参照しているシートZの数式もその編集に追従してくれる。これ表計算ソフトには不可欠な機能なんですが、一方この仕様によりしばしば発生するのが「セルやレンジ単位で削除コマンドを実行したときに、他のセルの数式が壊れることがある」という問題です。具体的に言うと、あるセルやレンジの内容を別セルQの数式内から参照してるときに元のセルやレンジを削除すると、「参照先が存在しない」というエラーが発生して、そのレンジ表記が"#REF!"に書き換えられてしまい、数式が壊れます(#REF!問題)。Spreadsheetは人と共有したときに威力を発揮するので、入力用シートはいろんな人に編集を許すこともあります(form経由で情報追記だけを許可する方法もあるけど、複数情報の追記とか行の入れ替えとか再編集とか不便なんですよね)。すると、裏側にある処理用シートなどで、この#REF!問題がバンバン発生する。「なんかうまく動かないんだけど」と言われて見に行くと、たいてい入力用シートでのセル削除に伴って別シートの数式が壊れてるわけです。面倒でもコピペの繰り返しだけで編集すれば問題は起こらないよ」とカバーする方法もありますが、こんな説明が通じる人はそんなに多くないし、そもそも解決策として美しくない。いうわけで、複数の人に編集権限を与えるシートの内容に対し、別シートで後処理を行いたいときは:編集用のシートAと後処理用のシートZの間に中間処理用のシートBを挟むシートAが編集されるたびに、GASのonEdit()でその内容全体をシートBにコピーするシートZからはこの中間処理用のシートBを参照させるという方法がおすすめです。sheet_copyTo.getRange(copyRange).setValues(copyValue);}こうすることで、シートAには別セルから数式で参照されているレンジが一切なくなるので、シートAを(行・列の削除を含め)どんなに編集しても、他のシートでの#REF!問題を誘発しなくなります(GASに記述されたレンジ表記はセル内数式のレンジ表記と違って、ユーザーの編集作業に自動追従しないからです)。シートAの内容はGASにより毎回シートBにコピーされ、このシートB上のレンジは他のシートのセル内数式で非破壊的かつ安全に参照できます。このやり方の唯一の難点は、編集動作がほんのちょっとだけ遅くなることでしょうか。※もちろん、Excelでもマクロで同じ逃げ方ができます。使用する機会は稀だと思いますが...。