GASを使ってサービスアカウント経由でBigQueryのデータを定期取得する

## 課題
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を任意で入力します

![スクリーンショット 2024-10-21 15.20.05（2）.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/12c361c7-4ef2-71b6-23ce-d1421e693ca3.png)

サービスアカウントがBigQueryでジョブ実行できるようにBigQuery ジョブユーザー、BigQuery データ閲覧者の事前定義ロールを付与します。

![スクリーンショット 2024-10-21 15.21.00（2）.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/fb21832c-e323-61a9-5426-29cb89361e0c.png)

GAS側で利用する鍵を作成します。

![スクリーンショット 2024-10-21 15.21.35（2）.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/e2c337a3-93f8-d851-d5b7-3fe1f2ab8eec.png)


キータイプをJSONとして鍵をダウンロードします。

![スクリーンショット 2024-10-21 15.22.05（2）.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/c358559d-ba3f-3f3a-f9ea-2ceecb424b58.png)

## スクリプトプロパティの設定
GASの[プロジェクトの設定]の中で、以下のような内容でスクリプトプロパティを設定します。

- CLIENT_EMAIL
    - サービスアカウントのメールアドレス
- PRIVATE_KEY
    - ダウンロードした鍵のJSONの`private_key`の項目にある `-----BEGIN PRIVATE KEY-----\n` から始まる文字列
- PROJECT_ID
    - Google CloudのプロジェクトID




![スクリーンショット 2024-10-21 16.22.12（2）.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/147689/1a122afd-7d31-96e3-1b46-831eb10e9895.png)

ここで設定した値は GASのコードから利用することができます。

## GASの設定

以下のGASコードは、BigQueryのクエリ結果をスプレッドシートに書き込むサンプルです。

```javascript
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だけではなくインフラとして以下の設定が必要です。

1. アクセスポリシーでのサービスアカウントの追加
2. 境界ポリシーでのEgress設定（外部への通信許可）