【VBA→GAS検証】万能カスタム関数でExcelの数式を自動スピル対応させる

記事内には広告が含まれます

2026年9月14日2026年9月20日

はじめに

前回の記事では、GASのカスタム関数に2次元配列をreturnさせることで、Excelのスピル（FILTER・UNIQUE等の自動展開）を再現できることを実機で確認しました。ただし前回作ったMY_SEQUENCEやMY_UPPERCASE_SPILLのようなカスタム関数は、やりたい処理ごとに専用の関数を1つずつ書く必要があります。

今回は、どんな関数の結果にも対応できる「万能カスタム関数」と、Excelファイルをインポートした際に既存の数式を自動でスキャンし、その万能関数で包み込む仕組みの2つを検証します。これが実現できれば、Drive経由方式でExcelファイルを取り込んだ際にスピル先が空白になってしまう問題（gas-10の出発点になった課題）に対して、数式を1つずつ手作業で書き換えることなく、まとめて自動対応できるようになります。

全体像：どんな数式もUNIVERSAL_WRAPで包んで再展開する

前回学んだとおり、GASのカスタム関数から見ると、FILTER・UNIQUE・XLOOKUPなど元の関数が何であっても、渡ってくるデータは「単一の値」か「2次元配列」のどちらかに集約されます。つまり、この2パターンだけに対応する処理を1つ書いておけば、どんな関数の結果にも使い回せることになります。

①単一の値・2次元配列のどちらが渡されても同じロジックで一括処理する「万能カスタム関数」を作る
②既存の数式を`=万能関数(元の数式)`の形に自動で書き換える仕組みを作る
③Excelファイルをインポートした直後にこの書き換えを実行すれば、スピル欠落を自動で復元できる

実際にGASエディタとスプレッドシートで動作を確認しながら解説します。

Step1：万能カスタム関数UNIVERSAL_WRAPを実装する

