Google スプレッドシートのデータコネクタであるコネクテッド シートを使用すると、スプレッドシートから数十億行のデータにアクセスし、分析、可視化、共有できます。コネクテッド シートでは次のこともできます。
- スプレッドシートの使い慣れたインターフェースを使用して、パートナー、アナリスト、その他の関係者と共同編集する。
- ユーザーが共同編集者にアクセス権を委任できるようにする。
- CSV 書き出しデータを別途作成することなく、単一の信頼できるデータ分析ソースを利用する。
- 境界内のデータを分析することで、ユーザーの IP アドレスやデバイス情報などの属性に基づいてアクセスを制限する。
BigQuery または Looker のコネクテッド シートから、手動または定義済みのスケジュールに従ってクエリを実行できます。Google スプレッドシートでは、これらのクエリの結果がスプレッドシートに保存されるため、分析と共有を行うことができます。BigQuery とコネクテッド シートを使用する方法の詳細に関する動画チュートリアルをご覧ください。
コネクテッド シートのクエリイベントは、ドライブのログイベントで確認できます。
データを分析するように BigQuery を設定する
手順 1: Google Cloud を有効にする組織に対して Google Cloud が有効になっていることを確認します。手順については、ユーザー、グループ、組織部門に対して有効になっているアプリを確認するをご覧ください。Google Cloud を有効にする必要がある場合は、ユーザーに対して Google Cloud を有効または無効にするをご覧ください。
コネクテッド シートで BigQuery を使用する方法については、Google スプレッドシートで BigQuery データを使ってみるをご覧ください。
Identity and Access Management（IAM）ロールを使用して、ユーザーがアクセスできるデータに関して権限を割り当てます。スプレッドシートで BigQuery プロジェクトを追加または使用するには、BigQuery におけるユーザーの IAM のロールが bigquery.user または bigquery.jobUser と bigquery.dataViewer である必要があります。
これらのロールについては、BigQuery の IAM 事前定義ロールをご覧ください。
ユーザーが行える操作は、スプレッドシートのオーナーの権限ではなく、IAM ロールとスプレッドシートに対する権限によって決まります。組織外のユーザーは、管理者が許可した場合にのみ、組織のスプレッドシートを操作できます。
| スプレッドシートでの操作 | BigQuery で必要な IAM ロール | スプレッドシートで必要な権限 |
|---|---|---|
| BigQuery のテーブルまたはビューを使用して、グラフ、ピボット テーブル、数式、抽出を作成する |
bigquery.user またはbigquery.jobUser と bigquery.dataViewer |
編集者 |
| BigQuery データから作成したグラフ、ピボット テーブル、数式、抽出、プレビューを表示する | なし | 編集者または閲覧者 |
| カスタムの BigQuery クエリを作成または編集する |
bigquery.user または bigquery.jobUser と bigquery.dataViewer |
編集者 |
| カスタムの BigQuery クエリを表示する | なし | 編集者または閲覧者 |
| BigQuery のデータを更新する |
bigquery.user または bigquery.jobUser と bigquery.dataViewer |
編集者 |
BigQuery コンソールでデータセットに対する IAM ロールを割り当てます。詳しくは、IAM を使用してリソースへのアクセスを制御するをご覧ください。
IAM を使用して BigQuery データにアクセスできるユーザーを承認するだけでなく、VPC Service Controls を使用して、ユーザーの IP アドレスやデバイス情報などの属性に基づいてアクセスを制限するサービス境界を作成することもできます。コネクテッド シートを使用して VPC Service Controls で保護された BigQuery データにアクセスできるのは、Google スプレッドシートでクエリ結果をユーザーのスプレッドシートにコピーできるように境界を構成した場合に限られます。詳しくは、アクセス制御をご覧ください。
データを分析するために Looker を設定する
コネクテッド シートを Looker で使用するには、Google 管理コンソールで個別に制御されていないサービスへのアクセスを有効にする必要があります。詳しくは、個別に制御されていないサービスへのアクセスを管理するをご覧ください。また、Looker 管理者はまず Looker 管理 UI でコネクテッド シートを有効にする必要があります。詳しい手順については、Looker 向けコネクテッド シートの使用をご覧ください。
BigQuery 向けコネクテッド シートへのアクセス権の委任をユーザーに許可する
BigQuery 向けコネクテッド シートへのアクセス権の委任をユーザーに許可して、それらのユーザーが他のユーザーと連携してデータ分析やクエリの実行を行えるようにできます。
アクセス権を委任するには、他のユーザーとシートを共有する必要があります。ただし、リンクを使って一般公開で共有されているシートへのアクセス権を委任することはできません。アクセス権を委任したユーザーと、ドライブのログイベントまたは Cloud Audit Logs でクエリを実行したユーザーを確認できます。
アクセス権の委任を有効または無効にする
-
管理者アカウントで Google 管理コンソール にログインします。
管理者アカウントを使用していない場合は、管理コンソールにアクセスできません。
-
メニュー アイコン
[アプリ] > [Google Workspace] > [ドライブとドキュメント] > [機能とアプリケーション] に移動します。
アクセスするにはサービス設定の管理者権限が必要です。
- [コネクテッド シートに対するアクセス権の委任] で編集アイコン
をクリックします。
- （省略可）設定を一部のユーザーにのみ適用するには、左側で組織部門（主に部門に使用）または設定 [グループ]（詳細）を選択します。手順を見る
グループ設定は組織部門よりも優先されます。詳細
- [委任設定] で [スプレッドシートに対する編集権限を持つユーザーが、コネクテッド シートに対するアクセス権の委任を有効化できるようにする] チェックボックスをオンまたはオフにします。
- 組織部門またはグループの設定を行う場合は、[特定の組織部門またはグループ内のユーザーのみが委任を行える] をオンにします。
- シートにアクセスできるどのユーザーにもアクセス権の委任を許可するには、[任意のユーザーが委任を行える] をオンにします。
これには、スプレッドシートへのアクセス権を持つ組織外のユーザーも含まれます。
-
[保存] をクリックします。 または、組織部門 の [オーバーライド] をクリックします。
継承された値を後で復元するには、[継承]（グループの場合は [設定解除]）をクリックします。
委任を有効にしたら、以下の手順に沿ってシートへのアクセス権を委任するようユーザーに説明してください。
コネクテッド シートのログイベントを表示する
コネクテッド シートが BigQuery と Looker のデータにアクセスすると、ドライブのログイベントにエントリが記録されます。エントリは BigQuery アクセス用の Cloud Audit Logs と Looker アクセス用の System Activity History Explore にも記録されます。このログには、誰がいつデータにアクセスしたのか表示されます。
Reports API を使用してドライブのログイベントを分析するGoogle 管理コンソールからドライブのログイベントを分析する方法について詳しくは、ドライブのログイベント データにアクセスするをご覧ください。
Reports API を使用して、コネクテッド シートのクエリイベントを表示できます。次の例では、コネクテッド シートのクエリイベント タイプごとにすべてのドライブ イベントを取得します。
GET https://admin.googleapis.com/admin/reports/v1/activity/users/all/applications/drive?eventName=connected_sheets_query
この API 呼び出しに対する完全な JSON レスポンスは、このページで後述する「完全な JSON レスポンス」セクションに示されています。
クエリを開始したユーザーがアクターとして表示されます。
"actor": {
"email": "collaborator@example.com",
"profileId": "user’s unique Google Workspace profile ID"
}
スプレッドシートには、パラメータとして実行されたクエリに関する追加情報が記載されています。
"parameters": [
{
"name": "execution_trigger",
"value": "sheets_ui"
},
{
"name": "query_type",
"value": "big_query"
},
{
"name": "data_connection_id",
"value": "The Cloud project ID"
},
{
"name": "execution_id",
"multiValue": [
"jobs/big_query_job_id"
]
},
{
"name": "delegating_principal",
"value": "trix01@scary.docsplustesting.com"
},
execution_trigger フィールドは、スプレッドシートからクエリがどのように呼び出されるかに基づいて設定されます。
| ラベル | クエリの実行方法 |
|---|---|
| sheets_ui | スプレッドシートの UI から手動で作成する |
| スケジュール | スプレッドシートの更新スケジュール機能を使用する |
| api | Sheets API を使用する |
| apps-script | Apps Script を使用する |
query_type フィールドは、データコネクタに基づいて設定されます。| ラベル | データコネクタ |
|---|---|
| big_query | BigQuery |
| looker | Looker |
data_connection_id フィールドは、データ接続の ID に基づいて設定されます。BigQuery の場合、これは課金プロジェクト ID です。Looker の場合は、インスタンスの URL です。
execution_id は、実行されたクエリの ID に基づいて設定されます。
| 値の構造 | クエリのエンティティ |
|---|---|
| jobs/<JOB_ID> | BigQuery ジョブ |
| datasets/<DATASET_NAME>/tables/<TABLE_NAME> | BigQuery テーブル |
| query_tasks/<QUERY_TASK_ID> | Looker クエリ |
認証情報が使用されたユーザーのメールアドレスは、ログの delegating_principal フィールドで確認できます。
完全な JSON レスポンス
{
"kind": "admin#reports#activity",
"id": {
"time": "2022-10-26T17:33:51.929Z",
"uniqueQualifier": "report’s unique ID",
"applicationName": "drive",
"customerId": "ABC123xyz"
},
"actor": {
"email": "collaborator@example.com",
"profileId": "user’s unique Google Workspace profile ID"
},
"events": [
{
"type": "access",
"name": "connected_sheets_query",
"parameters": [
{
"name": "execution_trigger",
"value": "sheets_ui"
},
{
"name": "query_type",
"value": "big_query"
},
{
"name": "data_connection_id",
"value": "The Cloud project ID"
},
{
"name": "execution_id",
"multiValue": [
"jobs/big_query_job_id"
]
},
{
"name": "doc_id",
"value": "aBC-123-xYz"
},
{
"name": "doc_type",
"value": "spreadsheet"
},
{
"name": "is_encrypted",
"boolValue": false
},
{
"name": "doc_title",
"value": "Document title"
},
{
"name": "visibility",
"value": "shared_internally"
},
{
"name": "actor_is_collaborator_account",
"boolValue": false
},
{
"name": "delegating_principal",
"value": "owner@example.com"
},
{
"name": "owner",
"value": "owner@example.com"
},
{
"name": "owner_is_shared_drive",
"boolValue": false
},
{
"name": "owner_is_team_drive",
"boolValue": false
}
]
}
]
}
各スプレッドシートには、一意のスプレッドシート ID が含まれています（スプレッドシートの URL で確認できます）。BigQueryAuditMetadata 形式のログエントリには、BigQuery データアクセス リクエストの送信元となるスプレッドシートの ID が含まれています。
Google Cloud コンソールのログ エクスプローラを使用して、ログの取得と分析を行うクエリを作成できます。ログ エクスプローラで、次のように入力します。
protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId
!= NULL_VALUE
次の例に示すように、スプレッドシート ID が空でないエントリが表示されます。
metadata: {
@type: "type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"
firstPartyAppMetadata: {
sheetsMetadata: {
docId: "aBC-123_xYz"
}
}
スプレッドシートでは、ジョブラベルを使用してクエリのジョブに情報が追加されます。次の例に示すように、分析するデータが多くなることがあります。
jobInsertion: {
job: {
jobConfig: {
labels: {
sheets_access_type: "normal"
sheets_connector: "connected_sheets"
sheets_trigger: "user"
}
sheets_trigger フィールドの値は、スプレッドシートからクエリがどのように呼び出されるかに基づいて設定されます。
| ラベル | クエリの実行方法 |
|---|---|
| ユーザー | スプレッドシートの UI から手動で作成する |
| スケジュール | スプレッドシートの更新スケジュール機能を使用する |
| api | Sheets API を使用する |
| apps-script | Apps Script を使用する |
たとえば、コネクテッド シートの更新スケジュール設定に対応するエントリを検索するには、ログ エクスプローラで次のクエリを使用します。
protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId
!= NULL_VALUE
protoPayload.metadata.jobInsertion.job.jobConfig.labels.sheets_trigger
= "schedule"
アクセス権の委任が有効になっていた場合は、認証情報を使用してクエリを実行したユーザーのメールアドレスをログで確認できます。次の例に示すように、クエリをトリガーしたユーザーのメールアドレスを確認することもできます。
"authenticationInfo": {
"principalEmail": "[オーナー名]@example.com",
"serviceAccountDelegationInfo": [
{
"firstPartyPrincipal": {
"principalEmail": "[共同編集者名]@example.com",
"serviceMetadata": {
"service": "sheets"
}
}
}
]
}
注: serviceAccountDelegationInfo フィールドは、クエリにアクセス権の委任が使用された場合にのみ表示されます。この場合、principalEmail に表示されるユーザーはアクセス権を委任したユーザーです。
詳しくは、ログ エクスプローラの使用とログ エクスプローラでのクエリ作成についての記事をご覧ください。
詳しくは、BigQuery の監査ログ、スプレッドシート ID、BigQueryAuditMetadata 形式、SheetsMetadata、スプレッドシートの共有、Google Sheets API についての記事をご覧ください。
- Looker インスタンスの左側で、[探索]
[履歴] をクリックします。
- [フィールドの検索] に「API クライアント名」と入力し、フィルタ アイコン
をクリックしてこのフィールドをデータセットに追加します。
- [フィルタ] で [が次と等しい] を選択し、その横のフィールドに「コネクテッド シート」と入力します。
- [フィールドの検索] に「コネクテッド シートのスプレッドシート ID」と入力し、このフィールドをデータセットに追加します。
- [フィールドの検索] に「コネクテッド シートのトリガー」と入力し、このフィールドをデータセットに追加します。
- [フィールドの検索] に「履歴のスラッグ」と入力し、このフィールドをデータセットに追加します。
- 履歴のスラッグは、ドライブのログイベントに記録されている QUERY_TASK_ID と同等です。ドライブのログで特定のクエリを見つけるには、そのフィールドにフィルタを追加します。
- （省略可）データセットに [ユーザー名] や [履歴作成日] などのフィールドを追加するには、それらのフィールドを選択します。
- （省略可）フィルタを追加するには、それらのフィールドを選択します。
たとえば、[履歴作成日] を [過去 7 日間] でフィルタするか、特定の [スプレッドシート ID] でフィルタし、特定のスプレッドシート ID から開始された Looker クエリのみを表示します。 - [実行] をクリックします。
トラブルシューティング
スプレッドシートで問題が発生した場合シートの上部にある [フィードバックを送信] をクリックします。
スプレッドシートのファイルで [更新] をクリックして、BigQuery データのすべての更新をコネクテッド シートに取り込みます。コネクテッド シート内のすべてのアイテムを更新するには、[データ] [データコネクタ]
[データを更新]
[すべて更新] をクリックします。
組織のスプレッドシート ファイルに特定の権限を設定した場合（組織外のユーザーによるスプレッドシート ファイルへのアクセスを制限するなど）、制限の対象となるユーザーはコネクテッド シートのファイルを開くことができなくなります。権限を変更する方法については、ドライブ ユーザーの共有権限を設定するをご覧ください。
それでも問題が解決しない場合は、Google スプレッドシート内の BigQuery データに関する問題を修正すると Looker 向けコネクテッド シートのトラブルシューティングをご覧ください。
