GASで入力ミスを自動で見つける｜スプレッドシートの確認作業を減らす方法

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• 51
• 52
• 53
• 54
• 55
• 56
• 57
• 58
• 59
• 60
• 61
• 62
• 63
• 64
• 65
• 66
• 67
• 68
• 69
• 70
• 71
• 72
• 73
• 74
• 75
• 76
• 77
• 78
• 79
• 80
• 81
• 82
• 83
• 84
• 85
• 86
• 87
• 88
• 89
• 90
• 91
• 92
• 93
• 94
• 95
• 96
• 97
• 98
• 99
• 100
• 101
• 102
• 103
• 104
• 105
• 106
• 107
• 108
• 109
• 110
• 111
• 112
• 113
• 114
• 115
• 116
• 117
• 118
• 119
• 120
• 121
• 122
• 123
• 124
• 125
• 126
• 127

// ===== 設定 =====
const SHEET_NAME = '受付リスト';       // チェックするシート名
const RESULT_SHEET_NAME = 'チェック結果'; // 一覧を出力するシート名（なければ自動で作る）
const HEADER_ROWS = 1;                 // 見出し行の数
const COL = { DATE: 0, NAME: 1, EMAIL: 2, QTY: 3 }; // 列の位置（A列=0, B列=1…）
const REQUIRED_COLS = [COL.DATE, COL.NAME, COL.EMAIL]; // 空欄NGの列
const DUPLICATE_COL = COL.EMAIL;       // 重複をチェックする列
const COLORS = { blank: '#ffe599', duplicate: '#f4b183', format: '#ea9999' };
const TYPE_LABELS = { blank: '空欄', duplicate: '重複', format: '形式' };
// =================

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('入力チェック')
    .addItem('チェックを実行', 'checkInput')
    .addToUi();
}

function checkInput() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) {
    SpreadsheetApp.getUi().alert(`「${SHEET_NAME}」シートが見つかりません。`);
    return;
  }

  const lastRow = sheet.getLastRow();
  if (lastRow <= HEADER_ROWS) {
    SpreadsheetApp.getUi().alert('チェックするデータがありません。');
    return;
  }

  const numCols = COL.QTY + 1;
  const headers = sheet.getRange(1, 1, 1, numCols).getValues()[0];
  const range = sheet.getRange(HEADER_ROWS + 1, 1, lastRow - HEADER_ROWS, numCols);
  const values = range.getValues();

  const issues = findIssues_(values);

  // 前回の色をリセットしてから、問題のあるセルだけ色を付ける（null = 色なし）
  const colors = values.map(row => row.map(() => null));
  issues.forEach(i => { colors[i.r][i.c] = COLORS[i.type]; });
  range.setBackgrounds(colors);

  writeResult_(ss, sheet, headers, issues);
  ss.toast(`${issues.length}件の確認ポイントが見つかりました`, '入力チェック', 5);
}

// 問題を探す（シートには触らない部分）
// values は見出しを除いたデータ。r, c は values の中の位置（0始まり）
function findIssues_(values) {
  const issues = [];
  const groups = {}; // 重複チェック用：値 → 行番号の配列

  values.forEach((row, r) => {
    // 1) 空欄
    REQUIRED_COLS.forEach(c => {
      if (isBlank_(row[c])) {
        issues.push({ r, c, type: 'blank', detail: '入力されていません' });
      }
    });

    // 2) 形式（空欄でないものだけ確認）
    if (!isBlank_(row[COL.DATE]) && !isValidDate_(row[COL.DATE])) {
      issues.push({ r, c: COL.DATE, type: 'format', detail: `「${row[COL.DATE]}」は日付として読み取れません` });
    }
    if (!isBlank_(row[COL.EMAIL]) && !isValidEmail_(row[COL.EMAIL])) {
      issues.push({ r, c: COL.EMAIL, type: 'format', detail: `「${row[COL.EMAIL]}」はメールアドレスの形式ではありません` });
    }
    if (!isBlank_(row[COL.QTY]) && !isPositiveInteger_(row[COL.QTY])) {
      issues.push({ r, c: COL.QTY, type: 'format', detail: `「${row[COL.QTY]}」は1以上の整数ではありません` });
    }

    // 3) 重複の下準備（前後の空白と大文字小文字は無視して比べる）
    if (!isBlank_(row[DUPLICATE_COL])) {
      const key = String(row[DUPLICATE_COL]).trim().toLowerCase();
      (groups[key] = groups[key] || []).push(r);
    }
  });

  // 同じ値が2回以上出てきた行は、すべて重複として記録する
  Object.keys(groups).forEach(key => {
    const rows = groups[key];
    if (rows.length >= 2) {
      rows.forEach(r => {
        issues.push({ r, c: DUPLICATE_COL, type: 'duplicate', detail: `同じ値が${rows.length}件あります` });
      });
    }
  });

  return issues;
}

function isBlank_(v) {
  return v === '' || v === null || v === undefined || (typeof v === 'string' && v.trim() === '');
}

function isValidDate_(v) {
  return Object.prototype.toString.call(v) === '[object Date]' && !isNaN(v.getTime());
}

function isValidEmail_(v) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(String(v).trim());
}

function isPositiveInteger_(v) {
  return typeof v === 'number' && Number.isInteger(v) && v >= 1;
}

// 結果を別シートに一覧で書き出す（セル番号をクリックすると該当セルへジャンプ）
function writeResult_(ss, sheet, headers, issues) {
  const sorted = issues.slice().sort((a, b) => a.r - b.r || a.c - b.c);
  const gid = sheet.getSheetId();
  const rows = sorted.map(i => {
    const a1 = sheet.getRange(i.r + HEADER_ROWS + 1, i.c + 1).getA1Notation();
    return [`=HYPERLINK("#gid=${gid}&range=${a1}","${a1}")`, headers[i.c], TYPE_LABELS[i.type], i.detail];
  });

  const result = ss.getSheetByName(RESULT_SHEET_NAME) || ss.insertSheet(RESULT_SHEET_NAME);
  result.clear();
  result.getRange(1, 1, 1, 4).setValues([['セル', '項目', '種類', '内容']]).setFontWeight('bold');
  if (rows.length > 0) {
    result.getRange(2, 1, rows.length, 4).setValues(rows);
  } else {
    result.getRange(2, 1).setValue('問題は見つかりませんでした');
  }
}