📈

Google SpreadSheet GAS で、2シートの差分を新しいシートに出力する関数

2023/08/24に公開

JavaScript

GAS

Google Sheets

tech

タイトルの通りです。
2シートを並べて配置して、左のシートを選択して動かしてください。
そしたら、2シートの間に比較結果出力シートが作成されます。元のシートのデータは変更されません。

function onOpen(e) {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu(`追加機能`)

    .addItem(
      `シート比較`,
      `onClickSheetDiff`,
    )

    ;
  menu.addToUi();
}

const onClickSheetDiff = () => {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetActive = spreadSheet.getActiveSheet();

  if (spreadSheet.getSheets().length === sheetActive.getIndex()) {
    Browser.msgBox(
      `確認`,
      `比較するシートを隣に配置してください。`,
      Browser.Buttons.OK,
    );
    return;
  }

  const sheetActiveNext = spreadSheet.getSheets()[sheetActive.getIndex()];
  const msgResult = Browser.msgBox(
    `確認`,
    `シート[${sheetActive.getName()}]と\\n`
    + `シート[${sheetActiveNext.getName()}]を比較します。\\n`
    + `よろしいですか？`,
    Browser.Buttons.OK_CANCEL,
  );
  if (msgResult !== `ok`) {
    return;
  }

  sheetDiff(sheetActive.getName(), sheetActiveNext.getName());
};

const sheetDiff = (sheetName1, sheetName2) => {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();

  const sheet1 = spreadSheet.getSheetByName(sheetName1);
  const sheet2 = spreadSheet.getSheetByName(sheetName2);

  const lastRow = Math.max(sheet1.getLastRow(), sheet2.getLastRow());
  const lastCol = Math.max(sheet1.getLastColumn(), sheet2.getLastColumn());

  if (lastRow === 0 || lastCol === 0) {
    return;
  }

  const sheetOutput = sheet2.copyTo(spreadSheet);
  sheetOutput.setName(`${sheetName1}_比較_${sheetName2}`);
  sheetOutput.activate();
  spreadSheet.moveActiveSheet(sheet1.getIndex() + 1);

  const range1 = sheet1.getRange(1, 1, lastRow, lastCol);
  const range2 = sheet2.getRange(1, 1, lastRow, lastCol);

  const values1 = range1.getDisplayValues();
  const values2 = range2.getDisplayValues();

  const formulas1 = range1.getFormulas();
  const formulas2 = range2.getFormulas();

  let diffCount = 0;
  let diffRows = [];
  let diffCols = [];

  for (let row = 0; row < values1.length; row+=1) {
    for (let col = 0; col < values1[row].length; col+=1) {

      if (values1[row][col] !== values2[row][col]) {
        range = sheetOutput.getRange(row + 1, col + 1);
        range.setValue(`${values1[row][col]}>>${values2[row][col]}`);
        range.setBackground(`yellow`);

        diffCount += 1;
        diffRows.push(row);
        diffCols.push(col);
      } else if (formulas1[row][col] != formulas2[row][col]) {
        range = sheetOutput.getRange(row + 1, col + 1);
        range.setBackground(`yellow`);

        diffCount += 1;
        diffRows.push(row);
        diffCols.push(col);
      }

    }
  }
  diffRows = [...(new Set(diffRows))];
  diffCols = [...(new Set(diffCols))];

  SpreadsheetApp.flush();

  Browser.msgBox(
    `確認`,
    `${diffCount}個のセルが異なっています。\\n`
    + `${diffRows.length}行が異なっています。\\n`
    + `${diffCols.length}列が異なっています。`,
    Browser.Buttons.OK,
  );

};

参考

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

参考

Discussion