【解消済み】Googleスプレッドシート QUREY関数とIMPORTRANGEの突然のエラー！ その原因は？ 緊急検証してみた
世間一般は Twitterの閲覧制限の件が話題ですが、実は今週（2023年6月下旬） Googleスプレッドシートの QUERY関数 + IMPORTRANGE関数 が「いきなりエラーがでるようになった」ってネットがザワついてました。（さざ波とも言えないレベルですがｗ）
気になったので緊急検証してみました！
note も予定してたネタを差し替えで、こちらを取り上げることに。影響が大きければ 平日更新しようかと思いましたが、よくよく調べてみると影響は限定的だったんで、じゃあ土日更新のタイミングでもいいかなと。
というわけで、「号外」って言うほどのネタじゃないです。
しかも、現在は解消されて元に戻ってるんです・・・。
あれ、同じスプレッドシート内の別シートに対して importrangeを使った時の挙動が戻ってる。なんだったんだ？？
— mir (@mir_for_note) July 1, 2023
ということは、今回の影響で 列指定を Col1,Col2 → A,Ｂ に修正して対応した人たちは、またやり直し？？
没ネタにしようかなと思いましたが、また発生する可能性もあるので掲載することにしました。
ついでに、IMPORTRANGEの基本、そしてQUERY関数と組み合わせる際のポイントにも触れておりますんで、トラブルの影響なかった人も読んで損はないです！
前回の note
QUERY関数とIMPORTRANGE関数で発生したエラーについて
今回、発生していたエラーは 前日まで問題なく使えていた QUERY関数の
=QUERY(importrange("スプレッドシートID","シート3!A:A"),"where Col1 is not null")
このような記述の式が、突如 #VALUE! エラーが 表示されるようになった。というものです。
エラーメッセージでは
関数 QUERY のパラメータ 2 のクエリ文字列を解析できません: NO_COLUMN: Col1
こんな表示が出ているとのこと。
同様の話題が、 Twitter やYahoo!知恵袋、Teratail、Googleドキュメントのフォーラムなど、幾つかのテック系QAサイトや SNSに書き込まれておりました。
QUERY関数の仕様変更か！？
こんな書き込みもありましたが、それだったらもっと多くの人に影響があって、こんなもんじゃない騒ぎになるでしょう。
今回は、そこまで大きい話題になってないので、影響がない人の方がおおいことになります。
そして mirの 環境でも QUERY関数 + IMPORTRANGE関数 の式は問題なく動いてる・・・。
ただ、1名だけじゃなく 同じようなトラブルについて複数書き込まれているってことは、なにかしらの条件で発生しているっぽい。
で、幾つかのパターンを検証することで原因がわかりました。
同じスプレッドシート内の別シート参照で IMPORTRANGEを使って QUERY関数と組み合わせていた 人がエラーになっていた
↑ これが答えでした。
同じスプレッドシート内 の別シートのデータを呼び出すのに IMPORTRANGEを使った時の仕様が変更された影響による エラーだったみたいです。
注：現在は元に戻っているので、仕様変更ではなく単なるトラブルだったようです
スプレッドシートに詳しい人からすると、
「別シート参照するのに、なんで IMPORTRANGE使ってるの？」
ってなるかと思います。
普通は、同じスプレッドシート内の別シートを参照する QUERY関数なら
=QUERY(シート3!A:A,"where A is not null")
このように IMPORTRANGEはつかわないからです。
でも、今回トラブルに見舞われた方々は、QURY関数で 別のスプレッドシートのデータを扱う時の方法を、別シートの時も同様と誤認して？ IMPORTRANGEを組み合わせて使っていたってことでしょうね。
まとめると、今回の不具合発生の原因は
IMPORTRANGEで 同じスプレッドシート内の 別シートを参照していた場合、今までは IMPORTRANGEが機能して サーバー経由で配列を返していた。
しかし、（不具合？で 一時だけ ）仕様が変更され IMPORTRANGEは無視され 普通に別シートを参照したのと同じ挙動で セル範囲を返すようになっていた。
QUERY関数はセル範囲を対象とする時と配列を対象とする時で、列指定の記述方法が違う為、もともと 配列用の記述（Col1,Col2）だった式が、IMPORTRANGEの結果が 配列→セル範囲 に変わったせいで 記述ミスと判定され エラーを返していた。
このようなものでした。
そりゃ影響ある人が少ないわけです。
とりあえず現在は元の仕様に戻り、同じスプレッドシート内の参照でも IMPORTRANGEを使った際は 配列を返すようになったので、今まで通り Col1,Col2という指定で問題ないです。（IMPORTRANGEを使わない方がよいですが）
これにて一件落着。
で、終わりでもいいんですが、せっかくなんで セル範囲と 配列、IMPORTRANGE 関数の基本についてふれておきましょう。
IMPORTRANGE関数を理解する
Googleスプレッドシートは、Excelほど歴史がない為か Googleの自由な 風土からくるものかわかりませんが、呼び名がイマイチ統一されていません。
そもそも日本で Googleスプレッドシートと呼ばれているサービス（アプリ）は、グローバルだと Google Sheets ですし。さらには スプシなどスプレだの通じるのかよくかわらない 略語も見かけます。
シートのことも タブって言う人がいたりして紛らわしいです。
で、一番やっかないなのが、Excelのブック（ワークブック）に該当する スプレッドシートファイルの呼び名です。
スプレッドシートが正しいんでしょうけど、これだと サービスとしてのスプレッドシートと混同することがあるんですよね。
で、ＱＡサイトでも 多いのが 「他のシートのデータを参照したい」が、同じスプレッドシート内の別シートを指しているのか、他のスプレッドシートファイルのことなのか不明瞭ってパターンです。
同じスプレッドシート内の別シート参照なら 関数は不要
「他のシートのデータを参照したい」が、同じスプレッドシート内の別シートを指すのであれば、 関数は不要です。
=シート1!A1
このように イコールの後ろにシート名とセルを !で繋げばよいですね。
日本語のシート名の場合は シングルクォート が必要なはず。でも、どこにつけるんだっけ？と気にする人がいますが、自動で補完してくれるので気にしなくていいです。
上のように入力してエンターで確定すると
='シート1'!A1
と変換されます。
ここで 別シートから取得できるデータは、セル範囲（いわゆる Rangeオブジェクト）です。
セル範囲であるということは、シートやシート内の位置などの情報を持っているってことです。
だから、OFFSET関数で 1つ下や1つ右のセルを取得することもできますし、ROWやCOLUMNで 行番号、列番号を取り出すことが可能です。
他のスプレッドシートファイルを参照する場合は IMPORTRANGE関数
「他のシートのデータを参照したい」が、他のスプレッドシートファイルを指すのであれば、IMPORTRANGE関数の出番です。
=importrange("スプレッドシートID","シート3!A1:A5")
スプレッドシートIDは 参照するスプレッドシートのURLの ↓ の部分です
https://docs.google.com/spreadsheets/d/スプレッドシートID/edit・・・
そのまま URLをぶち込んでも動きますが、IDだけの方が記述が短くなるのでおススメです。
このユニークな IDで参照するスプレッドシートを指定することで、
参照するデータがあるスプレッドシートを 別のフォルダに移す
参照するデータがあるスプレッドシートの名前を変える
このようなケースがあっても元ファイルを見失うことがありません。
Excelの別Excel 参照だと「あるある」ですよねｗ
IMPORTRANGEの 第2引数は、取得したい シート名とセル範囲です。
"シート3!A1:A5"
このように 文字列として指定する必要があります。
たまに誤解してシート名をセル参照させて INDIRECTを組み合わせようとする人がいますが、文字列のまま使えるので INDIRECTは必要ありません。
単体セルだけではなくセル範囲を指定可能ですが、飛び飛びの範囲や複数シートを1つのIMPORTRANGE関数で まとめて取得といったことは出来ません。
それぞれにIMPORTRANGE関数が必要となります。
そして、こちらは文字列指定なので 参照元のシート名を変更してしまうと、「シート3」が見つからなくなり エラーになります。
自動で連動して式内のシート名は変わりません。同様に 行や列を追加した場合も 相対的な動作はしません。
この点には注意が必要です。
マスターデータを触らせたくないけどユーザーに利用させたい、そんな時には超便利な IMPORTRANGE関数なんですが、動作が重い、更新にラグがあるなどの不満もあります。
他のIMPORT系（IMPORTDATA、IMPORTXMLなど）もそうですが、インターネット経由でデータを取得する関数である為、ネット状況によってはかなり動きが悪い時があります。
参照するデータ量が多かったり、importrange関数を大量に使っていたりすると、途中で固まることもあるので注意。
つまり、本来 ='シート1'!A1 で参照できる 同じスプレッドシート内の 別シートを IMPORTRANGEで取得するのは、ものすごく無駄に遠回りしていることになります。
東京駅から秋葉原まで、普通に 山手線 内回りなら 2駅なのに、外回りに乗って 品川、新宿、池袋を経由して 秋葉原まで 28駅かけて 行くようなものですｗ
IMPORTRANGE関数は 初回に「アクセス許可」が必要
IMPORTRANGE関数は 初回に「アクセス許可」を手動で 実施する必要があります。
「手動で」と書いたのは、この処理は GASでは出来ない為です。もしかすると抜け道的な方法があるのかもしれませんが・・・。
当然ですが、初回のアクセス許可は呼び出したい元データがある スプレッドシート（今回の場合は スプシ1）のアクセス権がある人しかボタンは押せません。
少し複雑なのですが、初回許可をする人は オーナーである必要はありません。（公式には オーナーがやる必要があるような記載になっていますが）
IMPORTRANGEのアクセス許可ボタンが押せる人
呼び出したいデータがあるスプレッドシート： 閲覧権限があればOK
IMPORTRANGEを使うスプレッドシート： 編集権限があればOK
一度許可してしまえば アクセス許可ボタンを押す人以外は、元データが入ったスプレッドシートへのアクセス権限がなくてもいいわけです。
元データの入ったスプレッドシート内の 1つのシートのデータだけ利用してもらいたいけど、他のシートは見せたくない。
こんな時はIMPORTRANGE関数を使うと良いですね。
ただし、推察されやすい シート名だと ユーザーが適当に打ち込んで 呼び出すことが出来ちゃうので注意です。なんちゃってセキュリティ程度だと思ってください。
許可はスプレッドシートファイル単位の許可となっているので、一度許可をすれば、その参照するスプレッドシート内の別のシートや別のセルを参照する際は許可は必要ありません。
また、IMPOTRANGEの初回許可をしたスプレッドシートファイルを ユーザーがコピーした場合、複製されたスプレッドシート側でも IMPORTRANGEの承認が必要となります。
つまり、元データのスプレッドシートへのアクセス権を渡していなければ、ユーザー側で勝手に別のスプレッドシートからIMPORTRANGEで不正にアクセスすることを防止できます。
例外的に元データのある参照したいスプレッドシートが 「リンクを知っているインターネット上の誰もが閲覧できます」という リンク共有設定になっている場合は、アクセス許可なしで importrangeが機能します。
こんなケースは滅多にないでしょうが。。
まれに、複雑な式の中で importrangeを使おうとすると、このアクセスを許可ボタンが 表示されないことがあります。
その際は、一度 別のセル にimportrange部分だけ 1セルだけ呼び出す記述にして、初回の許可を実施、こうすると式が動きます。許可ボタンを押せばこの式は不要なので消してしまってOKです。
IMPORTRANGEは スプレッドシート同士でしか使えない
自分がオーナーなのに、importrangeで参照しようとすると「アクセスする権限がありません。」って出るんですが・・・。
このような書き込みをたまに 見かけますが、原因は参照しようとしているのが Googleドライブにアップロードした Excelファイルというケースが多いです。
スプレッドシートの互換表示で開けていると、見た目はスプレッドシートですし、それっぽい IDもURLにつくので IMPORTRANGE出来そうに思えますが、残念ながらExcelに対して importrangeによる参照はできません。
見分け方は ファイル名の横の 緑の .XLSX というタグです。これが付いてるファイルは Excelファイルです。
逆に Googleドライブ内の Excel ファイル側から 他の スプレッドシートファイル をimportrangeで参照しようとすると、このように 「権限を追加してます」という表示で、ずーっとグルグルしたまま進みません。
Excelファイルは対象外ですといったエラーが出てくれると良いんですが、どちらも、なんか出来そうに見えちゃうんでタチが悪いですｗ
IMPORTRANGE関数を使うには、参照する側、される側、どちらも Googleスプレッドシートでないとダメってことです。
どうしても IMPORTRANGEを使いたい場合は Excelファイルを スプレッドシートとして保存してから使いましょう。
IMPORTRANGE は セル内画像はNG。結果は配列で返る
シート内や別シートの参照であれば、セル内画像も取得することができます。
しかし、別のスプレッドシートファイルから IMPORTRANGE関数を使ってセル内画像を取得することはできません。
「サポートしていません」とエラー表示がでます。これはどうにもならないので、諦めましょう。
そして IMPORTRANGE関数の結果は セル参照ではなく 配列データ を返す という点にも注意が必要です。
配列なので、当然 行番号や列番号といった シート内の位置情報をもっていませんし、OFFSETも使えません。
また、SUMIFやSUMIFS、MAXIFSといった一部の集計関数も 配列を扱えません。
importrangeを組み合わせた式で 「引数は範囲である必要があります。」とエラーメッセージが出たら、この関数は 配列だとダメなんだなと思いましょう。
こちらは 諦める必要はなく、別の関数で代用できます。
例えば、SUMIFやSUMIFSなら SUMPRODUCT、MAXIFSなら FILTERやQUERYで絞り込んでからMAXなどで対応できます。
どうしても無理だと思ったら、作業用シートに一度出力して、それを参照して計算するのも良いでしょう。
IMPORTRANGE関数 の基本 について理解できたでしょうか？
とりあえずは IMPORTRANGE関数 に関しては、
別のスプレッドシートを参照する時に使う
同じスプレッドシート内の別シート参照には使わない（無駄である）
初回に許可が必要（参照するスプレッドシートを開ける人が）
Googleドライブ内の Excelファイルには使えない
このポイントを押さえておきましょう。
QUERY関数 の列指定の基本
このIMPORTRANGEで相性がよく、組み合わせて利用されることが多いのが おなじみ QUERY関数です。
この理由については FILTE関数シリーズの初回で触れています。
今回発生した 不具合は、同じスプレッドシート内の 別シートを IMPORTRANGE経由で取得した時の挙動が
今まで： IMPOTRANGEが機能して 配列を返す
トラブル期間： IMPOTRANGEが無効になり セル範囲を返す
現在： 再びIMPOTRANGEが機能するように戻り 配列を返す
このようになった為です。この 結果が 配列か セル範囲かの影響を受けるのが QUERY関数だった為、QUERYが仕様変更！？と勘違いした人もいたのでしょう。
QUERY関数は対象のデータが、セル範囲か配列かによって 列指定の記述の仕方が変わる 特殊な関数です。
QUERY関数 セル範囲を対象とする時の 列指定
=QUERY('シート3'!A:B,"select A,B where A ='りんご'")
このように普通に別シートを参照した場合は、セル範囲が QUERYの対象データとなります。
この時 列を指定する記述は 列のアルファベット
A列は A
B列は B
と表記します。
列のアルファベットは、必ず大文字で記載する必要があります。
QUERY関数は 記述チェックが厳しいので注意。
わかりやすいですが、クエリの記述部分は 文字列なので 対象範囲を変えた際は 手動で 列指定の部分も修正が必要になります。
よくあるのが、 QA系サイトの質問で 元の表のセル位置がわからず A1セルから と思って回答したら、タイトルや余白があって B3 から表でしたというパターン。
=QUERY('シート3'!A:B,"select A,B where A ='りんご'")
↓
=QUERY('シート3'!B3:C,"select B,C where B ='りんご'")
このように対象データのセル位置が違うと、修正箇所が多くて面倒です。
これがもっと 列数が多くて複雑だったらと思うと恐ろしいですね。
QUERY関数 配列を対象とする時の 列指定
一方、IMPORTRANGEで取得した 配列 を対象とする場合、QUERY関数は Col1,Co2 … といった 指定になります。この列番号は 取得した配列データの中で、一番左を1としてそこから 右に 2,3… となっていきます。
配列の左から1番目 ・・・ Col1
配列の左から2番目 ・・・ Col2
配列の左から3番目 ・・・ Col3
=QUERY(IMPORTRANGE("スプレッドシートID","シート3!B3:C"),"select Col1,Col2 where Col1 ='りんご'")
セル範囲だと Bで指定していたものが Col1 と3文字になり 式が長くなるのが嫌って人もいますが、 mir は圧倒的にこっちの記述をおススメします。
極論を言えば、同じシートだろうがなんだろうが、範囲は配列化して、Col1,Col2 という記述に統一しちゃうのもアリです！
QUERY関数は Col指定の方がよい理由
とりあえず理由としては、以下の3つがあげられます。
■表の位置を気にせず 使える
■セル範囲 → 配列 は簡単にできる
■Col1,Col2 部分が長い場合は 関数で生成しやすい
あとは、QUERYに渡す前にデータを加工する必要があって、結果的に 配列として処理するケースの方が多いってのもあります。
■表の位置を気にせず 使える
セル範囲を対象とした QUERYの場合は、表のセル位置が違う時の修正箇所が非常に多かったですが、配列の場合は
=QUERY(IMPORTRANGE("スプレッドシートID","シート3!B3:C"),"select Col1,Col2 where Col1 ='りんご'")
↓
=QUERY(IMPORTRANGE("スプレッドシートID","シート3!E5:F"),"select Col1,Col2 where Col1 ='りんご'")
1ヶ所だけで select や where 句の部分は修正の必要がありません。要は式の汎用性が高いってことです。
■セル範囲 → 配列 は簡単にできる
2番目の理由です。
配列 → セル範囲 は 作業シートや 作業セルを使って 一度書き出すしか方法はありませんが、セル範囲 → 配列 は簡単にできます。
セル範囲
↓
{ セル範囲 } ・・・ これは配列
このように { } 中カッコで 括ってあげるだけで配列になります。
同じシート内、同じスプレッドシート内 でも
=QUERY({'シート3'!E5:F},"select Col1,Col2 where Col1 ='りんご'")
このようにするだけで、後ろの記述を Col1,Col2指定 とすることができます。
■Col1,Col2 部分が長い場合は 関数で生成しやすい
例えば A～Tまでの 20列のデータのうち QUERY関数で C列（3列目）だけは条件には使うけど出力しないという場合、
select A,B,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T
このような記述をする必要があります。残念ながら
select A,B,D:T とか select A,B,D-T
のような書きかたは出来ません。
じゃあ、 Col1,Col2 の書き方だともっと大変では？と思いますが、この部分は文字列なので 関数で生成をすることが出来ます。
"Col1,Col2,Col"&JOIN(",Col",SEQUENCE(17,1,4))
とか
"Col"&JOIN(",Col",FILTER(SEQUENCE(20),SEQUENCE(20)<>3))
とか
TEXTJOIN(",",true,ARRAYFORMULA(IF(SEQUENCE(20)=3,,"Col"&SEQUENCE(20))))
とすることで、
Col1,Col2,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20
このように文字列を生成できます。
↓ こうでもいいじゃん、って言う関数に強い人もいるかもしれませんが
TEXTJOIN(",",true,FILTER(CHAR(64+SEQUENCE(20)),SEQUENCE(20)<>3))
↓
A,B,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T
関数より 出力結果の方が文字数少ないって微妙ですよねｗ
mir的には わざわざ関数で生成するなら Col1,Col2 記述かなと。色々応用もききますしね。
好みなので記述の仕方は自由でいいと思いますが、mir的には 同じシート内の参照だろうと、同じスプレッドシート内の別シート参照であろうと、配列化して Col1,Col2指定する人が増えることを願っておりますｗ
まとめ
今回は、臨時ネタということで、 QUERY関数 と IMPORTRANGE関数の組み合わせで発生したトラブルの検証結果について書きました。
意外と同じスプレッドシートの別シート参照に IMPORTRANGE使っちゃってる人がいることもわかりました。
合わせて、IMPORTRANGE関数を使う上での注意点、そしてQUERY関数の 対象データが セル範囲か配列かによって列指定が変わることをまとめてみました。
次回こそは プルダウンネタをと思ったけど、前回の クロス表のネタから派生した Arrayformula と COUNTIFS の組み合わせ を書こうかなと思います。
この記事が気に入ったらサポートをしてみませんか？