Google Apps Scriptを使い日々のコピペ作業から解放できた話 #GoogleAppsScript

こんにちは。Livesense Advent Calendar 2015の15日目を担当しますエンジニアのkurobaraです。
リブセンスでは、会社の口コミサイトである転職会議のメディアチームに所属しています。

昨年に引き続き、一応転職会議メディアを開発することもありますが、以下のことをあいも変わらずやっています。

転職会議メディアに関係する業務システムの開発
サービスの運用監視(障害対応したり)
サービスの改善、不具合対応
etc...

そうそう、そういえばRubyKaigi 2015楽しかったですね。
僕は色々ありましたが、限られた時間内で楽しんできました。
あれだけ大規模なイベントを運用されたスタッフさんには頭が下がる思いです。

と、Rubyの話を引き合いに出しましたが、この記事ではRubyの話は一行も出ないですｗ

コピペ作業辛い・・・

そんな言葉よく聞きますよね。
かく言う自分も定常作業としてのコピペ作業は嫌いです。
(スポットかつ少量なら兎も角ですが・・・)

特にExcelでシートからシートへ毎日作業するのとか苦行ですよね・・・

そんな訳で、そういう業務から解放したときの話をしたいと思います

そもそも何があったか

割りと衝撃的なのですが弊社では、営業だろうが、エンジニアだろうが関係なくSQLを使ったり
SpreadSheetを使って分析をしたりすることが多いです。
(参考: 営業さんまで、社員全員がSQLを使う「越境型組織」ができるまでの3+1のポイント | リブセンス)

その中で、分析作業の一部を実行している営業さんとのある日の会話が、本記事を書くための発端だったりしますw

[営業] (´・ω・｀) 「個々人のSpreadSheetから日々変化するデータの集計コピペ辛い・・・」

[営業] ( ＾ω＾) 「コピペ作業が辛いからボタンひとつでコピペ作業が完了してくれるものが欲しい」

[営業] ( ＾ω＾) 「あ、勿論チームごとに分かれてることが理想な」

[営業] (/ω・＼)ﾁﾗｯ「集計も終わっていると、尚嬉しいなぁ」

