何気なく以前作成した「VBAにて2つのExcelシートの差分を取り出してみる」の記事なのですが、気がつくと随分とアクセス数が多いことに気が付きました。確かに支払調書であったり、毎月の給与計算をする上で前月との比較差分を取って、変わっているケースというのを人間の目でチェックするのは、はっきり言って不毛な上に、量が多ければ多いほどミスが発生するリスクが高まるという作業なので、これを自動化できれば楽になる上にミスもなくなる仕事の代表例かもしれません。

そこで、今回Google Apps ScriptにてGoogleスプレッドシートで同じものを作成してみました(但し、Excel版と違って基準元と比較元を並べて表示する機能は今回見送り)。配列で取得して配列で一発書きしてるので、1セル毎に書いてるExcel版よりは理論上はスピードは遥かに上ですが、GAS自体の処理速度が遅いので、果たして・・・

難易度:

今回使用するスプレッドシート等

サンプルデータは、疑似個人情報データ生成サービスを用いて生成しています。比較先のデータはいくつか変更が加えられ、またいくつかの新規データを追加し、いくつかのデータを削除してあります。

Excel版にあった基準元/比較先を並べて表示するスペシャル版機能は搭載していません。

図:基本機能はすべて移植しています

使い方

システム要件

今回のシートは主にマスターデータなどで、2つのシートを比較しその差分を取り出すプログラムです。そのため、以下のルールが存在します。

  1. 1列目はID、2列目は名前などの列を用意します。
  2. 精査対象ファイルの「test」という名前のシートのみをチェック対象にしています。2枚以上ある場合、そのシートを選ぶことはできません。
  3. 1行目は見出しである必要があります。
  4. 見出しは基準元も比較先も同じ見出し列の数、見出し名である必要があります。(Excel版にあったカラム同一性チェックは搭載していません)
  5. マスターチェックであるため、基準元も比較先も、例えば同じ人物のデータが複数存在してはいけません。
  6. 基準元を基準に、比較先のデータに変更のあるセルは赤字で表示されます。また、比較先にしかないデータは追加シートに、基準元にしかないデータは削除シートに抽出されます。
  7. 取り込めるデータは、Googleスプレッドシート形式のみです。Pickerの拡張子を変更すれば、Drive上のxlsxなどからも取得できるかもしれません。

事前準備

使うためには、以下の事前準備が必要です。本アプリケーションはGoogle DriveのSpreadsheetを指定するために、Google Pickerを利用している為、APIキーの取得と設定が必要です。他ですでにPicker API用のキーを取得済みならば、セット以外の作業は不要です。

プロジェクトを作成する

Google Cloud Consoleに行き、ログインします。Cloud Consoleにログインしたら右上のGoogle Cloud Platformの▼をクリックして既存のプロジェクトを開くか?新たに➕記号をクリックしてプロジェクトを作成します。作成まで少々時間が掛かります。

無料アカウントの場合作成できるプロジェクトの数に制限(Google Apps Scriptはちょっと違うみたい)があるので、ホイホイと作らないように注意しましょう。また、APIの利用も上限(クォータ)が付いているので、大量にAPIを叩くと制限に掛かってしまいます。さらに、Cloud SQLのような有償のサービスなども用意されていますが、クレジットカード決済が必要です。

今回、mushroomというプロジェクトを作ってみました。この時、デフォルトでいくつかのAPIが既に有効になっていますが、取り敢えず不要なので、左サイドバーより「APIとサービス」⇒「ダッシュボード」を開き、APIを全部無効にしましょう。

図:まずはプロジェクトを作らないと

図:デフォルトでいくつかのAPIが有効になっちゃってる

APIを有効にする

APIとサービスのダッシュボード上部より「APIとサービスの有効化」よりサービスを探して、有効化します。今回はファイルやフォルダをチョイスする「Google Picker API」を元に進めてみます。検索画面より「Picker」と入れると、Google Picker APIが見つかります。このAPIは引き続き認証情報を作成する必要があります。

図:Google Picker APIはよく利用します

認証情報を作る

引き続き認証情報を作る必要があるAPIがあります。今回のPicker APIも同様です。右上の「認証情報を作成」をクリックするか?左サイドバーの「認証情報」より作成開始します。ここで作成するのはAPIキーと呼ばれる単一のコードで、Google Maps APIなどでもよく使っていた1行の文字列です。

認証情報セクションの認証情報を作成をクリックし、APIキーを選択。これですぐにキーが生成されるのでコピーしておきます。制限を加える場合には、作成したキーを開き、以下の設定を加えます。

  1. APIの制限では、Google Picker APIのみで使えるように制限を加えておきます。
  2. アプリケーションの制限は通常は不要ですが、特定のスプレッドシートでのみ有効にしたいのであれば、HTTPリファラーにてscript.google.comを含んだアドレスの入力が必要です。

図:GASで使うにはひと手間が必要なHTTPリファラーの取得

APIキーをセットする

サンプルのスプレッドシートを開き、メニューより「作業項目」を開き、APIキーを登録を実行。取得したAPIキーを入力して保存すると、使えるようになります。

図:こんな感じのダイアログが使えます

チェック手順

APIキーをセットしたら、メニューより「作業項目」→「作業の実行」をすると、サイドバーが現れます。以下の手順で実行します。

  1. 基準元データの取り込みのデータ選択をクリック
  2. Pickerが開かれるので、システム要件に合致したスプレッドシートファイルを選択・読み込み。
  3. 同じく比較先データの取り込みのデータ選択をクリック
  4. Pickerが開かれるので、システム要件に合致したスプレッドシートファイルを選択・読み込み。
  5. この時点で問題がなければ、基準元・比較先にすでにデータが読み込まれています。
  6. 最後に差分検証の実行の実行ボタンをクリックすると作業開始
  7. 差分のみ抽出の問い合わせに対して、「はい」と答えると、差分があった列の値のみが表示され、「いいえ」の場合は全データが差分抽出に記録されるようになります(どちらもスピードに差はありません)
  8. 差分のあったデータは赤字で表示され、基準元にだけ存在するレコードは削除シートに、比較先にのみ存在するデータは追加シートにそれぞれ記録されます。

図:非常にシンプルな機能のみです

ソースコード

実際に差分チェックを行うコードのみを掲載しています。

  • やってることはひどく単純ですが、全てメモリ上で行っている為、Excel版よりかはシンプルで高速で動く「ハズ」です。
  • JavaScriptのほうがVBAよりも配列操作が楽なので、コード量自体も少ないです。
  • 差分抽出、削除、追加、色設定用の4つの書き込み用配列と一時用配列を用意してpushしたりしています。
  • 色データはメインの値を書き込み後に、setFontColorsにて同じく一括書き込みをさせています。
  • 削除や追加などにデータが無い場合にそなえて、try-catchにてエラートラップを用意しています。
  • なお、スプレッドシートからの値の取得はgetValuesではなくgetDisplayValuesを使っています。日付や時刻などのデータの変換作業や、型の違う値の混在なども対応させる為です。
  • スプレッドシートデータをJSON化してレコード単位で最初のチェックを掛けて同一レコードならば処理をスルーするようにしたら、もうちょっとだけ高速化できるかも。

関連リンク

共有してみる: