🌞
スプレッドシートでGCPのoauth認証付きcloud runを使用する
要件
家計簿作っててスプレッドシートを簡易DBとして使用しているのですが、簡易的なクエリ結果の応答がスプレッドシート上で手早く欲しかったので調査、実装してみました。
expected: スプレッドシートから関数の形で認証付きのcloud runの関数を使いたかった
-
=CLOUD_RUN_FUNC(A1)とかやったらcloud runにA1をpostしてoutputが帰ってくるイメージ - うっかり公開も乱用もされたくないので認証をつける
- そのためbasicとかではなくgoogle accountのoauthで
actual: おおよそ実現できたがマクロの形
- セルを選択し、メニューバーのマクロから指定すると選択範囲を入力とし出力で置換される
- 認証の箇所が解決できず
- 関数の実行形式ではidentity_token()がnullだった
- マクロの実行形式ではきちんと取得できている
- GASのデバッグでも取得できている
- 認証方式が違うのか？
- oauth周りが個人とworkspaceで違う？らしく、workspaceならできるのかも(未検証)
手順
gcp、サーバー用コンテナ、GASの3箇所で作業が必要でした。
gcp
- 以下を同一プロジェクトで作成
-
cloud runを作成
- 起動元に許可するアカウントを紐づけるのがポイント
- これのリストが許可アカウントリストになる
-
oauthの構成
- 本記事特有の処理はないです
- projectでuniqueなので他で使ってる場合は要調整
- 画面に従ってポチポチ
- google auth platform -> 対象(画面)
- 公開ステータス: テスト中、ユーザーの種類: 外部
- テストユーザー
- oauthの許可アカウントリスト
- 100までらしいけどよっぽど足りるはず
- google auth platform -> 対象(画面)
-
コンテナ
- 今回はsimpleなflask
- I/O
- 選択範囲は二次元配列で受け取る
- 結果も二次元配列で
- 形状はGAS側で調整できるが加工したくないのでそのまま
サンプル実装
import os
from flask import Flask, request, jsonify
app = Flask(__name__)
@app.route('/', methods=['POST'])
def hello_world():
# dataは2次元配列で渡される
# A1:B3 -> [[A1, A2, A3], [B1, B2, B3]]
data = request.json['data']
return jsonify({'data': _inner_func(data)})
def _inner_func(data_array):
return [[f"Hello, {data}" for data in data_list] for data_list in data_array]
if __name__ == '__main__':
port = int(os.environ.get('PORT', 8080))
app.run(host='0.0.0.0', port=port)
GAS
- スプシ -> ツールバーの「拡張機能」-> apps script
- 新規jsで以下のコードを貼り付け
- 「プロジェクトの設定」
- appsscript.jsonを表示にチェック
- エディタで権限とマクロ名を追加
- 「Google Cloud Platform（GCP）プロジェクト」でcloud run, oauthを作成したプロジェクトを選択
- appsscript.jsonを表示にチェック
- 画面右上「デプロイ」-> 新しいデプロイ
コード.gs
function CLOUD_FUNCTION_MACRO(range) {
const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const active_range = ss.getActiveRange();
const values = active_range.getValues();
const token = ScriptApp.getIdentityToken();
const response = UrlFetchApp.fetch('https://`YOUR-CLOUD-RUN-URL`.a.run.app', {
method: 'POST',
headers: {
'Authorization': 'Bearer '+token,
'Content-Type': 'application/json'
},
payload: JSON.stringify({
data: values,
})
});
const res = JSON.parse(response.getContentText());
console.log(res.data);
active_range.setValues(res.data);
}
appsscript.json
{
"timeZone": "Asia/Tokyo",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
+ "oauthScopes": [
+ "openid",
+ "https://www.googleapis.com/auth/script.external_request",
+ "https://www.googleapis.com/auth/spreadsheets"
+ ],
+ "sheets": {
+ "macros": [{
+ "menuName": "CLOUD_FUNCTION_MACRO",
+ "functionName": "CLOUD_FUNCTION_MACRO"
+ }]
+ }
}
実行
- スプシに戻る
- マクロ -> インポートで貼り付けた関数名が表示されているので選択
- するとマクロの選択肢に追加で表示される
- 範囲選択してマクロ実行するとcloud run実行結果で置換される
- 初回はgoogleの認証窓が出てくる
- 信頼されてない〜とか許容して権限付与〜とかチェックして続行
- サンプル動画は認証後
面倒さ
- たまにマクロをインポートし直さなければいけない
- appsscript.json編集時か？特定できていない
- 同様にgoogleの認証窓もデプロイのたびに出てくる
うれしさ
- gasが漏れてもユーザー認可はこちらで指定できる
- pythonコードを使用できるので柔軟
- DBに問い合わせたりBQに問い合わせたり計算したり
- (マクロなら)関数と違い単一の実行であること
- コストの不安が少ない
- 前の記事で触れたように、chromeはバックグラウンドで定期的にリロードしてそうな挙動
- 関数はその都度呼ばれそうで、内部でコストのかかる処理をやってたりすると悲惨なことに
未知なところ
- 関数とマクロで認証は何が違うんだろうか
- マクロのタイムアウト設定はありそう
- 重たい計算やDB, BQへの重たいクエリの場合は考慮すべきか
終わりに
スプレッドシートからcloud runを認証付きでマクロとして使用するところまで到達しました。
スプレッドシートを集計に使用する業務にも応用できそうな気がします。
認証付きなのでうっかり流出しても大惨事にならないのがポイント高いです。
GCPだからかスプレッドシートに対する認証周りもそこまでは手間ではなかった感じです。
最後までお読みいただきありがとうございました。
Discussion