Google Apps Scriptを利用してExcel連携を自動化してみた

2022.06.29Update

遠藤

基幹システムグループの遠藤です。

Google Apps Script(GAS)とAWSを利用してExcel管理のデータをシステムで取り込む作業を自動化したのでご紹介したいと思います。
今回はスクリプトファイルの作成や実行ボタン配置などの手順は省いてGASでS3にアップロードするところをメインにご説明します。

経緯

これまでは毎月月末に他部署がメンテしているExcelをSlackやメールで受け取り、システム担当がサーバへ転送、取込みバッチ実行というようなことをしていました。
さらに、システムへの取込みでエラーがあれば差戻してExcelを修正してもらい再連携、再取込み・・・
現在でもこういった運用をしているところは多いのではないでしょうか。
この煩わしいやりとりとや手作業は辛い！やりたくない！ということで自動化することにしました。

全体はこんな構成

GASを使いたいのでExcelからスプレッドシートに変更
更新後にGASを実行することでS3にCSVファイルがアップロードされる

ーーーーーーー今回はここまで説明しますーーーーーーー

ファイルがS3にアップロードされたのを検知してLambdaが動く
結果をSlackで通知、S3から詳細結果ファイルをダウンロードできるようにURLも添付
リトライは何度でもOK
システム担当の出番はなくなりました！

入出力イメージ

このようなスプレッドシートのデータが

CSVファイルに変換されてS3にアップロードされます。

AWS側でやったこと

S3 Bucketを作成
- GASとファイルをやりとりするところです。
GAS用のIAMユーザを作成
- S3にPutできるポリシーをアタッチしてください。
  - s3:PutObject
- GASからアップロードするときにこのアクセスキーが必要になります。

GASのコードはこんな感じ

// スプレッドシートのデータを二次元配列で取得する

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');

var records = sheet.getDataRange().getDisplayValues();

// 配列をCSV形式のオブジェクトに変換

var obj = Utilities.newBlob(records.join('\n'), 'text/csv');

// S3にアップロード（ここでIAMユーザのアクセスキーを使います）

var s3 = S3.getInstance('accesskeyId', 'secretAccesskey');

s3.putObject('bucket', 'path', obj);

S3のライブラリは忘れずにいれてください。
- 今回はS3-for-Google-Apps-Scriptを利用しています。
- 新しいエディタの場合は以下のスクリプトIDで検索すると追加できます。
  - 1Qx-smYQLJ2B6ae7Pncbf_8QdFaNm0f-br4pbDg0DXsJ9mZJPdFcIEkw_

さいごに

今回はGASを利用してS3にファイルをアップロードする方法をご説明しました。
サクッと実装できて運用時間を削減できるのでお試しを。

※サンプルではアクセスキーがベタ書きになっていたり、IAMユーザとS3のアクセス制御については細かく書いておりませんので、セキュリティ面には注意してください。
※ベタ書きしない方法としてはプロパティストアというのがあるようです。

We are hiring!

ニフティでは、さまざまなプロダクトへ挑戦するエンジニアを絶賛募集中です！
ご興味のある方は以下の採用サイトよりお気軽にご連絡ください！

ニフティ株式会社採用情報

Tech TalkやMeetUpも開催しております！
こちらもお気軽にご応募ください！

Event – NIFTY engineering