asoview! Advent Calendar 2019 の 3 日目の記事です。
サーバサイドエンジニア/開発マネージャのタケウチです。
最近はビジネスチームと連携する役割が増えてきたのですがデータのやりとりが必要なときは Google Spreadsheet を使っています。いつのまにかいろんなことができるようになっていて改めて便利だなーと思ったりします。 いまさらすぎて涙が出そうですが、よく使っている関数や機能(スクリプト除く)を書いていきたいと思います。
Google 翻訳
いつもお世話になっている GOOGLETRANSLATE 関数です。DETECTLANGUAGE 関数で言語識別もできます。
画像表示
IMAGE 関数でセルに画像を表示できます。
IMPORTRANGE 関数
IMPORTRANGE 関数は他のスプレッドシートの特定範囲をインポートします。スプレッドシートを他のチームと共有するとどんどんシートが増えていくのでこれをうまく使うとシートを増やさずに済むかもしれません。ただし、参照位置を文字列で指定するため行・列変更には弱いです。
他にも IMPORTXML, IMPORTHTML, IMPORTFEED, IMPORTDATA というものがあるのでもう大体のフォーマットのデータはスプレッドシートに取り込めそうです。
範囲の結合
={A1:B2;C1:C2} といったように複数の範囲を ; で区切って {} で囲うと範囲を結合できます。いろんなシートにデータが散らばっている場合などに使えます。範囲の代わりに IMPORTRANGE でも同様にできます。
QUERY 関数
QUERY はスプレッドシートの範囲を "Google Visualization API のクエリ言語" という SQL っぽい言語でデータを加工したりフィルタなどできます。 また、 group by や pivot で集計もできるので非常に便利です。
例えば以下の 2 シートがあったとします。
where でフィルタはこんな感じ。列指定もできます。
group by で集計はこんな感じ。 集合関数 が使えて label で列名を指定できます。
pivot はこんな感じ。列展開されます。
ドキュメントには他にもいろいろ書いてあります。
ARRAYFORMULA
ARRAYFORMULA 関数は配列（範囲）に対応していない関数で配列を使うことができます。
例えば、最高評価が S だったとき Legend と表示したい場合、 E2 に =ARRAYFORMULA(IF(D2:D13="S", "Legend", "")) と書くとその下の行も自動的に表示されます。
