【GAS】スプレッドシートの重複行を削除する

¡Hola! マイコです。

データを扱う業務って、地味で地道な前処理こそ大切ですよね。
データ量が少なければ目検でもなんとかできますが、数百、数千行のレコードとなると、前処理は自動化したくなるものです。
その中でも欠損値や重複の削除などはよくある作業ではないでしょうか。

今回はスプレッドシートの重複削除処理を自動化してくれるGASを作ってみたのでご紹介します。

課題の整理

ユーザの投稿内容を保存するスプレッドシートがあります。
同一ユーザが複数回入力していることがあり、その場合はユーザが最後に投稿した行のみになるよう、整形する必要がありました。

元データ：
・シークエンス、投稿日時、メールアドレス、ユーザID、名前の順の列
・名前はユーザ入力のため姓名の間にスペースがある場合とない場合がある
・1回だけ投稿しているユーザが多いが複数回入力しているユーザも多数

重複削除の方針：
・1ユーザ1行のみ。重複は削除
・同一ユーザによる複数回投稿は、最新の行を残す。それ以外は削除

解決方法

ユーザにも直感的にわかりやすいよう「重複削除シートの作成」と「削除実行」の二段階で解決することにしました。

「重複削除シートの作成」は一つの関数にまとまっていますが、2つのステップから構成されます。まず、「元データ」を複製した「重複削除」というシートを新規作成。その「重複削除」シートに「削除対象」という列を作り、削除対象と判定された行にチェックを入れます。削除対象判定は後述します。
削除実行の方はシンプルに、「削除対象」列にチェックが入った行を削除します。

削除実行まで全自動で実行せずに分割する理由は、削除実行前に人目で確認してチェックを入れたり外したりできるようにするためです。

1️⃣重複削除シートの作成

・「元データ」は保持し「重複削除」というシートを新規に作成する
・「重複削除」シートに元データを転記し、「重複行」と「重複対象」というカラムを追加する
・E列の名前は姓名の間にスペースがある場合は削除
・削除判定は、今回はE列の名前で行う。
・重複があった場合、最新のタイムスタンプのレコードのみを残す
・「重複行」列には、重複行の行数を表示
・「重複対象」列はチェックボックス形式。重複している行のうち最新ではないものにチェックを入れる。
・削除対象にチェックが入っている行はグレー、残す行は黄色でハイライトする。

「重複削除」シート

作成したGAS

function duplication_detection() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // 「重複削除」シートの作成
  var sheet = ss.getSheetByName('重複削除');
  if (sheet !== null) {
    ss.deleteSheet(sheet);
  }
  sheet = ss.insertSheet('重複削除');

  // カラム名を設定
  var headers = ['seq', '最終日時', 'メールアドレス', 'ID', '名前', '重複行', '削除対象'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  var sourceSheet = ss.getSheetByName('元データ');
  var lastRow = sourceSheet.getRange('A:E').getValues().filter(row => !row.includes('')).length;
  var getInfo = sourceSheet.getRange(1, 1, lastRow, 5).getValues(); 

  sheet.getRange(2, 1, getInfo.length, 5).setValues(getInfo);

  // G列にチェックボックスを追加
  sheet.getRange(2, 7, getInfo.length).insertCheckboxes();

　// E列から全角スペースおよび半角スペースを削除する処理を追加
  var eColumnRange = sheet.getRange(2, 5, getInfo.length, 1);
  var eValues = eColumnRange.getValues();
  for (var i = 0; i < eValues.length; i++) {
    eValues[i][0] = eValues[i][0].replace(/　/g, '').replace(/ /g, '');
  }
  eColumnRange.setValues(eValues);

  // 重複チェック
  for (var i = 0; i < eValues.length; i++) {
    var duplicateRows = [];
    
    for (var j = 0; j < eValues.length; j++) {
      if (eValues[i][0] === eValues[j][0]) {
        duplicateRows.push(j + 2);
      }
    }
    
    if (duplicateRows.length > 1) {
      sheet.getRange(i + 2, 6).setValue(duplicateRows.join(', '));

      var maxRow = Math.max(...duplicateRows);
      if (i + 2 === maxRow) {
        sheet.getRange(i + 2, 1, 1, 7).setBackground('yellow');
      } else {
        sheet.getRange(i + 2, 1, 1, 7).setBackground('#979797'); // グレー
        sheet.getRange(i + 2, 7).check();
      }
    }
  }
}

2️⃣重複削除の実行

・「削除対象」にチェックが入っている行を削除する

「重複削除」の実行

作成したGAS

function deleteCheckedRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('重複削除');
  
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(2, 7, lastRow - 1, 1); // G列の2行目から最終行までの範囲を取得
  var values = range.getValues();

  var rowsToDelete = []; // 削除する行番号を保持する配列

  // G列を走査して、チェックが入っている行番号を配列に追加
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] === true) { // チェックボックスがオンの場合
      rowsToDelete.push(i + 2); // 行番号は0始まりではなく1始まりなので、2を足す
    }
  }

  // 行を削除する際、下から上に削除しないと、行番号が変わってしまうので、逆順で削除
  for (var j = rowsToDelete.length - 1; j >= 0; j--) {
    sheet.deleteRow(rowsToDelete[j]);
  }
}

3️⃣スプレッドシート上にメニューを出す（オプショナル）

スプレッドシート上にメニューを表示すると使いやすいです。

スプレッドシート上のメニュー

作成したGAS

　function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuItems = [
    {name: '1.重複削除シート作成', functionName: 'duplication_detection'},
    {name: '2.重複削除実行', functionName: 'deleteCheckedRows'},
  ];
  spreadsheet.addMenu('カスタムメニュー', menuItems);
}

これらのスクリプトをスプレッドシートに連携させて実行できるようにしておけば、完成です！

このGASを使って、数百行あったスプレッドシートの重複削除処理をスムーズに行えるようになりました。

めでたしめでたし。