[GAS × BigQuery] サクッと入門・クエリの実行🔥

こんにちは。
フロントエンドエンジニアのみつです。

GAS(Google Apps Script)からBigQueryを実行したい時の自分用チートシート。

SQLをBigQueryの管理画面で書いて実行の確認が取れたら、これを使うのみ！！

という関数がなにやら出来たっぽいのでそのメモです。

:::note warn
- GASエディタで、BigQuery APIをONにしている必要があります。
- 基本的に、スプレッドシートに紐づくGASを操作するためのコード（コンテナバインド型）を想定しています。
:::

## 目次
- [共通の値を書いておく:writing\_hand:](#共通の値を書いておくwriting_hand)
- [BigQueryにクエリを投げる:writing\_hand:](#bigqueryにクエリを投げるwriting_hand)
- [クエリ結果を受け取って、データを抽出する:writing\_hand:](#クエリ結果を受け取ってデータを抽出するwriting_hand)
- [使用例:writing\_hand:](#使用例writing_hand)
  - [sample関数](#sample関数)
  - [sample関数 実行キャプチャ](#sample関数-実行キャプチャ)
- [まとめ:writing\_hand:](#まとめwriting_hand)
- [参考URL](#参考url)


## 共通の値を書いておく:writing_hand:
```javascript
const getBigQueryVariables = () => {
  const jobDetailsTemplate = {
    status: "",
    statusMessage: "",
    errorMessage: "",
    result: null,
  };

  return {
    jobDetailsTemplate,
  };
};
```
projectIDとかも共通している値があればこの中に入れちゃうの良さそうかも、と思ったり！

## BigQueryにクエリを投げる:writing_hand:

**jobs.query**を使います。

> Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout.

また、numRetriesで**3回までは失敗してもリトライ**するようにしています。
```javascript
const customJobQuery = (projectID, sql) => {
  const bigqueryVariables = getBigQueryVariables();
  const jobDetails = bigqueryVariables.jobDetailsTemplate;

  try {
    jobDetails.result = BigQuery.Jobs.query(
      {
        useLegacySql: false,
        query: sql,
      },
      projectID
    );

    let numRetries = 0;
    while (
      (jobDetails.result["jobComplete"] !== true ||
        jobDetails.result["numDmlAffectedRows"] !== 0) &&
      numRetries < 4
    ) {
      Utilities.sleep(500);

      jobDetails.result = BigQuery.Jobs.query(
        {
          useLegacySql: false,
          query: sql,
        },
        projectID
      );

      numRetries += 1;
    }

    if (jobDetails.result["jobComplete"] === true) {
      jobDetails.status = "success";
      jobDetails.statusMessage = "success"; // TODO: Change message to be more descriptive
    }
  } catch (e) {
    jobDetails.status = "error";
    jobDetails.statusMessage = "error";
    jobDetails.errorMessage = e.message;
  }

  return jobDetails;
};
```

## クエリ結果を受け取って、データを抽出する:writing_hand:
jobDetailsのresultに、
BigQuery.Jobs.queryの結果が入っているので、取り出して値だけにしてあげるのも一応。
```javascript
const customJobQueryGetResult = (jobDetails) => {
  const rows = jobDetails.result["rows"].map((row) => {
    return row["f"].map((cell) => cell["v"]);
  });
  return rows;
};
```

## 使用例:writing_hand:
### sample関数
```javascript
const sample = () => {
  const projectID = "project-id";
  const sql = "SELECT ... FROM ...";

  const jobDetails = customJobQuery(projectID, sql);
  const rows = customJobQueryGetResult(jobDetails);

  console.log(rows);
};
```

### sample関数 実行キャプチャ
※BigQueryAPIをONにしておく必要があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1841912/1b6b516a-dd3b-368c-9684-9cf1ef3fcb5f.png)



## まとめ:writing_hand:
できるだけペタっと貼ってそのまま使えるような状態を目指しました〜！

projectIDとSQLだけ修正してそのまま既存のプロジェクトにご利用ください:relaxed:

自分も使います:relaxed:

おわり。

## 参考URL

https://cloud.google.com/bigquery/docs/reference/rest

https://developers.google.com/apps-script/advanced/bigquery?hl=ja