【GAS】Googleスプレッドシートでカスタム関数を作成する時のTips

今回は Googleスプレッドシートの GASで自作するカスタム関数（自作関数、オリジナル関数）を作る際のTipsを書きたいと思います。

これまでも mir の noteでは

何度か カスタム関数は登場していますが、改めてGASカスタム関数の作成時の注意点やコツ、テクニックをまとめておこうかなと。

先週のnoteは 「Googleドキュメントから GASを使ってハイライトした単語をGoogleスプレッドシートへ色別に書き出す」というネタを書きました。

Googleスプレッドシートのカスタム関数の使いどころ

以前は シート関数の組み合わせで対処できない複雑な計算処理や ChatGPTの回答を出力するGPT関数など、GASでカスタム関数を作成するケースが結構ありましたが、最近はだいぶ減った気がします。

カスタム関数より LET、LAMBDA、名前付き関数 が便利かも

GASのカスタム関数を使い機会が減った理由は「名前付き関数」という機能が使えるようになったことが大きいです。

以前はシート関数の組み合わせでは難しかった処理も、LET、LAMBDAの登場で 基本的にはほぼ全て頑張れば対応できるようになりました。

難しい場合は作業エリアを挟めば良いですしね。

つまり シート名やセルの色など、どうやってもシート関数では扱えないものを除けば、シート関数を組み合わせた数式で良いってことです。（基本的にはそっちの方が処理は早いです）

さらに複雑な数式を名前付き関数として定義すれば、セルの値を計算・加工する処理なら カスタム関数の出番はほぼありません。

最近だと、スプレッドシートのデータを note 上で使える表の TEX記述に変換する式の作り方の回で 名前付き関数を取り上げています。

https://note.com/mir4545/n/n38441a07b6e8

GPT関数も使わなくなったかも

また、かつてはGPT関数などもコードを書いてカスタム関数としていましたが

今や自分でコードを書かずに、簡単で安全にGPT関数が利用できるアドオンがありますし

わざわざGPT関数化せずとも Geminiサイドバーや Geminiアプリの ファイル指定で解決することが多いんですよね。

GAS カスタム関数の使いどころ

というわけで、最近のカスタム関数の使いどころとしては

• シート名やシートIDなどをシート情報を使った処理

• セルの色や文字の色、ハイパーリンクなどのセルの装飾情報を使った処理

• 外部APIを利用した処理（IMPORTDATA関数で出来る場合もあり）

この辺りがメインかなと思います。

これらは組み込みのシート関数では取得出来ませんし、GASを実行するよりも引数を与えてシート上で結果を返す カスタム関数として使いたいって需要が高いかなと。

GASの実行と違ってカスタム関数は 権限がかなり制限されますが、スクリプトの初回認証が不要という点も魅力です。

このうちシート情報を取得するカスタム関数は、以前noteで紹介しています。

とういわけで、今回は2つ目の セルの装飾情報 を扱うカスタム関数をメインに取り上げていきます。その作成過程で カスタム関数を上手に使う為のTipsについて書いていきます。

カスタム関数でセルの情報を扱う際の注意点

セル内の値ではなく、セルの塗りつぶし色や文字の装飾情報をカスタム関数で扱う場合、引数でセル範囲を指定 する際にに注意が必要です。

セルの背景色を扱うカスタム関数の引数設定

たとえばGASでセル範囲の色（塗りつぶしの色）を取得したい場合、rangeに対して getBackGrounds()を使用します。（単体セルなら getBackGround()でも可）

セルを引数として、塗りつぶしの色の色コードを出力するカスタム関数を作ると、

function BGCOLOR(range){
  const sheet = SpreadsheetApp.getActiveSheet();
  const colors = sheet.getRange(range).getBackgrounds();
  return colors;
}

こんな感じになるんですが、これをシート上で使う際

引数のセル範囲を A1:B3 としてしまうと、このようにエラーとなります。

エラーの原因はログを出してみればわかりますが、

引数の range を A1:B3 と指定してしまうと、

このようにセル範囲の値の二次元配列

が引数として渡されていることになるので、

const colors = sheet.getRange([ [ 'A', 'D' ], [ 'B', 'E' ], [ 'C', 'F' ] ]).getBackgrounds();

こんな処理をしてるってことです。

そりゃ当然 getRangeはエラーになりますね。

これを回避する為に、一般的に今回のようなセルの色を取得するカスタム関数の場合は引数を "A1:B3" と セル範囲をA1表記で記した文字列で指定することになります。

