Google Spread Sheet の Spill 関数の便利さ

map は Spill 関数(formula)のひとつ。 一番簡単な例として =map(A1:A3, lambda(it, it*2)) を考える。

これは A1:A3 の範囲を配列として受取り、lambda 関数を配列のそれぞれの要素に map します。 ECMAScript(JavaScript) で表現すれば次の通りです:

const results = [1, 2, 3].map((it)=> it*2)
console.log(results)

結果は次の配列 [ 2, 4, 6 ] になりますが、3つの要素があるので一つのセルに収まりません。 あふれた部分が垂直方向に Spill されます。

本題とは関係ないのですが、 もし垂直方向ではなく水平方向に Spill させたければ transpose をいっしょに使います。

=transpose(map(A1:A3, lambda(it, it*2)))

そして、この map の最初に適用する配列は必ずしもシートの範囲である必要はないということです。 たとえば、 [1, 2, 3] の配列を返すカスタム関数 getValues() を定義したとします。

function getValues(){
  return [1, 2, 3];
}

そして A1のセルに次の Formula を定義:

=map(getValues(), lambda(it, it*2))

結果は次のようになります。

そもそも Spill 関数を体験したいだけなら map や lambda など使わなくても、そのまま =getValues() を使っても Spill します。

pokemons JSON

この機能を使ってもう少し具体的な例を考えます。 次のような JSON があったとします。

pokemons.json

{
  "items": [
    {
      "id": 1,
      "name": "Pikachu"
    },
    {
      "id": 2,
      "name": "Metapod"
    },
    {
      "id": 3,
      "name": "Piplup"
    }
  ]
}

この JSON をコンパクト形式に直した上で A1 に配置しておきます。（つまり、これをデータソースとして配置しておく。）

$ cat pokemons.json | jq . -c
{"items":[{"id":1,"name":"Pikachu"},{"id":2,"name":"Metapod"},{"id":3,"name":"Piplup"}]}

そして、 toPokemonName カスタム関数を定義:

function toPokemonName(json, id){
  const items = JSON.parse(json).items.filter((it)=> it.id==id);
  if( items.length>0 ){
    return items[0].name;
  } else {
    return 'UNKNOWN';
  }
}

A2 に次の Formula を記述:

=toPokemonName(A1, 1)

結果は次のようになります:

1 を指定したので 1 のIDを持つ Pikachu が表示されました。

ならば、たとえば、A2:A4 に Pokemon ID を記述しておき、B2 に次の Formula を記述:

=map(A2:A4, lambda(id, toPokemonName($A$1, id)))

結果は次のようになります:

つまり、A2:A4 は人間がメンテナンスするセル（自由に人間が編集する） 残りの部分（といってもこの例では、たかだか B2:B4 までの範囲に過ぎないが） はカスタム関数が計算する、という形のシートをつくることができる。

A5 にも追加で ID を記入してそれを反映したければ B2 の関数を書き直す必要が生じる。 もし範囲の指定方法を A2:A4 ではなく A2:A* のように記述したら値が存在するだけ自動で範囲設定できるような記述があればいいのに。 （MS-Excelにはそういう記述方法があった記憶がある。） たぶん、Google Spread Sheet でも方法があるに違いない（知らない）。

ありました。単に A2:A と書けばよいようです。
ただ、実質 A2:A10000 のように十分大きい数を指定したのと同じに振舞うらしく 値があるところまで 指定されるわけではないようです。
したがって map 関数にそれをを使うと値がないところまで範囲指定されているため lambda 関数側では値が無い場合を想定した定義が必要です。 単に if_error で囲めばいいとか、そんな程度のことですが。

以上です。