Google Apps Scriptで2つのスプレッドシートの差分を取り出してみる【GAS】
何気なく以前作成した「VBAにて2つのExcelシートの差分を取り出してみる」の記事なのですが、気がつくと随分とアクセス数が多いことに気が付きました。確かに支払調書であったり、毎月の給与計算をする上で前月との比較差分を取って、変わっているケースというのを人間の目でチェックするのは、はっきり言って不毛な上に、量が多ければ多いほどミスが発生するリスクが高まるという作業なので、これを自動化できれば楽になる上にミスもなくなる仕事の代表例かもしれません。
そこで、今回Google Apps ScriptにてGoogleスプレッドシートで同じものを作成してみました（但し、Excel版と違って基準元と比較元を並べて表示する機能は今回見送り）。配列で取得して配列で一発書きしてるので、1セル毎に書いてるExcel版よりは理論上はスピードは遥かに上ですが、GAS自体の処理速度が遅いので、果たして・・・
目次
今回使用するスプレッドシート等
サンプルデータは、疑似個人情報データ生成サービスを用いて生成しています。比較先のデータはいくつか変更が加えられ、またいくつかの新規データを追加し、いくつかのデータを削除してあります。
Excel版にあった基準元/比較先を並べて表示するスペシャル版機能は搭載していません。
図：基本機能はすべて移植しています
使い方
システム要件
今回のシートは主にマスターデータなどで、２つのシートを比較しその差分を取り出すプログラムです。そのため、以下のルールが存在します。
- 1列目はID、2列目は名前などの列を用意します。
- 精査対象ファイルの「test」という名前のシートのみをチェック対象にしています。2枚以上ある場合、そのシートを選ぶことはできません。
- 1行目は見出しである必要があります。
- 見出しは基準元も比較先も同じ見出し列の数、見出し名である必要があります。（Excel版にあったカラム同一性チェックは搭載していません）
- マスターチェックであるため、基準元も比較先も、例えば同じ人物のデータが複数存在してはいけません。
- 基準元を基準に、比較先のデータに変更のあるセルは赤字で表示されます。また、比較先にしかないデータは追加シートに、基準元にしかないデータは削除シートに抽出されます。
- 取り込めるデータは、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などでもよく使っていた１行の文字列です。
認証情報セクションの認証情報を作成をクリックし、APIキーを選択。これですぐにキーが生成されるのでコピーしておきます。制限を加える場合には、作成したキーを開き、以下の設定を加えます。
- APIの制限では、Google Picker APIのみで使えるように制限を加えておきます。
- アプリケーションの制限は通常は不要ですが、特定のスプレッドシートでのみ有効にしたいのであれば、HTTPリファラーにてscript.google.comを含んだアドレスの入力が必要です。
図：GASで使うにはひと手間が必要なHTTPリファラーの取得
APIキーをセットする
サンプルのスプレッドシートを開き、メニューより「作業項目」を開き、APIキーを登録を実行。取得したAPIキーを入力して保存すると、使えるようになります。
図：こんな感じのダイアログが使えます
チェック手順
APIキーをセットしたら、メニューより「作業項目」→「作業の実行」をすると、サイドバーが現れます。以下の手順で実行します。
- 基準元データの取り込みのデータ選択をクリック
- Pickerが開かれるので、システム要件に合致したスプレッドシートファイルを選択・読み込み。
- 同じく比較先データの取り込みのデータ選択をクリック
- Pickerが開かれるので、システム要件に合致したスプレッドシートファイルを選択・読み込み。
- この時点で問題がなければ、基準元・比較先にすでにデータが読み込まれています。
- 最後に差分検証の実行の実行ボタンをクリックすると作業開始
- 差分のみ抽出の問い合わせに対して、「はい」と答えると、差分があった列の値のみが表示され、「いいえ」の場合は全データが差分抽出に記録されるようになります（どちらもスピードに差はありません）
- 差分のあったデータは赤字で表示され、基準元にだけ存在するレコードは削除シートに、比較先にのみ存在するデータは追加シートにそれぞれ記録されます。
図：非常にシンプルな機能のみです
ソースコード
実際に差分チェックを行うコードのみを掲載しています。
//データ
var firstArray;
var secondArray;
//差分チェックを行う
function diffcheck() {
//差分フラグ
var sabunflg;
//問い合わせ
var ui = SpreadsheetApp.getUi();
var re = ui.alert("差分チェック", "2つのシートのデータを比較しますか？", ui.ButtonSet.YES_NO);
switch(re){
case ui.Button.YES:
break;
case ui.Button.NO:
ui.alert("差分チェックをキャンセルしました。");
return 0;
break;
}
//問い合わせ2
var re2 = ui.alert("差分チェック", "差分だけを抽出しますか？", ui.ButtonSet.YES_NO);
switch(re2){
case ui.Button.YES:
sabunflg = true;
break;
case ui.Button.NO:
sabunflg = false;
break;
}
//2つのシートデータを取得する
var sheet = SpreadsheetApp.getActiveSpreadsheet();
firstArray = sheet.getSheetByName("基準元").getDataRange().getDisplayValues()
secondArray = sheet.getSheetByName("比較先").getDataRange().getDisplayValues();
//差分フラグを見て処理を分岐
var ret;
if(sabunflg == true){
ret = sabuninsert(0);
}else{
ret = sabuninsert(1);
}
//終了メッセージを表示
ui.alert("データの抽出作業を完了しました");
}
//差分抽出メインルーチン
function sabuninsert(allflg){
//スプレッドシートを取得
var sheet = SpreadsheetApp.getActiveSpreadsheet();
//差分抽出の中身をクリアする
var sabunsheet = sheet.getSheetByName("差分抽出");
sabunsheet.getDataRange().clearContent();
//削除シートの中身をクリアする
var delsheet = sheet.getSheetByName("削除");
delsheet.getDataRange().clearContent();
//追加シートの中身をクリアする
var addsheet = sheet.getSheetByName("追加");
addsheet.getDataRange().clearContent();
//行見出しの取得
var kijunf = sheet.getSheetByName("基準元");
var hikakuf = sheet.getSheetByName("比較先");
var tCol = kijunf.getLastColumn();
var rRec = kijunf.getLastRow();
var cRec = hikakuf.getLastRow();
var mArray = kijunf.getRange(1,1,1,tCol).getValues();
//各シートに見出しをコピー
sabunsheet.getRange(1,1,1,tCol).setValues(mArray);
delsheet.getRange(1,1,1,tCol).setValues(mArray);
addsheet.getRange(1,1,1,tCol).setValues(mArray);
//基準元シートを回し、比較先シートを探索する（1列目と2列目の値をもって特定する）
var checkman, checkman2;
var sabflg , delflg;
var eArray = []; //書き込み用配列
var dArray = []; //削除用書き込み用配列
var tArray = []; //1行ごとで使う一時配列
var tArray2 = []; //1行ごとで使う一時配列（削除用）
var colors = []; //文字色設定用
var tcolor = []; //文字色設定用一時配列
var aArray = []; //追加用書き込み用配列
var tArray3 = []; //1行ごとで使う一時配列（追加用）
for(var i = 1;i<rRec;i++){
//1列目と2列目データを取得する
checkman = firstArray[i][0] + firstArray[i][1];
//フラグを初期化
sabflg = false;
delflg = true;
//一時配列を初期化
tArray = [];
tArray2 = [];
tcolor = [];
//比較先シートを回す
for(var j = 1;j<cRec;j++){
//1列目と2列目データを取得する
checkman2 = secondArray[j][0] + secondArray[j][1];
//firstArrayのcheckmanと比較
if(checkman == checkman2){
//IDと名前が一致しているので、差分チェックを行う
//削除フラグを閉じる
delflg = false;
//配列データを1個ずつチェックする
for(var k = 2;k<tCol;k++){
//2つの配列を比較して同じ値の場合にはスルーする
if(firstArray[i][k] == secondArray[j][k]){
if(allflg == 0){
//空のデータを2つ入れる
tArray.push("");
tcolor.push("#000");
}else{
//全数値表示なので、値を記述させる
//比較先データをpushする
tArray.push(secondArray[j][k]);
//文字色は灰色を指定
tcolor.push("#d1d1d1");
//差分フラグを立てる
sabflg = true;
}
}else{
//比較先データをpushする
tArray.push(secondArray[j][k]);
//文字色は赤色を指定
tcolor.push("#ff0000");
//差分フラグを立てる
sabflg = true;
}
}
//差分フラグが立ってたら、1列目と2列目を記述する
tArray.splice(0, 0, firstArray[i][0]) ;
tArray.splice(1, 0, firstArray[i][1]) ;
tcolor.splice(0, 0, "#000");
tcolor.splice(1, 0, "#000");
//書き込み用配列にpushする
eArray.push(tArray);
colors.push(tcolor);
//内側のループを抜ける
break;
}
}
//削除フラグを見てTrueの場合、削除シートにデータを追加
if(delflg == true){
//配列データを1個ずつチェックする
for(var p = 0;p<tCol;p++){
//secondArray側の値を書き込む
tArray2.push(firstArray[i][p]);
}
//書き込み用配列にpushする
dArray.push(tArray2);
}
}
//配列データをスプレッドシートに書き込み
//差分抽出へ書き込み
try{
var colcnt = eArray[0].length; //カラムの数を取得する
var rowcnt = eArray.length; //行の数を取得する
sabunsheet.getRange(2,1,rowcnt,colcnt).setValues(eArray);
sabunsheet.getRange(2,1,rowcnt,colcnt).setFontColors(colors);
}catch(e){
}
//削除へ書き込み
try{
colcnt = dArray[0].length;
rowcnt = dArray.length;
delsheet.getRange(2,1,rowcnt,colcnt).setValues(dArray);
}catch(e){
}
//比較先に追加されてるケースを抽出
var checkman3,checkman4;
var addflg = true;
for(var n = 1;n<cRec;n++){
//1列目と2列目のデータを取得する
checkman3 = secondArray[n][0] + secondArray[n][1];
//フラグを初期化
addflg = true;
//配列を初期化
tArray3 = [];
//基準元シートを回す
for(var m = 1;m<rRec;m++){
//1列目と2列目のデータを取得する
checkman4 = firstArray[m][0] + firstArray[m][1];
//firstArrayのcheckmanと比較
if(checkman3 == checkman4){
//削除フラグを閉じる
addflg = false;
//内側のループを抜ける
break;
}
}
//追加フラグを見てTrueの場合、追加シートにデータを追加
if(addflg == true){
//配列データを1個ずつチェックする
for(o = 0;o<tCol;o++){
//secondArray側の値を書き込む
tArray3.push(secondArray[n][o]);
}
//書き込み用配列にpushする
aArray.push(tArray3);
}
}
//追加へ書き込み
try{
colcnt = aArray[0].length;
rowcnt = aArray.length;
addsheet.getRange(2,1,rowcnt,colcnt).setValues(aArray);
}catch(e){
}
//処理を返す
return 0;
}
- やってることはひどく単純ですが、全てメモリ上で行っている為、Excel版よりかはシンプルで高速で動く「ハズ」です。
- JavaScriptのほうがVBAよりも配列操作が楽なので、コード量自体も少ないです。
- 差分抽出、削除、追加、色設定用の4つの書き込み用配列と一時用配列を用意してpushしたりしています。
- 色データはメインの値を書き込み後に、setFontColorsにて同じく一括書き込みをさせています。
- 削除や追加などにデータが無い場合にそなえて、try-catchにてエラートラップを用意しています。
- なお、スプレッドシートからの値の取得はgetValuesではなくgetDisplayValuesを使っています。日付や時刻などのデータの変換作業や、型の違う値の混在なども対応させる為です。
- スプレッドシートデータをJSON化してレコード単位で最初のチェックを掛けて同一レコードならば処理をスルーするようにしたら、もうちょっとだけ高速化できるかも。
