Googleスプレッドシート×BigQuery連携|コネクテッドシートの料金と権限の決まり

スプレッドシートとBigQueryのつなぎ方は向きで4つあります。当社の検証環境でコネクテッドシートのピボットを作ると課金は84 MiB、更新はキャッシュで0でした。共有した相手の権限と、外部テーブルが止まったエラーも載せています。

この記事をシェア:
目次 クリックで開く

この記事の要点

  1. つなぎ方は、データを読む向きの違いで4つあります。シートから BigQuery を集計するコネクテッドシート、BigQuery からシートを読む外部テーブル、結果を一度だけシートに保存する方法、Apps Script や連携ツールで定期的に運ぶ方法です。
  2. 600万行の架空の明細にコネクテッドシートでピボットを1つ作ると、見積もり 83.92 MB に対して課金は 84 MiB でした。更新はキャッシュに当たり、課金は0です。9月に絞ると読んだのは 3.87 MiB で、課金は最低額の 10 MiB でした。
  3. 共有した相手は BigQuery の権限が無くても作成済みのピボットを見られますが、作る・更新するには権限が要ります(公式)。外部テーブルは作るにも読むにもドライブの権限が要り、クエリのたびにシートを読み直して課金されます。

スプレッドシートと BigQuery をつなぐ4つの方法

コネクテッドシート

シートが BigQuery の表を読む

  • ピボット・グラフ・抽出を作る・更新するとクエリが実行される
  • 作る・更新するには BigQuery の権限が要る

外部テーブル

BigQuery がシートを表として読む

  • クエリのたびにシートを読み、キャッシュされない
  • 作る人にも読む人にもドライブの権限が要る

結果を保存する

BigQuery の結果を新しいシートへ

  • コンソールの[結果を保存する]から
  • bq コマンドや API からはできない

Apps Script・連携ツール

決まった時刻に運ぶ

  • Apps Script は1回の実行が6分まで
  • Fivetran・TROCCO などにシートのコネクタ

出典:BigQuery「コネクテッド シートの使用」、「外部テーブルの概要」、「クエリ結果をファイルにエクスポートする」、Apps Script の割り当て

スプレッドシートと BigQuery は、どちらがどちらを読むかで、つなぎ方が4つに分かれます。どれを選んでも、クエリの料金は BigQuery で処理したデータの量で決まり、誰が何をできるかは Google Cloud の権限(IAM)とシートの共有の両方で決まります。

当社は BigQuery を中心にしたデータ基盤の設計・構築と、データポータル(旧 Looker Studio)などでの可視化を支援している会社です。この記事では、2026年10月14日に当社の検証用の Google Cloud プロジェクト(東京リージョン、オンデマンドの料金)で、架空の受注明細600万行と、架空の店舗別の目標を入れた50行のシートを使って試した結果を書きます。試したのはコネクテッドシートと外部テーブルで、残りの2つは公式の文書で確かめた仕様だけを書きます。

集計を Excel・スプレッドシート・BigQuery のどこで行うかは「集計の置き場所」を比較した記事と下の動画で、BigQuery の料金の構造と東京リージョンの単価はBigQuery の費用の記事で扱っています。

コネクテッドシートでつなぐ:接続とプレビューでは、ジョブ一覧にクエリが出なかった

シート自体のセルの上限は、公式の中で値が分かれています。ドライブのヘルプは、1ファイル2,000万セルか100 MB としています(Google ドライブ ヘルプ)。コネクテッドシートの管理者ヘルプは全タブの合計で1,000万セルとし、大きなデータを抽出してこれを超えると #NUM! のエラーになる、と書いています(管理者ヘルプ)。明細を BigQuery に置いたまま、シートでは集計だけを受け取るのがコネクテッドシートです。要るのは、組織で Google Cloud が使える状態になっていること、課金が有効な Google Cloud のプロジェクト(無ければ BigQuery のサンドボックス)、読む表への BigQuery の権限です(ヘルプ「Google スプレッドシートで BigQuery データを使ってみる」、管理者ヘルプ)。[データコネクタ]が出ないときは、最初の条件から確かめます。

コネクテッドシートで BigQuery の表につなぐ

1

データコネクタ

  • [データ]→[データコネクタ]→[BigQuery に接続](隣に[Looker に接続])

2

データ接続の追加

  • クラウド プロジェクトを選ぶ
  • 「課金プロジェクト」の表示

3

表を選んで接続

  • blog_demo の sales_part_clus を選び[接続]
  • 「データを接続しました」「600万行・10列」

出典:当社の検証環境で架空データを使って試した結果(2026年10月14日、東京リージョン・オンデマンド)

接続すると、シートの上に[グラフ][ピボット テーブル][関数][抽出][計算された列][列の統計情報][高度なインサイト]が並び、表のプレビューが出ました。この間の BigQuery のジョブの一覧を後から見ると、コネクテッドシートのジョブは出ておらず、最初のジョブはピボットを適用したときのものでした。プレビューは500行までで、ピボット・関数・グラフは表の全体を使います。シートから BigQuery のデータを書き換えることはできず、スマートフォンのアプリは表示とコピーだけです(BigQuery「コネクテッド シートの使用」、ヘルプ)。

コネクテッドシートの行の上限(出典は BigQuery のドキュメントとシートのヘルプ、抽出は試していない)
操作上限(公式。ページで値が分かれるものは両方)
ピボット テーブルの結果BigQuery のドキュメントは20万行、シートのヘルプは10万件
抽出(シートに行を取り出す)50万行か10 MB まで。5万行まではセルの数の制限なし、5万〜50万行は500万セルまで(BigQuery のドキュメント)。シート全体のセルの上限を超えると #NUM!

ピボットを1つ作ると、BigQuery でクエリが1本実行された

ピボット テーブルの行に channel(店舗・EC・電話)、値に amount の合計を置きました。エディタの下に出る処理量の見積もりは、amount を足した時点で 83.92 MB でした。[適用]で EC 31,511,731,700 などの合計(架空の値)が返り、BigQuery ではジョブが1本、約1秒で実行されていました。

ピボット1つで、見積もりから課金まで

88,001,508 を 1,048,576(2の20乗)で割ると 83.92。課金は MB 単位で切り上げ

シートの見積もり83.92 MB
BigQuery のジョブ処理 88,001,508 バイト
課金88,080,384 バイト(84 MiB)

出典:当社の検証環境で架空データを使って試した結果(2026年10月14日、東京リージョン・オンデマンド)。切り上げは BigQuery の料金

シートの「MB」は、処理したバイト数を 1,048,576 で割った値と一致し、課金はちょうど 84 MiB でした。BigQuery の料金は MB 単位で端数を切り上げます(BigQuery の料金)。[適用]の前に見積もりを見れば、そのピボットが読む量の上限が分かります(この表はクラスタにしてあり、見積もりは上限の値です)。シートが実行した SQL は次のとおりです(プロジェクト名は伏せています)。

SELECT BIT_COUNT(__mask & 1) AS rowDepth, BIT_COUNT(__mask & 0) AS colDepth,
  IF(__mask & 1=0,NULL,MAX(`channel`)) AS `channel`, SUM(`amount`) AS `agg0_`
FROM ((
SELECT * FROM `<project>.blog_demo.sales_part_clus`
)CROSS JOIN UNNEST([1]) __mask)  GROUP BY __mask, IF(__mask & 1=0,NULL,UPPER(`channel`))

中は SELECT * ですが、読まれたのは使った2列だけで、同じ集計のドライラン(実行せずに処理量を見積もる)も 88,001,508 バイトでした。表全体の約0.81 GiB は読んでいません。ピボットに置く列を増やすと、処理量も増えます。

ジョブには sheets_connector: connected_sheets、sheets_trigger: user などのラベルが付いていました。公式の文書は、コネクテッドシートのジョブは ID が sheets_dataconnector で始まるので、INFORMATION_SCHEMA.JOBS_BY_PROJECT で絞れば誰がどれだけ課金されたかを数えられる、と案内しています(BigQuery「コネクテッド シートの使用」)。

同じピボットの更新は、キャッシュで課金されなかった

同じピボットを2回更新すると、ジョブは2本とも出ましたが、どちらもキャッシュに当たって課金は0でした。キャッシュは約24時間、ユーザーとプロジェクトごとに残ります。キャッシュは、参照する表が変わった後や、CURRENT_TIMESTAMP() のように実行の時刻で値が変わる関数を使うとき、Cloud Storage 以外の外部テーブルを読むときには使われません(BigQuery「キャッシュに保存されたクエリ結果の使用」)。毎日読み込む表なら、読み込みの後の最初の更新は課金されます。

日付で絞ると読む量は約34分の1、課金は最低 10 MiB