[自分] (゜o゜; 「・・・・」

どうやら、日々個々人が記入していたSpreadSheetの内容を集計する必要があり、毎回手動で実行していた模様でした。

会話の内容だけでも(できなくは無さそうですが)これで突き進むと確実に地雷を踏むので、こことかここにあるようなことにならないようきっちり確認を取ります

本当に欲しかったものはなんだろうか？

という訳で、真面目にヒアリングして得られた要件を簡単にまとめてみます

日付とチーム名が入った名前のシートをマスターシートからコピーで集計用のシートが欲しい
集計処理自体はマスターシートに既に存在している
チームがいくつかあるので、チームに応じた集計をして欲しい
シートの内容は各シートの該当項目からのコピーで構わない
但し、行の中にデータの欠損がある場合は、コピーしなくてもよい
コピー先は、項目に応じてコピーをして欲しい
集計は、メニュー選択で実行できるようにして欲しい
集計が完了したら、画面にダイアログでもいいので完了通知が欲しい

実現したいこと自身も殆どがVBAと同じ内容に加えて、
できなくは無さそうだが、スプレッドシートの関数リストだけでは辛そうなので、~~Google版 VBA~~Google Apps Scriptを使用して改善します

Google Apps Scriptとは

そもそもGoogle Apps Scriptとは何者だということで、ドキュメントを見てみます

11 Google apps, 1 platform in the cloud

「11のGoogle Appをクラウド上の１つのプラットフォームで実現」と標榜にあるよう、
Googleが提供するあらゆるサービスを統合処理する一大サーバーサイド・スクリプト環境とでも言えばいいでしょうか。

連携できるサービスは、以下のものです

が、他にも実験的な機能としてBigQueryやGoogle+、YouTubeなども連携出来たりします。
詳細はこちらを確認していただければと思います。

これらをJavascript1.6(一部、1.7, 1.8の新機能も含む)で実装できます。

尤も、SpreadSheetから使うとなると超スゴイVBAぐらいに覚えておけば、多分大丈夫でしょう。

今回の話は、SpreadSheetの操作のみに絞ってGoogle Apps Scriptを使ってみます。
他のサービス連携の話はまた今度。

(スプレッドシートの関数リストにある関数類は、こいつらがラップされていて裏でこいつらが実行されているのかな？)

実現要素の細分化とドキュメント確認

~~VBAで培った頭を元に~~実現に必要な内容を細分化してみます。
(集計は、マスターシートにあるようなので考慮しない方向)

シートのコピー
(コピー対象の)シート範囲選択
データの挿入処理(選択範囲のコピー)
フィルタリング処理
メニュー&ダイアログの表示

ここまでできれば、個別の内容の動作確認コードを合体させるだけなので難しくないですね。

という訳でSpreadSheetのAPIドキュメントを確認しつつ、個別に実装コード向けのコードを書いてみます。

実現技術要素

シートのコピー

まずは簡単なところで、シートのコピーを行いましょう。

やることは簡単で、

シート名を使用して、コピー対象のシートを特定する
コピー対象のシートへ移動する
重複シートを作成する
重複したシートの名前を変更する

var sheet = SpreadsheetApp.getActive().getSheetByName('Your Sheet Name');
SpreadsheetApp.setActiveSheet(sheet);
SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet().setName('New Sheet Name');

SpreadSheetでの操作イメージとしては以下というところでしょうか。

シートに移動して右クリックで「コピーを作成」
コピーしたシートを右クリックで「名前を変更」

当該APIドキュメント

シートの範囲選択

こちらもやることはそんなに難しくないですね。

シート名を使用して、コピー対象のシートを特定する
コピー対象のシートへ移動する
開始のセルから移動範囲を選択
範囲選択したセルの値を全て取得

以下は2Cセルから4Fまでの範囲の値を取得するコードです

var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
var selectedRange = sheet.getRange(2, 3, 3, 4);
var selectedValues = selectedRange.getValues();

注意点として上げるとすれば・・・

row * columnを表現した2次元の配列であること
移動範囲の指定には、指定した位置のセルから範囲にカウントすること

ぐらいでしょうか。
エンジニア的な配列の添字扱いで0からスタートすると痛い目に見る形ですかね

上記のコードで取得できる値は以下のような形になっています。

[ 
  ['2Cセルの値', '2Dセルの値', '2Eセルの値', '2Fセルの値'],
  ['3Cセルの値', '3Dセルの値', '3Eセルの値', '3Fセルの値'],
  ['4Cセルの値', '4Dセルの値', '4Eセルの値', '4Fセルの値']
]

SpreadSheetでの操作イメージとしては以下というところでしょうか。

シートに移動
範囲指定開始位置のセルに移動し、セルの移動数を設定
右クリックで「コピー」

当該APIドキュメント

データの挿入処理(選択範囲のコピー)

これは少々難しい・・・というよりも面倒ですね。

何故かというと、以下のように一旦行を挿入するという処理が入ったあとで、値を設定する必要があるからです

上記で示したような行列を表す挿入用のデータ作成
シート名を使用して、コピー対象のシートを特定する
コピー対象のシートへ移動する
空白行を挿入
挿入先のセルを範囲選択した
範囲選択したセルの値を挿入用のデータを設定

以下のコードは、７行目に挿入データ分の３行追加、7Cから9Fの範囲へデータを設定するという形です。

var data = [ 
  ['7Cセルに設定したい値', '7Dセルに設定したい値', '7Eセルに設定したい値', '7Fセルに設定したい値'],
  ['8Cセルに設定したい値', '8Dセルに設定したい値', '8Eセルに設定したい値', '8Fセルに設定したい値'],
  ['9Cセルに設定したい値', '9Dセルに設定したい値', '9Eセルに設定したい値', '9Fセルに設定したい値']
];

var sheet = SpreadsheetApp.getActive().getSheetByName('Your Sheet Name');
sheet.insertRowsBefore(7, 3);
var selectedRange = sheet.getRange(7, 3, 3, 4);
selectedRange.setValues(data);

因みに、insertが必要なければ範囲を選択してそのまま値を設定してあげればよいです。
また、上記のコードでは指定行よりも前に行を挿入しましたが、後に挿入することも可能です。

当該APIドキュメント

フィルタリング処理

フィルタリング処理の前にバリデーションを付けたほうが良かったり・・・

そうも言えない事情もあったりするので地味ですが、foreachでグルグルループを回しながら確認するしか無いですね

凄く地味ですが、以下のような形でfor文を回すしかないですねorz

以下の例は、カラムが空文字もしくは0で無ければログ出力する形です

var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
var selectedRange = sheet.getRange(2, 3, 3, 4);
var selectedValues = selectedRange.getValues();

var numberOfRow = selectedValues.length;
for (var rowIndex = 0; rowIndex < numberOfRow; rowIndex++) {

  var numberOfColumn = selectedValues[rowIndex].length;
  for (var columnIndex = 0; columnIndex < numberOfColumn; columnIndex++) {
    var columnValue = selectedValues[rowIndex][columnIndex];
    if (columnValue === "" || columnValue === 0){
      continue;
    }
    Logger.log(columnValue);
  }

}

当該APIドキュメント

Rangeクラス

メニュー&ダイアログの表示

SpreadSheetを開いたときにメニューを表示できるようにします。
これは、トリガーを使うようにしましょう

ダイアログは、スクリプトエディタを開いたときに最初からテンプレートとして実装されているものをそのまま活用します

function openDialog() {
  Browser.msgBox("Display Message");
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
                  {
                    name : "MenuName",
                    functionName : "openDialog"
                  }
                ];
  sheet.addMenu("Your Menu", entries);
}

