あとで見るURLを二重登録しない｜GASで読書・動画ウォッチリストをdry-run監査

function auditWatchlistDuplicatesFree() {
  const DRY_RUN = true;
  const SOURCE_SHEET = 'Watchlist';
  const LOG_SHEET = 'AuditLog';
  const MAX_ROWS = 500;

  if (!DRY_RUN) {
    throw new Error('この無料例はDRY_RUN: trueのまま実行してください。');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const source = spreadsheet.getSheetByName(SOURCE_SHEET);
  if (!source) {
    throw new Error('Watchlistシートが見つかりません。');
  }

  const values = source.getDataRange().getDisplayValues();
  if (values.length < 2) {
    throw new Error('見出し行の下に確認用データを1行以上入れてください。');
  }
  if (values.length - 1 > MAX_ROWS) {
    throw new Error('最初は500行以内に絞って確認してください。');
  }

  const headers = values[0].map(value => value.trim());
  const titleIndex = headers.indexOf('タイトル');
  const urlIndex = headers.indexOf('URL');
  if (titleIndex < 0 || urlIndex < 0) {
    throw new Error('1行目に「タイトル」と「URL」が必要です。');
  }

  const runId = Utilities.getUuid().slice(0, 8);
  const seen = new Map();
  const logs = [];

  values.slice(1).forEach((row, offset) => {
    const sourceRow = offset + 2;
    const title = row[titleIndex].trim();
    const rawUrl = row[urlIndex].trim();

    if (!title && !rawUrl) return;
    if (!title) {
      logs.push([new Date(), runId, sourceRow, 'INPUT_HOLD', '', 'タイトルが空です']);
      return;
    }
    if (!rawUrl) {
      logs.push([new Date(), runId, sourceRow, 'INPUT_HOLD', '', 'URLが空です']);
      return;
    }

    const canonicalUrl = normalizeWatchlistUrlFree_(rawUrl);
    if (!canonicalUrl) {
      logs.push([new Date(), runId, sourceRow, 'INPUT_HOLD', '', 'httpまたはhttpsのURLを確認してください']);
      return;
    }

    const key = shortHashFree_(canonicalUrl);
    if (seen.has(canonicalUrl)) {
      logs.push([new Date(), runId, sourceRow, 'DUPLICATE', key, '先に登録された行: ' + seen.get(canonicalUrl)]);
    } else {
      seen.set(canonicalUrl, sourceRow);
      logs.push([new Date(), runId, sourceRow, 'OK', key, '重複なし']);
    }
  });

  const logSheet = spreadsheet.getSheetByName(LOG_SHEET) || spreadsheet.insertSheet(LOG_SHEET);
  if (logSheet.getLastRow() === 0) {
    logSheet.appendRow(['実行日時', 'run_id', '元の行', '判定', '照合キー', '確認メモ']);
  }
  if (logs.length > 0) {
    logSheet.getRange(logSheet.getLastRow() + 1, 1, logs.length, logs[0].length).setValues(logs);
  }

  console.log(JSON.stringify({
    dryRun: DRY_RUN,
    checkedRows: values.length - 1,
    logRows: logs.length,
    sourceUpdated: false
  }));
}

function normalizeWatchlistUrlFree_(rawUrl) {
  const match = rawUrl.trim().match(/^(https?):\/\/([^\/?#]+)(\/[^?#]*)?(?:\?([^#]*))?(?:#.*)?$/i);
  if (!match) return '';

  const protocol = match[1].toLowerCase();
  let host = match[2].toLowerCase();
  let path = match[3] || '/';
  let query = match[4] || '';

  if (host === 'youtu.be') {
    const videoId = path.replace(/^\//, '').split('/')[0];
    if (!videoId) return '';
    host = 'www.youtube.com';
    path = '/watch';
    query = 'v=' + encodeURIComponent(videoId);
  }

  const keptParams = query
    .split('&')
    .filter(Boolean)
    .filter(part => {
      const key = part.split('=')[0].toLowerCase();
      return !key.startsWith('utm_') && !['fbclid', 'gclid'].includes(key);
    })
    .sort();

  if (path !== '/') path = path.replace(/\/$/, '');
  return protocol + '://' + host + path + (keptParams.length ? '?' + keptParams.join('&') : '');
}

function shortHashFree_(value) {
  const digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, value);
  return Utilities.base64EncodeWebSafe(digest).slice(0, 12);
}