X投稿の重複を出す前に止める｜GASで投稿キューをdry-run監査

const X_QUEUE_AUDIT_CONFIG = {
  queueSheet: '投稿候補',
  postedLogSheet: '投稿済みログ',
  auditLogSheet: '監査ログ',
  DRY_RUN: true,
  maxApproxChars: 280,
};

function setupXPostQueueAuditFree() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const queue = ensureSheetWithHeaders_(ss, X_QUEUE_AUDIT_CONFIG.queueSheet, [
    'record_id',
    'post_text',
    'approved',
    'status',
    'check_result',
    'checked_at',
  ]);

  ensureSheetWithHeaders_(ss, X_QUEUE_AUDIT_CONFIG.postedLogSheet, [
    'record_id',
    'normalized_hash',
    'posted_at',
  ]);

  ensureSheetWithHeaders_(ss, X_QUEUE_AUDIT_CONFIG.auditLogSheet, [
    'checked_at',
    'record_id',
    'result',
    'approx_chars',
    'normalized_hash',
    'dry_run',
  ]);

  if (queue.getLastRow() === 1) {
    queue.getRange(2, 1, 3, 6).setValues([
      ['post-001', '作業前に入力を確認すると、やり直しを減らしやすくなります。', true, 'READY', '', ''],
      ['post-002', '  作業前に入力を確認すると、やり直しを減らしやすくなります。  ', true, 'READY', '', ''],
      ['post-003', 'まだ確認していない投稿候補です。', false, 'DRAFT', '', ''],
    ]);
  }
}

function auditXPostQueueFree() {
  if (X_QUEUE_AUDIT_CONFIG.DRY_RUN !== true) {
    throw new Error('この無料例はDRY_RUN: trueでのみ実行できます。');
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const queue = requireSheet_(ss, X_QUEUE_AUDIT_CONFIG.queueSheet);
  const postedLog = requireSheet_(ss, X_QUEUE_AUDIT_CONFIG.postedLogSheet);
  const auditLog = requireSheet_(ss, X_QUEUE_AUDIT_CONFIG.auditLogSheet);

  const queueValues = queue.getDataRange().getValues();
  if (queueValues.length < 2) {
    throw new Error('投稿候補がありません。少量のサンプルを入れてください。');
  }

  const queueHeaders = queueValues[0].map(String);
  validateHeaders_(queueHeaders, [
    'record_id',
    'post_text',
    'approved',
    'status',
    'check_result',
    'checked_at',
  ]);

  const postedHashes = readPostedHashes_(postedLog);
  const seenInCurrentRun = new Set();
  const now = new Date();
  const resultValues = [];
  const checkedAtValues = [];
  const auditRows = [];

  queueValues.slice(1).forEach((row) => {
    const item = rowToObject_(queueHeaders, row);
    const recordId = String(item.record_id || '').trim();
    const rawText = String(item.post_text || '');
    const normalizedText = normalizePostText_(rawText);
    const normalizedHash = normalizedText ? sha256Text_(normalizedText) : '';
    const approxChars = Array.from(normalizedText).length;
    const result = validateQueueRow_({
      recordId,
      rawText,
      normalizedText,
      normalizedHash,
      approxChars,
      approved: item.approved,
      status: item.status,
      postedHashes,
      seenInCurrentRun,
    });

    if (normalizedHash) {
      seenInCurrentRun.add(normalizedHash);
    }

    resultValues.push([result]);
    checkedAtValues.push([now]);
    auditRows.push([
      now,
      recordId || '(IDなし)',
      result,
      approxChars,
      normalizedHash,
      true,
    ]);
  });

  const resultCol = queueHeaders.indexOf('check_result') + 1;
  const checkedAtCol = queueHeaders.indexOf('checked_at') + 1;
  queue.getRange(2, resultCol, resultValues.length, 1).setValues(resultValues);
  queue.getRange(2, checkedAtCol, checkedAtValues.length, 1).setValues(checkedAtValues);
  auditLog.getRange(auditLog.getLastRow() + 1, 1, auditRows.length, auditRows[0].length).setValues(auditRows);
}

function validateQueueRow_(input) {
  if (!input.recordId) {
    return 'BLOCK: record_idが空です';
  }

  if (!normalizeBool_(input.approved)) {
    return 'WAIT: approvedがTRUEではありません';
  }

  if (String(input.status || '').trim().toUpperCase() !== 'READY') {
    return 'WAIT: statusがREADYではありません';
  }

  if (!input.normalizedText) {
    return 'BLOCK: post_textが空です';
  }

  if (input.approxChars > X_QUEUE_AUDIT_CONFIG.maxApproxChars) {
    return `BLOCK: 文字数の目安を超えています (${input.approxChars})`;
  }

  if (input.postedHashes.has(input.normalizedHash)) {
    return 'BLOCK: 投稿済みログと同じ本文です';
  }

  if (input.seenInCurrentRun.has(input.normalizedHash)) {
    return 'BLOCK: 今回の候補内に同じ本文があります';
  }

  return 'OK: dry-run監査を通過しました。投稿画面で最終確認してください';
}

function readPostedHashes_(sheet) {
  const values = sheet.getDataRange().getValues();
  if (values.length === 0) return new Set();

  const headers = values[0].map(String);
  validateHeaders_(headers, ['record_id', 'normalized_hash', 'posted_at']);
  const hashIndex = headers.indexOf('normalized_hash');
  return new Set(
    values.slice(1)
      .map((row) => String(row[hashIndex] || '').trim())
      .filter(Boolean)
  );
}

function normalizePostText_(value) {
  return String(value || '')
    .normalize('NFKC')
    .replace(/\s+/g, ' ')
    .trim();
}

function sha256Text_(value) {
  const bytes = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    value,
    Utilities.Charset.UTF_8
  );
  return bytes.map((byte) => (byte + 256) % 256)
    .map((byte) => byte.toString(16).padStart(2, '0'))
    .join('');
}

function rowToObject_(headers, row) {
  const item = {};
  headers.forEach((header, index) => {
    item[header] = row[index];
  });
  return item;
}

function normalizeBool_(value) {
  if (value === true) return true;
  return String(value || '').trim().toUpperCase() === 'TRUE';
}

function validateHeaders_(actual, required) {
  const missing = required.filter((header) => !actual.includes(header));
  if (missing.length) {
    throw new Error(`必要な列がありません: ${missing.join(', ')}`);
  }
}

function ensureSheetWithHeaders_(ss, name, headers) {
  const sheet = ss.getSheetByName(name) || ss.insertSheet(name);
  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.setFrozenRows(1);
  } else {
    validateHeaders_(sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(String), headers);
  }
  return sheet;
}

function requireSheet_(ss, name) {
  const sheet = ss.getSheetByName(name);
  if (!sheet) {
    throw new Error(`${name}がありません。先にsetupXPostQueueAuditFreeを実行してください。`);
  }
  return sheet;
}