【Officeスクリプト】2つのワークシートの差分をチェックして背景色を変更する

こんにちは。aliceです。
先週末は久しぶりに海を見ながらのんびり過ごしました。何も考えずにのんびりする時間も必要ですね。

今回はOfficeスクリプトを使って2つのワークシートの差分をチェックして背景色を変更します。

こんな感じのExcelがあります。

Sheet1とSheet2のセルを比較して、値が違っていたセルだけ色を付けます。
C6セルの値が違っていたので黄色で塗りつぶしてみました。

それでは、やっていきましょう。

for文

まずはシンプルにfor文で書きました。

function main(workbook: ExcelScript.Workbook) {
  const sheet1 = workbook.getWorksheet("Sheet1");
  const sheet2 = workbook.getWorksheet("Sheet2");
  const sheet1Values = sheet1.getUsedRange().getValues();
  const sheet2Values = sheet2.getUsedRange().getValues();

  // Sheet1の行数と列数を取得する。
  const numRows = sheet1Values.length;
  const numCols = sheet1Values[0].length;

  // Sheet1の各セルに対して、Sheet2と比較し異なる場合は黄色に設定する
  for (let i = 0; i < numRows; i++) {
    for (let j = 0; j < numCols; j++) {
      if (sheet1Values[i][j] !== sheet2Values[i][j]) {
        sheet1.getCell(i, j).getFormat().getFill().setColor("FFFF00");
      }
    }
  }
}

セルを1つずつチェックしていきます。
シンプルなパターンです。

行番号と列番号からセルを取得したいときはgetCellメソッドでできます。

ExcelScript.Range interface - Office Scripts

セルの背景を塗りつぶすのはわからなかったので自動記録で。
コメントも入れてくれるので便利です✨

forEachメソッド

せっかっくなのでforEachメソッドでも書いてみました。

2つのforEachメソッドを使って行番号と列番号（インデックス）を取得して、比較していくパターンです。

function main(workbook: ExcelScript.Workbook) {
  const sheet1 = workbook.getWorksheet("Sheet1");
  const sheet2 = workbook.getWorksheet("Sheet2");
  const sheet1Values = sheet1.getUsedRange().getValues();
  const sheet2Values = sheet2.getUsedRange().getValues();
  
  sheet1Values.forEach((row, rowIndex) => {
    row.forEach((cell, colIndex) => {
      if (cell !== sheet2Values[rowIndex][colIndex]) {
        sheet1.getCell(rowIndex, colIndex).getFormat().getFill().setColor("FFFF00");
      }
    });
  });
}

forEachメソッド

Array.prototype.forEach() - JavaScript | MDN

最初のforEachメソッドでSheet1の値を格納した2次元配列から、行の値が入った1次元配列と行番号（rowIndex・インデックス）を取得します。

• row  : ["種類", "名称", "金額"]

• rowIndex : 0

次のforEachメソッドで最初のforEachメソッドから取得した行の値が入った1次元配列配列から、セルの値と列番号（colIndex・インデックス）を取得します。

• cell  : 種類

• colIndex : 0

最後に、2つのforEachメソッドから取得した行番号（rowIndex）と列番号（colIndex）を使用して、2つのシートの値を比較します。

こちらですね。

if (cell !== sheet2Values[rowIndex][colIndex]) {
    sheet1.getCell(rowIndex, colIndex).getFormat().getFill().setColor("FFFF00");
}

forEachメソッドとちょっと仲良くなりました😊

違っていたセルのA列の文字を赤くする

最後に違っていたセルのA列の文字を赤くしてみたいと思います。

このスクリプトを使いたいデータがあるのですが、ものすごーく横に長いのです😭
恐るべしマスタ。

ということで「この行には一致していないセルがいるよ！」という意味で、A列の文字の色を赤くしてみたいと思います。

function main(workbook: ExcelScript.Workbook) {
  const sheet1 = workbook.getWorksheet("Sheet1");
  const sheet2 = workbook.getWorksheet("Sheet2");
  const sheet1Values = sheet1.getUsedRange().getValues();
  const sheet2Values = sheet2.getUsedRange().getValues();

  // Sheet1の行数と列数を取得する。
  const numRows = sheet1Values.length;
  const numCols = sheet1Values[0].length;

  // Sheet1の各セルに対してSheet2と比較し異なる場合はA列の文字を赤くして、該当するセルの背景を黄色にする
  for (let i = 0; i < numRows; i++) {
    for (let j = 0; j < numCols; j++) {
      if (sheet1Values[i][j] !== sheet2Values[i][j]) {
        sheet1.getCell(i, 0).getFormat().getFont().setColor("FF0000");
        sheet1.getCell(i, j).getFormat().getFill().setColor("FFFF00");
      }
    }
  }
}

できた！
あとは活用しますー✨

Officeスクリプト記事まとめ📝

Officeスクリプト｜alice｜note

『Officeスクリプト入門講座』を書きました📝

Officeスクリプト入門講座：ノンプログラマーのためのスキルアップ研究会