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(/[0-9]/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。
下記を参考に。

スプレッドシートに図形でボタンを作成しGASを実行&トリガーを使ったメニューの追加
Googleが提供するGoogle Apps Script(GAS)のプログラミングで、Google Apps(主にスプレッドシート)を操作する方法を解説していく連載。今回は、スプレッドシートに図形でボタンを作成しGASを関連付ける方法、トリガーを使ったメニューの追加について解説する。

速度に関して

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

GASのスプレッドシート 配列 高速化|teratail
前提・実現したいことここに質問したいことを詳細に書いてくださいスプレッドシートにランダム関数を最終行まで(約3000個)、一列に並べるスクリプトを作りたい 発生している問題・エラーメッセージ以下のソースコードでは処理はできるのですが、とても遅いです。配列を用いてもっと高速化する方法を教えて欲しいと

公式リファレンス

Spreadsheet Service  |  Apps Script  |  Google Developers

気づいたこと

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

参考

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

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

Google Formでも利用可能かも

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

コメント