はじめに
Google スプレッドシートは、表計算から関数やマクロによる自動化、グラフによる可視化まで多くの機能を備えた非常に便利なサービスです。しかし、シートが複数になったり、1つのシートの列や行が多くなったりすると動作が重くなることがあります。また、ファイルが重くなった際にファイルを分割することで、かえって運用が煩雑になるケースも出てきました。このようなスプレッドシートの課題に直面した際に思い浮かんだのが、BigQueryでした。
BigQueryについては、Google Cloudのサービスで「非常に優秀なデータ分析基盤でコストパフォーマンスが良い」という印象はありましたが、実際の設定方法などを詳しく理解していなかったので、サービスの理解も兼ねてスプレッドシートのデータを読み込ませてみることにしました。
最終的には、グラフで可視化するためにLooker Studioも使用しています。
対象読者と目的
後述しますが、私のレベルはスプレッドシートの関数、ピボットテーブル、グラフによる可視化はできるものの、SQL文やBigQueryのテーブル作成方法などの前提知識はない状態でした。
そのため、今回の記事は 「今までスプレッドシートしか使っていなかった人が、BigQueryでのデータ取り込みから加工、Looker Studioでの可視化までできるようになる」 ことを目指す内容となっています。私と同じようにBigQueryを初めて使う方の励みになればと思い、この記事を執筆しました。
本記事は初歩的な内容であり、誤った情報を含んでいる可能性もありますが、経験豊富な方々には温かく見守っていただけると幸いです。
また、記事はなるべく見やすく整理して記載しますが、試行錯誤の過程もできるだけ共有しています。
生成AIの力を借りることで、SQLの前提知識がなくてもBigQuery上でさまざまなクエリを実行できました。
スタートとゴール
本記事執筆前の私のスキルレベルは以下の通りです。
- スプレッドシート：基本的な関数、ピボットテーブル、グラフの作成は可能
- Google Cloud：他のサービスで触った経験あり
- SQL：全くの初心者
- BigQuery：テーブル作成方法などの前提知識はなし
- その他：データウェアハウスサービスの知識もなし
上記のスタート地点から、以下のゴールを目指しました。（無事達成できました）
- BigQuery内でデータの取り込みから加工までを実施 ※
- 加工したデータを使い、Looker Studioで可視化
※生データの都合上、一部の加工はスプレッドシートで実施しています。
対象データ
今回対象とするのは、会社ごとの売上データです。
データは月ごとにスプレッドシートのシートで分かれており、以下のような形式になっています。
| 日付 | 会社名 | 売上 |
|---|---|---|
| 2025年7月 | A社 | 製品A:¥1,000 |
| 2025年7月 | B社 | 製品A:¥2,000 |
| 2025年7月 | C社 | 製品B:¥3,000 |
STEP0：テーブル作成の前に
BigQuery（以下、BQ）にデータを取り込むには、まずデータセットを作成する必要があります。
データセットは複数作成できるため、運用のベストプラクティスを調べたところ、以下のような基準で分割すると良いとのことでした。
-
データの処理段階：生データは
raw、中間データはstg、可視化用のデータはmart - 部署や目的：営業や経理などの部署や、データの利用目的ごと
データセットごとに権限を制御できるため、このように分けるのが効果的だそうです。
当初はデータセットの存在を全く意識しておらず、すべてのテーブルを同じデータセットに入れてしまっていました。
今回は売上データなので、生データ用にsales_raw、加工済みデータ用にsales_stg、可視化用にsales_martというデータセットを作成しました。
STEP1：データの取り込み
次に、作成したデータセットにテーブルを作り、スプレッドシートのデータをBQに取り込みます。
ここで苦労したのは、以下の2点です。
- BQへの効率的なデータ取り込み方法
- データ型やスキーマ（列定義）の不一致
データの取り込み方法
スプレッドシートのデータは月ごとにシートで分かれているため、どうすれば効率よくBQに取り込めるかを考えました。
そこで、BigQuery APIを用いたApps Scriptを活用することにしました。
const PROJECT_ID = 'your-project-id';
const DATASET_ID = 'your-dataset-id';
const TABLE_ID = 'your-table-id';
function onOpen() {
SpreadsheetApp.getUi().createMenu('BigQuery連携')
.addItem('このシートをBigQueryに追記', 'appendDataToBigQuery')
.addToUi();
}
function appendDataToBigQuery() {
const sheet = SpreadsheetApp.getActiveSheet();
// ヘッダー行を除き、2行目から最終行までを取得
if (sheet.getLastRow() < 2) {
SpreadsheetApp.getUi().alert('データが見つかりません。');
return;
}
const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
const rows = dataRange.getValues();
if (rows.length === 0 || rows[0][0] === "") {
SpreadsheetApp.getUi().alert('データが見つかりません。');
return;
}
const bqRows = rows.map(row => {
// 日付を 'yyyy-MM-dd' 形式にフォーマット
const formattedDate = Utilities.formatDate(new Date(row[0]), Session.getScriptTimeZone(), 'yyyy-MM-dd');
return {
json: {
date: formattedDate,
company_name: row[1],
sales: row[2]
}
};
});
try {
const job = BigQuery.Jobs.insert({
configuration: {
load: {
destinationTable: {
projectId: PROJECT_ID,
datasetId: DATASET_ID,
tableId: TABLE_ID,
},
writeDisposition: 'WRITE_APPEND', // 既存のテーブルに追記
sourceFormat: 'NEWLINE_DELIMITED_JSON',
autodetect: true, // スキーマを自動検出
}
}
}, PROJECT_ID, Utilities.newBlob(bqRows.map(r => JSON.stringify(r.json)).join('\n'), 'application/octet-stream'));
SpreadsheetApp.getUi().alert('BigQueryへのデータ追記ジョブを送信しました。Job ID: ' + job.jobReference.jobId);
} catch (e) {
SpreadsheetApp.getUi().alert('エラーが発生しました: ' + e.toString());
}
}
このスクリプトにより、スプレッドシート上に[BigQuery連携]というカスタムメニューが追加され、それをクリックするだけでアクティブなシートのデータを特定のテーブルに追記できるようになりました。
当初は、スプレッドシートをローカルにCSVとしてダウンロードし、そのファイルをアップロードする方法を試していました。しかし、私のMac環境ではCSVダウンロード時に文字化けが発生しました。さらに、シートごとにファイルを書き出す必要があったため、一度Excel形式に変換してから各シートを個別にCSV化するという非常に手間のかかる手順を踏んでいました。
データ型・スキーマの不一致
上記のApps Scriptコードでもdateの形式を明示的に指定していますが、この経験から、スプレッドシートのデータ表示形式とBQテーブルのデータ型やスキーマを一致させることがいかに重要かを学びました。
これらが一致していないと、データ取り込み時にエラーが発生してしまいます。
少し話はそれますが、スキーマ（列定義）に特殊文字やスペースが含まれていると、後の工程であるLooker Studioでの可視化の際にエラーが発生することも分かりました。そのため、列名には特殊文字を使わないように注意が必要です。
余談：テーブルの運用方法
スプレッドシートでは月ごとにシートが分かれていたため、「BQでもテーブルを月ごとに分けるべきか？」と考えました。しかし、結論としては単一のテーブルでパーティション分割を行う方が良いと分かりました。
理由は以下の通りです。
- 複数テーブルに分かれていると、複数月にまたがる分析が非常に煩雑になる
- クエリを実行するたびに、複数のテーブルを読み込む必要がある
- スキーマを変更する際、すべてのテーブルを一つずつ修正しなければならない
1つのテーブルの行数が膨大になっても、パーティション分割やクラスタリングでパフォーマンスを維持するのがベストプラクティスのようです。
STEP2：データの加工
営業担当者データの結合
取り込んだ売上データを加工する前に、元のスプレッドシートに会社ごとの営業担当者名が含まれていないことに気づきました。
分析の精度を高めるため営業担当者ごとのデータも可視化したいと考え、売上データに営業担当者の情報を追加することにしました。
会社ごとの営業担当者リストは別のスプレッドシートに以下のような形式でまとまっていました。
| 営業担当者 | 会社名 |
|---|---|
| Aさん | A社 |
| Bさん | B社 |
このデータは1つのシートにまとまっていたため、BQの「Googleドライブからのテーブル作成」機能を使って、このスプレッドシートを直接参照するテーブルを作成しました。
準備が整ったので、SQLのJOIN句を使い、売上データと営業担当者データを結合しました。
テーブルか、論理ビューか
JOINで結合した結果を、新しいテーブルとして保存すべきか、それとも論理ビューとして保存すべきか迷いました。
結論として、今回のケースでは以下の理由から論理ビューとして保存する方が良いと判断しました。
- データ更新の手間：新しいテーブルとして保存した場合、元のデータが更新されるたびに、結合後のテーブルも手動で更新する必要があります。一方、ビューは常に最新の元データを参照するため、自動的に内容が更新されます。
- ストレージコスト：テーブルとして保存するとデータを複製することになり、ストレージコストが余分にかかります。
売上データの整形
冒頭で紹介した売上データは、以下のような形式でした。
| 日付 | 会社名 | 売上 |
|---|---|---|
| 2025年7月 | A社 | 製品A:¥1,000 |
| 2025年7月 | B社 | 製品A:¥2,000 |
| 2025年7月 | C社 | 製品B:¥3,000 |
しかし、実際には「売上」列の1つのセルに、「製品A:¥1,000, 製品B:¥2,000」のように複数の製品の売上データが含まれている場合があり、このままでは分析が困難でした。
そこで、SQLの正規表現関数（REGEXP_EXTRACTなど）を使い、製品ごとの売上をそれぞれ別の列として抽出しました。
最終的なテーブルのイメージは以下の通りです。元の「売上」列は不要なので削除しました。
こちらも新しいテーブルは作成せず、論理ビューとして準備しました。
| 日付 | 会社名 | 製品A | 製品B |
|---|---|---|---|
| 2025年7月 | A社 | ¥1,000 | ¥2,000 |
| 2025年7月 | B社 | ¥2,000 | ¥3,000 |
SQLの前提知識は全くありませんでしたが、生成AIを活用することで、無事に目的のクエリを作成できました。
STEP3：Looker Studioでの可視化
いよいよ、Looker Studioでデータを可視化します。
BQで作成したビューをデータソースとして接続し、グラフを作成しました。
以下のように、会社ごとの売上データをグラフで表示できました。
さらに、Looker Studioのフィルタ機能を使えば、インタラクティブに営業担当者ごとや製品ごとの売上を表示することも可能です。
BQ側であらかじめ営業担当者ごとのビューを作成することもできますが、「どこまでをBQで加工し、どこからをLooker Studioで操作するか」は、要件やパフォーマンスを考慮してケースバイケースで判断するのが良さそうだと感じました。
感想
スプレッドシートからBQでのデータの取り込み、加工およびLooker Studioでの可視化まで実施することができました。1つのテーブルで完結できることから運用の手間を省くことができ、SQL文を書くことでさまざまな分析ができそうです。
また生成AIのおかげで前提知識がないSQL文も記述することができスムーズにクエリを投げることができました。
テーブルの[売上]の中には他にもさまざまな売上が含まれているので抽出してさまざまな分析を実施したいと思います。
最後までお読みいただきありがとうございました。