これはTypeScriptアドベントカレンダー2020の23日目の記事です。
Office Scripts使って単純業務効率化していこうという記事です。
Qiita初投稿＆締め切りギリギリで書いてるのでがばってる可能性高しです。
ことの発端
普段はサービス企画をしており職種としてはエンジニアとは呼べないのですが、業務を色々改善するにはコードは書けるに越したことはないので、ちょこちょこ書いてます。
今のチームに入ったのは最近なんですが、かなりレガシーかつ属人的な作業が多くこれはなかなか困ったものだとなってました。
状況
- チームでコード書くのは私一人
- 保守するのも私一人
- 人事異動激しいのでいつでも抜けれるように,
- でも業務は減らしたい
業務内容（雑）
- とあるexcelデータから月次で数十-１００件程度取得
- 行ごとにデータを確認して、いい感じに集計、文章に整形してメール送付
- いろんな業務条件が重なっていて行ごとのif-else条件のようなものが発生し、条件ごとの文章を作成しないといけない
- 目視でやるとダブルチェックで毎月10時間弱かかる
かなり微妙な立ち位置かつ、自由気ままにできるわけでもないのですが、せっかく何か業務改革するなら新しいものの有用性を試したいですが以下の３パターンから選択することにしました。
1. アプリ開発
一番何でもできて一番時間かかります。
個人的にはこういうところで技術力や感覚をキープできると嬉しいですが、こういう業務に割けるのは多くて１０％です。さすがに１０％稼働でアプリ一個作るのは時間の無駄なので却下
2. Power automate
社内でも利用率が高く、めっちゃ便利です。私も単純なメール通知や、Teamsのデータをいじるときなどは使います。
実際大手企業なんかだとO365(M365)が全社に入ってることもあり、相性は抜群ですよね。
ただできることにもそれなりに制限はありそうです。
3. Office Scripts
excelをいい感じにいじくるには今までVBAを使われてる印象がありましたが、最近はTypeScriptでかけるという素晴らしいやつがMicrosoft謹製で開発されています。
よくよく考えるとTypeScript自体もMicrosoftですね。
Office スクリプト ドキュメント - Office Scripts
ただプレビュー版のようなので、ガチガチの商用システムに乗せるのはやめた方良さそうです。
※2020.12時点での仕様をベースに記載しています。今後のアップデートなどで大きな改修が起こる可能性があるので、ご注意ください。
実際に書いていく
対応しているexcel onlineだとこんな感じの画面が出てきます。
※自動化タブがない場合は組織設定ができてないので、管理者に問い合わせてください。
1. 操作を記録
これを最初は使うことをおすすめします。
理由は普段通りexcelを触るだけでよしなにコードを生成してくれるからです。
後続の作業をする前にいろんな作業を試してみて、業務とコードを照らし合わせることが出来ます。
また、コードを書ける人とexcelの業務操作ができる人が別れている場合にもこのやり方を別の人に実施してもらうことで、手間が省けますね。
2. コードエディター
上である程度コードの雛形ができてきたら、記述してきます。
詳しくは書ききれないですが、TypeScriptの記法がある程度わかっていればすぐに編集できると思います。
また、excel onlineから直接実行もできるので、デバッグもしやすいです。
（弊社環境ではなぜか動いたり動かなかったりしました。Power-automateからだと何故か動作するので詳しい方いたらコメントお願いします）
コードの中身について
基本的には以下のmain関数の中にやりたいことを記載していきます。
ExcelSheetに関わらないデータのやり取りやロジックはmain関数から切り離せるのである程度はきれいに記述できます。
function main(workbook: ExcelScript.Workbook) {
// 対象のワークシート
let sheet = workbook.getActiveWorksheet();
// シート名をログ出力
console.log(sheet.getName());
}
データ構造について理解する
ここを読むのが一番手っ取り早いです。
Excel on the web での Office スクリプトのスクリプトの基本事項 (プレビュー)
特にWorkBook、WorkSheet, Range, Tableあたりを抑えておくと良いと思います。
APIリファレンスを読むとプレビュー版とはいえかなりメソッドも充実しているので、普段のexcelでやるような業務はほぼ置換できるのではないでしょうか？
できること、
- データの読み取り、書き取り
もちろんできます。get〇〇, set〇〇というメソッドで検索するとだいたい出てくるので素晴らしい。
そんなに複雑なことしてないので、今の所これがないと困るといったメソッドは特にありませんでした。
function main(workbook: ExcelScript.Workbook) {
// 対象のワークシート.
let selectedSheet = workbook.getActiveWorksheet();
// B1セルのデータを取得.
let range = selectedSheet.getRange("B1");
// ログに出力.
console.log(range.getValue());
}
-
データの書式の変更（色や文字体）
セルごとの色を変えたり、フォントの情報も書き換えられます。業務上需要があるのかはわかりませんが、例えばエラーが出たときに該当セルを赤くするなどしてもいいかもしれないですね。 -
Power Automateとの連携
さっき出てきたPower Automateとの連携もできます。今回の業務ロジックでは月１の処理が必須だったので相性は抜群です。補足ですが、Power Automate単体でそこそこ何でもできるので、特殊な事情がない限りはまずPowerAutomate単体で業務改善できないか試してみてください。 -
組み込みオブジェクトなどの利用
TypeScript組み込みのDateオブジェクトや、Mathなどは利用できました。なので、かんたんな計算や日付に関わる処理は問題なく動作していることを確認しました。
function getCurrentJSTDate(): Date {
let date: Date = new Date();
// JSTに変換
date.setTime(date.getTime() + 1000 * 60 * 60 * 9);
return date
}
- クラスの利用
これは微妙かなと思っていたのですが、特に問題なく出来ました。
main関数の外、内での定義についての挙動の変化なども特に見られなくなりました。
多少複雑なコードを書く場合は積極的に使っていってもいいかと思います。
function main(workbook: ExcelScript.Workbook) {
// 対象のワークシート
let sheet = workbook.getActiveWorksheet();
let currentDate = new CurrentDate()
// 2020
console.log(currentDate.year);
}
class CurrentDate{
year: number
month: number
constructor() {
const date = this.getJSTDate()
this.year = date.getFullYear();
this.month = date.getMonth() + 1;;
}
getJSTDate(): Date {
let date: Date = new Date();
// JSTに変換
date.setTime(date.getTime() + 1000*60*60*9);
return date
}
}
できないこと（多分）
業務の片手間までしか触れない（officeライセンスを持ってないので、自宅で試せない）ため、完全に把握してるわけではないです。そもそも複雑すぎる処理が出てくるようであればOffice Scriptを使うべきではないと思ってるので、そういった業務になった場合は業務ロジック自体を見直しましょう。
- 外部モジュールの参照
- 型定義ファイルの作成など
終わりに
AdventCalenderに書く頃にはできてるやろ！！って思っていました（）
業務が年末で忙しくなってしまったので、ちまちまやって今年度中には完成させたいです。
また、テストとかバージョン管理どうしようかな問題があるのですが、今の所はexcelに関係ないところは切り離して、jestでテストコード書いて、gitでバージョン管理してます。なんかいい感じにやるやりかたあるのかもしれないですが、そこまで調べてないのでOfficeScriptsの記事がいっぱい増えてくれたらいいなという願いを込めて終わります。
あと自分の作ったもの載せたいのですが、業務に深く関わってるものなので、今回は割愛します。
また詳しく理解したり、外に出せるようなものに編集できたらまた書くかもしれないです。