/**
 * 単一の値・2次元配列(スピル結果)のどちらが渡されても、同じロジックで一括処理して返す万能カスタム関数。
 * 文字列は前後・全角スペースを除去し、それ以外の値(数値・日付・エラー等)はそのまま返す。
 * @param {*} inputData 任意の関数の結果や、セル範囲そのもの
 * @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;
}

cleanValueの中身が実際の加工ロジックです。今回は例として、Excelから移行したデータにありがちな「全角・半角スペースの混入」を一括クレンジングする処理にしました。数値や日付、エラー値はそのまま素通しします。ここを別の処理に差し替えれば、どんな用途にも同じ骨格を使い回せます。

セルに直接=UNIVERSAL_WRAP(" りんご　")のように単一の値を渡すと、前後の空白が除去された「りんご」が返りました（実機確認済み）。

A1に前後スペース入りの文字列、B1に=UNIVERSAL_WRAP(A1)を入力し、両方とも「りんご」と表示されている様子

続いて、前回同様FILTERの結果を引数として渡してみます。item・category・priceの表（前後にスペースが混じった商品名入り）を用意し、次の数式を入力しました。

=UNIVERSAL_WRAP(FILTER(D2:F5,E2:E5="fruit"))

FILTERが返した「categoryがfruitの行」3行×3列がそのままUNIVERSAL_WRAPに渡り、商品名の前後スペースが除去されたうえで、同じ3行×3列の形で再展開されました（実機確認済み）。

=UNIVERSAL_WRAP(FILTER(...))がapple/carrot/orangeの3行×3列に展開され、商品名の前後スペースが除去されている様子

Step2：エラー値を渡すとどうなるか

FILTERなどは条件に一致する行がないと#N/Aを返します。こうしたエラー値をUNIVERSAL_WRAPに渡すとどうなるかも確認しました。

=1/0で#DIV/0!エラーを発生させたセルを、=UNIVERSAL_WRAP(そのセル)に渡したところ、結果も#DIV/0!のままでした。ログで確認すると、UNIVERSAL_WRAP関数の中身（cleanValueの処理）自体は一切実行されていません。

GASのカスタム関数は、引数にエラー値が含まれている場合、関数本体を呼び出さずにエラーをそのまま伝播する仕様であることが実機で確認できました。そのためUNIVERSAL_WRAP側でエラー値を想定した分岐を書く必要はありません。

Step3：Excelインポート時に数式を自動でラップする仕組み

ここからが本題です。Excelファイルを取り込んだ直後に、シート内の数式を自動でスキャンし、対象の関数（XLOOKUP・FILTER・UNIQUE・SORT）を含む数式だけをUNIVERSAL_WRAP(...)で自動的に包む仕組みを作ります。

最初に組んだコードは次のようなものでした。

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('検証:万能関数')
    .addItem('数式にUNIVERSAL_WRAPを自動設定', 'applyUniversalWrapToFormulas')
    .addToUi();
}

function applyUniversalWrapToFormulas() {
  var targetFunctions = ['XLOOKUP', 'FILTER', 'UNIQUE', 'SORT'];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('対象シート名');
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  if (lastRow === 0 || lastCol === 0) return;

  var range = sheet.getRange(1, 1, lastRow, lastCol);
  var formulas = range.getFormulas();
  var updated = false;

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

      var hasTarget = targetFunctions.some(function(fn) {
        return new RegExp(fn + '\\s*\\(', 'i').test(formula);
      });
      if (hasTarget) {
        formulas[r][c] = '=UNIVERSAL_WRAP(' + formula.substring(1) + ')';
        updated = true;
      }
    }
  }

  if (updated) {
    range.setFormulas(formulas); // ★この一括書き戻しに問題があった
  }
}

getFormulas()でシート全体の数式を配列として取得し、対象の関数が含まれる数式だけを書き換えて、最後にsetFormulas()でまとめて書き戻す、という素直な実装です。

★重大な発見：一括書き戻しが「数式ではない生データ」を消してしまう

このコードを実機で実行したところ、対象の数式（FILTERを含むセル）は狙いどおりUNIVERSAL_WRAPで自動的にラップされました。ところが実行後にシート全体を確認すると、同じ範囲内にあった「item」「category」「price」といった見出しや、”りんご”のような数式ではない生の文字列セルが、すべて空白になっていました。

原因はrange.getFormulas()の仕様にあります。数式が入っていないセル（生の文字列や数値）は、getFormulas()では空文字列""として返されます。今回のコードは対象外のセルもそのままformulas配列に残してrange.setFormulas(formulas)で範囲全体を書き戻していますが、空文字列をsetFormulas()で書き戻すと、そのセルの中身（数式ではない生データ）ごと消去されてしまうことが実機で判明しました。数式が入っていたセル（今回変更しなかったものも含む）は同じ文字列を書き戻すだけなので影響を受けませんが、生データのセルだけが巻き添えで消えてしまう格好です。

これは前々回の記事で見つかった「GASのシート操作関数は列単位ではなく範囲・シート全体を対象にする」という一貫した落とし穴の一例です。今回はinsertRowsBefore()のような行操作ではなく、getFormulas()/setFormulas()という一見安全に見える読み書きのペアで同じ種類の事故が起きました。Excelファイルをインポートした直後のシートには、数式だけでなく見出しや元データもそのまま残っているのが普通なので、この実装のまま使うと、自動ラップのたびに周囲のデータを消してしまう危険なコードだったことになります。

修正：変更が必要なセルだけを個別に書き換える

対策として、範囲全体をまとめて書き戻すのをやめ、対象の数式が見つかったセルだけsetFormula()で個別に書き換える方式に変更しました。

function applyUniversalWrapToFormulas() {
  var targetFunctions = ['XLOOKUP', 'FILTER', 'UNIQUE', 'SORT'];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('対象シート名');
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  if (lastRow === 0 || lastCol === 0) return;

  var formulas = sheet.getRange(1, 1, lastRow, lastCol).getFormulas();
  var updatedCount = 0;

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

      var hasTarget = targetFunctions.some(function(fn) {
        return new RegExp(fn + '\\s*\\(', 'i').test(formula);
      });
      if (hasTarget) {
        var newFormula = '=UNIVERSAL_WRAP(' + formula.substring(1) + ')';
        sheet.getRange(r + 1, c + 1).setFormula(newFormula); // 該当セルだけ書き換える
        updatedCount++;
      }
    }
  }

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

getFormulas()でどのセルに何が入っているかを調べるところまでは同じですが、書き戻す際は該当セルだけをgetRange(r+1, c+1).setFormula(...)で個別に更新するようにしました。この修正版で再実行したところ、対象の数式は正しくUNIVERSAL_WRAPでラップされ、周囲の見出しや元データは一切変更されませんでした（実機確認済み）。同じシートに対してもう一度実行しても、既にUNIVERSAL_WRAPを含む数式はスキップされるため「0件」の完了通知が出るだけで、二重にラップされることもありません。

修正版を実行した結果。A10の数式が=UNIVERSAL_WRAP(FILTER(...))に自動書き換えされ、隣接するD1:F5の見出し・データはそのまま残っている様子

VBAとの対比： VBAでRange.Formulaを書き換える場合、通常はループの中で1セルずつCells(r, c).Formula = ...のように代入するため、対象外のセルに触れる余地がありません。一方GASではgetFormulas()/setFormulas()のように範囲全体を配列としてまとめて読み書きできる分、「変更するつもりのないセルまで巻き込んで上書きしてしまう」というVBAでは起こりにくい事故が起きやすいことが今回の検証でわかりました。

できること・できないことの整理

項目	結果
単一の値を万能関数で処理できるか	✅ 実機確認済み（Step1）
スピル関数（FILTER等）の結果を万能関数で処理できるか	✅ 実機確認済み（Step1）
エラー値を渡したときの挙動	⚠️ 関数本体は呼ばれず、エラーがそのまま伝播する（Step2）
対象の数式だけを自動でラップできるか	✅ 実機確認済み（Step3・修正版）
範囲全体を`setFormulas()`で書き戻す実装の危険性	❌ 数式ではない生データが消える（Step3で実機確認・要修正）
修正版（`setFormula()`個別更新）の安全性	✅ 周囲のデータを変更せず実機確認済み
同じ数式に対する再実行の安全性（冪等性）	✅ 2回目は「0件」で二重ラップされないことを確認済み

よくある質問

Q. targetFunctionsに指定していない関数（例：VLOOKUP）はどうなる？

→ 対象外の関数はラップされません。自動で対応したい関数が増えた場合は、targetFunctionsの配列に追記してください。

Q. すでにUNIVERSAL_WRAPで包まれている数式を、もう一度実行するとどうなる？

→ formula.indexOf('UNIVERSAL_WRAP')でチェックしているためスキップされます。実機でも2回目の実行は「0件の数式を自動ラップしました」という通知だけで、数式が二重にラップされることはありませんでした。

Q. getFormulas()/setFormulas()を使うときは、常に個別更新にすべき？

→ 少なくとも、対象範囲に数式以外のセル（見出しや元データ）が混在する可能性がある場合は、今回のように該当セルだけをsetFormula()で更新する方が安全です。範囲全体が数式だけで構成されていると確信できる場合を除き、範囲丸ごとのsetFormulas()は避けた方が無難です。

まとめ

GASのカスタム関数は「単一の値」か「2次元配列」のどちらかを受け取れば十分なため、加工ロジックを差し替えるだけで使い回せる「万能カスタム関数」を作れる（実機確認済み）
引数にエラー値が含まれる場合、カスタム関数の本体は呼び出されず、エラーがそのまま伝播する
既存の数式を正規表現でスキャンし、対象の関数だけを万能関数で自動ラップする仕組みも実現できる
ただしgetFormulas()で取得した範囲全体をそのままsetFormulas()で書き戻す実装は、数式ではない生データのセルを巻き添えで消してしまう危険があることが実機で判明した。該当セルだけをsetFormula()で個別に更新する方式に修正することで解決した

次回予告

今回作った自動ラップの仕組みは、あくまで検証用のシート上で確認したものです。次の記事では、あらかじめGASコードを仕込んだ「親ファイル」にExcelファイルをインポートし、実際のファイルでこの自動ラップの仕組みを検証します。さらにその後の記事で、この仕組みをDrive経由方式の完全自動アップロードパイプラインに組み込めるかを検証する予定です。