スプレッドシートで条件に合うセルを変色

Question

スプレッドシートをデータベースとして使っており、次のようになっています。
 ／|      A      |  　　B　　|
――――――――――――――
／|     ID     |      日付     |
1 |  111222 |2019/01/25|
2 |  333444 |　　　　　   |
3 |  111222 |　　　　　   |
4 |  555777 |　　　　　   |

A列にID、B列には日付をいれるとします。

A列のIDには重複があるのですが、
B列に日付を入力したタイミングで、
IDが重複していて日付が入っていない方のみ、セルに色をつけることはできるでしょうか。

条件付き書式にて色々試しましたが、
日付が入る方のみ着色されたり、また両方日付が入っていない際には着色をしないという条件を踏まえるとどうしてもできませんでした。

Answer

スクリプト書いた都合上DとEになってますが、ご容赦下さい

F列をつかっていいなら条件書式でこう書けます
```
=AND(COUNTIF($D:$D,$D1)>1,$E1="", COUNTIF($D:$D,$D1) <> COUNTIF($F:$F,$D1))
```

Answer

関数苦手なんでスクリプトでやってみた。
色をつけはずしする処理は重いうえ、それを繰り返し処理するので、データが多いと使いものにならないかな。
(ロジックを考えて繰り返し回数を抑制しようとしたがバグを生みそうだったので、全部色を外して、対象につける操作にしています)

```javascript
function onEdit(e) {
  const col = e.range.getColumn();
  if(col !== 2) { return; } //B列以外は何もしない
  const row = e.range.getRow();
  adjustColors(e.source, e.range.getSheet().getRange(row, col - 1).getValue());
}
function getPairs(activeSheet, needle) {
  const checkRange = "A:B";
  const vals = activeSheet.getRange(checkRange).getValues().map(function(e,i) { return [e[0], e[1], i+1];}).filter(function(e) { return e[0] === needle;});
  return vals;
}
function adjustColors(activeSheet, needle) {
  const colour = "red";
  const targetCol = 2; //B
  const sameIdPairs = getPairs(activeSheet, needle);
  resetColorOfRowsCol(sameIdPairs.map(function(e) { return e[2]; }), targetCol); //全セルの色消す
  const isAllEmpty = sameIdPairs.filter(function(e) { return e[1] === ""; }).length === sameIdPairs.length;
  if(isAllEmpty) { return; } //着色しない
  setColorOfRowsCol(sameIdPairs.filter(function(e){ return e[1] === "";}).map(function(e) { return e[2]; }), targetCol, colour);
}
function setColorOfRowsCol(rows, column, colour) {
  rows.forEach(function(e) {
    SpreadsheetApp.getActiveSheet().getRange(e, column).setBackground(colour);
  });
}
function resetColorOfRowsCol(rows, column) {
  rows.forEach(function(e) {
    SpreadsheetApp.getActiveSheet().getRange(e, column).setBackground(null);
  });
}
```

Answer

結局機能的に無理だったと

Answer

Googleスプレッドシートが実行できない環境なので確認できていませんが。。。
Excelの条件付き書式なら次のような感じの数式で判定できました。
A列を選択してから設定。
```Excel
=AND(COUNTIF($A:$A,$A1)>1,$B1="")
```