GASからサービスアカウントを使ってBigQueryを叩けるようにする

2023.09.12Update

上原

はじめに

こんにちは、会員システムグループの上原です。
本記事ではサービスアカウントを使ったGoogle Apps Script(GAS)とGoogle Cloud BigQueryの連携方法を紹介していきます。

やりたいこと

今回、BigQueryを叩いてとってきたデータをスプレッドシートにまとめる処理を行う、GASスクリプトを改修していきます。
GASでBigQueryサービスを使用すると簡単にBigQueryを叩けますが、その際に実行ユーザーの権限が使用されるため、もしもそのユーザーが退職した時にスクリプトを実行できなくなって困ります。これは特にインストーラブルトリガーで定期実行設定しているようなスクリプトで問題になります
(参考:https://note.com/0375/n/n3929ccfca5fc)。
この問題の解決策として、GCPからサービスアカウント(アプリケーション向けのユーザー)を払い出し、GASにその認証情報を使用させます。
これによりサービスアカウントで実行されるようになるので、属人化を排除することができます。
ということで、GCPから払い出したサービスアカウントを使ってGASからBigQueryを叩けるようにしていきます。

やり方

[GCP] サービスアカウントを追加する

GCPのIAMと管理でサービスアカウントを開きます
サービスアカウントの作成をクリックし、名前や説明を入力します。

作成して続行をクリックします
ロールを適用します
- 以下のロールがあればいいです
  - BigQueryジョブユーザー(roles/bigquery.jobUser)
  - BigQueryデータ閲覧者(roles/bigquery.dataViewer)

完了をクリックします

[GCP] サービスアカウントの認証情報を払い出す

再びGCPのIAMと管理でサービスアカウントを開き、作成されたサービスアカウントをクリックします
キータブに切り替え、鍵を追加をクリックし新しい鍵を作成をクリックします

キーのタイプとしてjsonを選び作成をクリックします

このタイミングでjsonが勝手にダウンロードされるはずです。中身を確認すると秘密鍵の内容を見ることができます。

{

"type": "service_account",

"project_id": "gcp_example_id",

"private_key_id": "12345678",

"private_key": "-----BEGIN PRIVATE KEY-----\n秘密鍵の中身\n-----END PRIVATE KEY-----\n",

"client_email": "daily-gas-bigquery@gcp_example_id.iam.gserviceaccount.com",

"client_id": "12345678",

"auth_uri": "https://accounts.google.com/o/oauth2/auth",

"token_uri": "https://oauth2.googleapis.com/token",

"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",

"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/daily-gas-bigquery%40gcp_example_id.iam.gserviceaccount.com",

"universe_domain": "googleapis.com"

}

この認証情報は再発行できない(作り直す必要がある)かつGASの設定で使うので大切に保管しておきましょう。

[GAS] OAuth用のライブラリを追加する

GASでEditorの画面からLibrariesをクリックします。OAuth用のライブラリを追加します。

Librariesをクリックし、Script IDを入力します。
- ScriptIDは以下のGitHubレポジトリに書いてあります。2023/09/08現在は1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDFです。
https://github.com/googleworkspace/apps-script-oauth2

Lookupをクリック後Addをクリックします。
- VersionやIdentifierはそのままでokです。

[GAS] jsonに書いてある権限情報をScript Propertiesに設定する

Project Settingsに移動しScript Propertiesに変数を追加します。
- BIGQUERY_CLIENT_EMAILADDRESS
  - サービスアカウントのメールアドレスダウンロードしたjsonのclient_emailに書いてあります
  BIGQUERY_CLIENT_PRIVATEKEY
  - サービスアカウントの秘密鍵ダウンロードしたjsonのprivate_keyに書いてあります

[GAS] BigQueryを叩くための関数を追加する

editorに戻りbigquery.gsを作成します。
bigquery.gsに以下を貼り付け、gcp_example_idの箇所を自分のプロジェクトIDに直してください。

function get_bigquery_service() {

// https://github.com/googleworkspace/apps-script-oauth2/issues/122#issuecomment-507436277

// BIGQUERY_CLIENT_PRIVATEKEYの\nが\\nとなって改行がうまく反映されないため、\\nを\nにreplaceします

const PRIVATE_KEY = PropertiesService.getScriptProperties().getProperty("BIGQUERY_CLIENT_PRIVATEKEY").replace(/\\n/g, '\n');

return OAuth2.createService("bigquery")

.setTokenUrl("https://accounts.google.com/o/oauth2/token")

.setPrivateKey(PRIVATE_KEY)

.setIssuer(PropertiesService.getScriptProperties().getProperty("BIGQUERY_CLIENT_EMAILADDRESS"))

.setPropertyStore(PropertiesService.getScriptProperties())

.setScope("https://www.googleapis.com/auth/bigquery");

}

function create_request_option(method, payload) {

const service = get_bigquery_service();

if (service.hasAccess()) {

return {

"method": method,

"muteHttpExceptions": true,

"contentType": "application/json",

"payload": JSON.stringify(payload),

"headers": { Authorization: "Bearer " + service.getAccessToken() }

};

} else {

Logger.log(service.getLastError());

}

function create_query(query) {

// https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query

// TODO: gcp_example_idを書き直してください

const url = "https://bigquery.googleapis.com/bigquery/v2/projects/{gcp_example_id}/queries"

const payload = { query: query, useLegacySql: false };

const fetch_options = create_request_option("POST", payload)

const response = UrlFetchApp.fetch(url, fetch_options)

const response_json = JSON.parse(response)

Logger.log(response_json)

return response_json

}

function get_query_results(jobId, pageToken) {

// https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/getQueryResults

// TODO: gcp_example_idを書き直してください。

let url = `https://bigquery.googleapis.com/bigquery/v2/projects/{gcp_example_id}/queries/${jobId}`

if (pageToken != null) {

url = `${url}?pageToken=${pageToken}`

}

const payload = {}

const fetch_options = create_request_option("POST")

const response = UrlFetchApp(url, fetch_options)

const response_json = JSON.parse(response)

Logger.log(response_json)

return response_json

}

[GAS] コードの修正

BigQueryサービスにアクセスする箇所で新規に作成した関数を呼び出すよう置き換えます。
- BigQueryサービスから帰ってくる値とレスポンスは同じはずなので、他に修正は必要ないはずです

// BigQueryサービスを使った場合の書き方

// var queryResults = BigQuery.Jobs.query(request, "example-id");

var queryResults = create_query(queryToBigQuery);

// BigQueryサービスを使った場合の書き方

//queryResults = BigQuery.Jobs.getQueryResults('example-id', jobId, {

// pageToken: queryResults.pageToken

//});

queryResults = get_query_results(jobId, queryResults.pageToken);

典型的には以下のようなコードになるはずです

// BigQueryへのクエリを書く

const querytobigquery = "select * from ~~~~";

// BigQueryにリクエストを送る

var queryresults = create_query(querytobigquery);

var jobid = queryresults.jobreference.jobid;

// クエリが完了するまで待たないとエラーになる可能性があるので完了を待つ

var sleeptimems = 500;

while (!queryresults.jobcomplete) {

utilities.sleep(sleeptimems);

sleeptimems *= 2;

queryresults = get_query_results(jobid, null);

}

// クエリの結果からすべてのレコードを取得する

var rows = queryresults.rows;

while (queryresults.pagetoken) {

queryresults = get_query_results(jobid, queryresults.pagetoken);

rows = rows.concat(queryresults.rows);

}

// rowsを処理してスプシに入れる

(もしあれば)ServicesからBigQueryを削除します

[GAS] 実行してみよう

実行してみる
- これで自分のアカウントではなくサービスアカウントの権限でBigQueryが叩かれるようになります
- どういった権限で叩かれているかはGCPのログを確認してみてください
これでうまく動くはずですが、うまく動かない箇所があれば、以下を確認してみてください
- サービスアカウントが作成されているかどうか、足りないロールがないか
- GASに設定した認証情報が正しいかどうか

最後に

以上、BigQueryをGASからサービスアカウントで叩く方法をまとめてみました。
ネット上にある情報を参考にしても動かなくて困っていたのですが、この記事通りにすれば動くはず…！というのをまとめてみたので、もしお役に立てれば幸いです。