こんにちは、6月にG-gen にJoinした又吉です。 今回は、 Google Workspace の全エディションにおいて無償で利用可能な Connected Sheets をご紹介します。
はじめに
Connected Sheets とは
Connected Sheets とは、Google Sheets (スプレッドシート) 上で BigQuery に保存されているデータの可視化・分析・共有ができる Google Sheets の拡張機能です。
Google Sheets にはシート当たり最大 1,000 万セルまたは 1 万 8,278 列 (列 ZZZ) までしか持てない制限があるため、本来は 1,000 万セルを超えるビックデータの分析には適していません。しかし BigQuery を使ってビックデータを分析するには、SQL の知識が必要になってしまいます。
そこで SQL を使わずエクセルライクにビックデータを分析 したい方や簡単な集計はスプレッドシート上で済ませたい方などにオススメなのが Connected Sheets です。
前提条件
Connected Sheets を利用するためには Google アカウントが必要です。
また、 BigQuery API が有効化された Google Cloud プロジェクトが必要になります。
料金
Connected Sheets 自体は無料です。
従来は Google Workspace の Enterprise エディションでのみ Connected Sheets が利用可能でしたが、2022年6月から個人用の無償 Gmail アカウントを含む全エディションで利用可能になりました。
Connected Sheets 自体は無料であるものの、BigQueryの料金は通常通り発生することにご留意ください。BigQueryの料金については、以下の Google Cloud 公式ページを参照ください。
アクセス制御
Connected Sheets で表示するデータは参照元の BigQuery データセットに存在するため、Connected Sheets に対して接続を開始したり、クエリを行う利用者の Google アカウントは BigQuery の対象データセットに対してアクセス権限を持っている必要があります。
またアクセス権の委任という選択肢もあります。これを行うと、最初に設定を行う人の Google アカウントの権限で BigQuery データセットに接続し、後から利用する人もその人の権限を利用して Connected Sheets を利用することができます。ただしアクセス権の委任機能は Enterprise / Education Standard / Education Plus / Enterprise Essentials / Enterprise Essentials Plus のいずれかのエディションでのみ利用可能です。
以下の Google Cloud 公式ページを参照ください。
セットアップ
今回扱うデータは BigQuery の一般公開データセットにある「アイオワ州の酒類購入情報」（アイオワ州の公式資料より）から取得したいと思います。
まずは、新規のスプレッドシートから、[データ]→[データコネクタ]→[BigQueryに接続]をします。
接続先の情報を入力します。
[対象のGoogle Cloudブロジェクト]→[公開データセット]→
[iowa_liquor_sales]→[sales]→[接続]
今回接続したデータは2,399万行24列(約5.8億セル)のビックデータになりますが、ほんの10秒程度で一部データがプレビュー表示されました。
操作方法
概要
スプレッドシートでデータを分析するように、列データをもとに昇順/降順での並び替え、条件でフィルタをかけたりすることができます。
また、赤枠にある各種項目でグラフを作ったり、ピボットテーブルでデータを形成したり、より柔軟な分析ができます。
一つずつ簡潔に説明していきます。
グラフ
グラフを用いるとデータを容易に可視化できます。グラフの種類も折れ線、縦棒、円、散布図、地図などから選択できます。
以下は都市別の売上をグラフ化してます。
ピボットテーブル
ピボットテーブルは柔軟に分析ができるので、中でもよく使う分析項目かと思います。
こちらの例では月別の売上を表示させてます。右側のエディタから表示させたい行、列、値を選択し、グループ化やフィルタなどの条件もスプレッドシートの操作感で行えます。
実際に裏ではこのような感じのSQLに変換され、BigQuery 側で処理されています。Connected Sheets を用いることで、SQL を打たずにビックデータの分析ができるのは魅力的ですよね。
関数
関数はスプレッドシートで普段使うような集計関数などが利用できます。
データの抽出
データの抽出は、BigQuery からデータをスプレッドシート上に取り出しておける機能です。最大 50,000 行または 10 MB の BigQuery データを抽出できます。
Connected Sheets のデータをグラフ化したり、あるいはスプレッドシート関数で Connected Sheets のデータを参照すると都度 BigQuery にクエリが発行されてしまいます。参照先テーブルが大規模だと、クエリに時間がかかったり、料金の増大に繋がります。
データの抽出機能を使ってシートにデータを取り出しておけば Google Sheets の世界の中でデータの扱いが完結するので、パフォーマンス向上や料金節約に繋がります。
計算された列
計算された列は、任意の列に対し関数を組み込んで新しい列を作成できる機能です。
以下は、sale_dollars列に対し130を掛けて、japanise_enという新しい列を作成しました。
列の統計情報
列の統計情報は、任意の列情報を表示してくれます。
以下は、city列の統計情報を表示してます。一意の値がどれくらいあるのか、出現回数が最も多いのは何なのかなど、ぱっと調べたい時に有用ですね。
データの更新
BigQuery データと Connected Sheets の同期は 手動更新 または 自動更新 から選択できます。
手動更新は Connected Sheets の画面左下から選択できます。
また、自動更新の単位は1時間・1日・1週間・1ヶ月から選ぶことができます。
自動更新を設定するには、Connected Sheets 上部の更新オプションを押下します。
その後、画面右側の更新スケジュールより、「繰り返す間隔」「開始日」を選択し保存します。
注意点としては、更新するたびに裏で BigQuery に対してクエリが投入されるので、更新頻度が高いとその分、クエリ料金が高くなる可能性があります。
必要最小限の頻度でのデータ更新をオススメします。
実践
G-gen 社内で実際に Connected Sheets を使って分析を行っている事例は、以下の記事で紹介されています。ぜひ、ご参照ください。
又吉 佑樹(記事一覧)
クラウドソリューション部
はいさい、沖縄出身のクラウドエンジニア！
セールスからエンジニアへ転身。Google Cloud 全 11 資格保有。Google Cloud Champion Innovator (AI/ML)。Google Cloud Partner Top Engineer 2024。Google Cloud 公式ユーザー会 Jagu'e'r でエバンジェリストとして活動中。好きな分野は AI/ML。
Follow @matayuuuu