スプレッドシートQuery関数番外編 〜Query関数のエラー原因ランキング〜

クエリイメージスプレッドシート
この記事は約8分で読めます。

さて、結構前にはなってしまいますが、スプレッドシートQuery関数の解説シリーズ記事を書いていました。その記事が案外リファレンスされており、結果HPへの流入もいただいておりありがたい限りです。

今回は、そんなシリーズ記事を書いていて、読者さんからいくつか質問を受けた内容などをエラーパターンとして注意点を分類して、解説していこうかなと思います。このブログなぜかchatbotで問い合わせ受け付けるようにしているのですよ、サービス提供でもないblogとしてはレアな存在だとは思いますが汗

スポンサーリンク
もしも_楽天

よくあるQueryエラーのランキング

これまでに受けたQueryエラーに関する原因を分類したランキングを紹介します。

Top5は以下になります。

  1. 抽出参照対象列選択誤り(非表示・select対象外 etc)
  2. 大文字・小文字間違い
  3. セル参照方法誤り(テキスト or 数値)
  4. Colの利用漏れ
  5. データの上書きなどできない機能を利用しようとしている

これだけ見ると、自分はやらないよって人も多いかと思いますが、一回沼にハマると実は自分ではエラー原因を探すのって大変ですよね。質問されてきた方も別に日常であれば気づけていたものも多いと思います。言葉にするとケアレスミスなんですが、あってるはずと自己認識してしまうと、結構盲点になります。

実はケアレスミスが最も多いエラーの要因

要因を一つづつ潰していく癖をつけておくといいかもしれません。query関数もプログラミングと同様、1つスペルミスをするだけで、当然動作しないので。

注:問い合わせを受けた質問のランキングでしかない(このサイトを見るユーザ層でのカテゴライズがされている)ので、汎用的にこのエラー要因ランキングがquery関数ユーザの全体を表しているかは正直不明ですのでご容赦ください。

Query関数エラー要因ランキング項目解説

それでは、実際に1つづつよく見かけたエラー要因の解説をしていきます。ランキング5位から最もよく聞かれる1位に向けてカウントダウンしていきましょう。

第5位 データの上書きなどできない機能を利用しようとしている

さて、これはそもそもSQLを日頃使っている方が陥りやすいエラーポイントなのかもしれませんが、たまに問い合わせできますね。通常のSQLだったらやれるはずなのに・・・は、正直自分も思うことあるので、気持ちもわかります。クエリ関数はSQLのDML(Data Manipulation Language)の一部だけ利用可能です。DDLや DCLはそもそも対象外です。

このタイプのエラーを起こしてしまう場合、Query関数が何を元に動いているのかを理解するのがいいと思います。細かく全ては説明できませんが、以下の理解をしてもらうといいと思います。

・クエリ関数はGoogle Visualization APIのクエリ言語を利用している

・Visualization APIは基本SQLに似ているが、違いもあって、詳細はsyntaxセクションの確認が必要

Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します

https://support.google.com/docs/answer/3093343?hl=ja

The syntax of the query language is similar to SQL. Developers familiar with SQL should be able to quickly learn and use this query language. There are many SQL tutorials available on the Web. There are some differences between this query language and SQL which are described in the syntax section.

https://developers.google.com/chart/interactive/docs/querylanguage

【Syntaxセクションに規定されているクエリ句】

ClauseUsage
selectSelects which columns to return, and in what order. If omitted, all of the table’s columns are returned, in their default order.
whereReturns only rows that match a condition. If omitted, all rows are returned.
group byAggregates values across rows.
pivotTransforms distinct values in columns into new columns.
order bySorts rows by values in columns.
limitLimits the number of returned rows.
offsetSkips a given number of first rows.
labelSets column labels.
formatFormats the values in certain columns using given formatting patterns.
optionsSets additional options.
fromThe from clause has been eliminated from the language.

また、関連する記事がこちらになるので、興味あれば軽くみてもらえるといいかと。

第4位 Colの利用漏れ

第4位は、Colを利用し忘れるケース、または使わなくていいのにColを使ってしまうケースです。

Colはimportrangeのように、データ対象が当該スプレッドシート外の部分から参照される場合のみに必要になります。

