【VBA→GAS検証】Drive経由方式で失われたExcelのスピルはUNIVERSAL_WRAPで復元できるか

// EXPAND・MAXIFSは前回の記事で「自動ラップでは解決できない真の限界」と判明したため対象外にしている
const TARGET_SPILL_FUNCTIONS = ['XLOOKUP', 'FILTER', 'UNIQUE', 'SORT', 'SEQUENCE'];

function importFileViaDrive(base64Data, fileName, mimeType, sheetName) {
  const decoded = Utilities.base64Decode(base64Data);
  const blob = Utilities.newBlob(decoded, mimeType, fileName);

  const resource = { name: fileName, mimeType: MimeType.GOOGLE_SHEETS };
  const convertedFile = Drive.Files.create(resource, blob);

  try {
    const tempSs = SpreadsheetApp.openById(convertedFile.id);

    // シート名を指定した場合はそのシートだけ、未指定なら全シートを取り込む
    let tempSheets;
    if (sheetName) {
      const s = tempSs.getSheetByName(sheetName);
      if (!s) throw new Error(`シート「${sheetName}」が見つかりませんでした。`);
      tempSheets = [s];
    } else {
      tempSheets = tempSs.getSheets();
    }

    const targetSs = SpreadsheetApp.getActiveSpreadsheet();
    let totalRows = 0;
    let totalWrapped = 0;

    tempSheets.forEach(function(tempSheet) {
      const lastRow = tempSheet.getLastRow();
      const lastCol = tempSheet.getLastColumn();
      if (lastRow === 0 || lastCol === 0) return;

      // シートを丸ごとコピーする(値・数式・書式すべて含む)。
      // Range.copyTo()は同一スプレッドシート内でしか使えないため、
      // 別のスプレッドシートへもシートごと複製できるSheet.copyTo()を使う。
      const desiredName = tempSheet.getName();
      const existing = targetSs.getSheetByName(desiredName);
      if (existing) {
        targetSs.deleteSheet(existing);
      }
      const targetSheet = tempSheet.copyTo(targetSs);
      targetSheet.setName(desiredName);

      const values = targetSheet.getRange(1, 1, lastRow, lastCol).getValues();
      const formulas = targetSheet.getRange(1, 1, lastRow, lastCol).getFormulas();

      // 対象の数式が入っているセルだけを見つけて、クリーニングしてから書き換える。
      // それ以外のセル(数式ではない通常のデータ)は、コピーした時点の値・書式のまま。
      let wrappedCount = 0;
      for (let r = 0; r < formulas.length; r++) {
        for (let c = 0; c < formulas[r].length; c++) {
          let formula = formulas[r][c];
          if (!formula) continue;

          let cleaned = formula.replace(/_xlws\.|_xlfn\./g, '');
          cleaned = rewriteFilterIfEmptyToIferror(cleaned);

          const hasTarget = TARGET_SPILL_FUNCTIONS.some(fn =>
            new RegExp(fn + '\\s*\\(', 'i').test(cleaned)
          );
          if (!hasTarget) continue;

          // ARRAY_CONSTRAINの行数・列数は常に信用せず取り除く(詳細は上記の解説参照)。
          // クリアする範囲だけは申告値をそのまま使い、無関係なデータを誤って消さないようにする。
          const footprint = getSpillFootprint(cleaned) || { rows: 1, cols: 1 };
          const finalFormula = stripArrayConstrain(cleaned);

          // スピル先の範囲をクリア(clearContent()は値だけを消し、書式は残す)してから数式を書き込む。
          // コピーしてきた静的な値が残っていると、生きた数式がスピルできず#REF!になるため。
          targetSheet.getRange(r + 1, c + 1, footprint.rows, footprint.cols).clearContent();
          const wrapped = '=UNIVERSAL_WRAP(' + finalFormula.substring(1) + ')';
          targetSheet.getRange(r + 1, c + 1).setFormula(wrapped);
          wrappedCount++;
        }
      }

      totalWrapped += wrappedCount;
      totalRows += lastRow;
    });

    return `${tempSheets.length}シート・合計${totalRows}行のデータを取り込みました（自動ラップ：${totalWrapped}件）。`;
  } finally {
    Drive.Files.remove(convertedFile.id);
  }
}

/**
 * FILTER(...)の第3引数が文字列リテラルなら、FILTER呼び出し自体を
 * IFERROR(FILTER(残りの引数), 元の文字列) に書き換える（詳しくは前回の記事のStep4参照）。
 */
