Googleスプレッドシート IMPORTRANGE関数 超応用例
Googleスプレッドシートを代表する便利関数のひとつ IMPORTRANGE関数についてまとめてみました。
これまでも mirの note の中でたびたび登場してるんで、既に書いているネタと重複する部分もありますが、地味な仕様変更もあったんで 改めてIMPORTRANGE関数の 基本や注意点、活用方法まで紹介していきます。
なお、タイトルは「超応用例」としていますが、IMPORTRANGE関数単体では超応用例といえるものはあまりないですｗ そして、やはり今回は超応用例まではたどり着きません！
前回までは Googleスプレッドシートのハイパーリンク、HYPERLINK関数について全３回にわたって特集してきました。
IMPORTRANGE関数の基本
まずは、この関数を初めて知る人、使ったことがな人でも「これから使えるようになる」為に IMPORTRANGE関数の基本からいきましょう。
なお、IMPORTRANGE関数を語る時に問題となるのが、「参照先」と「参照元」という日本語。
これ、日本語の感覚だと
「参照先」・・・参照したい元データが入ったセル
「参照元」・・・参照する為の式が入っていて結果を出力させるセル
ってとらえがちなんですが、スプレッドシートの代表格である Excel の言葉だと
「参照元」・・・参照したい元データが入ったセル
「参照先」・・・参照する為の式が入っていて結果を出力させるセル
このような定義なんですよね。。
非常にわかりにくいんで、今回は参照元とか参照先という言葉を極力省いて、
「スプレッドシートB」
参照したい元データが入ったスプレッドシート
「スプレッドシートA」
参照する為の Importrang関数を入れるスプレッドシート
このように仮置きして記述していきます。
IMPORTRANGE関数は別スプレッドシートを参照する関数
IMPORTRANGE関数は、Googleスプレッドシートで 別のスプレッドシートの情報（特定セル内の値）を参照する時に使う関数です。
別スプレッドシートというのは、別ファイル（Excelでいう別ブック）です。
たまに別スプレッドシートのことを別シートと言う人がいますが、同一スプレッドシート内の別シートと混同するので、言葉を明確に分けた方がよいです。
たとえば スプレッドシートAというファイルから スプレッドシートBというファイルの シート2のA1:C10を参照したい！（情報を引っ張ってきたい）
こんな時に使うのが IMPORTRANGE関数です。
ちなみに上のリンク先が、Google公式の IMPORTRANGE関数の解説なんですが、色々難しいことを書いてるわりに 肝心なことが書いてなかったり、わかりづらいんですよね。。
IMPORTRANGE関数の作成方法
=IMPORTRANGE(スプレッドシートの URL, 範囲の文字列)
※スプレッドシートのURLは スプレッドシートIDでも可
※範囲の文字列は シート名!セル位置のA1表記
それでは IMPORTRANGE関数を実際どのように作成すればよいか？
引数の設定（記述） を見ていきましょう。
IMPORTRANGEは2つの引数が必要になります。（どちらも必須です）
引数1： スプレッドシートのURL、またはスプレッドシートID
引数2： シート名、セル位置を指定する文字列
引数1で 参照するスプレッドシートファイル（ブック）を指定、引数2でのそのスプレッドシートファイル（ブック）のどのシートのどのセル範囲か？を指定します。
つまり、参照したいデータを
スプレッドシート（URLまたはIDで指定）
＆
シート（シート名で指定し、後ろに ! をつける）
＆
セル（A1表記で指定 範囲指定可能）
IMPORTRANGE("スプレッドシートURL(またはID)","シート名!セル位置")
このように指定するわけです。
公式には 引数1は スプレッドシートのURLとだけ書いていますが、ここはスプレッドシートIDでも代用が可能。
スプレッドシートIDはURLの
https://docs.google.com/spreadsheets/d/ スプレッドシートID /edit#gid=シートID
この部分です。ハイパーリンクシリーズで登場した gid= の後ろの シートID の方と混同しやすいので注意しましょう。
ちなみに GoogleスプレッドシートのURLは取得の仕方によって色々なパターンがありますが、
https://docs.google.com/spreadsheets/d/
で始まるものであれば、どれでも問題ありません。
ただし、Googleドライブ上で Ctrl + C で取得するURLは IMPORTRANGEで利用出来ないので注意が必要です。
また、シートを指定するシートIDがURLに含まれていても無視されます。第1引数のURLではなく、シートの指定は第2引数で行う必要があります。
URL、IDどちらを使ってもよいんですが、上の画像を見るとわかる通り、スプレッドシートURLを使うと式が長くなります。
mirとしては式がぐっと短くなる スプレッドシートIDでの指定をおススメします。
残念ながらこのスプレッドシートIDは、手作業によるコピペか GASを使う以外に取得する方法はありません。
第2引数の方は、基本的には INDIRECT関数をA1形式で使う時と同じですね。
たとえば、第1引数の スプレッドシートURL（またはID）で指定した スプレッドシートBの シート2の A1:C10を取得したい場合は
"シート2!A1:C10"
このように前後に " をつけて、文字列として指定します。
たまに勘違いして IMPORTRANGEに渡す第2引数を
=IMPORTRANGE("スプレッドシートID",シート2!A1:C10)
✖ 第2引数を "で括って文字列にしていない為、この（式を入れた）スプレッドシートの シート2!A1:C10に入っている文字列を参照しようとしている
としたり
=IMPORTRANGE("スプレッドシートID",INDIRECT("シート2!A1:C10"))
✖ 第2引数をINDIRECTを付けて参照にしようとしている
こんなことをする人がいますが、完全に間違いです。
繰り返しますが、第2引数の シート名!A1表記のセル位置 は文字列とする必要があります。
また、間違いではないですが
=IMPORTRANGE("スプレッドシートID","'シート2'!A1:C10")
△ 日本語シート名を シングルクォートで括って 'シート2' とする必要はない
このように日本語シート名をシングルクォートで括る書き方も、問題なく使えはしますが 記述ミスに繋がりやすいのでおススメしません。
シート名は シングルクォートで括る必要はありません。
もしシート名を忘れて（省略して）、第2引数を "A1:C10" とした場合は、エラーにはならず 先頭のシート（一番左のシート）のセルを参照するという動きになります。
IMPORTRANGEの引数をセル参照にする
IMPORTEANGEは 第1引数、第2引数ともに文字列を指定するので、セルに入れた値を参照して使うことが出来ます。
たとえば スプレッドシートIDを B2 セルに入れた場合、
=IMPORTRANGE(B2,"シート2!A1:C10")
B2セルにスプレッドシートIDが入っている
このような式になります。
理由は後で書きますが、IMPORTRANGE関数は、このようにスプレッドシートIDは外だし（別セルに入れて参照）する式の作り方がベストです。
同じくシート名もセル参照にできますし、これらを プルダウンと組み合わせるといったことも可能です。
=IMPORTRANGE(B1,B2&"!A1:C10")
このように式を組んで B2セルにシート名をプルダウンでセットすることで、別スプレッドシートを プルダウンでシートを切り替えて参照することが出来ます。
IMPORTRANGE関数は初回にアクセス許可が必要
順番が前後しますが、IMPORTRANGE関数は 初回は 正しい式を入れても最初は #REF!エラーが出てしまい 結果が表示されません。
これはエラーセルに ポインタをあてるとわかりますが、上の画像の通り IMPORTRANGE関数は 他のスプレッドシートを最初に参照する時に「アクセスを許可」が必要になる為です。
「アクセスを許可」を押すことで、取得したいデータが入っている（スプレッドシートB）が importrange関数を入れた このスプレッドシート（スプレッドシートA）から見える状態（アクセス許可状態）となり、結果が出力されるようになります。
アクセス許可については、なかなか複雑なので後ほど詳しく触れますが、最初の「許可」は 参照される側（スプレッドシートB）にとっての「最初」であり、スプレッドシートファイル（ブック）単位 の許可なります。
つまり
「アクセス許可」で スプレッドシート Bを 参照（閲覧）することを スプレッドシートA に許可
すれば 、別シートや別範囲でも 以降はスプレッドシートAから スプレッドシートBへは アクセスし放題。
一方、スプレッドシートAから、また別のスプレッドシート Cを参照したい場合は、初回はアクセス許可が必要になります。
また、スプレッドシートAをコピーしたファイル（スプレッドシートAのコピー）を作った場合は、そちらから スプレッドシートBへのアクセスはやはり初回に「アクセス許可」をする必要があります。
データが取得できれば、あとは ほぼリアルタイムで スプレッドシートB の更新がスプレッドシートA にも連動されます。
これで IMPORTRANGE関数が使えるようになりましたね！
IMPORTRANGE関数が使えると何が便利か？
別スプレッドシートにデータを連動させることができるので、様々な使い方が考えられます。
・他のメンバーが見える元データのスプレッドシートとは別で、
他の人に見えないスプレッドシートでこっそり集計したい
・元データのスプレッドシートは共有したくないが、
一部のデータだけメンバーに公開したい
・複数のスプレッドシートのデータを 一つのスプレッドシートで
まとめてみたい
たまに、Googleスプレッドシートの 特定のシートだけ共有したい！なんて要望がありますが、残念ながら Googleスプレッドシートは特定のシートや指定したセル範囲だけを共有するといったことは出来ません。
こんな時、IMPORTRANGEを使うことでスプレッドシートBは共有することなく、スプレッドシートBの見せたい部分だけを スプレッドシートAに出力して共有することで、セキュアに 見せたい部分だけをメンバーに共有することが可能となります。（閲覧用ですが）
他には営業チームに 3人のメンバーと1人のリーダーがいて、スプレッドシートファイルをメンバー毎に用意して、日報のように使用していたとします。
この時、上司がメンバーのスプレッドシートをいちいち 開いて報告を確認だと面倒ですね。
これをリーダー用のスプレッドシートからIMPORTRANGE関数で各シートを参照することで、一元表示するといった使い方ができます。
さらに、これは応用例で触れたいと思いますが、QUERY関数を組み合わせれば 各々のスプレッドシートの報告の 本日の分だけを 1つのシートで確認といったことも可能です。
非常に便利なIMPORTRANGE関数ですが、大量データを取得したり複数のIMOPORTRANGE関数を使っていると 動きが重くなるという欠点があります。
同じスプレッドシート内の別シートで処理した方が良いケースもあるので、そこを見極めて利用することをお勧めします。
IMPORTRANGE関数の「アクセスを許可」を理解する
IMPORTRANGE関数で初回に必要となる「アクセスを許可」は結構挙動が複雑で、公式ページの解説も十分とは言えません。
特に秘匿性の高いスプレッドシートをIMPORTRANGEで参照させる場合は、ここをしっかり理解しておくことが重要です。
アクセスを許可は絶対に必要？
これは「基本的」には最初の1回は絶対必要です。
仮に IMPORTRANGE関数を入れた スプレッドシートA、元データーのあるスプレッドシートB どちらも自分がオーナー（自分が作成したファイル）であっても、「アクセスを許可」は必要となります。
この「アクセスを許可」は残念ながら GASで自動対処する方法は見当たらず、手作業でボタンを押す必要があります。
ただし例外があって、以下の2つの場合だと「アクセスを許可」無しで IMPORTRANGEによるデータ取得が可能です。
参照したいスプレッドシートがリンク共有状態である場合
同一スプレッドシート内の別シートを 参照する場合
2の方は、IMPORTRANGEを使う必要がないケースなんで割愛します。
1についてですが、たとえば スプレッドシートB が「リンクを知っている全員」を閲覧者（または編集者）と共有されている場合は、「アクセスを許可」が不要となります。
こちらの方法は、スプレッドシートBの リンク共有状態が継続されている限りは使えます。
スプレッドシートBの リンク共有を再び「制限あり」に変えた場合は、IMPORTRANGE関数で「アクセスを許可」が出るようになります。
というわけで、過去に公開したスプレッドシートを 公開停止後にもずっとIMPORTRANGEでデータを覗き見されちゃう、といったことは発生しませんのでご安心を。
ま、普通は大事な元データが入ったスプレッドシートを リンク共有（全世界から見える状態）にすることはないので、こちらも一般的に使える方法とは言えませんね。
手作業による「アクセスを許可」は必須と考えておきましょう。
「アクセスを許可」は オーナー以外でも出来る
つぎに 「アクセスを許可」が出来るのは誰か？
実は スプレッドシートA、スプレッドシートBのオーナー以外でも 条件を満たした共有メンバーであれば「アクセスを許可」することが可能です。
その条件がコチラ
・IMPORTRANGE関数を入れる スプレッドシートAの編集権限がある
・元データの入ったスプレッドシートBの 閲覧以上の権限があり、
ダウンロードや印刷禁止が設定されていない
当然ですが、IMPORTRANGE関数が入った スプレッドシートAの編集権限がないと 「アクセスを許可」のボタンは表示されません。
スプレッドシートAの編集者があっても、元データのあるスプレッドシートBにアクセスできない（共有されていない）場合は
このように「アクセスを許可」ボタンが表示されません。
また スプレッドシートBの閲覧権限が あっても、ダウンロードや印刷が禁止された設定の場合は 「アクセスを許可」ボタンは表示されません。
スプレッドシートBにダウンロード、印刷が禁止されていなければ 、
スプレッドシートＡ（IMPORTRANGE の式を入れたシート）は編集権限
スプレッドシートＢ（参照したい元データがあるシート）は閲覧権限
というメンバーでも「アクセスを許可」が可能となります。
ちょっと複雑ですが、要は オーナー以外の共有メンバーも IMPORTRANGEでデータ取得ができちゃうってことです。
ちなみに IMPORTRANGEのアクセス許可は、「アクセスを許可」した ユーザーに紐づいて います。
たとえば オーナーではなく 共有メンバーの一人 田中さんが アクセス許可していた場合、元データのある スプレッドシートBの共有メンバーから 田中さんを削除してしまうと、「アクセスを許可」はリセットされ再度オーナーか共有メンバーの誰かが「アクセスを許可」しないとデータが取得できなくなります。
「アクセス許可をしたのは誰か？」は結構重要ってことですね。
「アクセスを許可」はひっそりとログに残る
しかし、オーナー以外もIMPORTRANGEによるアクセス許可が出来るのは、便利な反面知らないうちにアクセスされているという恐怖もあります。
残念ながら誰かが勝手に IMPORTRANGEで こっそり参照していたとしても、元データの Googleスプレッドシート上では確認する術がありませんし、オーナーに通知される仕組みもありません。
しかし、スプレッドシートが IMPORTRANGE関数で参照されたかどうか、また いつ、だれが アクセス許可したか、これを確認する方法が一応あります。
それは履歴（ログ）に残るわずかな痕跡を探す方法です。
Googleドライブで 該当のスプレッドシートのアイコンを選択した状態で、〇にi のボタンでサイドバーに詳細・履歴を表示し、タブを履歴に切り替えます。
このファイルに対する更新の履歴が確認できるのですが、ここに残る
〇〇さん が 1個のアイテムを共有しました
不明 閲覧者
これが、IMPORTRANGEによる 参照を許可した ログとなります。
つまり、この〇〇さんが 表示されている日時に アクセス許可 をした犯人（実行者）ってことです。
ちなみにこの変更ログは、スプレッドシート側の 変更履歴には残りません。Googleドライブ側の履歴でのみ確認できます。
そして、「不明」を閲覧者とて共有したというログは残るものの、共有設定画面では確認できないという困りもの。
Google Drive Activity API で取得できるかもしれませんが、ちょっとハードルが高そうですね。
不安がある場合は 一度 共有メンバーを全員権限削除して、一度ファイルを誰とも共有してない状態にすることで、共有メンバーがアクセス許可した IMPORTRANGE関数を解除させる方法があります。
再び共有メンバーを元のロールに戻しても、IMPORTRANGE関数は 再度アクセスを許可が必要となるので、ある程度の防止策にはなるかと。
ちなみに元データのあるスプレッドシートBのオーナー自身がアクセス許可した場合は、アクセス許可の取り消し方法は無さそうでした。
ゴミ箱にあるスプレッドシートでもIMPORTRANGEできる
ゴミ箱にあるスプレッドシートは、中身を見ようと開いても、このような表示が出てしまいます。
ゴミ箱に入れたまま 中身を参照したり編集は出来ません。
ホーム画面へ移動（諦める）か、ゴミ箱の外に移す（ゴミ箱から出して中身を確認）の2択となります。
しかし、URL（スプレッドシートID）がわかっていれば、ゴミ箱に入っているスプレッドシートをアクセス許可して、IMPORTRANGEで参照することが可能です。
つまり、ゴミ箱に入れられたとしても IMPORTRANGEによる参照は問題ないので、スプレッドシートA側を見ている人からは、まさか元データの入っているスプレッドシートBが ゴミ箱に入ってるとは気づきません。
本当に最後のさいご、ゴミ箱に入れて30日後に完全削除となって、ようやくIMPORTRANGEがエラーを返して気づくことになります。時すでに遅しですね。
せめて、ゴミ箱に入った段階でIMPORTRANGE関数がエラーを返してくれると良いのですが。
どのスプレッドシートから IMPORTRANGE関数で参照されているかわからない
IMPORTRANGE関数は 元データのスプレッドシート側からは、いったいどのスプレッドシートから IMPORTRANGEで参照されているか？
いわゆるスプレッドシート間のリレーションがまったくわかりません。
これが結構問題で、上のように誤って他のスプレッドシートから 参照している元データの入ったスプレッドシートを削除してしまうといったことも発生しがちです。
これを解決する方法として
↑こちらのように GASで 探索する方法もありますが、より簡単な方法があります。
それが、スプレッドシートID でGoogleドライブを検索する方法です。
上は、スプレッドシートBのスプレッドシートIDでGoogleドライブを検索した結果ですが、2件スプレッドシートがヒットしました。
スプレッドシートB 自体 や 数式内に直接 スプレッドシートIDを入れ込んでいる ファイルは検索にヒットしませんが、スプレッドシートIDをセルに入力して、それをIMPORTRANGE関数の引数として参照しているファイルはヒットしています。
そうです、これが使えるのは 基本のパートで書いた
=IMPORTRANGE(B2,"シート2!A1:C10")
B2セルにスプレッドシートIDが入っている
このような式の作り方をしているスプレッドシートです。
IMPORTRANGEを使う時は スプレッドシートIDはセルに入れて セル参照する式にするって ルール化すれば、このように 「あれ、このスプレッドシートのデータ、他のシートで参照してるんかな？」って時に ID検索でそれらしいスプレッドシートを簡単に見つけることができるわけです。
もちろん、自分が見えない（権限のない）スプレッドシートから参照されていた場合は 探しようがありませんが、原始的ながらも割とアリな運用方法かなと思います。
IMPORTRANGE関数の注意点
IMPORTRANGE関数は万能ではありません。出来ないことも多いので、そこを理解した上で使用しましょう。
IMPORTRANGEは Googleスプレッドシート同士でしか使えない
自分がオーナーなのに、importrangeで参照しようとすると「アクセスする権限がありません。」って出るんですが・・・。
このような書き込みをたまに 見かけますが、原因は参照しようとしているのが Googleドライブにアップロードした Excelファイルというケースが多いです。
スプレッドシートの互換表示で開けていると、見た目はスプレッドシートですし、それっぽい IDもURLにつくので IMPORTRANGE出来そうに思えますが、残念ながらExcelに対して importrangeによる参照はできません。
見分け方は ファイル名の横の 緑の .XLSX というタグです。これが付いてるファイルは Excelファイルです。
逆に Googleドライブ内の Excel ファイル側から 他の Googleスプレッドシートファイル をimportrangeで参照しようとすると、このように 「権限を追加してます」という表示で、ずーっとグルグルしたまま進みません。
Excelファイルは対象外ですといったエラーが出てくれると良いんですが、どちらも、なんか出来そうに見えちゃうんでタチが悪いですｗ
IMPORTRANGE関数は残念ながらExcelファイルには使えず、参照する側、される側、どちらも Googleスプレッドシートでないとダメってことです。
Excelファイルを スプレッドシートとして保存してから使いましょう。
IMPORTRANGE関数はオートフィルしない方がよい
IMPORTRANGE関数を下にオートフィルして相対参照したい！ これもたまに見かける質問です。こちらは出来なくはないですが、おススメしません。
まず、当然ですが IMPORTRANGE内の セル指定は 文字列なので下にフィルしたからといって 1,2,3… と数字は増えませんし、相対参照的な変化もしません。
もし、これをやりたい場合は たとえば 4行目に 元データの A1セルを出力して下に A2,A3…としたい場合は
=IMPORTRANGE($B$1,$B$2&"!A"&ROW()-3)
このように 式の後ろの部分 Aまでを文字列として 行番号は
&ROW()-3
とすることで、 4行目の時は1、5行目が2、6行目が3 となり、IMPORTRANGEの参照を一つずつ下にズラすことが出来ます。
ですが、動画を見てわかるとおり、式を入れた一つ一つのセルで 読み込み処理が走る為、非常に重くなります。
10セル程度なら影響はありませんが、100セル、1000セルとIMPORTRANGE関数が一気に再計算されたら・・・。恐ろしいことです。
前時代のExcelじゃないんだから、1セル1式ではなく IMPORTRANGEでは範囲はまとめて取得しましょう。
=IMPORTRANGE($B$1,$B$2&"!A1:A10")
なお、一つの式で複数セルへ結果を返す（Excelのスピル）は、Googleスプレッドシートの場合は Arrayformulaを付けて処理することが多いですが、こちらのIMPORTRANGE関数は 結果を配列で返せる関数です。
Arrayformulaは必要ありません。
IMPORTRANGE関数は 一方通行である
これはIMPORTRANGEもシート関数なんで当たり前なんですが、あくまでもIMPORTRANGE関数は、他のスプレッドシートのデータを表示しているだけです。
出力したデータを書き換えるといった、双方向の編集は出来ません。
また、IMPORTRANGE関数で取得したデータの横の列に 情報を加える運用をしている人がいますが、元データの途中に行が追加された場合は、手入力の情報とズレが発生するんで注意が必要です。
これを無理に解決しようと、GASに手を出す人もいますが個人的にはあまりお勧めしません。
スプレッドシート（シート）間の関数を使った連携は、データベースのような双方向性はなく、一方通行（参照するもの）だと割り切りましょう。
色やフォントなど文字やセルの装飾は 取得・連動できない
同じくこれも 当たり前っちゃ当たり前ですが、元シートで 文字に色を付けたり太字にしたり、フォントサイズを変えても IMPORTRANGEで出力している側には連動しません。
色や太字だけでなく、行や列の幅なども連動はしません。
文字の色などは 同じ条件付き書式を設定するなどで対応しましょう。
IMPORTRANGEでは画像や数式は取得できない
IMPORTRANGE関数で 別スプレッドシートから取得できるのは テキストデータのみです。残念ながら 別シートのセル内画像は取得できません。
このようにエラーが表示されるので、ターゲットのセルに画像があることだけはわかります。
※ 2024年9月追記
例外的に IMAGE関数で表示させている画像、および IMAGE関数の画像をコピー値貼付けした画像だけは、IMPORTRANGEで取得が可能です。
また、画像の左側のスプレッドシートは、右の スプレッドシートBの シート4の B2 セルをIMPORTRANGEで取得しているのですが、取得できるのは 9という結果の値のみです。
この9は =SUM(A4:C4) の計算結果なのですが、この計算は 左側のシートAからは見えないってことです。
デメリットもありますが、これを応用すれば 計算式を見せたくないシートで使えそうですね。
IMPORTRANGEでは意外とセルの表示形式が連動する
これは結構意外かもしれません。
色や画像、式は 取得できないIMPORTRANGE関数ですが、元データのシート（スプレッドシートB）のセルの表示形式は 連動・取得できます。
日付や 〇個といった カスタム表示形式、通貨表記など 右のスプレッドシートBで 表示形式を変更した際、連動してスプレッドシートAも 変化しているのがわかりますね。
セル内の値の変化ではなく、表示形式の変化でも IMPORTRANGE関数が再計算（再読み込み）されるのも驚きですね。
IMPORTRANGE関数で取得したデータは セル範囲でない
IMPORTRANGE関数で取得したデータは 単なる配列であって、セル範囲ではありません。
これがどう影響するかというと、引数としてセル範囲のみ受け付ける関数が使えないってことです。
たとえば セルの位置が関係する ROW関数やCOLUMN関数、さらに指定したセルを起点とする OFFSET関数や 主要な関数だと SUMIF、SUMIFS関数なども使えません。
これらを使った式は、他の関数で代替する必要があるってことです。
IMPORTRANGE関数にQUERY関数を組み合わせる場合は書き方が変わる
IMPORTRANGE関数で取得したデータが 配列であることで、関数の書き方が変わるのが QUERY関数です。
これは有名なので知っている人も多いでしょうが、なんとなく
IMPORTRANGEと組み合わせる時は QUERY関数の 列指定が
A,B ではなく Col1,Col2 にする
って感覚で覚えてたりしませんか？
これはIMPORTRANGE関数で取得したデータが配列であるため、範囲と違って A列、B列という情報を持たない、ってのが理由になります。
※同じスプレッドシート内の別シート（シート5）を参照する時
=QUERY('シート5'!A:C,"select B,C where A is not null order by C desc")
※別のスプレッドシートのシート3をIMPORTRANGEで参照する時
=QUERY(IMPORTRANGE(B1,"シート3!A:C"),"select Col2,Col3 where Col1 is not null order by Col3 desc")
↑
でも最新の仕様だと、このQUERY関数の列指定ルールがちょっと変わったんですよね。
次のパートでそれについて書きます。
同一スプレッドシートで IMPORTRANGE関数を使った時の奇妙な特徴
今回は、これを最後に書いて終わりとしましょう。
同一スプレッドシートの別シートを参照する際、IMPORTRANGE関数を使う必要は全くないんですが、実は 割と最近 同一スプレッドシート内で IMPORTRANGE関数を使って別シートを参照した時の挙動が変わったんで紹介したいと思います。
通常の同一スプレッドシート内の別シート参照方法
上で書いた通り そもそも 同じスプレッドシート（ブック）内の 別シートを参照するなら、IMPORTRANGE関数を使う必要はありません。
普通にシート1の A1 セルを取得したいなら
=シート1!A1
これで OKです。シート名の シングルクォートはEnterで確定すると勝手に補完されるんで、式を書く時には付けなくてよいです。
単体セルではなく セル範囲を取得したい場合は、Googleスプレッドシートの場合は自動ではスピらないので
中カッコで括る方法
={'シート1'!A1:C10}
ARRAYFORMULAを使う方法
=ARRAYFORMULA('シート1'!A1:C10)
このように記述します。
しかし、これらはどちらも結果は 配列となります。
一方、実は INDEX関数を使うと配列ではなくセル範囲の参照として返すことが出来ます。
意識して使いわけるケースは少ないかと思いますが。。
同一スプレッドシート内の別シート参照 でIMPORTRANGEを使った時の挙動
=IMPORTRANGE("この式を入れてるスプレッドシートのID","シート1!A1:C10")
もともと（2023年の前半くらい迄）は 、IMPORTRANGE関数で 同一スプレッドシート内の別シートを取得する式を使った場合でも、 初回の 「アクセス許可」が必要でした。
また、一回外部（インターネット）を経由して遠回りて読み込んでいるようで、「読み込み」時間が発生していました。
つまり、以前は別スプレッドシートを参照した時と同じ挙動だったわけです。
しかし上の動画でわかる通り、現在は読み込み時間はなくサクッと情報が反映されます。また、初回の「アクセス許可」も表示されません。
どうやら 2024年の年初前後あたりから、同一スプレッドシートで IMPORTRANGEを使った場合は、直接別シートを参照した時と同じ動きに変わったようです。
しかし、それだけではありませんでした。
QUERY関数も実は 仕様変更されていた
同一スプレッドシート内の別シート参照に INMPORTRANGEを使うと セル範囲の参照として扱われるのであれば、QUERY関数を組み合わせるとどうなるか？
なんと
=QUERY(IMPORTRANGE("スプレッドシートID","シート1!A1:C10"),"select B,C order by C desc")
=QUERY(IMPORTRANGE("スプレッドシートID","シート1!A1:C10"),"select Col2,Col3 order by Col3 desc")
と、QUERY関数の列指定が A,B,C 表記、Col1,Col2,Col3 表記 どちらも使えます。
これは IMPORTRANGEの仕様変更か！と 発見じゃ～と思いましたが、さらに調べると
=QUERY('シート1'!A1:C10,"select Col2,Col3 order by Col3 desc")
なんとこのように 普通にセル範囲の参照でも QUERY関数で Col1,Col2指定が出来るようになっていました。。
どうやら QUERY関数の仕様変更みたいです。
でも、このQUERY関数の仕様変更は、IMPORTRANGEの同一スプレッドシート内のデータ参照の仕様変更に合わせたものじゃないかと思うんですよね。
これについては、ちょうど 仕様が変更したり戻ったりしてゴタゴタしてた時期に、mirも IMPORTRANGEとQUERYの仕様変更について noteで取り上げたので、興味がある奇特な方はそちらも参照ください。
次回 IMPORTRANGE の超応用例へ
今回は 超応用例までたどり着きませんでしたが、長くなったのでここまでとします。
次回こそは、他の関数と組み合わせた IMPORTRANGE関数の 超応用例に入っていきたいと思います。
いいなと思ったら応援しよう！
