GAS で書いた function をスプレッドシートから呼び出してスピルする

GAS で書いた function は Google スプレッドシートから関数として呼び出すことができます。VBA と同じです。

それは検索したら出てくるのですが、今回は少し工夫して、単一のセルを渡してもセル範囲を渡しても動くようにします。一言で言うと、自作 function で Excel 関数のスピルをします。

作業環境
やりたいこと
コード
説明
新たな疑問
結び
参考

作業環境

Google アカウント（個人用）
Windows 10 Home 22H2 (OS Build 19045.3208)
FireFox 115.0.2 (64 ビット)

やりたいこと

GAS で書いた function を Google スプレッドシートから呼び出しつつ、Excel 関数のスピルを実現したいです。

TWICE 関数なる function を GAS で書いたとします。これは、引数をひとつ取って、それを文字列にしたものを2つ連結して返す人畜無害な関数です。

これをスプレッドシートから呼び出すには、セルに

=TWICE(A1)

のように入力します。A1 セルに "hey " という文字列が入っていた場合、"hey hey " という文字列を返します。

以下のように、TWICE 関数にセル範囲を渡す数式を、1か所のセル（画像では B1 セル）だけに入力し、複数の結果を得られるようにすること、つまりスピルさせるのが今回の目標です。

=TWICE(A1:A3)

関数に複数のセルを渡して一括で処理する方法に ArrayFormula 関数もありますが、まあそれはそうとしてスピルをします。

コード

function toArray2D_(x) {
  return x instanceof Array ? (x[0] instanceof Array ? x : [x]) : [[x]]
}

function twice_(a) {
  return String(a) + String(a)
}

function TWICE(a) {
  return toArray2D_(a).map(x => x.map(y => twice_(y)))
}

twice_ 関数は実際の自作 function に置き換えてください。また TWICE 関数の名前も実際に使いたい名前に変えてください。

toArray2D_ 関数は何も書き換えなくていいはずです。

説明

toArray2D_ 関数は、引き数をひとつ取り、それを2次元配列にして返す関数です。スプレッドシート上でのデータの処理に使う物なので、3次元以上の配列については考えていません。if 文を使うとカッコだらけになってテンションが下がるので三項演算子で書いていますが、if 文にするとこうなります。

function toArray2D_(x) {
  if (x instanceof Array) {
    if (x[0] instanceof Array) {
      return x
    } else {
      return [x]
    }
  } else {
    return [[x]]
  }
}

twice_ 関数が自作 function です。これはスプレッドシートからは呼び出しません。関数名の末尾に _ が付いているものは呼び出せません。

TWICE 関数が呼び出し用です。引き数をひとつ取り、それを toArray2D_ 関数で2次元配列に変換し、map 関数を二重に呼び出すことで2次元配列の各要素を twice_ 関数に渡しています。2次元配列の各要素に map しているので、戻り値も2次元配列です。この戻り値をスプレッドシート上で展開することでスピルしています。

これで、=TWICE(A1:A3) のようにセル範囲を渡すと複数の結果を得てスピルさせられるようになりました。もちろん TWICE(A1) や TWICE(12) のように単一のセルまたは値を渡した場合も動作します（そのどちらにも対応するための toArray2D_ 関数です）。

また、セル範囲の方向は縦に限りません。=TWICE(A1:B1) でも =TWICE(A1:B3) でも可能です。

もし、2つの引き数 a と b を取り、a は必ず単一のセルを、b は単一のセルあるいはセル範囲を受け取りたい場合は、

function TWICE2(a, b) {
  return toArray2D_(b).map(x => x.map(y => twice_(a, y)))
}

のようにしたらいいです。

新たな疑問

自作 function を ArrayFormula 関数に渡したら上手く動いてくれませんでした。

具体的には、引き数をひとつ取って、それを文字列として返すだけの無益無害な STR 関数を定義しました。

function STR(a) {
  return String(a)
}

E1 セルに以下の数式を入力します。B1 セルから B3 セルには何らかの文字列が入っています。

=STR(B1:B3)

すると、E1 セルに以下の文字列が入りました。

hey hey ,you you ,no way no way

これは、B1 セルから B3 セルの文字列を , で連結したものです。STR 関数は2次元配列を返すようになっていないのでこうなります。

では ArrayFormula 関数を使ってみましょう。E2 セルに以下の数式を入力します。

=ArrayFormula(STR(B1:B3))

E2 セルには E1 セルと同じ文字列が入りました。

hey hey ,you you ,no way no way

どうして。

わかったら書きます。

結び

無事に GAS の自作 function でスピルができました。「単一セルかもしれないし、セル範囲かもしれない」という不確定な存在を「一切合切悉く2次元配列に変換する関数」で確定させ、あとは map 関数（二重）に任せるという方法を取りました。関数くんたちがんばって！

for 文？　知らない。

参考

条件 (三項) 演算子 - JavaScript | MDN

タイダログ

もっと怠けますか？ (y/n)

GAS で書いた function をスプレッドシートから呼び出してスピルする

作業環境

やりたいこと

コード

説明

新たな疑問

結び

参考