当該APIドキュメント

Simple Triggers

技術要素を組み合わせて使ってみる

前提

そんなに難しくないですが、こういう条件でやってみようと思います。

メニューをつける
完了時にダイアログを表示する
シートAをコピーして別途シートCとする
シートAの5行目は各列を集計したデータを表示する
シートBのデータをシートCの5行目移行に追加する
コピーしたシートCでは、追加したシートBのデータも含めて集計されている
シートはそれぞれ以下のような形とします。

シートA

シートB

ソースコード

これまでの解説を活かしたコードになるため、以下のような形になります。

function sampleCode() {
  // シートAのコピーとしてシートCを作成する
  var sheetA = SpreadsheetApp.getActive().getSheetByName('シートA');
  SpreadsheetApp.setActiveSheet(sheetA);
  SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet().setName('シートC');

  // シートBからコピーするデータを取得する
  var sheetB = SpreadsheetApp.getActive().getSheetByName('シートB');
  var selectedRangeBySheetB = sheetB.getRange(2, 2, 4, 4);
  var selectedValuesBySheetB = selectedRangeBySheetB.getValues();

  // シートCにシートBで選択したデータを挿入する
  var sheetC = SpreadsheetApp.getActive().getSheetByName('シートC');
  sheetC.insertRowsBefore(5, 4);
  var selectedRangeBySheetC = sheetC.getRange(5, 2, 4, 4);
  selectedRangeBySheetC.setValues(selectedValuesBySheetB);

  // ダイアログを表示
  Browser.msgBox("Complete!!");
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {
      name : "Sample",
      functionName : "sampleCode"
    }
  ];
  sheet.addMenu("Advent Calendar", entries);
}

実行結果のシート
メニューも付与されており、コピー元のシートAに集計処理が仕込んであるので無事に集計もできてますね。

まとめ

さくっと組み込んでしまったことで、営業さんの業務時間や作業効率を短縮できたようです。
(営業さんの体感で、1h~1.5hほど&各メンバーへの調整時間も無くなったそう)

久しぶりに~~VBA~~、Google Apps Scriptでプログラミングをしましたが、
普段実装するようなサーバサイドアプリケーションでは無かったので、新鮮でした(ぉぃ

Google App Scriptでコレぐらいの単純なSpreadSheetの操作(各SpreadSheetのデータを集計&コピー、セル移動、シートコピー)ができると、応用として更に下記のような形で情報収集、集計&KPIダッシュボードみたいなこともできるようになります。

集計や作業の自動化を取ることができれば、以下のような施策立案、実施にとりかかる時間も増えてきます！

ぜひ、もっとGoogle Apps Scriptを活用して日常の面倒な仕事を自動化できるようにしましょう!!