function rewriteFilterIfEmptyToIferror(formula) {
  const upper = formula.toUpperCase();
  const idx = upper.indexOf('FILTER(');
  if (idx === -1) return formula;

  const openIdx = idx + 'FILTER'.length;
  const closeIdx = findMatchingParen(formula, openIdx);
  if (closeIdx === -1) return formula;

  const argsText = formula.substring(openIdx + 1, closeIdx);
  const args = splitTopLevelArgs(argsText);

  if (args.length >= 3 && /^"[^"]*"$/.test(args[args.length - 1].trim())) {
    const ifEmptyText = args[args.length - 1].trim();
    const newArgs = args.slice(0, args.length - 1);
    const newFilterCall = 'IFERROR(FILTER(' + newArgs.join(',') + '),' + ifEmptyText + ')';
    const rebuilt = formula.substring(0, idx) + newFilterCall + formula.substring(closeIdx + 1);
    return rewriteFilterIfEmptyToIferror(rebuilt);
  }
  return formula;
}

function findMatchingParen(str, openIdx) {
  let depth = 0;
  let inQuote = false;
  for (let i = openIdx; i < str.length; i++) {
    const ch = str[i];
    if (ch === '"') {
      inQuote = !inQuote;
    } else if (!inQuote) {
      if (ch === '(') depth++;
      else if (ch === ')') {
        depth--;
        if (depth === 0) return i;
      }
    }
  }
  return -1;
}

function splitTopLevelArgs(argsText) {
  const args = [];
  let depth = 0;
  let inQuote = false;
  let current = '';
  for (let i = 0; i < argsText.length; i++) {
    const ch = argsText[i];
    if (ch === '"') {
      inQuote = !inQuote;
      current += ch;
    } else if (!inQuote && ch === '(') {
      depth++;
      current += ch;
    } else if (!inQuote && ch === ')') {
      depth--;
      current += ch;
    } else if (!inQuote && ch === ',' && depth === 0) {
      args.push(current);
      current = '';
    } else {
      current += ch;
    }
  }
  if (current.length > 0) args.push(current);
  return args;
}

/**
 * ARRAY_CONSTRAIN(...,行数,列数)の最後の2引数から、スピルする行数・列数を取り出す。
 * ARRAY_CONSTRAINで囲まれていない数式の場合はnullを返す(呼び出し側で1x1として扱う)。
 */
function getSpillFootprint(formula) {
  const upper = formula.toUpperCase();
  const idx = upper.indexOf('ARRAY_CONSTRAIN(');
  if (idx === -1) return null;

  const openIdx = idx + 'ARRAY_CONSTRAIN'.length;
  const closeIdx = findMatchingParen(formula, openIdx);
  if (closeIdx === -1) return null;

  const argsText = formula.substring(openIdx + 1, closeIdx);
  const args = splitTopLevelArgs(argsText);
  if (args.length < 3) return null;

  const rows = parseInt(args[args.length - 2].trim(), 10);
  const cols = parseInt(args[args.length - 1].trim(), 10);
  if (isNaN(rows) || isNaN(cols)) return null;

  return { rows: rows, cols: cols };
}

/**
 * ARRAY_CONSTRAIN(元の式,行数,列数)から、ARRAY_CONSTRAINだけを取り除いて元の式を返す。
 * 元のExcelファイルの時点でエラーだった数式は、ARRAY_CONSTRAINの行数・列数自体が
 * ブロックされていた時点の暫定値である可能性があるため、信用せずに取り除く。
 */
function stripArrayConstrain(formula) {
  const upper = formula.toUpperCase();
  const idx = upper.indexOf('ARRAY_CONSTRAIN(');
  if (idx === -1) return formula;

  const openIdx = idx + 'ARRAY_CONSTRAIN'.length;
  const closeIdx = findMatchingParen(formula, openIdx);
  if (closeIdx === -1) return formula;

  const argsText = formula.substring(openIdx + 1, closeIdx);
  const args = splitTopLevelArgs(argsText);
  if (args.length < 1) return formula;

  const inner = args[0].trim();
  return formula.substring(0, idx) + inner + formula.substring(closeIdx + 1);
}

/**
 * 単一の値・2次元配列(スピル結果)のどちらが渡されても、同じロジックで一括処理して返す万能カスタム関数。
 * @customfunction
 */
function UNIVERSAL_WRAP(inputData) {
  if (!Array.isArray(inputData)) {
    return cleanValue(inputData);
  }
  return inputData.map(function(row) {
    return row.map(cleanValue);
  });
}

function cleanValue(value) {
  if (typeof value === 'string') {
    return value.replace(/^[\s　]+|[\s　]+$/g, '');
  }
  return value;
}