データ入力規則の弱点 — コピペすり抜け問題を解決する

// コード.gs
/**
 * 入力ルールシートで定義したルールに違反する入力（直接入力・コピペ問わず）を、
 * シートが編集された瞬間に検出し、違反セルを赤くハイライトする。
 *   - シンプルトリガー onEdit(e) を使う。コードを保存してシートをリロードすればすぐ有効。
 *   - 範囲貼り付け（複数セル）にも対応。e.range 全体を1セルずつ走査する。
 *   - スプレッドシートの「データの入力規則」と違い、貼り付けですり抜けない。
 */

const RULES_SHEET = '入力ルール';
const HIGHLIGHT_BG = '#ffcccc';
const RESET_BG = '#ffffff';

function onEdit(e) {
  if (!e || !e.range) return;

  const range = e.range;
  const sheet = range.getSheet();
  const ss = sheet.getParent();

  if (sheet.getName() === RULES_SHEET) return;

  const rulesSheet = ss.getSheetByName(RULES_SHEET);
  if (!rulesSheet) return;

  const rules = loadRules_(rulesSheet);
  if (rules.length === 0) return;

  const startRow = range.getRow();
  const startCol = range.getColumn();
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();

  // ヘッダー行(1行目)に重なる部分は検査対象外
  const dataStartRow = Math.max(2, startRow);
  const dataEndRow = startRow + numRows - 1;
  if (dataEndRow < 2) return;

  const lastCol = Math.max(sheet.getLastColumn(), startCol + numCols - 1);
  const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0]
    .map(function(h) { return String(h).trim(); });

  const checkRows = dataEndRow - dataStartRow + 1;
  const checkRange = sheet.getRange(dataStartRow, startCol, checkRows, numCols);
  const values = checkRange.getValues();
  const backgrounds = checkRange.getBackgrounds();

  let violations = 0;
  for (let i = 0; i < checkRows; i++) {
    for (let j = 0; j < numCols; j++) {
      const colIndex = startCol - 1 + j;
      const colName = headers[colIndex] || '';
      if (!colName) continue;

      const colRules = rules.filter(function(r) { return r.colName === colName; });
      if (colRules.length === 0) continue;

      const cellValue = values[i][j];
      let hasError = false;
      for (let r = 0; r < colRules.length; r++) {
        if (checkRule_(colRules[r], cellValue)) {
          hasError = true;
          break;
        }
      }

      backgrounds[i][j] = hasError ? HIGHLIGHT_BG : RESET_BG;
      if (hasError) violations++;
    }
  }

  checkRange.setBackgrounds(backgrounds);

  if (violations > 0) {
    ss.toast(violations + ' 件のルール違反を検出（赤いセルを確認）', '入力ガード', 5);
  }
}

function checkRule_(rule, cellValue) {
  if (rule.ruleType === '必須') {
    return cellValue === '' || cellValue === null || cellValue === undefined;
  }
  // 必須以外は空欄スルー
  if (cellValue === '' || cellValue === null || cellValue === undefined) return false;

  if (rule.ruleType === '最小値') {
    return typeof cellValue === 'number' && cellValue < Number(rule.ruleValue);
  }
  if (rule.ruleType === '最大値') {
    return typeof cellValue === 'number' && cellValue > Number(rule.ruleValue);
  }
  if (rule.ruleType === 'リスト') {
    const allowed = String(rule.ruleValue).split(',').map(function(v) { return v.trim(); });
    return allowed.indexOf(String(cellValue).trim()) === -1;
  }
  if (rule.ruleType === '形式') {
    const pattern = formatPattern_(String(rule.ruleValue));
    return pattern && !pattern.test(String(cellValue));
  }
  return false;
}

/** 形式名を正規表現に変換。プリセット未対応の指定は正規表現として直接解釈する。 */
function formatPattern_(spec) {
  const s = String(spec).trim();
  if (s === 'メール' || s === 'メアド') return /^[^\s@]+@[^\s@]+\.[^\s@]{2,}$/;
  if (s === '電話' || s === '電話番号') return /^0\d{1,4}-\d{1,4}-\d{4}$/;
  if (s === '半角英数') return /^[A-Za-z0-9]+$/;
  if (s === '半角数字') return /^\d+$/;
  try { return new RegExp(s); } catch (e) { return null; }
}

function loadRules_(rulesSheet) {
  const last = rulesSheet.getLastRow();
  if (last < 2) return [];
  const data = rulesSheet.getRange(2, 1, last - 1, 3).getValues();
  const rules = [];
  for (let i = 0; i < data.length; i++) {
    const colName = String(data[i][0]).trim();
    const ruleType = String(data[i][1]).trim();
    if (!colName || !ruleType) continue;
    rules.push({ colName: colName, ruleType: ruleType, ruleValue: data[i][2] });
  }
  return rules;
}

function clearGuardHighlights() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  if (sheet.getName() === RULES_SHEET) {
    SpreadsheetApp.getUi().alert('「' + RULES_SHEET + '」シート自体は対象外です。');
    return;
  }
  const last = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  if (last < 2 || lastCol < 1) {
    SpreadsheetApp.getUi().alert('クリア対象のデータがありません。');
    return;
  }
  const range = sheet.getRange(2, 1, last - 1, lastCol);
  const backgrounds = [];
  for (let i = 0; i < last - 1; i++) {
    const row = [];
    for (let j = 0; j < lastCol; j++) row.push(RESET_BG);
    backgrounds.push(row);
  }
  range.setBackgrounds(backgrounds);
  SpreadsheetApp.flush();
  SpreadsheetApp.getUi().alert('ハイライトをクリアしました。');
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('自動ボタン')
    .addItem('ハイライトをクリア', 'clearGuardHighlights')
    .addToUi();
}

function onEdit(e) {
  if (!e || !e.range) return;
  const range = e.range;
  ...
}

const startRow = range.getRow();      // 範囲左上の行番号
const startCol = range.getColumn();   // 範囲左上の列番号
const numRows = range.getNumRows();   // 範囲の行数
const numCols = range.getNumColumns();// 範囲の列数

const values = range.getValues();    // numRows × numCols の2次元配列
for (let i = 0; i < numRows; i++) {
  for (let j = 0; j < numCols; j++) {
    const cellValue = values[i][j];           // 範囲内 (i,j) の値
    const absRow = startRow + i, absCol = startCol + j; // シート上の絶対座標
    // …ここでルール違反チェック
  }
}