課題
Google CloudプロジェクトにおいてVPC-SC（VPC Service Controls）を導入すると、Google SpreadSheetのConnected Sheetsでの定期的なクエリ実行が制限されてしまいます。
要因
Connected Sheetsの定期更新ではエンドユーザーのIPアドレスが反映されないため、VPC-SCのアクセスレベルのポリシーに違反してしまいます。
参考：
注: コネクテッド シートの定期更新には、IP アドレスやデバイス情報などのエンドユーザー コンテキストは反映されません。エンドユーザー コンテキストを使用してアクセスを制限する VPC Service Controls の境界では、定期更新が失敗します。
https://cloud.google.com/bigquery/docs/connected-sheets?hl=ja#vpc-service-controls
そしてConnected Sheetsでは認証情報をサービスアカウントに付け替えることもできないため「アクセスレベルでサービスアカウントを許可する」といった選択肢も選べません。
そこで代替手段として、Google Apps Script（GAS）とサービスアカウントを用いてGoogle SpreadSheetからデータを取得する方法を紹介します。
サービスアカウントと鍵の作成
まずはGASで利用するサービスアカウントとそれに紐づく鍵を作成します。
Google Cloudコンソールにて、[IAMと管理] > [サービスアカウント]と移動し、[サービスアカウントの作成]をクリックしサービスアカウント名とIDを任意で入力します
サービスアカウントがBigQueryでジョブ実行できるようにBigQuery ジョブユーザー、BigQuery データ閲覧者の事前定義ロールを付与します。
GAS側で利用する鍵を作成します。
キータイプをJSONとして鍵をダウンロードします。
スクリプトプロパティの設定
GASの[プロジェクトの設定]の中で、以下のような内容でスクリプトプロパティを設定します。
- CLIENT_EMAIL
- サービスアカウントのメールアドレス
- PRIVATE_KEY
- ダウンロードした鍵のJSONの
private_keyの項目にある-----BEGIN PRIVATE KEY-----\nから始まる文字列
- ダウンロードした鍵のJSONの
- PROJECT_ID
- Google CloudのプロジェクトID
ここで設定した値は GASのコードから利用することができます。
GASの設定
以下のGASコードは、BigQueryのクエリ結果をスプレッドシートに書き込むサンプルです。
const target_query = "SELECT * FROM projectName.datasetName.tableName"; //実行するSQL
const target_sheet_name = "sheetName"; // 書き込み先のシート名
const target_mode = 'append'; // 'append'はシートの末尾に追記 'overwrite'はシートを上書き
function queryBigQuery() {
const scriptProperties = PropertiesService.getScriptProperties();
const privateKey = scriptProperties.getProperty('PRIVATE_KEY');
const clientEmail = scriptProperties.getProperty('CLIENT_EMAIL');
const projectId = scriptProperties.getProperty('PROJECT_ID');
const formattedPrivateKey = privateKey.replace(/\\n/g, '\n');
const jwt = createJwt(clientEmail, formattedPrivateKey);
const accessToken = getAccessToken(jwt);
const url = `https://bigquery.googleapis.com/bigquery/v2/projects/${projectId}/queries`;
const query = {
query: target_query,
useLegacySql: false
};
const options = {
method: 'post',
contentType: 'application/json',
headers: {
Authorization: 'Bearer ' + accessToken
},
payload: JSON.stringify(query),
muteHttpExceptions: true
};
let response = UrlFetchApp.fetch(url, options);
let result = JSON.parse(response.getContentText());
if (result.error) {
Logger.log('エラーが発生しました: ' + result.error.message);
return;
}
if (!result.jobComplete) {
const jobId = result.jobReference.jobId;
const location = result.jobReference.location;
const getQueryResultsUrl = `https://bigquery.googleapis.com/bigquery/v2/projects/${projectId}/queries/${jobId}?location=${location}`;
const getOptions = {
method: 'get',
contentType: 'application/json',
headers: {
Authorization: 'Bearer ' + accessToken
},
muteHttpExceptions: true
};
let maxAttempts = 10;
let attempt = 0;
let polling = true;
while (polling && attempt < maxAttempts) {
Utilities.sleep(1000);
response = UrlFetchApp.fetch(getQueryResultsUrl, getOptions);
result = JSON.parse(response.getContentText());
if (result.error) {
Logger.log('エラーが発生しました: ' + result.error.message);
return;
}
if (result.jobComplete) {
polling = false;
} else {
attempt++;
}
}
if (polling) {
Logger.log('クエリの完了待ちがタイムアウトしました。');
return;
}
}
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName(target_sheet_name);
if (!sheet) {
Logger.log(`シート「${target_sheet_name}」が見つかりません。`);
return;
}
const rows = result.rows;
if (rows && rows.length > 0) {
const headers = result.schema.fields.map(field => field.name);
if (target_mode === 'overwrite') {
sheet.clear();
sheet.appendRow(headers);
Logger.log('シートを上書きしました。');
} else if (target_mode === 'append') {
const lastRow = sheet.getLastRow();
if (lastRow === 0) {
sheet.appendRow(headers);
}
}
// データの配列を準備
const data = rows.map(row => row.f.map(field => field.v));
// シートの末尾にデータを一括追加
sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
Logger.log('データが正常に' + (target_mode === 'overwrite' ? '上書き' : '追加') + 'されました。');
} else {
Logger.log('取得されたデータがありません。');
}
}
function getAccessToken(jwt) {
const tokenUrl = 'https://oauth2.googleapis.com/token';
const payload = {
grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
assertion: jwt
};
const options = {
method: 'post',
contentType: 'application/x-www-form-urlencoded',
payload: payload
};
const response = UrlFetchApp.fetch(tokenUrl, options);
const token = JSON.parse(response.getContentText());
return token.access_token;
}
function createJwt(clientEmail, privateKey) {
const header = {
alg: 'RS256',
typ: 'JWT'
};
const now = Math.floor(Date.now() / 1000);
const payload = {
iss: clientEmail,
scope: 'https://www.googleapis.com/auth/bigquery',
aud: 'https://oauth2.googleapis.com/token',
exp: now + 3600,
iat: now
};
const base64UrlEncode = (obj) => {
return Utilities.base64EncodeWebSafe(JSON.stringify(obj)).replace(/=+$/, '');
};
const unsignedJwt = base64UrlEncode(header) + '.' + base64UrlEncode(payload);
const signatureBytes = Utilities.computeRsaSha256Signature(unsignedJwt, privateKey);
const signature = Utilities.base64EncodeWebSafe(signatureBytes).replace(/=+$/, '');
return unsignedJwt + '.' + signature;
}
ポイント
- GASに直接SQLを記述すると、保守性や可読性が低下するため、SQLはBigQuery側のビューとして保持し、GAS側では SELECT * で参照する形が良いと思います。
- BigQuery側の処理（クエリ実行）は非同期になるため、処理の状況を都度ポーリングで問い合わせています。
- BigQuery側での処理に時間がかかるようなSQLはGAS側でタイムアウトしてしまうため、待機時間を長くするかSQL自体を見直す必要があります。
まとめ
このようにGASとサービスアカウントを利用することで、VPC-SC環境下でもSpreadsheetからBigQueryに対してのデータ取得を自動実行できるようになります。
※補足
VPC-SCの境界を超えた通信を許可するためには、GASだけではなくインフラとして以下の設定が必要です。
- アクセスポリシーでのサービスアカウントの追加
- 境界ポリシーでのEgress設定（外部への通信許可）