こんにちは、中都です。
BigQueryにあるデータを、使い慣れたスプレッドシートで分析したい。そんなときは、コネクテッド シートを使えば、SQLを書かずに集計表を作り、毎朝の更新まで設定できます。今回は、接続からグラフ・ピボットテーブル、売上予測まで、実際の画面を使って説明します。
動画でも解説しています
スプレッドシートが重い、更新が面倒。こんな悩みはありませんか？
CSVのローデータをそのまま貼って管理していると、データが増えるほどシートが重くなります。月ごとにファイルを分けると、今度は過去の数字や変更履歴を追いにくくなるんですよね。
僕が見てきた現場でも、重くなるから毎月シートを作り直したり、誰かが元データを触って壊してしまったりすることがありました。別ファイルからIMPORTRANGEで集める方法もありますが、参照が増えると管理が大変になります。
この記事は、とくに次のような方に読んでほしいです。
- スプレッドシートへCSVを貼り付け、毎朝手作業で数字を更新している方
- BigQueryはエンジニアが使うもので、自分には難しそうだと感じている方
- BigQueryにデータはあるのに、日々の集計や報告にうまく使えていない方
紹介するのは、ローデータはBigQueryに保管し、集計や分析はスプレッドシートで行う方法です。この記事では、次の状態を作っていきます。
| 今、困っていること | この記事でできるようになること |
|---|---|
| 生データを貼り続けると、表が重くなる | 必要なデータをBigQueryで集計し、日別売上や商品別の表として見る |
| SQLを書けず、BigQueryのデータを使えていない | メニュー操作でグラフ・ピボットテーブルを作る |
| 朝のたびにデータを貼り直している | 取り込みの完了時刻に合わせ、集計表の更新をスケジュールする |
後半では、列の統計情報や売上予測も試してみます。BigQueryへデータを取り込む設定は済んでいる前提で、そこから先の使い方を説明します。
元データの保管と、日々の集計を分ける
コネクテッド シートがBigQueryへ集計を依頼し、その結果をシートに表示します。グラフやピボットテーブルの操作は、いつものスプレッドシートに近い感覚で進められます。
BigQueryでクエリの「結果を保存」からスプレッドシートへ書き出す方法もあります。ただし、一度書き出した表が、その後のBigQueryの変更に合わせて自動更新されるわけではありません。継続して使う表には、接続と更新の設定が必要です。
接続した元データは、通常のセルのように上書きできません。編集できないと聞くと制約のようですが、僕はむしろメリットだと思っています。分析する人の操作でBigQueryの元データを書き換えずに済むからです。ただし、取り込み元の誤りまで自動で直るわけではありません。データを整える役割と、数字を見る役割を分けておきましょう。
接続前に用意するもの
- パソコンで開いたGoogleスプレッドシートと、そのファイルの編集権限
- Google CloudとBigQueryを利用できるGoogleアカウント
- 請求先を設定したBigQueryプロジェクトと、読み取りたいテーブルまたはビュー
- 対象データを読み、クエリを実行できるBigQueryの権限
今回は注文日、商品SKU、数量、売上が入ったデモ用の注文データを使います。あなたの環境では、プロジェクト・データセット・テーブル名と各列の意味を管理者に確認してください。データがまだBigQueryへ入っていない場合は、その取り込み設定が先です。
また、SQLを自分で書かなくても、集計や更新の際にはBigQueryのクエリが実行されます。処理量などに応じた料金がかかるため、常に無料で使えるとは限りません。詳しくはGoogle公式の利用要件とBigQueryの料金を確認してください。
1. スプレッドシートをBigQueryへ接続する
接続メニューを開く
空のスプレッドシートを開き、上部の「データ」→「データコネクタ」→「BigQueryに接続」を選びます。Google Cloudのプロジェクトを選ぶ画面が開けば、次へ進めます。
プロジェクト・データセット・テーブルを選ぶ
対象のプロジェクトを選択し、続いてデータセット、テーブルの順に選びます。ここで選ぶのは、これから集計したいデータが入っているテーブルです。名前が似ている別の環境を選ばないよう、列名も照合してから「接続」を押します。
接続完了の案内が出たら「使用する」を押します。画面の表記によっては「分析を開始」です。データベースのアイコンが付いたタブに列名とデータが表示されれば接続完了です。ここは接続先のプレビューなので、行を削除したり、元の値を直接書き換えたりするための表ではありません。
2. グラフとピボットテーブルで集計する
日別売上を折れ線グラフにする
接続したデータのタブで、上部の「グラフ」を押します。配置先に「新しいシート」を選んで作成し、右側のグラフエディタで次のように設定します。
- グラフの種類：折れ線グラフ
- X軸：注文日の列。日単位で見たい場合は年月日でグループ化
- 系列：売上の列。日別売上にする場合は合計で集計
設定を変えただけでは表示に反映されないことがあります。「適用」を押し、日付ごとの売上がグラフになったことまで確認してください。以後、最新のデータへ切り替えるときは更新操作を行います。
月別・商品別の数量と売上を表にする
元の接続タブへ戻り、「ピボットテーブル」を押して新しいシートに作成します。右側のエディタで「行」に注文日とSKUを追加し、注文日は年月でグループ化します。「値」には数量と売上を入れ、それぞれ合計にします。
ここは「列」と「値」を間違えやすいところです。商品の売れた個数と金額を集計したいので、数量と売上は「値」に入れます。「適用」を押し、月ごと・SKUごとの数量と売上が出ていることを確認しましょう。
ローデータをシートへ全部貼らなくても、このように必要な単位の表を作れます。表ができたら、元データの一部と合計を照合してください。注文の取消・返品・税込／税抜の扱いなど、同じ「売上」でも定義が違うと比較できません。
この表を使って「先月より売れた商品はどれ？」まで調べたい方には、記事の最後で集計表をAIへつなぎ、普段の言葉で質問する方法も紹介します。まずは、毎朝の数字が揃うところまで設定していきましょう。
平均値だけ知りたいときは関数を使う
一つの数値を取り出したいときは、接続タブの「関数」から使う関数を選びます。実演では「AVERAGE」を選び、新しいシートで数量の列を参照しています。必要な列を指定し、変更を適用すると、その列の平均が表示されます。
この場合に分かるのは、選んだ数量列の1行あたりの平均です。1行が注文なのか注文明細なのかによって意味が変わるので、そのまま「顧客1人あたりの購入数」などと言い換えないようにしてください。
3. 明細が必要なときだけ抽出する
通常のセル範囲として明細を使いたい場合は、接続タブの「抽出」を押して新しいシートを作ります。右側の抽出データエディタで、必要な列、フィルタ、並び順、行数制限を指定して「適用」を押してください。選んだ明細がシートに並べば完了です。
実演のデモデータは約1,300行ですが、自社データで最初から全件を抽出する必要はありません。「直近の期間」「確認したい商品」「必要な列」に絞ります。大量の行をシートへ戻すと、せっかくBigQueryへ分けたのに、再び表が重くなる原因になります。
4. 統計情報や売上予測も、スプレッドシートから試せる
平均・中央値・合計をまとめて見る
接続したデータのタブで「列の統計情報」を開き、確認したい売上の列を選びます。平均、中央値、合計、最小値、最大値や分布をまとめて確認できます。
今回のデモデータでは、売上列の1行あたりの平均が約2,443円、中央値が1,980円になりました。一つずつ関数を組まなくても、列の特徴をつかめます。これは顧客1人あたりの売上ではなく、選択した列の統計値です。
「高度なインサイト」から予測を作る
続いて「高度なインサイト」（画面によっては「高度な分析」）から「予測を作成」を選びます。僕も普段は使ったことがなかったので、「どんなことができるんだろう」と、ちょっとワクワクしながら試しました。
時系列に注文日の列、予測する列に売上額を選びます。実演ではそれぞれ order_date_jst と total_price_jpy を選択し、日単位・合計・10日先までの予測に設定しました。自分の表の列と期間に置き換え、「作成」を押します。
すると、日付ごとの予測値と信頼区間の上限・下限が新しいシートに出て、グラフでも見られました。実演の表では予測ステータスが「SUCCESS」になっています。
どれぐらい正確かまでは、まだ分からないのですが、こういう機能で在庫や売上を予測して、キャッシュフローの改善につなげられたら面白いですよね。
「こんなのができました」と見せられたら、上司からめちゃめちゃ評価されますよね。
予測が表示できたことと、予測が当たることは別です。まず実績と同じ日付・集計条件で照合してから、判断材料に使ってください。予測機能では追加のGoogle Cloud権限が必要になる場合があります。設定項目はGoogle公式の予測機能の説明でも確認できます。
5. 毎朝の自動更新を設定する
接続しただけではリアルタイムに同期されません。まず接続タブの下部にある更新のメニューから「更新オプション」を開き、「すべて更新」を押します。作成した表やグラフが更新され、エラーがないことを確かめてください。
グラフもピボットテーブルも、このボタンでまとめて更新できます。一つずつ更新しなくていいので、めちゃめちゃ便利です。
続いて、更新オプションの「更新スケジュール」を開きます。現行ヘルプでは「更新を設定」→「今すぐ設定」と案内される場合もあります。毎朝見るなら更新間隔を1日単位にし、開始日と時間帯を選んで保存します。
たとえば始業が9時なら、それより前の7〜8時台を候補にできます。ただし、BigQuery側へ前日データが到着した後に更新することが前提です。取り込みが終わる前に更新すると、処理自体は成功していても前日の数字が欠けます。注文日のタイムゾーンも含めて、取り込み側の担当者と時間を合わせておきましょう。
保存したら、更新スケジュールに選んだ条件が残っていることを確認します。そして最初の定期更新後に、更新日時とデータの対象日を確認してください。「設定を保存できた」と「翌朝に必要なデータが揃った」は、分けて確かめる必要があります。
更新の操作・制限はGoogle公式のコネクテッド シートの更新手順でも確認できます。
自動更新が止まったときに見るところ
- 更新する人の権限：定期更新は設定したユーザーとして動きます。担当変更時はBigQueryの権限と、スケジュールの引き継ぎを確認します。
- データソースの変更：別のユーザーが既存のデータソースを追加・更新すると、スケジュールは自動停止します。更新設定を開き、オーナーが再開するか、担当者が引き継ぎます。
- 表やグラフの状態：プレビュー中やエラー状態のオブジェクトは、定期更新の対象になりません。手動で適用・更新して原因を確認します。
- 元データの到着：更新日時だけ新しく、対象日の行がない場合は、BigQueryへの取り込みを確認します。
まとめ：スプレッドシートを活かすために、データの保管先を分ける
グラフ、商品別の集計、統計情報、売上予測、毎朝の更新。今回は、SQLを書くことなく、ここまでデータを使えました。
- 大量のローデータはBigQueryに置き、必要な集計をスプレッドシートで作る。
- 元データを直接編集できないことは、誤操作からデータを守るメリットになる。
- 毎朝の更新は、BigQueryへ前日データが届く時間に合わせて設定する。
僕は、BigQueryってスプレッドシートをもっと強く、安全に使うための土台なんだなと改めて感じました。今の業務を一度に変えるのは大変なので、まずは毎日使っている一つの集計表から、少しずつ移してみてください。
Synapse MCPなら、集計した数字をAIに自然言語で質問できる
毎朝の貼り直しがなくなっても、報告のたびに「どの商品が伸びた？」「売上が落ちたのはどれ？」と集計し直す仕事は残ります。せっかく揃えた数字を、知りたいことに合わせてその場で読めるようにしたいですよね。
そこで使えるのが、Synapse MCPです。Googleスプレッドシートを接続すると、AIが必要なセルの値を読み、普段の言葉で伝えた質問に沿って比較や整理を進められます。商品別・月別の売上が入った集計表なら、たとえば次のように頼めます。
| AIに聞きたいこと | 確認したい出力 |
|---|---|
| 先月とその前の月の売上を商品別に比べて | 商品ごとの売上と、前月からの増減額 |
| 売上が増えた商品を、増加額の大きい順に5つ教えて | 増加額で並べた商品別ランキング |
表の作り方を毎回考える前に、知りたいことをそのまま言葉にできます。数量の列もあれば、答えを見て「その商品の数量も比べて」と、会話の中で掘り下げられるのが便利なところです。
GA4のアクセス分析も、会話で進められる
スプレッドシートだけでなく、GA4もSynapse MCPへ接続できます。探索レポートの項目設定を知らなくても、「先月のPVと今月のPVを比較して」「よく読まれている記事を教えて」と自然言語で質問できます。
この画像では、Claudeへ「GA4のデータをグラフ化して」と頼み、日別の推移が返ってきています。GA4の週次レポートをAIで作る実演記事では、データ取得からグラフ化、レポート作成までを紹介しています。
今月と比べる場合は、月途中の数字を先月1か月分と比べないよう、日数をそろえて頼みます。次は、ご自身のデータで試すための質問例です。
接続したGA4のWebサイトで、今月1日から昨日までと、先月の同じ日数のPVを比較してください。
対象サイト・集計期間・PVの値・増減数を示してください。
集計がまだ完了していない日があれば教えてください。まずは自分の集計表を一つつなぐ
今回のスプレッドシートを使うなら、Synapse MCPでGoogleスプレッドシートを接続し、対象ファイルを選択します。続いて利用するAIへMCP接続を登録します。詳しい設定はスプレッドシートをAIにつなぐ手順をご覧ください。
接続後は、まずヘッダーを含む小さな範囲を読み、元の表と数字が一致するか確かめます。その後で、知りたいことを次のように聞いてみてください。
接続した売上表で、データが揃っている直近2か月を商品別に比較してください。
最初にファイル・タブ・対象月・金額の単位を確認してください。
必要な範囲をすべて読んで、月別売上と増減額を表にしてください。
同じ商品の小計と明細は二重に足さず、欠けたデータを推測で埋めないでください。BigQueryの更新はコネクテッド シート側で行い、Synapse MCPはシートへ出力された集計済みのセル値を読みます。シートの書き換えや更新操作を依頼する機能ではありません。
フリーは新規登録から14日間・3サービスで、自動課金はありません。利用条件は料金ページで確認できます。