Google スプレッドシートでハマった３つの落とし穴
Ubie Discoveryで事業開発をしているKennyです。
Google スプレッドシートみなさん使っていますか？表計算アプリケーションとしてだけではなく、Google フォームのデータをスプレッドシートに連携し簡易的なお問い合わせ管理に使っている企業も多いのではないでしょうか。
Ubieでもスプレッドシートは活用していますが、活用している中でハマった落とし穴がありますので共有できればと思います。
落とし穴(1)：セル数に上限がある
便利なスプレッドシートですが、実は保存可能なセル数に上限があります。公式のヘルプには、以下のようにスプレッドシートで利用可能なセル数の上限について記載されています。
Google スプレッドシートで作成したスプレッドシートまたは Google スプレッドシート形式に変換したスプレッドシートは 500 万セルまたは 18,278 列（列 ZZZ）まで。
※この記事の執筆時（2022年1月現在）では、1000万セルでエラーになりました。過去にも200万セルから500万セルへ拡張されたことがあるので、今後も増えてくれるかもしれませんね。
新規スプレッドシートを立ち上げると、作成されたシートはデフォルトでAからZ列まで26列あるので、1行で26セル使っていることになります。そのため、384,615行までは行追加できますが、それ以上追加しようとするとエラーが表示されます。
この操作を行うと、ワークブック内のセル数が 10000000 の制限を超えてしまいます。
ここで注意しないといけないことが２つあります。
何も文字が入っていない空セルもカウントされる
セル数上限は1つのシートではなくすべてのシートの合算
チームで共有しているファイルの場合には知らず知らずにシートが増えていることがありますよね。「誰かが使っているかも」と思うと削除もできず増えていき、知らぬうちにセル数を消費していっています。
ちなみに、Google フォームの回答が入るスプレッドシートがセル上限だった場合にどうなるかというと、データが入ってきません。
フォームはエラーにならず、スプレッドシートにもエラー表示は出ませんので見落としてしまう可能性が高く、注意が必要です。
不要なセルを削除すると、入れられなかったデータが急になだれ込んで入ってきますが、この状態でいつまでデータが担保されるのかは不明です。
お問い合わせフォームなどでGoogle フォームを使われている方、一度チェックしてみてはいかがでしょうか。
■対応策
不要なセル、シートは削除する
コレに尽きます。特にシートを作成するとZ列までデフォルトで作られるので、そこまで列数が必要ないシートでは列を削除することも有効です。
落とし穴(2)：vlookupは大文字小文字の区別をしない
vlookup、めちゃくちゃ使いますよね。活用している人は多いと思います。
ところがvlookupは大文字小文字の区別をしてくれないため、誤ったデータを参照してしまうことがあるので注意が必要です。
実際に試してみます。
下の画像のように、A列に大文字・小文字のデータを用意しておき、D列の検索キーでvlookupをしてみると、結果は意図していないデータが取れていることがわかります。
なお、「vlookup 大文字小文字 区別しない問題」はGoogle スプレッドシートだけではなく、Microsoft Excelでも同じ挙動をしてしまいます。
筆者はSalesforceのデータを扱っているときにこの落とし穴にハマりました。
Salesforceでは商談IDやリードIDなどIDが自動で作成されますが、SalesforceではIDの大文字小文字で別のデータとして扱われます。
Salesforceから取り出したデータをIDでvlookupすると、意図しないデータが取れてしまう可能性があるので注意してください。
■対応策
index と match関数を使う
以下のように、２つの関数を組み合わせることで大文字小文字を区別して取り出す事ができます。
=INDEX(拾いたいデータ列,MATCH(TRUE,EXACT(検索キーのID,マッチングしたいID),0))実際に、さきほどのデータで試してみます。
狙い通り大文字小文字が区別されてデータが取り出せていることがわかります。
落とし穴(3)：Query関数で文字列と数字が混在する列はデータが空になることがある
Query関数は、スプレッドシート内のデータをSQL風に記載して取り出す事ができて非常に便利な関数です。
IMPORTRANGE関数と組み合わせれば、他のスプレッドシートファイルにあるデータの特定の値だけに絞って取り出したり、並び順を変えて表示したり色々なことが出来ます。
ところが、存在するはずのデータが何故か空白になってしまうという落とし穴にはまりました。原因がわからなすぎて、かなり苦しみました。
実際に再現させてみます。
以下の表のようなデータを用意します。
ここで、B列は数値形式のデータ（123と456）と文字列のデータ(b1111）が混在しており、数値形式のデータの方が数が多い状態になっています。
この表をQuery関数を使ってシンプルにE〜G列に出力すると、文字列のデータが消えていることがわかります。
理由がさっぱりわからず色々と調べていると、公式ドキュメントに原因が記載されていました。
1 つの列に異なる種類のデータが含まれている場合は、その列に大多数含まれる種類のデータをクエリに使用します。小数の種類のデータは NULL 値とみなされます。
文字列と数値形式が混在しており、数値形式の方が多いと文字列のデータを空白に置き換えてしまうとのこと。なんでそんな事に・・・
■対応策
TO_TEXT()で文字列化させたデータをQuery関数にわたす
さきほどの表のQuery関数を以下のように変更してみます。
=QUERY(ARRAYFORMULA(TO_TEXT(A:C)),,1)
TO_TEXT関数でデータを文字列化させ、ARRAYFORMULAでA〜C列全体に配列展開させます。これをQuery関数に渡すことで、数値形式と文字列形式が混在した場合でも取れるようになります。
ただ、いくつか注意する点もあります。
注意1）クエリ文字列で列名表現が使えなくなる
TO_TEXTで文字列化したデータの場合、クエリ文字列で"A","B"などの列名表現が使えなくなります。
これを回避するためには、A、Bなどの列名表現をCol1、Col2という表現に置き換えることで取り出せます。
注意2）where句で比較できなくなることがある
たとえば以下のように、「特定の日付以前のデータ」だけに絞るようなwhere句が設定されていたとします。データは絞れていますが、「文字列と数値形式混在問題」のせいでG2のデータが取れていませんね。
混在問題を解決するため、全体をTO_TEXTさせてQueryを実行します。（列名も「Col」表現に変えています）
残念ながらデータは取り出せていません。日付も文字列化されたために比較ができなくなっています。
「文字列と数値形式混在問題」があったのはB列だけだったので、以下の通りB列だけTO_TEXT関数に渡し、それ以外はそのままにします。"{"、"}"で括り、列をカンマでつなげてQuery関数の第一引数に渡します。
=QUERY({A:A,ARRAYFORMULA(TO_TEXT(B:B)),C:D},"select * where Col4 <= date '2022-01-06'",1)
日付での絞り込みをしつつ、混在問題も解決したデータが取り出せました！
まとめ
スプレッドシートはチームで共同編集したりが簡単にできて、Google Apps Scriptも使えば色々なことができて非常に便利なアプリケーションです。ですが意外なところに落とし穴があったりするものなので注意してください！
追記
スプレッドシートの小技を記事化しました
Vlookupで複数列を一気に取得する方法
列が追加されてもVlookupが壊れないようにする簡単な方法