読者です 読者をやめる 読者になる 読者になる

プログラミング 美徳の不幸

Ruby, Rails, JavaScriptなどのプログラミングまとめ、解説、備忘録。

spreadsheetでカラムに対して一意のバリデーションをつけ重複行の色を変える

spreadsheetでカラムの一意性バリデーションってできそうで意外とできないんですよね。
例えば何かの業者のリストとか作ってると重複してると上から電話かけたときに下でまた同じとこに電話しちゃうみたいなことある。

で、なんとかしたかったのでgoogle app scriptですよ...


function getAllIndexes(arr, val) {
    var indexes = [], i = -1;
    while ((i = arr.indexOf(val, i+1)) != -1){
        indexes.push(i);
    }
    return indexes;
}

function removeDuplicates() {
  var urlColumn = 2;
  var sheet = SpreadsheetApp.openById('spreadsheetのID').getSheetByName('シートの名前');
  var data = sheet.getDataRange().getValues();
  var urlList = [];
  
  for (i in data) {
    var row = data[i];
    urlList.push(row[urlColumn]);
  }
  
  for (i in data) {
    var row = data[i];
    var duplicate = false;
    var indexes = getAllIndexes(urlList, row[urlColumn]);
    
    if (indexes.length > 1) {
      sheet.getRange(Number(i) + 1, 1, 1, sheet.getLastColumn()).setBackground('#ff0000');
    }
  }
}

spreadsheetのIDとはURLの
spreadsheets/d/xxxxxxxxxxx/edit
のxxxxの部分。
なおNumber(i)にしないと文字列の結合を行うという罠がある。これに関しては確かにjsあるあるだけど、そもそもforループで提供されるループの位置なので、これを文字列で返してくるほうがおかしい気がする。(for x in arrayって普通のjsにもあるんでしたっけ? あった気がするけど使っちゃいけないやつだった気がする。それはxがstringだからとかじゃなく、in objectだと想定外のものも回しちゃうみたいな話だった気が)

で、スプレッドシートの「ツール」=>「スクリプトエディタ」で上記のコード貼っつけて実行する。saveのタイミングで自動で実行してほしいけど、そのへんは面倒なんでやってない。