同じピボットに order_date のフィルタ([次の間にある]2026-09-01〜09-30)を足すと、見積もりは 3.87 MB でした。実行された SQL には WHERE ((order_date BETWEEN ('2026-9-1') AND … が入り、処理は 4,053,888 バイトでした。同じ3列(channel・amount・order_date)を全期間で読むと 136,001,508 バイトなので、約34分の1です。この表は日付で分けた(パーティションにした)表で、1,004日分のうち9月の30日分だけが読まれました。ただし課金は 10,485,760 バイト(10 MiB)で、クエリが参照する表ごとの最低額 10 MB でした(BigQuery の料金)。

コネクテッドシートの操作ごとに課金された量(MiB)

架空の受注明細600万行・約0.81 GiB(日付パーティション+店舗のクラスタ)

ピボットを作る(全期間・2列)84 MiB
同じピボットを更新(2回とも、キャッシュ)0
9月に絞る(読んだのは 3.87 MiB、最低額)10 MiB

出典:当社の検証環境で架空データを使って試した結果(2026年10月14日、東京リージョン・オンデマンド)

課金は合わせて 94 MiB でした。オンデマンドは毎月 1 TiB まで無料で、同じ請求先アカウントのほかのクエリと合わせて数えます。日付で分けた表の作り方と、関数で日付を包むと絞り込みが効かない例は、費用の記事で同じ表を使って試しています。

シートを渡す相手が増えると、誰かが全期間・多くの列のピボットを作って、そのたびに大きく課金されることがあり得ます。オンデマンドの料金で費用を制限する方法として公式が挙げているのは、1日あたりのカスタムの割り当てです(BigQuery「費用の見積もりと管理」)。[IAM と管理]→[割り当てとシステム上限]の Query usage per day(プロジェクト全体)か Query usage per day per user(ユーザーごと)で、1日に処理できる量を TiB で決めます。超えるクエリは usageQuotaExceeded のエラーで止まります。値はおおよそで、処理量を厳密に止めるものではありません。ユーザーごとの値はプロジェクトの全員に同じ値で掛かり、人ごとに別の値は決められません。プロジェクトの上限に達すると、そのプロジェクトの全員のクエリが止まります。また、割り当ての無い別のプロジェクトからそのデータセットにクエリを実行できる人には効きません。そのため、コネクテッドシートで選べる課金プロジェクト(ジョブを実行できるプロジェクト)も絞っておきます(BigQuery「カスタムのクエリ割り当てを作成する」)。コネクテッドシートのクエリも課金プロジェクトのジョブとして実行されていましたが、割り当てに達したときにシートがどう表示されるかは試していません。

割り当てとは別に、シートに見せるものを小さくする手段もあります。コネクテッドシートは表のほかにビューにもつなげます。ただ、ビューは見せる列と行を決めるためのもので、ビューを読むたびに元の表を読むクエリが実行され、課金は元の表の分です(BigQuery「論理ビューの概要」)。読む量を減らすのは、日別・店舗別などに集計済みの表です。ピボットが読むのはつないだ表の列なので、600万行の明細より小さい集計済みの表なら、読む量もそれに応じて小さくなります(課金は1本ごとに最低 10 MB)。なお、自分で SQL を実行するときは、クエリごとに[課金される最大バイト数]を付けて、見積もりが上限を超えるクエリを実行前に止められます。これはクエリを実行する人がコンソールや bq で1本ごとに付ける設定で、使い方と止めた例は費用の記事にあります。

共有した相手は、見るだけなら BigQuery の権限が要らない

相手ができることは、シートの持ち主ではなく、相手自身の IAM のロールとシートへの権限で決まります。作成済みのピボットやグラフを見るだけなら、IAM のロールは要りません。ピボット・グラフ・関数・抽出を作る、クエリを作る・直す、データを更新するには、シートの編集者の権限に加えて、BigQuery の次のどちらかのロールが要ります。BigQuery ユーザーのロールか、BigQuery ジョブユーザーと BigQuery データ閲覧者の2つのロールです。条件を満たさない人は、作成済みの分析を見られても、更新や自分のコネクテッドシートの作成はできません。

コネクテッドシートで、誰が何をできるか

シートの閲覧者・編集者BigQuery の権限なし
できる作成済みのピボット・グラフ・抽出を見る
できない作る、更新、スケジュール更新
シートの編集者BigQuery ユーザー、またはジョブユーザー+データ閲覧者
できるピボット・グラフ・抽出を作る、クエリを作る・直す、更新
実行されるクエリジョブとして課金される
Google Workspace の管理者管理コンソール
委任を許す[ドライブとドキュメント]→[機能とアプリケーション]
範囲組織部門・グループで絞る

出典:管理者ヘルプ、BigQuery のドキュメント。共有した相手からの操作は試していない

管理者が「コネクテッド シートに対するアクセス権の委任」を許すと、編集者は自分の BigQuery の認証情報を、シートを共有した相手に委任できます。リンクで一般公開したシートには委任できません。委任したときは、ログの delegating_principal に、認証情報を使われた人が残ります。コネクテッドシートからのアクセスは、スプレッドシートの ID とともに Cloud Audit Logs に記録されます。見る人が多いシートなら、見るだけの人には BigQuery のロールを付けずに共有し、作る・更新する人を決めてその人だけにロールを付けるか、委任で済ませるかを選びます。どちらでも、誰の権限でクエリが実行されたかは後からログで追えます。

使える機能はエディションで違います。一般提供の時点(2020年7月)は Enterprise 系だけでしたが、2025年に加わった機能(保存したクエリからの接続、大きな CSV の取り込み)は、すべての Google Workspace と個人の Google アカウントが対象です。委任を使えるのは、Enterprise Standard・Plus、Education Standard・Plus、Enterprise Essentials・Essentials Plus だけです。1時間ごとのスケジュール更新を加えた2021年7月のお知らせでも、Business Starter・Standard・Plus は対象外でした。使う機能が自社の契約で使えるかは、管理者に確かめてもらうのが確実です。

スケジュール更新は、スケジュールを設定した人として動きます。そのため、設定した人の OAuth の認証が取り消されたとき、その人にシートの編集権限が無いとき、その人のアカウントが削除されたとき(退職など)は、定期の更新が失敗します。アカウントが削除されたときは、ほかの人がスケジュールを引き継ぐ必要があります。また、利用者の IP アドレスや端末の情報を持たずに動くので、それを条件にした VPC Service Controls の境界では失敗します。スケジュール更新は試していません。

この節の出典:管理者ヘルプ「組織でコネクテッド シートを使用する」(ロール、委任)、ヘルプ「Google スプレッドシート内の BigQuery データに関する問題を修正する」(条件を満たさない人、定期更新の失敗)、ヘルプ「コネクテッド シートを使用して…分析、更新する」(設定した人として実行)、ヘルプ「Google スプレッドシートで BigQuery データを使ってみる」(監査ログ)、BigQuery「コネクテッド シートの使用」(VPC Service Controls)、Google Workspace ブログ 2020年7月、Workspace Updates 2021年7月、2025年5月、2025年11月

反対向きの外部テーブルは、作る段階でドライブの権限で止まった

次に、50行の店舗別の目標のシート(S001〜S050、合計 99,750,000、架空)を BigQuery から外部テーブルとして読み、BigQuery にある600万行の売上と並べようとしました。API から、Google スプレッドシートを読み込み元、列は自動検出、1行目は見出しとして飛ばす設定で作ると、次のエラーで失敗しました。

Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

使った認証は gcloud のふつうのログインで、Google Cloud を操作する権限(スコープ)しか入っていませんでした。ドライブのデータを読むには、追加の OAuth のスコープが要ります(BigQuery「ドライブのデータに対してクエリを実行する」)。

ドライブのスコープを付ける方法(公式)

gcloud

ログインし直す

  • gcloud auth login –enable-gdrive-access

アプリの既定の認証

スコープを指定

  • application-default login の –scopes に drive と cloud-platform

コンソール

画面から作る

  • [テーブルを作成]→ 作成元[ドライブ]→[スプレッドシート]

出典:BigQuery のドキュメント。Cloud Shell ならドライブでの認証は要らない。直した後の作成は試していない

作るには bigquery.tables.create の権限(BigQuery データ編集者などのロール)が要ります。シートは、共有リンクを使うか、BigQuery の認証に使うメールアドレスに閲覧の権限を付けます。ドライブのスコープは、ドライブのデータにアクセスするたびに要るので、作った人だけでなく、その外部テーブルを読む人の認証にも要ります。[シートの範囲]を省くと最初のシートが使われます(BigQuery のドキュメント)。当社はスコープを付け直した再実行をしていないので、JOIN の結果や処理量は書けません。

人が手で入力するシートでは、列の型の食い違いにも備えます。外部テーブルには、読むときに無視してよい不正なレコードの数(max_bad_records、Google スプレッドシートにも効く)の設定があり、既定は0です。0のときはすべてのレコードが有効である必要があり、超えるとジョブの結果は invalid のエラーになります(BigQuery「DDL statements」、REST リファレンス「tables」)。数値の列に文字が入るような行に備えるなら、この数を決めるか、列を文字列(STRING)として定義し、SQL の SAFE_CAST(変換できない値をエラーにせず NULL を返す)で数値にするかです(BigQuery「Conversion functions」)。どちらも試していません。また、外部テーブルを置くデータセットのロケーションは作成時にだけ決められ、後から変えられません。1つのクエリで参照する表は同じロケーションのデータセットに置く必要があるので、並べたい売上の表と同じロケーションにデータセットを作ります(BigQuery「データセットの作成」)。

外部テーブルを読むクエリは結果がキャッシュされず、実行するたびに課金されます。BigQuery は外部のデータの整合性を保証しません。クエリの最中にシートが変わると予期しない動きになることがあり、BigQuery の表より遅くなることもあります。公式は、あまり変わらない外部テーブルに同じクエリを何度も実行するなら、結果を BigQuery の表に書き込むことを検討するよう書いています(BigQuery「外部テーブルの概要」)。ですから外部テーブルは、目標やマスタのように人が手で直す小さな表をときどき使う場合に向きます。ダッシュボードから毎日読むなら、BigQuery の表に取り込んでおくことを検討してください。キャッシュが効き、編集中のシートで結果が揺れないからです。取り込んだ表なら、誤って上書きしても、既定で過去7日間(2〜7日で設定)の変更・削除されたデータをタイムトラベルで読めます。外部テーブルは、タイムトラベルの対象外です(BigQuery「タイムトラベルとフェイルセーフによるデータの保持」)。

一度だけ書き出す、定期で運ぶ(今回は試していない)

BigQuery のコンソールでは、クエリの結果から[結果を保存する]→[Google スプレッドシート]で、新しいシートに書き出せます。求められたらマイドライブへの書き込みを許可し、ファイル名は results- と日付で始まります。bq コマンドや API からはできません。また、5万文字を超えるセルは空になることがあります(BigQuery「クエリ結果をファイルにエクスポートする」)。一度渡すだけの集計なら、手間の少ない方法です。

Apps Script の BigQuery サービスを有効にすると、クエリを実行して結果を新しいシートに書いたり、ドライブの CSV を表に読み込んだりできます(Apps Script「BigQuery サービス」)。1回の実行は6分まで、トリガーの合計は1日に個人のアカウントで90分、Google Workspace で6時間までです(Apps Script の割り当て)。

連携ツールでは、Fivetran の Google Sheets のコネクタが名前付き範囲1つを表1つに同期します。範囲の行を足す・消すと表に反映され、範囲から外した列は表に残って空(NULL)になります。列の型は広がる方向にしか変わらず、数値の列に文字が入ると列全体が文字列(TEXT)になります(Fivetran のドキュメント)。TROCCO にも転送元の Google Spreadsheets があります(TROCCO Docs)。ツールの公開価格は費用の記事に並べています。

まずコネクテッドシートで、見積もりとジョブを見る

コネクテッドシートで足りるか

1

見たいのは、BigQuery にある明細の集計か

例:600万行の受注明細を、チャネル別に合計する

2

更新する人に、BigQuery の権限を渡せるか

見るだけの人には、権限なしで共有できる

3

見積もりとジョブの課金を確かめ、割り当てと課金プロジェクトを決めたか

例:全期間で 84 MiB、9月に絞ると 10 MiB

どれも当てはまるコネクテッドシートで始め、更新は表が変わる回数に合わせる
当てはまらない読む量が大きいなら集計済みの表かビューにつなぐ。シートの表を SQL で使うなら外部テーブルか取り込み、一度渡すなら結果を保存、定期なら Apps Script・連携ツール

出典:当社の検証環境で架空データを使って試した結果(2026年10月14日、東京リージョン・オンデマンド)、管理者ヘルプ

次の作業は、自社の BigQuery の表にコネクテッドシートでつなぎ、よく使う集計を1つピボットにすることです。[適用]の前に見積もりを見て、適用した後に sheets_dataconnector で始まるジョブの課金を確かめます。そのうえで、誰に BigQuery の権限を渡し、誰には見るだけで共有するかを決めます。

どの表をシートに見せるか、日付で分けた表や集計済みの表をどう用意するかは、当社のデータ分析・BI 支援で一緒に設計できます。

画面の項目名・処理量・エラーの原文は、2026年10月14日に当社の検証環境で架空データを使って試したものです。上限・権限・料金の決まりは、同じ日に各社の公式の文書で確かめました。

Google BigQuery 導入支援

GA4・広告・売上のデータを BigQuery に集め、Looker Studio で見られる形にします。スキャン量の課金を読める設計と、社内で運用できる状態までを支援します。

AT
aurant technologies 編集

上場企業からスタートアップまで、数多くのデータ分析基盤構築・AI導入プロジェクトを主導。単なる技術提供にとどまらず、MA/CRM(Salesforce, Hubspot, kintone, LINE)導入によるマーケティング最適化やバックオフィス業務の自動化など、常に「事業数値(売上・利益)」に直結する改善実績多数。

この記事が役に立ったらシェア: