【GASで時短】2つのシートの差分をチェックする

2つのシートを比較して、値や数式に差分があるかどうか教えてくれるGoogle Apps Scriptです。スプレッドシートでもdiffがしたい！という時にどうぞ。

こんな悩みを解決します

• 2つのシートの差分をチェックしたくなった！けど目で見るのは辛い
• diffコマンドみたいなのがスプレッドシートにもあればいいのになー

用意するもの

特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること

• 指定した2つのシートの値と数式を比較し、差分があったセルに色を付けることができます。
• 比較したいシートはダイアログボックスで指定できます。
• 別のスプレッドシートを指定することは（このアプリでは）できません。シートのコピーなどで対応いただければと思います。

使い方

1.スクリプトの実行

上のメニューにある「スクリプト実行」から「2つのシートの差分をチェックする」を実行します。

比較したいシート名を聞かれます。まずは1つ目のシート名を入力してください。

続いて2つ目のシート名を聞かれます。
このアプリでは差分が見つかったセルを黄色にします。色がつくのは2つ目のシートです。

2.結果の確認

シートのサイズによりますが、しばらく待つと「（シート名1）と（シート名2）を比較した結果、N個のセルで差分が見つかりました！」という完了メッセージが表示されます。

2つ目に指定したシートを見てみましょう。4つのセルが黄色くなっています。
E列は算数の得点が変わったことで、合計と平均も差分が出ているようです。
D10セルは1つ目のシートではSUM()関数を使っていましたが、シート2は1セルずつ足すという数式に変えてありました。
数値は変化ありませんが、数式の差分も拾えているようです。

もし2シート間で差分がない場合は「（シート名1）と（シート名2）を比較した結果、差分は見つかりませんでした！」というメッセージが表示されます。

また、もし比較対象として指定したシートがカラの場合は、「比較対象のシートにデータが入っていないようです。比較処理を中止します」というメッセージを表示し、何もせずスクリプトを終了します。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00101/index.html
 */

function onOpen() {
  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('2つのシートの差分をチェックする', 'check_diff');

  // メニューを画面に追加する
  menu.addToUi();
}

function check_diff() {

  // プロンプトを表示するためにUIを取得
  const ui = SpreadsheetApp.getUi();

  // 開いているスプレッドシートオブジェクトを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 比較対象のシート名を2つ入力してもらう。キャンセルが押されたらスクリプトを終了する。
  const response1 = ui.prompt('比較したい1つ目のシート名を入力してください', ui.ButtonSet.OK_CANCEL);
  if (response1.getSelectedButton == ui.Button.CANCEL) {
    return;
  }

  const response2 = ui.prompt('比較したい2つ目のシート名を入力してください', ui.ButtonSet.OK_CANCEL);
  if (response2.getSelectedButton == ui.Button.CANCEL) {
    return;
  }

  // 入力されたシート名を元に、シートオブジェクトを取得する
  const sheet1 = ss.getSheetByName(response1.getResponseText());
  const sheet2 = ss.getSheetByName(response2.getResponseText());

  // 比較したいシートが空の場合は、処理せず中止する
  if (sheet1.getLastRow() == 0 || sheet2.getLastRow() == 0) {
    ui.alert('比較対象のシートにデータが入っていないようです。比較処理を中止します。');
    return;
  }

  // 各シートの使用されている部分の範囲オブジェクトを取得する
  let range1 = sheet1.getRange(1, 1, sheet1.getLastRow(), sheet1.getLastColumn());
  let range2 = sheet2.getRange(1, 1, sheet2.getLastRow(), sheet2.getLastColumn());

  // 範囲内のセルの値を取得する
  const values1 = range1.getValues();
  const values2 = range2.getValues();

  // 範囲内のセルに入っている数式を取得する
  const formulas1 = range1.getFormulas();
  const formulas2 = range2.getFormulas();

  // 差分の件数をカウントするための変数を宣言
  let result = 0;

  // 1セルずつチェックしていく
  for (let i = 0; i < values1.length; i++) { // 行のループ

    for (let j = 0; j < values1[i].length; j++) { // 列のループ

      // セルの値 もしくは 数式がシート間で一致しない場合は
      if (values1[i][j] != values2[i][j] || formulas1[i][j] != formulas2[i][j]) {

        // 2つ目のシート上で、該当セルに色を付ける
        range = sheet2.getRange(i + 1, j + 1);
        range.setBackground('yellow');

        // 差分の件数カウンターを増加する
        result++;
      }
    }
  }

  // 終了メッセージを表示する。差分の有無でメッセージを変える
  if (result > 0) {
    ui.alert(response1.getResponseText() + 'と' + response2.getResponseText() + 'を比較した結果、\n' + result + '個のセルで差分が見つかりました!');
  } else {
    ui.alert(response1.getResponseText() + 'と' + response2.getResponseText() + 'を比較した結果、\n差分は見つかりませんでした!');
  }
}

関連するアプリ