はじめに
こんにちは、筧( @TakaakiKakei )です。
Google スプレッドシートをインターフェイスにして、 Google Apps Script 経由で AWS 環境にリクエストを送信する、 社内向けツールを開発する機会がありました。
今後も繰り返し使えそうな処理だったので、開発したコードを汎化してご紹介します。
前提
紹介するコードは、下記処理を行います。
- Google スプレッドシートにインプット情報を記載
- メニューバーからツール実行ボタンを押下
- Google Apps Script が起動
- Google Apps Script から Amazon API Gateway にリクエストを投げる
- Google Apps Script がインプット情報を削除＆別シートに履歴を記録
また、開発には Google Apps Script のコマンドラインツールである google/clasp を利用します。 言語は TypeScript で書きます。
コード
clasp/dev/src/invoke.ts
type Item = {
requester: string;
target_accountid: string;
before_payer_name: string;
after_payer_name: string;
skip_supportcase: string;
};
type Items = Item[];
const exec = () => {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const inputSheet = ss.getSheetByName('シート1');
if (!inputSheet) {
throw new Error('Not found inputSheet');
}
// 環境変数（ステージによって変更する）
const props = PropertiesService.getScriptProperties();
const APIKEY = props.getProperty('APIKEY');
const URL = props.getProperty('URL');
if (!APIKEY) {
throw new Error('Not found APIKEY of property');
}
if (!URL) {
throw new Error('Not found URL of property');
}
// データ入力シートのデータ取得
const inputSheetDataRange = inputSheet.getDataRange();
const arrays = inputSheetDataRange.getValues();
const arraysLen = arrays.length;
// 処理対象の行がなければ終了
if (arraysLen <= 1) {
Logger.log('None');
return;
}
// 二次元配列のデータをJSON形式に変換
const [header, ...rows] = arrays;
const _json = rows.map((row) =>
row.reduce((acc, cell, i) => ({ ...acc, [header[i]]: String(cell) }), {})
);
// APIGW へのリクエスト
const payload = JSON.stringify(_json);
Logger.log(`payload:\n${payload}`);
const response = UrlFetchApp.fetch(URL, {
method: 'post',
contentType: 'application/json',
payload: payload,
headers: {
'x-api-key': APIKEY,
},
});
Logger.log(`response:\n${response}`);
// 実行履歴シートの存在有無を確認して、なければ作成
const today = new Date();
const year = today.getFullYear();
const historySheetName = `履歴${year}`;
let historySheet = ss.getSheetByName(historySheetName);
if (!historySheet) {
// 実行履歴シートの作成
let newSheet = ss.insertSheet();
newSheet.setName(historySheetName);
if (!newSheet) {
throw new Error('Not found and Create historySheet');
}
historySheet = newSheet;
}
// 実行履歴シートに処理データを転記
const historySheetLastRow = historySheet.getLastRow();
inputSheetDataRange.copyTo(
historySheet.getRange(historySheetLastRow + 1, 1),
{ contentsOnly: true }
);
// データ入力用シートのデータ削除
inputSheet.deleteRows(2, arraysLen - 1);
return;
};
// スプレッドシートにツール実行ボタン表示
const onOpen = () => {
const ui = SpreadsheetApp.getUi();
//メニュー名を決定
const menu = ui.createMenu('実行メニュー');
//メニューに実行ボタン名と関数を割り当て: その1
menu.addItem('Payer移動自動化ツール実行', 'exec');
//スプレッドシートに反映
menu.addToUi();
};デプロイ方法
API の有効化
デプロイ先の Google アカウントにログインします。 下記から Google Apps Script API を有効化します。
clasp のインストール
cd {プロジェクトフォルダ}
npm i @google/clasp
npm install @types/google-apps-scriptプロジェクトの作成
mkdir {ステージ}
cd {ステージ}
clasp login
clasp create --type sheetsファイルの編集
tsconfig.json を下記の通り作成します。
{
"compilerOptions": {
"lib": ["esnext"],
"experimentalDecorators": true,
"strict": true
}
}appsscrript.json を下記の通り編集します。
{
"timeZone": "Asia/Tokyo",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}src/invoke.ts を作成して、前述の TypeScript のコードを貼り付けます。
コードの push
clasp pushプロパティの設定
clasp openGoogle Apps Script の画面が開いたら、設定画面のスクリプトプロパティにプロパティを追加します。
- APIKEY: {Amazon API Gateway の API キー}
- URL: {Amazon API Gateway のエンドポイント}
動かしてみる
clasp で作成したスプレッドシートを開きます。 データ入力シート用の"シート 1"を編集します。 下図のように、1 行目にヘッダーを、2 行目以降に値を追加します。
メニューバーの"実行メニュー"から"ツール実行"を選択します。
"シート 1"から入力したデータが削除され、"履歴 YYYY シート"に実行履歴が追加で記録されます。
Amazon API Gateway には以下のようなリクエストボディが送信されます。
[
{
"requester": "kakei",
"target_accountid": "0123456789014",
"before_payer_name": "Hoge",
"after_payer_name": "Fuga",
"skip_supportcase": ""
},
{
"requester": "kakei",
"target_accountid": "0123456789015",
"before_payer_name": "Hoge",
"after_payer_name": "Piyo",
"skip_supportcase": ""
},
{
"requester": "kakei",
"target_accountid": "0123456789016",
"before_payer_name": "Fuga",
"after_payer_name": "Hoge",
"skip_supportcase": ""
}
]おわりに
最後まで読んでいただきありがとうございます。
本コードが皆さんの役に立つと幸いです。 デプロイで躓くことがあれば、後述の参考ブログをご覧ください。
それではまた！
2
