【Tips】Googleスプレッドシートで、別シートを参照する場合～バックグラウンドで処理する場合は importrange（）を使おう～

GAS（Google Apps Script）を、Googleスプレッドシートと組み合わせて利用する場合に、ハマりがちな点などを共有させていただく小さい記事です。

別シートの値を引用した数式で、参照元シートの変更が反映されない場合がある

エクセルもそうですが、Googleスプレッドシートでは、複数の表計算シートにデータを分けて記録すると、整理しやすい場合が多いです。

例えば家計簿をつける場合など、毎日の出費は日別のシートに記録し、月ごとの集計は、専用の集計シートを作って計算すると、個々のシートの役割が明確になって分かりやすく管理できます。

こうした場合、他のシートの値を参照する数式表現は、同じブック内であれば、以下の様になります。

＝参照元シート名！セル番地

この様に書いておくと、参照元のシートを変えた場合、数式を記載しているシートでも値が連動して変わります。

こうした仕組みは、エクセルでもGoogleスプレッドシートでも、アプリを開いて作業している限りは大体同じです。

ところが、スプレッドシートの場合、アプリを開かないで値を変える（注）と、同じブック内であってもこの同期は保障されません。

注）GASを使うと、ユーザがアプリを開くことなく、値の編集が可能です。

先の例で言うと、日別のデータを変えたのに、月次の集計データは何も変わらない、という事が、スプレッドシートでは起こりえます。

私はずいぶんこの現象に悩みました。

数式に不備はないし、スプレッドシートを開いてチェックしている分には問題ないのに、GASを使ってバックグラウンドでデータを変更した場合は参照元のデータ変更が集計結果に反映されない、不思議だ・・・。

同じブック内であっても、importrange（）関数を使うのが安全(使いすぎ注意)

同じブック内であってもシートが異なると同期が働かない現象は、アプリを開いてシートを確認する場合には起こらないので、はなかなかその原因に辿り着けません。

結局、ユーザがアプリを開かないバックグラウンド状態では、同じブック内であっても、別シートの参照式は同期が保障されない仕様である、という事だと今では理解しています。

＝参照元シート名！セル番地

バックグラウンドで編集する、という現象は、WEBアプリだからこそ起こる、スプレッドシート固有の現象です。エクセルには無い状況なのでなおさら気づけませんでした。

こうした場合は、importrange（）関数　を使う事で、バックグラウンドであっても同期がとれる様になります。具体的にはセルに以下の数式を記述しておきます。

＝importrange（該当ブックのID、シート名！セル番地）

importrange（）関数　は、異なるブック間での参照に使う事が多いのですが、同じブックであっても、バックグラウンドで別シートを参照する数式を使う場合はお勧めの関数です。

ただし、この関数、多用するとシートの作動がとても重くなります。

アプリで該当シートを開いた時に、多数の関数がアクセスし終えるまで編集できない状態になりますので、最悪、ランタイムが大きくなりすぎてそのままブラウザがフリーズしてしまう事があり得ます。

やたらとシート内で記述しない様に注意しましょう。

使用数を最小限に抑えるには、引用範囲をまとめる

この関数は、引用範囲を個別のセルではなく、まとまった範囲で指定する事もできます。

＝importrange（該当ブックのID、シート名！セル範囲）

この仕様を利用すると、関数の使用数を最小限に抑えることができます。

例えば、以下の様に、書いておくと・・・

黄色い範囲（６行ｘ３列）は、左上の関数部分を除き、空白にしておきます。

１つの関数の記述で、６行ｘ３列の範囲のセルの値がまとめて引用されます。

別シートからの引用はこの様にまとめて行い、その後この値を通常の数式を使って自シート内の必要なセルに表示させると、importrange関数は最小限の使用で済ますことができます。