【VBA→GAS検証】親ファイル方式でExcelインポート時にスピルを自動ラップする

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('スピル自動ラップ')
    .addItem('数式にUNIVERSAL_WRAPを自動設定', 'applyUniversalWrapToFormulas')
    .addToUi();
}

/**
 * 全シートの数式をスキャンし、対象関数を含む数式だけをUNIVERSAL_WRAPで自動的に包む。
 * 範囲一括setFormulas()は数式以外の生データを消す危険があるため、
 * 該当セルだけをsetFormula()で個別に書き換えている(前回の記事参照)。
 * targetFunctionsにEXPAND・MAXIFSがないのは、自動ラップでは解決できない真の限界のため(Step5参照)。
 */
function applyUniversalWrapToFormulas() {
  const targetFunctions = ['XLOOKUP', 'FILTER', 'UNIQUE', 'SORT', 'SEQUENCE'];
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  let updatedCount = 0;

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

    const formulas = sheet.getRange(1, 1, lastRow, lastCol).getFormulas();

    for (let r = 0; r < formulas.length; r++) {
      for (let c = 0; c < formulas[r].length; c++) {
        let formula = formulas[r][c];
        if (!formula || formula.indexOf('UNIVERSAL_WRAP') !== -1) continue;

        // ①Excel由来の名前空間プレフィックス(_xlws. / _xlfn.)を除去
        let cleaned = formula.replace(/_xlws\.|_xlfn\./g, '');

        // ②FILTER(...)のif_empty引数(第3引数の文字列リテラル)を、
        //   ネイティブのIFERRORに置き換える(UNIVERSAL_WRAPにエラーを渡さないため)
        cleaned = rewriteFilterIfEmptyToIferror(cleaned);

        const hasTarget = targetFunctions.some(function(fn) {
          return new RegExp(fn + '\\s*\\(', 'i').test(cleaned);
        });
        if (!hasTarget) continue;

        // ③ARRAY_CONSTRAINの行数・列数は常に信用せず、常に取り除く。
        //   元のExcelファイルでスピルがブロック(#スピル!)されていた場合、この数値が
        //   ブロックされた時点の小さい暫定値に書き換えられていることがあり、しかも
        //   エラーにはならず一見成功したように見えるため、エラーの有無では判定できない。
        //   クリアする範囲だけは申告値をそのまま使う(無関係なデータを誤って消さないため)。
        const footprint = getSpillFootprint(cleaned) || { rows: 1, cols: 1 };
        const finalFormula = stripArrayConstrain(cleaned);

        sheet.getRange(r + 1, c + 1, footprint.rows, footprint.cols).clearContent();
        const newFormula = 'UNIVERSAL_WRAP(' + finalFormula.substring(1) + ')';
        sheet.getRange(r + 1, c + 1).setFormula('=' + newFormula);
        updatedCount++;
      }
    }
  });

  ss.toast(updatedCount + '件の数式を自動ラップしました', '完了', 3);
}

/**
 * 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); // 他にもFILTERがあれば再帰的に処理
  }
  return formula;
}

/**
 * openIdxの"("に対応する")"を、引用符内を除いて括弧の深さで探す(Step4参照)。
 */
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;
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('スピル自動ラップ')
    .addItem('数式にUNIVERSAL_WRAPを自動設定', 'applyUniversalWrapToFormulas')
    .addItem('自動実行を有効にする（初回のみ）', 'ensureOnChangeTrigger')
    .addToUi();
}

/**
 * onChangeのインストール型トリガーを一度だけ作成する(メニューから手動実行すること)。
 */
function ensureOnChangeTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const alreadyExists = ScriptApp.getProjectTriggers().some(function(t) {
    return t.getHandlerFunction() === 'onSpreadsheetChange' && t.getEventType() === ScriptApp.EventType.ON_CHANGE;
  });
  if (!alreadyExists) {
    ScriptApp.newTrigger('onSpreadsheetChange')
      .forSpreadsheet(ss)
      .onChange()
      .create();
  }
}

/**
 * スプレッドシートの構成が変わるたびに呼ばれる(詳細は下記の解説参照)。
 */
function onSpreadsheetChange(e) {
  if (e.changeType === 'INSERT_GRID' || e.changeType === 'OTHER') {
    applyUniversalWrapToFormulas();
  }
}