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

2021.07.30 / Looker Studio

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

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

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

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")

利用時の注意点

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

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

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

=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")