astamuse Lab

astamuse Labとは、アスタミューゼのエンジニアとデザイナーのブログです。アスタミューゼの事業・サービスを支えている知識と舞台裏の今を発信しています。

Google Apps Scriptで脱Excel化!?

f:id:astamuse:20210901103932p:plain お久しぶりでございます。scala等でバックエンドを開発しているaxtstarでございます。

はじめに

DX*1が叫ばれて久しい昨今ですが、日々作成されるデータが json だったり yaml だったりにもともとなっていて、簡単にプログラムで取り込めることはあまりないことだと思います。 人が作るデータ形式は、大体はExcelやcsvまたはスプレッドシートの形式が多いと思います。

私もDXの流れに乗って日々スプレッドシートに向かっております。

当然、人の作成したものですので誤りがあるのはある程度は仕方ないので、できる範囲のチェックを実施していることもあるかと思います。

例えば、文字列を範囲の中から選ぶようにするとか、

f:id:astamuse:20210901080608p:plain
範囲から選択

計算式を埋め込んでチェックをしていたり、

f:id:astamuse:20210901080023p:plain
計算式を埋め込んでチェック

関数を一元管理したい

上記以外にも関数などをスプレッドシートにApp Scriptで追加して効率化していました

例 URLエンコード、URLデコード

/**
 * URLエンコード
 * @param value 対象文字列
 */
function encode(value) {
    return encodeURIComponent(value);
}
/**
 * URLデコード
 * @param value 対象文字列
 */
function decode(value) {
    return decodeURIComponent(value);
}

↑こういうのをスプレッドシートで開いて、コピペで張り付けていました。

f:id:astamuse:20210901090744p:plain
コピペで張り付ける前に開くスクリプトエディタ

チェックすべきスプレッドシートが少なかったころは、このようなやり方でも問題は無かったのですが、多くなってくると、どんどんどんどん、どのスプレッドシートに関数があったかよくわからなくなってきます。 また一度作った関数を改良した場合などはもっとカオスで。。。
せっかく 脱Excel をスプレッドシートで実現したのに、App Scriptがいわゆる メンテ不能なExcelマクロ と同様になってしまうという罠に陥りかけました。

そこで目を付けたのが、Google製の clasp というツール。

github.com

このツールはローカルで作られたApp ScriptのプロジェクトからターゲットのスプレッドシートにApp Scriptを埋め込むことができます。

下記で認証して認可してしまえば、あとはスクリプトのIDを準備すればOKでした。*2

clasp login

許可

f:id:astamuse:20210831171420p:plain
claspに認可を与える図

こういうのを用意(xxxxxxxxxxxxxxxxxxxxxxはスクリプトのID*3が入ります)
.clasp.json

{
    "scriptId":"xxxxxxxxxxxxxxxxxxxxxx",
    "rootDir": "./src"
}

フォルダ構成

.
├── .clasp.json
├── clasp
└── src
     └── Code.js

App Scriptのアップロード

clasp push

f:id:astamuse:20210901092610p:plain

おおすごい、楽だ!!!

何度もやってるバリデーションを汎用化してみた

不思議なもので 、こうやってバージョン管理が簡単にできるようになってくると、関数の修正にもモチベーションが出てきます。

App Scriptで必須チェックや文字列チェック、数値範囲チェックなどの関数を作って、チェック列で視覚的にわかるようにしてみました。

f:id:astamuse:20210901004850p:plain
record_check() 関数を作って入力の不備をお知らせしている図

ダウンロード機能も追加してみた

また、バリデーションの列が追加されてしまったために、ダウンロード時に Excel化 してその列を削除するみたいなことをするのは、本末転倒なのでスプレッドシートから直接CSVへのダウンロードも合わせて作りました。

f:id:astamuse:20210831181140p:plain
ダウンロードしている図

この変更のせいで初回?実行時にGドライブへの権限が求められるようになってしまいました。

どうやったか?

こちらにあげてますのでよろしければ参考にしていただければと思います

github.com

まとめ

  • 関数を一元管理できるのは、イマドキとしては当然なのですが大変すばらしことだった。
  • 認証認可の部分はちょっとややこしいが、イマドキとしては仕方ないことだった。
  • どこにあるかよくわからないものはモチベーションを下げるということがよくわかった。

それではハッピーなDXライフを!!

注意点

AppScriptを実際に実行するには結構認証認可手順が必要です。

  • AppSscriptAPIの実行許可
  • デプロイツール自体の認証認可(これはclaspの話です)
  • AppScriptの認証(初回)
  • 実行時の認可(使用する権限によって)

これだけ必要なのがちょっとつらいところかもしれません。

<<参考>>

AppSscriptAPIの実行許可

f:id:astamuse:20210831170959p:plain
AppSscriptの実行許可

*1:デジタルトランスフォーメション

*2:App Scriptを全部書き換えてしまうハズなのでその辺は最初注意が必要です。

*3:スプレッドシートのIDではなくスクリプトのIDです

Copyright © astamuse company, ltd. all rights reserved.