Excelの「CASキラー」から過去の化学資産を救う

function rescueCAS(input) {
    let year, month, day;
    let val = String(input).trim();

    if (val.startsWith('_')) { return val; }

    // 危険なCAS番号（真ん中01-12、お尻1-9）だけを保護
    if (/^\d+-\d+-\d+$/.test(val)) {
        let parts = val.split('-');
        let m = parseInt(parts[1], 10);
        let d = parseInt(parts[2], 10);
        if (m >= 1 && m <= 12 && d >= 1 && d <= 9) { return '_' + val; }
        return val;
    }

    // シリアル値からの復元
    if (/^\d+$/.test(val)) {
        let serialNum = parseInt(val, 10);
        if (serialNum < 60) { serialNum += 1; }
        let dateObj = new Date(Date.UTC(1900, 0, serialNum - 1));
        year = dateObj.getUTCFullYear();
        month = dateObj.getUTCMonth() + 1;
        day = dateObj.getUTCDate();
    } 
    // 日付文字列からの復元
    else if (val.includes('/') || val.includes('-')) {
        let parts = val.split(/[\/\-]/);
        if (parts.length === 3) {
            year = parseInt(parts[0], 10);
            month = parseInt(parts[1], 10);
            day = parseInt(parts[2], 10);
        }
    }

    if (year && month && day) {
        let yearStr = String(year);
        let shortYear = yearStr.length === 4 ? yearStr.slice(-2) : yearStr;
        
        // 【最新修正】Excelが削ってしまった真ん中の「0」を確実に2桁に補正（1月 -> "01"）
        let monthStr = String(month).padStart(2, '0');
        let potentialCAS = `${shortYear}-${monthStr}-${day}`;
        
        if (validateCAS(potentialCAS)) { return '_' + potentialCAS; }
    }
    return val;
}

function validateCAS(casString) {
    let digits = casString.replace(/-/g, '').split('').map(Number);
    if (digits.length < 5) return false;
    
    let checkDigit = digits.pop();
    let sum = 0;
    for (let i = 0; i < digits.length; i++) {
        sum += digits[i] * (digits.length - i);
    }
    return (sum % 10) === checkDigit;
}

function main(workbook: ExcelScript.Workbook) {
    let range = workbook.getSelectedRange();
    if (!range) { return; }

    let values = range.getValues();
    let texts = range.getTexts();
    let rowCount = values.length;
    let colCount = values[0].length;
    let newValues: (string | number | boolean)[][] = JSON.parse(JSON.stringify(values));

    for (let r = 0; r < rowCount; r++) {
        for (let c = 0; c < colCount; c++) {
            let rawValue = values[r][c];
            let displayText = String(texts[r][c]).trim();
            
            if (displayText.indexOf('_') === 0) { continue; }

            if (displayText.indexOf("'") === 0 || /^\d+-\d+-\d+$/.test(displayText)) {
                let cleanCAS = displayText.replace(/'/g, '');
                let parts = cleanCAS.split('-');
                let m = parseInt(parts[1], 10);
                let d = parseInt(parts[2], 10);
                
                if (m >= 1 && m <= 12 && d >= 1 && d <= 9) {
                    newValues[r][c] = "_" + cleanCAS;
                } else {
                    newValues[r][c] = cleanCAS;
                }
                continue;
            }

            let year: number = 0; let month: number = 0; let day: number = 0;

            if (typeof rawValue === 'number' && Number.isInteger(rawValue)) {
                let serialNum = rawValue;
                if (serialNum < 60) { serialNum += 1; }
                let dateObj = new Date(Date.UTC(1900, 0, serialNum - 1));
                year = dateObj.getUTCFullYear();
                month = dateObj.getUTCMonth() + 1;
                day = dateObj.getUTCDate();
            } else if (displayText.indexOf('/') !== -1 || displayText.indexOf('-') !== -1) {
                let parts = displayText.split(/[\/\-]/);
                if (parts.length === 3) {
                    year = parseInt(parts[0], 10);
                    month = parseInt(parts[1], 10);
                    day = parseInt(parts[2], 10);
                }
            }

            if (year > 0 && month > 0 && day > 0) {
                let yearStr = String(year);
                let shortYear = yearStr.length === 4 ? yearStr.slice(-2) : yearStr;
                
                // 【最新修正】TypeScript（Officeスクリプト）用に1桁月の0埋めを厳密に処理
                let monthStr = String(month);
                if (monthStr.length === 1) { monthStr = "0" + monthStr; }
                
                let potentialCAS = `${shortYear}-${monthStr}-${day}`;
                if (validateCAS(potentialCAS)) { newValues[r][c] = `_${potentialCAS}`; }
            }
        }
    }
    range.setValues(newValues);
}

function validateCAS(casString: string): boolean {
    let cleanString = casString.replace(/-/g, '');
    let splitChars = cleanString.split('');
    let digits = splitChars.map((char) => { return Number(char); });
    let checkDigit = digits.pop();
    let sum = 0;
    for (let i = 0; i < digits.length; i++) {
        sum += digits[i] * (digits.length - i);
    }
    return (sum % 10) === checkDigit;
}