よく間違えて質問されるエラーケースに、同一スプレッドシート内の別のシートのデータを参照する場合にColを利用してしまっているケースがあります。別シートであっても、同一のスプレッドシートであれば、Colを使う必要はなく、直接カラムを指定すればOKです。

関連する記事は以下になります。

第3位 セル参照方法誤り(テキスト or 数値)

第3位は、セル参照方式を利用する場合で、文字列か数値かで参照の記入方法が少し違うことによるエラーです。ここら辺から、本当によくエラーがなおらないのですが・・・という質問を受けますね。

まずは、おさらいで、基本的な参照方法を思い出していきましょう。

文字列参照したい場合

文字列としてデータを参照したい場合は、以下のようにクエリを作成します。

=QUERY('sample'!A1:H7,"where D='"&A1&"'")

【’”&セル番地&”‘】となっていますが、これは“条件式の1部” & セル & “条件式の続き”という構成だと思ってもらえればOKです。

数値参照したい場合

次にセルに数値が入っていて、その数値自体を参照したい場合です。

=QUERY('sample'!A1:H7,"where D="&A1&"")

【”&セル番地&”】となっていますが、これは最初の”でQueryの条件式を一回コメントアウトして、A1セルをつなげて、さらに”でクエリ句をつなげる感じだと思ってもらえればOKです。

第2位 大文字・小文字の選択間違い

さて、第2位ですが、実は想像以上に多かったのが、大文字・小文字問題です。えぇ結構びっくりしてますが、案外質問されるものですね。

主にはwhere句内のカラム選択においてなのですが、カラムAを選択したい場合に、where a=…としてしまうようなケース。また、Colをcolとするケースなどが見受けられました。

これは正直私のブログもよくないのですが、selectやgroup byなどのsyntax指定部分については、小文字でも動くので、小文字で書いてしまっています。ただ、本来SQLではここも大文字で書くのが推奨されていたはずで、とりあえず大文字で書く。そして、ColだけはColと書く、と理解してもらうと早いかと思います。

第1位 抽出参照対象列選択誤り(非表示・select対象外 etc)

映えある??第1位は、抽出参照対象列選択誤りです。

具体的には何かというと、以下のようなパターン。

  • データ範囲で指定されていない、セルを後からwhere句に要素追加した際に、指定してしまった
  • queryで表示されたシートを見てセルを判断してしまい、元データとカラムがずれてしまった(select *ではなく、selectでいくつかカラムをピックアップしていた場合)
  • Colを数える際に、元データに非表示列があったことに、気づかず表示されたカラムのみを数えてしまった

そんなことやらないよぉって思うかもしれませんが、案外多いんですよ。そして、データ範囲指定外のセル参照してしまっているケースとかは、実は案外自分で気づけなかったりするようです。

まとめ

今回は、【スプレッドシートQuery関数番外編 〜Query関数のエラー原因ランキング〜】として、このブログのチャット問い合わせによくくるqueryエラーの質問・原因別ランキングを紹介しました。

どれも、別に自分ならやらないなって思うようなケアレスミスではありますが、いざハマるとなかなか沼から抜け出せないようです。そんな時は初心に戻ってエラーしそうな箇所を真っ白な頭で再度考えてみる、もしくは、それこそゼロからクエリ関数を書き直してみるのが早いのかもしれませんね。

少しでも皆さんのクエリエラー撲滅にお役に立てれば嬉しいです。では、また。

連載目次: spreadsheet_query関数を使いこなそう

SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。

  1. スプレッドシートQuery関数を使ってみよう ①概要と目的
  2. スプレッドシートQuery関数を使ってみよう ②クエリ関数の構成
  3. スプレッドシートQuery関数を使ってみよう ③SELECT句
  4. スプレッドシートQuery関数を使ってみよう ④WHERET句
  5. スプレッドシートQuery関数を使ってみよう ⑤GROUP BYを使ってみる
  6. スプレッドシートQuery関数を使ってみよう ⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
  7. スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
  8. スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
  9. スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策
  10. スプレッドシートQuery関数番外編 〜Query関数のエラー原因ランキング〜
タイトルとURLをコピーしました