課題

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 から始まる文字列
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設定（外部への通信許可）