2つのSpreadsheetをGASで差分チェックする方法

概要

スプレッドシートを使っているとたまにDiffを見たいときってありませんかね？
私はあります。

スプレッドシートのままDiffができたらいいんですが、ググってると全然見つからないので一度Excelに出力してExcel形式でDiffを見る方法も考えたんですが、Windows関連しか記事が見つからず、参考になる記事を見つけたとしても野良アプリをインストールして使う方法ばかりでセキュリティ的にそれはやりたくないという・・・

一応、以下の記事が参考になりそうだったんですが、StackOverflowの記事は差分をビジュアライズする機能はなく、Officeの杜さんが正しくやりたいことをまとめられていたものの200行以上のコードがあってわかりづらいなと思い、自分でもGASで軽量なDiffツールを作ることにしました笑

• Google Apps Scriptで2つのスプレッドシートの差分を取り出してみる
• How do I create a readable diff of two spreadsheets using git diff?

参考記事

• スプレッドシート関連
  • 【GAS】1行で書ける！特定列の最終行・特定行の最終列を取得する方法
  • 【GAS】スプレッドシートのセルに背景色を設定し、色掛けする方法(setBackground)
  • Spreadsheet Service | Apps Script | Google Developers
• Googleドライブ関連
  • 【GAS】Google Apps Scriptでスプレッドシートを複製コピーする方法とは？
  • 【GAS/Googleスプレッドシート】ファイル自身が格納されているフォルダの取得方法

スクリプト

GASに関する初歩的なことは、過去の記事「GAS 入門（Spreadsheet → GoogleDrive → Slack）」にまとめているので、初めて触る方は先にこっちを見ていただけるとmm

GitHub Gistにアップしています。
gremito/diffSpreadsheets.gs

差分を比較する2つのスプレッドシートは、一旦GASにIDをハードコーディングする形にしていますが、Officeの杜さんの記事と同じようにGoogle Pickerを使うようしたいのと、差分を見るシート名や行数と列数の指定などもGASにハードコーディングになるので、この辺はそのうち使いやすいように調整したいなと。

やっていることは、以下のフローで処理しています。

1. SpreadsheetApp.openById()でスプレッドシート情報を取得
2. getSheetByName()で指定したシート毎にシート情報を取得
3. getRange().getValue()と``で指定した行数と列数を元にセル情報を取得
4. ↑の差分元と比較元の値が違う場合は差分情報として行数と列数の値をキャッシュ
5. DriveApp.getFileById()で差分元のスプレッドシートが管理されているGoogleドライブ情報を取得
6. ↑を元にDriveApp.getFileById().makeCopy()で差分元のスプレッドシートを複製
7. キャッシュした差分情報を元に複製したスプレッドシートにsetBackground()で差分の箇所を色付け

実行

テストデータとして以下のスプレッドシートを用意しました。

どちらかのスプレッドシートからGASを作ってもいいですし、同じ階層にGASを作っても同じように動作します。
GASエディタ上で実行すると以下の結果となります。

課題

ハードコーディングをリファクタリングしたいし、テストデータにあるハイパーリンク有無も差分ありで比較したい。