Google スプレッドシートのデータを縦に積む方法

2021年9月14日 10:00

※こちらはgluサイトにて掲載しているコラムの転載記事です。

広告運用者がBIツールを導入し、広告パフォーマンスを管理・分析するニーズが高まってきている中、Google データポータル（旧：Google データスタジオ、以下データポータル）は無料で利用できる点もあり、注目が集まっています。

データポータルには、データソースを横に統合する機能（データの統合）はあるものの、縦に結合する機能（ユニオン）はありません。そのため、複数のデータを縦に繋げたい場合は、BigQueryやGoogle スプレッドシート（以下スプレッドシート）などのデータソース側で処理を行ってから、データポータルに接続する必要があります。

本コラムでは、スプレッドシートの関数を利用して、スプレッドシート側でデータを縦に積む方法を紹介します。

目次
・QUERY関数とIMPORTRANGE関数
　1. 同じスプレッドシートにある複数シートからデータを読み込む場合
　2. 異なるスプレッドシートから読み込む場合
・利用時の注意点
・おわりに

QUERY関数とIMPORTRANGE関数

スプレッドシート側でデータを縦に積む場合、QUERY関数とIMPORTRANGE関数を使用します。

IMPORTRANGE関数は、指定したスプレッドシートからセルの範囲を読み込むことができます。

QUERY関数は、Google Visualization API のクエリ言語（「select」「where」「from」など）を使用して、データ全体に対するクエリを実行することができます。

1. 同じスプレッドシートにある複数シートからデータを読み込む場合
例えば、あるスプレッドシートに存在する複数のシートのデータを縦に繋げたい場合、以下のような関数を組みます。

前提条件

●スプレッドシートのURLは、「https://docs.google.com/spreadsheets/d/aaaaaaaaaa/edit」とする。
●該当のスプレッドシートには、「data_202101」「data_202102」「data_202103」という名称のシートが存在する。
●それぞれのシートの項目名は統一されており、A1セルからE列の最後の行までのデータを参照して、データを縦に積む。

関数

=QUERY({
QUERY(IMPORTRANGE("aaaaaaaaaa","data_202101"), "select *");
QUERY(IMPORTRANGE("aaaaaaaaaa","data_202102"), "select *");
QUERY(IMPORTRANGE("aaaaaaaaaa","data_202103"), "select *")
},
"where Col1 is not null")

2. 異なるスプレッドシートから読み込む場合
例えば、複数のスプレッドシートのデータを縦に繋げたい場合、以下のような関数を組みます。

前提条件

●縦に繋げたいスプレッドシートのURLは、「https://docs.google.com/spreadsheets/d/aaaaaaaaaa/edit」と「https://docs.google.com/spreadsheets/d/bbbbbbbbbb/edit」と「https://docs.google.com/spreadsheets/d/cccccccccc/edit」の3つとする。
●該当のスプレッドシートには、「data」という名称のシートが存在する。
●それぞれのシートの項目名は統一されており、A1セルからE列の最後の行までのデータを参照して、データを縦に積む。

関数

=QUERY({
QUERY(IMPORTRANGE("aaaaaaaaaa","data!A1:E"), "select *");
QUERY(IMPORTRANGE("bbbbbbbbbb","data!A1:E"), "select *");
QUERY(IMPORTRANGE("cccccccccc","data!A1:E"), "select *")
},
"where Col1 is not null")

利用時の注意点

以下は今回紹介している関数を利用する際の注意点です。

1. IMPORTRANGE関数を使用して他のスプレッドシートからデータを読み込む場合は、読み込み先のスプレッドシートに明示的に権限を許可する必要があります。また複数URLの権限を同時に許可することはできないため、1つずつスプレッドシートの「アクセスを許可」してから、QUERY関数と組み合わせて縦に繋げる必要があります。
2. QUERY関数内で複数のIMPORTRANGE関数を使用した場合、最後のIMPORTRANGE関数の行には「;」を付けないようにします。他の行に「;」を付け忘れたり、最後の行に「;」をつけるとエラーになります。
3. 関数の記述は間違っていないにも関わらず、「読み込み中です」のままデータが反映されないことや「#REF!」というアラートが表示されることがあります。その場合は、F5を選択するなどで更新することで正常に反映される場合があります。
4. 読み込み元のデータ量が多いと読み込みができない場合があります。その場合は、データの参照範囲を小さくすることで回避できます。 ※以下記述参照。

データ量が多くてエラーがでる場合の回避記述例

=QUERY({
QUERY(IMPORTRANGE("aaaaaaaaaa","data!A1:E100000"), "select *");
QUERY(IMPORTRANGE("aaaaaaaaaa","data!A100001:E200000"), "select *");
QUERY(IMPORTRANGE("aaaaaaaaaa","data!A200001:E300000"), "select *")
},
"where Col1 is not null")

おわりに

シート毎に年月を分けてデータを管理している場合などは、今回の関数を利用してデータを縦に繋げて、データポータルに接続することで、今まで見にくかった長期間のデータが閲覧しやすくなります。

以下の記事では、スプレッドシートをデータソースとして利用したデータポータルのテンプレートを紹介しています。

【動画解説付き】運用型広告に使える Google データポータルレポートテンプレート

データポータルでは、Google 広告以外の広告パフォーマンスも閲覧したいとなると、各自でデータを収集する必要があり、データソースの管理工数が増加してしまいます。そこでgluのようなデータ収集システムを利用すれば、データソースの管理を効率化することが可能です。

レポート作成など自動化できる部分は自動化してしまい、本来注力していくべき多角的な分析や施策立案などに時間を割いていきましょう。

広告レポート作成、もっとラクに、簡単にしませんか？

広告レポートのExcel作成に時間がかかっており自動化したい
広告とコンバージョンデータの突合に時間がかかりミスが発生している
もっとたくさんの媒体を扱いたいが、レポーティングが大変
クライアントからレポート提出を早めるように言われている
BIツールを使った広告ダッシュボードを作りたい

という方は、まずはライトな相談から。支援内容とお問い合わせはこちらをクリック