Driveファイルの版番号が混ざる前に｜GASで名前と更新日をdry-run監査

function auditDriveVersionNamesFree() {
  const config = {
    sheetName: 'FileInventory',
    dryRun: true,
    maxRows: 500,
    expectedHeaders: ['file_id', 'file_name', 'updated_at'],
  };

  if (config.dryRun !== true) {
    throw new Error('安全のため dryRun: true のときだけ実行できます。');
  }

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

  const values = sheet.getDataRange().getValues();
  if (values.length < 2) {
    throw new Error('見出し行の下に確認用データを入れてください。');
  }

  const headers = values[0].map(function (value) {
    return String(value).trim().toLowerCase();
  });
  const missingHeaders = config.expectedHeaders.filter(function (header) {
    return headers.indexOf(header) === -1;
  });
  if (missingHeaders.length > 0) {
    throw new Error('不足している列: ' + missingHeaders.join(', '));
  }

  const body = values.slice(1);
  if (body.length > config.maxRows) {
    throw new Error('一度に確認できるのは ' + config.maxRows + ' 行までです。');
  }

  const column = {};
  config.expectedHeaders.forEach(function (header) {
    column[header] = headers.indexOf(header);
  });

  const issues = [];
  const versionGroups = {};

  body.forEach(function (row, offset) {
    const rowNumber = offset + 2;
    const fileId = String(row[column.file_id] || '').trim();
    const fileName = String(row[column.file_name] || '').trim();
    const updatedAt = toValidDate_(row[column.updated_at]);

    if (!fileId || !fileName || !updatedAt) {
      issues.push({ row: rowNumber, type: 'INPUT_INVALID' });
      return;
    }

    const parsed = parseVersionName_(fileName);
    if (!parsed) {
      issues.push({ row: rowNumber, type: 'VERSION_MISSING' });
      return;
    }

    const groupKey = normalizeStem_(parsed.stem);
    if (!versionGroups[groupKey]) {
      versionGroups[groupKey] = [];
    }
    versionGroups[groupKey].push({
      row: rowNumber,
      version: parsed.version,
      versionText: parsed.versionText,
      updatedAt: updatedAt,
    });
  });

  Object.keys(versionGroups).forEach(function (groupKey) {
    const records = versionGroups[groupKey];
    const seenVersions = {};

    records.forEach(function (record) {
      if (!seenVersions[record.versionText]) {
        seenVersions[record.versionText] = [];
      }
      seenVersions[record.versionText].push(record.row);
    });

    Object.keys(seenVersions).forEach(function (versionText) {
      const rows = seenVersions[versionText];
      if (rows.length > 1) {
        rows.forEach(function (rowNumber) {
          issues.push({ row: rowNumber, type: 'DUPLICATE_VERSION' });
        });
      }
    });

    const byVersion = records.slice().sort(function (a, b) {
      return a.version - b.version;
    });
    for (let index = 1; index < byVersion.length; index += 1) {
      const olderVersion = byVersion[index - 1];
      const newerVersion = byVersion[index];
      if (newerVersion.updatedAt.getTime() < olderVersion.updatedAt.getTime()) {
        issues.push({ row: newerVersion.row, type: 'UPDATE_ORDER_MISMATCH' });
      }
    }
  });

  const uniqueIssues = deduplicateIssues_(issues);
  const counts = uniqueIssues.reduce(function (result, issue) {
    result[issue.type] = (result[issue.type] || 0) + 1;
    return result;
  }, {});

  console.log(JSON.stringify({
    dryRun: true,
    checkedRows: body.length,
    issueCount: uniqueIssues.length,
    counts: counts,
    issues: uniqueIssues,
    privacy: 'ファイル名・ファイルID・メールアドレスはログへ出していません',
  }));
}

function parseVersionName_(fileName) {
  const match = fileName.match(/^(.*?)[_\-\s]+v(\d+)(?:\.(\d+))?(\.[^.]+)?$/i);
  if (!match) return null;

  const major = Number(match[2]);
  const minor = Number(match[3] || 0);
  if (!Number.isInteger(major) || !Number.isInteger(minor)) return null;

  return {
    stem: match[1],
    version: major * 1000 + minor,
    versionText: major + '.' + minor,
  };
}

function normalizeStem_(stem) {
  return String(stem)
    .normalize('NFKC')
    .trim()
    .replace(/\s+/g, ' ')
    .toLowerCase();
}

function toValidDate_(value) {
  const date = value instanceof Date ? value : new Date(value);
  return Number.isNaN(date.getTime()) ? null : date;
}

function deduplicateIssues_(issues) {
  const seen = {};
  return issues.filter(function (issue) {
    const key = issue.row + ':' + issue.type;
    if (seen[key]) return false;
    seen[key] = true;
    return true;
  }).sort(function (a, b) {
    return a.row - b.row || a.type.localeCompare(b.type);
  });
}