でも、これってユーザーに周知しづらいし、行挿入でも範囲が拡張しないしフィルコピーで相対参照も出来なくて使いづらいんですよね。。

だから、今回はまず セルの色を取得するカスタム関数で 通常は "A1:B3" と文字列で指定するところを A1:B3 と参照指定で書く為のTIPsを紹介します。

もちろん 組み込みのシート関数と同じように 絶対参照や別シートの参照でも機能するものとしていきます。

色を取得するカスタム関数で引数をセル参照で書く為のTips

いきなり回答です。引数を範囲指定で記述するコードがこちら。

/**
セルの塗りつぶし色のコードを返す関数 
*
* @param {A1:B3} range 対象となる範囲
* @return {String} 色コードの配列 
* @customfunction
*/
function BGCOLOR(range){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let formula = sheet.getActiveRange().getFormula(); //Tips1
  formula = formula.toUpperCase(); //Tips2
  const rangeString = formula.match(/BGCOLOR\((.+?)\)/)[1];  //Tips3

  const targetSheet = rangeString.includes("!")? ss : sheet ;  //Tips4
  const colors = targetSheet.getRange(rangeString).getBackgrounds();  //Tips5
  return colors;
}

このコード内の 5つのTipsについて解説していきます。

Tips0 冒頭にドキュメンテーションコメントを入れて 関数の予測入力対応

過去にも何度か登場してますが、カスタム関数冒頭の

/**
セルの塗りつぶし色のコードを返す関数 
*
* @param {A1:B3} range 対象となる範囲
* @return {String} 色コードの配列 
* @customfunction
*/

👆このドキュメンテーションコメント（JSDoc）は入れときましょう。

これを入れるだけで

スプレッドシート上で セルに =と打った後に入力していくと 関数候補として組み込み関数と同じように、途中まで関数名を入力すると 予測入力でカスタム関数が表示され、

このように説明も表示されます。

この機能 スマホアプリ版のスプレッドシートでも効果があるので便利です。

ちなみに 

この部分だけは説明に反映されないので、カスタム関数では記述をカットしてしまっても良いです。

Tips1 アクティブを理解して数式を文字列として取得

