はてブ02019.12.06この記事は約6分で読めます。withが助けてくれる←今回クエリ関数でよく起きる想定外動作の例さてこれまでクエリ関数の使い方をみてきました。BYとか色々勉強しました。サブクエリやデータ結合系の処理もありました。忘れてる機能がある方は是非過去回を見直してみてください。今回は少し毛色が異なるのですが、実際実務で当たっていると起きるクエリ関数がイメージした挙動をしない場合とその対応策です。結論ほとんどのイメージ外の挙動はデータ構成が悪影響して、データがブランクになるという物です。起きる例を3つほどみていきましょう。あるエラーリストデータに改行が入っていてうまく検索されないカラムデータが1つだけ別物になっていてうまく検索されないセルが結合されているデータに改行が入っている場合とその対応策さて、こんなデータがあったとします。TOMのデータが探したいなとなったとします。Query関数が扱いにくいデータさて、TOMのデータが探したいなとなったとします。名前でTOMを検索すれば出てくるはずですよね。‘TOM'”)の結果なぜでしょう?一旦原因を確認するには、全て表示してみるのが早いです。*で検索ちょっと待ってください、TOMのデータみてみると、名前のカラム改行されてaaaが入っていますね。'TOM%'")実はこれをやっても何も返ってきません。問題は一致かLikeかというところではなく、改行が含まれているからなのです。with改行が含まれている場合、改行をCHARで記号してって方法もあるのですが、もっと簡単にできるのが、containsです。TOMが含まれていれば検索されます。containsで検索した場合は、改行が含まれていても、containsで指定したデータが含まれていれば対応可能です。with、名前がTOMで始まっているということが確定している場合、でも対応可能です。withなどを使う場合の留意点containsを使う場合の留意点としては、名前がA”TOM”だとか、”TOM”THONみたいな人間もデータに含まれている場合、抽出してきてしまいます。contains(含む)は便利ですが、当然キレイに一致しないものも入ってくることにご留意ください。withでは含まれてきませんが、”TOM”THONは含まれてきてしまいます。withを使う場合は、データにスペシャルマーク(★とか!みたいな記号)が入ってデータを識別できる場合がいいですね。そもそもそんなデータを作らずに、ステータスごとにカラムを分けて特別なフラグを立てることをお勧めします。汚いデータはquery関数には敵です。カラムデータが1つだけ別物になっていてうまく検索されないさて先ほどと同じデータでまたみていきます。今度は年齢で検索しようとしてみます。Benの年齢はいくつだったかなぁ。'Ben'")あれ、Benの年齢がブランクだ。オリジナルをみてみると、実はBenのデータってaaaとなっていて、ageカラムは他は全て数値なのに、Benだけ文字列が入ってきてしまっていますね。aaaって表示されるんじゃないかと思われた方もいるかと思います。これがquery関数の仕様で以下のようなものがあります。cells.複数のデータタイプが混ざっている場合、少数のデータタイプがブランクセルになるというものです。バグかな?って思ってしまうような仕様ですが、現状の仕様です。今回のケースもBenの年齢だけが文字で、他は数値だったので、Benの年齢がBlankになってしまっていました。複数データタイプが含まれている場合の対応策としては、苦肉の策なのですが全て文字列かすると。関連articleはこのstackoverflowの記事になります。めんどくさそうですね。Overflowstackoverflow.comなので、実際はそんなことをするより、データに入力規則を先に入れておいて、数値以外のデータを入力できないようにするのがベターでしょう。数値もテキストも日付も選べますので、query関数で対応したいものと基本的に連動します。セルが結合されている厄介ですが、よくやられますよね。セルを結合するとどうなるかというと、先頭セル以外のデータはブランクとなってきます。これはGASでも同じ仕様ですよね。実はこれに対応する方法は、パッといいものがありません。