Google Spread SheetをApps Scriptを使って、電話番号の重複チェックを行う

やりたいこと

• 重複している値を色ぬりする
• ハイフンの有無を考慮
• ( )の有無を考慮
• 全角数字/半角数字を考慮
• 半角スペース/全角スペースを考慮

実現方法

• 標準関数
  →　条件付き書式設定を検討したが、やりたいことは実現できない。ハイフンの考慮とか、半角/全角とか、ただのテキストだけの完全一致なら関数だけでできる。
• Google Apps Script
  →　基本何でもできるので、GASを利用することにした。

重複チェックをしたいシート

1行目が項目名になっている。(項目の順番は関係ない)
会社名, 電話番号, email, etc…

実装したソース

function checkDuplicatePhoneNumber() {
  // 設定
  var duplicatedColor = "#FAB1AF"; //エラーがあった時の色
  var targetSheetName = SpreadsheetApp.getActiveSheet().getRange(3, 2).getValue(); //チェックするシートの名前を保持しているセル
  var targetColumnName = "電話"; //電話番号の列名
  
  //Sheetの取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName); 
  
  // 利用変数の初期化
  var colTel = -1;
  var values = sheet.getRange("1:1").getValues()[0]; //1行目に項目がある前提

  //　　電話列及び会社列の取得
  for (var i=0; i<values.length; i++) {
    Logger.log(values[i]);
    if (values[i] == targetColumnName) {
      colTel = i;
    }
  }
  if (colTel == -1 ) {
    return targetColumnName+"列が特定できません。";
  }
  
  values = sheet.getDataRange().getValues();
  for (var i=0; i<values.length; i++) {
    target = values[i][colTel];
    isDuplicated = false;
    for (var j=i+1; j<values.length; j++) {
      if (replaceStrForCheck(target) != "" && replaceStrForCheck(values[j][colTel]) == replaceStrForCheck(target)) {
        isDuplicated = true;
        sheet.getRange(j+1, colTel+1).setBackground(duplicatedColor);
      }
    }
    if (isDuplicated) {
      sheet.getRange(i+1, colTel+1).setBackground(duplicatedColor);
    } 
  }
}

function replaceStrForCheck(str) {
  removalChar = " |　|-|\\(|\\)";
  res = replaceToHankaku(str);
  res = removeCharacters(res, removalChar);
  return res;
}

// 全角数字to半角数字変換
// https://teratail.com/questions/91739
function replaceToHankaku(str) {
  var res = str.replace(/[０-９]/g, function(s) {
    return String.fromCharCode(s.charCodeAt(0) - 65248);
  });
  return res;
}

//正規表現を利用して指定した文字を除去します。
function removeCharacters(str, removalChar) {
  var regExp = new RegExp(removalChar, "g");
  var res = str.replace(regExp, "");
  return res;
}

使い方

ボタンを用意して、checkDuplicatePhoneNumberを呼び出せばOK。
下記を参考に。

http://www.atmarkit.co.jp/ait/articles/1708/14/news011.html

速度に関して

シート上で行うのではなくて、配列使って操作したほうが早いらしい。なので、できる限りそうする。

https://teratail.com/questions/99446

公式リファレンス

https://developers.google.com/apps-script/reference/spreadsheet/

気づいたこと

sheet.getDataRange().getValues();
は自動的に最後のデータが入っている箇所のデータを取得する。
入力規則が設定されていてもの場合も入力されていなければ、的確にカウントされる。

参考

セルをクリックしたという検知はできない。

https://www.cloudconnect.goog/thread/17247

Google Formでも利用可能かも

Google FormからもGoogle Apps Scriptは呼び出せるので、コアロジックだけ抜き出せば使えると思う。