function BGCOLOR(range){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let formula = sheet.getActiveRange().getFormula(); //Tips1

まずは 序盤の部分を解説していきます。

• Googleスプレッドシートのコンテナバインドスクリプトにおける アクティブなスプレッドシート（getActiveSpreadsheet）は、そのコードが書かれたエディタが格納されるスプレッドシートを指します。

つまり「この」スプレッドシートです。

一方、getActiveSheet()で 取得できるアクティブなシートは コードの実行方法によって違ってきます。

つまりカスタム関数BGCOLOR で getActiveSheet() した場合は、シート1の 適当なセルに入れた=BGCOLOR(XXX) の場合アクティブシートはシート1ですが、シート2のセルに入れている =BGCOLOR(XXX) のアクティブシートはシート2となるわけです。

では、getActiveRange() で取得できる アクティブなセル（Range）はどうなっているか？

これは アクティブなシートと同じ考え方になります。

まとめると、

カスタム関数におけるアクティブとは、こういうことです。

というわけで Tips1 の

この記述で

カスタム関数を入れたセルから getFormula() で数式を取得しているので、

=bgcolor(A1:B3)

という文字列が formula に格納されます。

式を入れた自分自身のセルから式を文字列で取得する・・・循環参照っぽいですがスクリプト内の処理なのでエラーにはなりません。

Tips2 toUpperCaseで 大文字・小文字のゆらぎに対応

formula = formula.toUpperCase(); //Tips2

次に =bgcolor(A1:B3) という文字列から A1:B3の部分の文字列だけを取得します。

ここで早速 正規表現を使ってぶっこ抜きたいところですが、気になるのは シート上では関数名を小文字で使ってる点。

カスタム関数は、シート上で利用する際に 関数名の大文字、小文字を区別しない仕様なのです。

=SUM(A1:B3) を =sum(A1:B3) と書いても動く 組み込みのシート関数と一緒ですね。

この大文字・小文字のゆらぎをそろえる為に Stringクラスの toUpperCase() を使って全てのアルファベットを大文字に揃えます。

formula を大文字にして再代入しているので、ここは letで宣言しています。

Tips3 matchと正規表現 キャプチャグループで セル位置文字列を取得

const rangeString = formula.match(/BGCOLOR\((.+?)\)/)[1];  //Tips3

関数名を大文字に揃えた =BGCOLOR(A1:B3) から、いよいよ A1:B3 だけを抜き出しましょう。

ここで登場するのが match() と正規表現です。

今回の関数は引数が一つで、BGCOLOR(この部分を取得) となっているので、

正規表現はこのように記述しています。

わかりづらいですが、もともとの関数の () 部分 は正規表現だとグループ化する為のメタ文字として扱われてしまうので、カッコを普通に文字として扱ってもらう為に \ を前に付けてエスケープしています。

また、matchの返り値は配列で 一つ目のキャプチャグループは

2番目[1] となるので

const rangeString = formula.match(/BGCOLOR((.+?))/)[1]

とす記述して rangeStringに A1:B3 というセル位置の文字列 を格納します。

Tips4 別シートを参照しているかどうかを判断し分岐させる

ここまでくれば、あとは

sheet.getRange(rangeString)

で 対象セルを取得すればOKじゃない？って思いたくますが、まだ他のシートを参照するケースを考慮していません。

つまり

別シートのセルを指定する参照にも対応させる必要があるってことです。

それが

 const targetSheet = rangeString.includes("!")? ss : sheet ;  //Tips4

この部分です。

まず BGCOLOR(この部分を取得)　で取得したセル位置を表す文字列の中に、
「!」があったら他のシートを参照していると判断して処理を分岐します。

ここは、特定の文字を含んでいるかどうか？を TRUE,FALSEで返す includes() メソッドが最適ですね。

で、その後の分岐を三項演算子で、

const targetSheet = rangeString.includes("!")? ss : sheet

と書いて

と分岐して targetSheet に格納しています。

Tips5 getRange() でシート指定もできる & 絶対参照の$があっても問題なし

getRange(a1Notation) は、先にシートを指定して シートクラスで sheet.getRange("A1") という記述で使うことが多いですよね。

実はシートクラスではなく、スプレッドシートクラスにも getRange(a1Notation) があって  ss.getRange("シート1!A1") と引数でシート込みでセル範囲を指定することが出来ます。

でも 実は 👆 こんな感じで sheetクラスの getRange(a1Notation)でも シート名を指定すれば別シートのセルを参照出来るんですよね。

だから

/**
セルの塗りつぶし色のコードを返す関数 
*
* @param {A1:B3} range 対象となる範囲
* @return {String} 色コードの配列 
* @customfunction
*/
function BGCOLOR(range){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let formula = sheet.getActiveRange().getFormula(); //Tips1
  formula = formula.toUpperCase(); //Tips2
  const rangeString = formula.match(/BGCOLOR\((.+?)\)/)[1];  //Tips3

  const colors = sheet.getRange(rangeString).getBackgrounds();  //Tips5
  return colors;
}

👆このように Tips4をすっとばしてもいいんですが、シートクラスで別シートを指定って変な感じがするんで、今回は少し丁寧にスプレッドシートクラスで記述する方法としました。

面倒なら sheet.getRange(rangeString) でまったく問題ないです。

ちなみに、セルを絶対参照とした場合の  $ですが

getRange(a1Notation) で取得する際に まったく影響しません。$はあっても無くても気にする必要はありません。

Tips0～5を踏まえて作成したカスタム関数は、シート上で入力時に範囲をマウス選択で指定できるようになり

さらに対象範囲の拡張にも自動で連動するようになりました。

まずは、セルの色などセルの装飾を扱うカスタム関数のTipsが理解できたでしょうか。

カスタム関数の 再計算を制御する為のTips

続いて GASのカスタム関数でたびたび問題となる、再計算に対応する為のTipsを見ていきましょう。

再計算して欲しくないのに、シートを開きなおした際に 一気に再計算が走ってシートが重くなったり、再計算して欲しい時に再計算してくれなかったりと、カスタム関数の再計算の制御 は悩ましいと感じてる人が多いんじゃないでしょうか？

Tips6 カスタム関数の再計算をスイッチで実行する

たとえば、今回のようなセルの色を扱うカスタム関数の場合、セルの色の変更は 引数の変更ではないので検知できず、セルの色を変えても再計算されず結果が変わりません。

これを解決する最も簡単な方法は、ダミーの引数を再計算スイッチとする方法です。

/**
セルの塗りつぶし色のコードを返す関数 
*
* @param {A1:B3} range 対象となる範囲
* @param {F2} recalc 再計算スイッチセル
* @return {String} 色コードの配列 
* @customfunction
*/
function BGCOLOR(range,recalc){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let formula = sheet.getActiveRange().getFormula(); //Tips1
  formula = formula.toUpperCase(); //Tips2
  const rangeString = formula.match(/BGCOLOR\((.+?)\)/)[1];  //Tips3

  const targetSheet = rangeString.includes("!")? ss : sheet ;  //Tips4
  const colors = targetSheet.getRange(rangeString).getBackgrounds();
  return colors;
}

たとえば 今回のカスタム関数に2つ目の引数として recalc を設定したとします。

recalcは実際の処理の中では一切使われないダミー引数です。

ただし、引数が増えたので matchで セル範囲を文字列として取得する match 部分の記述は少し変える必要があります。

第2引数の recalcを F2セルとしてシート上で使ってみましょう。

色の変更では再計算されませんが、第2引数として設定している再計算スイッチのF2セルに変化があると カスタム関数が再計算されているのがわかりますね。

このように ダミー引数を再計算スイッチとする方法が、もっとも負荷なく手間少なく再計算の実行を制御できる方法かなと考えます。

Tips7 カスタム関数の無駄な再計算を反復計算で止める

それでは逆に シートを更新（開きなおし）した時に 無駄に走る再計算を止めたい場合はどうすればよいでしょうか？

実はこの方法は、以前の GPT関数の回で触れています。

ここで「アレ」と表している「反復計算」のテクニック。

これをアレンジして、先ほどのF2のチェックボックスを今度は 計算固定スイッチとして利用してみましょう。

まず ファイル ＞ 設定 ＞ 計算タブ で 反復計算を オンにします。（最大反復回数は1としておきましょう）

カスタム関数は Tips6でアレンジしたものではなく、元のコードのものを使用します。

/**
セルの塗りつぶし色のコードを返す関数 
*
* @param {A1:B3} range 対象となる範囲
* @return {String} 色コードの配列 
* @customfunction
*/
function BGCOLOR(range){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let formula = sheet.getActiveRange().getFormula(); //Tips1
  formula = formula.toUpperCase(); //Tips2
  const rangeString = formula.match(/BGCOLOR\((.+?)\)/)[1];  //Tips3

  const targetSheet = rangeString.includes("!")? ss : sheet ;  //Tips4
  const colors = targetSheet.getRange(rangeString).getBackgrounds();
  return colors;
}

こちらの方法は、カスタム関数のコードは一切変更しなくてよいのが利点と言えます。

で、セルにそのままカスタム関数を記述するのではなく

このように IF関数でくくって

という分岐処理を記述します。

F2が未チェックのロックしていない状態であれば、引数のセルに変化があった場合、カスタム関数は再計算されますが

チェックを入れてロックをすれば 再計算を停止させ、今表示されている{D1:E3} をそのまま表示することが出来ます。

👆 直接 BGCOLOR関数を入れている下の結果は 再計算されましたが、上のIF関数でスイッチ固定した式は 値に変化が無いのがわかりますね。

結果が展開されるセル範囲を {D1:E3} と手動記述しているのがイマイチですが、カスタム関数の再計算を出来るだけ無駄に発生させず、負荷をかけない手段としては有効じゃないでしょうか。

カスタム関数の再計算を制御するTipsを2つ紹介しました。

二次元配列（セル範囲）をまとめて処理する為のTips

最後のTipsは一つの式で複数セルへ結果を返す、スピル式（配列を返す式）とする為のTipsです。

カスタム関数は多用すると非常に重くなるので、1セル1式ではなく、なるべく一つの式でまとめて配列処理が出来るように最適化する必要があります。

セルの色を扱うケースは getBackgrounds() というメソッドが セル範囲の塗りつぶしの色をまとめて取得して、色コードの文字列を二次元配列で返すことが出来るんで意識せず 配列処理が出来ていますが、メソッドや処理内容によっては引数が二次元配列だった場合を意識してカスタム数式を作成する必要があります。

公式のサンプルコードで map のネストを理解する

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

公式のサンプルコードが、まさに二次元配列に対応した最適化されたコードなんですが、ポイントは

input.map(row => row.map(cell => cell * 2))

この部分です。

配列メソッドの map の理解が必要になりますが、この2段階mapは Googleスプレッドシートのセル範囲に対して GASで二次元配列を処理する基本となります。

そもそも サンプルの カスタム関数 DOUBLE(input)は、inputの数値を2倍にするだけのシンプルな関数です。

ただ、シート上の計算なら

このようにARRAYFORMULAを付ければ シンプルに 配列 *数値 で 配列内部が全て *2（2倍） されるんですが、GAS（JavaScript）だとこれが出来ません。

上の場合 A1:E5 は

このような二次元配列として取得されるんですが、これに直接 演算子で *2 と計算しても 型が違うので 演算子が機能しません。

その為の上の配列から1つ1つの要素である 数値を取り出してから *2をする必要があります。

ここの処理を簡単に記述できるのが 配列メソッドの map です。

ただしスプレッドシートのセル範囲は 二次元配列となっているので、map 1回で取り出したものは 一次元配列（行データ）となります。

その為、二次元配列 input の要素である row （行単位の一次元配列）をまず取り出し、一次元配列の row に対してもう一度 map で 個々の要素（cell）を取り出してから実際にやりたい処理（cell*2）をする。

この手順を繰り返す二重ループ処理となります。

つまり  

input.map(row => row.map(cell => cell * 2))

この 2段階mapは

  for(i=0; i<input.length; i++){
    for(j=0; j<input[0].length; j++){
      input[i][j] = input[i][j] * 2 ;
    }
  }

forをネストした、こんな処理と一緒ってことですね。

シート関数のMAPと同じで 元の配列と同じサイズの配列を返すので、配列の成形を気にしなくてよいのも便利です。

ちなみに このケースは たとえば =DOUBLE(E5) と単体セルを引数に指定した場合は、inputは 配列でなく単体の数値となる為 mapが使えません。

単体セルだった場合を想定し 処理を分岐させる為に

Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;

Array.isArray() で inputが配列か？を 判別して 三項演算子で 分岐処理させ、配列でなかった場合は input * 2 としています。

Tips8 配列処理できる最適化した式を作る。二次元配列は2段階 mapで 処理する

では、この 二次元配列を2段階 mapで処理する 実践例を見てみましょう。

たとえば、このようにE1:F4セルにある ハイパーリンクが設定されたテキストから、ハイパーリンクのURLだけを抜き出したいといったケース。

これはGASを使わないと無理な処理なので、カスタム関数の出番となります。

/**
 * ハイパーリンクのURLを抜き出す関数
 *
 * @param {A1:B2} range 対象となる範囲
 * @return {String} リンクURLの配列   
 * @customfunction
 */
function GETLINKURL(range) {
  const sheet = SpreadsheetApp.getActiveSheet();
  let formula = sheet.getActiveRange().getFormula(); 
  formula = formula.toUpperCase(); 
  const rangeString = formula.match(/GETLINKURL\((.+?)\)/)[1]; 
  const richTextArray = sheet.getRange(rangeString).getRichTextValues();
  const url = richTextArray.map(r => r.map(cell => cell.getLinkUrl()));
  return url;
}

コードの前半は 先ほどの Tips1～5がベースです。

今回は rangeが別シートの場合でも分岐せずにsheet.getRnage(rangeString) として簡略化しちゃっています。（これで動きます）

ハイパーリンクのURLはrangeから直接取得できず、一旦リッチテキストを取得して、そこから抜き出す処理になります。

リッチテキストはrangeに対して getRichTextValues() すれば、二次元配列で抜き出せます。

これは単体セルを指定した場合でも [[richtextvavlue]] と二次元配列になるので、先ほどのように Array.isArray() で判定し分岐する必要はありません。

しかし、リッチテキストから リンクURLを取得する getLinkUrl() は配列に対してそのまま使えないので、二次元配列内の個々の要素であるリッチテキストを取り出して使用する必要があります。

ここで、

const url = richTextArray.map(r => r.map(cell => cell.getLinkUrl()));

map の2段階処理が必要となります。

先ほど書いた通り 👆 このように mapは 元の配列のサイズ（形）そのままに結果を返せるのが魅力です。

ちなみに 配列だったら そこから要素を取り出して処理（map）の部分は、やや難しいですが再帰で処理する方法もあります。

👇こちら officeの杜さん。非常に内容充実しています。

カスタム関数を活用する為のTipsまとめ

まとめです。カスタム関数を上手に利用する為の

0～8までの 9つのTipsを紹介しました。

他にも日時を扱う時や外部APIを利用する時のTipsがあるんですが、これはまた別の機会に書きたいと思います。

知ってるネタもあったかもしれませんが、一つでも発見や今まで抱えてた問題の解決に繋がるTipsがあれば幸いです。

是非 スキをポチっとお願いします！

実務でこれらのTips（再計算制御以外）が活用できるケースだと、たとえば

こんな感じでセルの色 でSUMIFしたい！みたいなケースじゃないでしょうか。

今回はコチラのカスタム関数まで書きませんが、機会があれば実際のコードと解説を noteに書きたいと思います。

GAS回が続いたので、次回はシート関数ネタを予定。