【Workflows】アクセス権があるはずのGoogleスプレッドシートにアクセスできない時の対処法

# 事象

先日こんなことがありました。

- BigQuery に Google スプレッドシートを参照する外部テーブルがある
- その外部テーブルを参照して別のテーブルを更新したい
- BigQuery のジョブは Workflows から発行する

図にするとこんな感じです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1217064/0d5f80db-d79f-e129-abe4-b94e4d52efd6.png)

ワークフローを実行すると、クエリが失敗。
アクセスが拒否されたとのこと。

```text
Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.
```

試しにコンソールから自分でクエリを実行してみると成功します。
みなさんは真っ先にこう考えるでしょう。
「サービスアカウントにスプレッドシートの閲覧権限がないんでしょ」と。

確かに、ワークフローを動かすサービスアカウントにスプレッドシートの閲覧権限をつけなければなりません。
そうでないとクエリ実行時に外部テーブルを通してスプレッドシートにアクセスができないのです。

しかし今回はきちんとサービスアカウントに閲覧権限がついています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1217064/75515ecb-1341-d2b9-8707-7e7f024d6cb6.png)


# 原因

もう一度エラー文を見てみましょう。

```text
Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.
```

**ドライブの資格情報を取得中にアクセスが拒否されました。**

そう、スプレッドシートではなく Google Drive へアクセスできていなかったのです。

スプレッドシートは Google Drive 上に存在します。
外部からアクセスするには、Google Drive API を通さなければならないようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1217064/e90f9960-312b-2639-4c2e-4bd844440fcb.png)


# 対処法

これは修正前のワークフローの yaml です。

```yaml
main:
  steps:
    # 1. GCSからSQLファイルを取得する
    - get_sql_file:
        call: googleapis.storage.v1.objects.get
        args:
          bucket: "sql_for_workflow"
          object: "replace_table.sql"
          alt: "media"
        result: sql_file_data

    # 2. SQLをデコードする
    - decode_sql:
        assign:
          - sql_query: ${text.decode(sql_file_data)}

    # 3. BigQuery でクエリを実行する
    - run_bigquery:
        call: googleapis.bigquery.v2.jobs.insert
        args:
          projectId: ${sys.get_env("PROJECT_ID")}
          body:
            configuration:
              query:
                query: ${sql_query}
                useLegacySql: false
            jobReference:
              location: "asia-northeast1"
        result: query_result
```

BigQuery から Google Drive へのアクセスできるように**コネクタのスコープを明示的に指定します。**

- `https://www.googleapis.com/auth/bigquery` : BigQuery API にアクセスするためのスコープ
- `https://www.googleapis.com/auth/drive.readonly` : Google Drive の読み取り専用アクセスを許可するスコープ

スコープは他にも色々あり、適切なものを選びます。詳細は[こちら](https://developers.google.com/identity/protocols/oauth2/scopes?hl=ja#drive)で確認できます。

修正後の yaml

```yaml
main:
  steps:
    # 1. GCSからSQLファイルを取得する
    - get_sql_file:
        call: googleapis.storage.v1.objects.get
        args:
          bucket: "sql_for_workflow"
          object: "replace_table.sql"
          alt: "media"
        result: sql_file_data

    # 2. SQLをデコードする
    - decode_sql:
        assign:
          - sql_query: ${text.decode(sql_file_data)}

    # 3. BigQuery でクエリを実行する
    - run_bigquery:
        call: googleapis.bigquery.v2.jobs.insert
        args:
          projectId: ${sys.get_env("PROJECT_ID")}
          body:
            configuration:
              query:
                query: ${sql_query}
                useLegacySql: false
            jobReference:
              location: "asia-northeast1"
          # 次の2行を追加
          connector_params:
            scopes: "https://www.googleapis.com/auth/bigquery, https://www.googleapis.com/auth/drive.readonly"
        result: query_result
```