Google Spreadsheet - カスタム関数 RANGEJOIN

画像アップロードのテストを兼ねて… Google Spreadsheet カスタム関数の作り方メモ

組み込みのJOINは一次元配列のみを対象としていて２行以上の範囲には使えません。
そこで、２行以上の範囲の連結に対応した RANGEJOIN カスタム関数を作ります。

/**
 * RANGEJOIN
 *
 * @param {string} separator 区切り文字
 * @param {Range} range 範囲
 * @return {string}
 * @customfunction
 */
function RANGEJOIN(sep, range) {
  return range.join(sep);
}

カスタム関数作成のガイドライン

Custom Functions in Google Sheets の簡単な訳

命名規則

• 関数の名前は、組み込み関数と被らないように。
• アンダースコアで終了する命名は、App Scriptではプライベート関数になる為、
  カスタム関数として呼び出すことができません。

• function myFunction()

  で定義してください。
  

  var myFunction= new Function()

  は、カスタム関数としては呼び出せません。
• スプレッドシートの関数名は、慣例上大文字ですが、大文字小文字の区別はありません。

引数

組み込み関数の様に、カスタム関数も引数を取ることができます

• 単項のセルを引数に渡した場合、引数はそのセルの値
• 複数の範囲を引数に渡した場合、引数はその範囲の値の2次元配列
• 引数には、乱数(RAND())や日付(NOW()) のような、非決定的な値を取ることはできません。`Loading ....` と表示されてしまいます。

戻り値

カスタム関数は、セルに表示する値を返す必要があります

• 単一の値を返す場合、その値が呼び出し元のセルに表示されます。
• ２次元の配列であれば、呼び出し元のセルを左上として、隣接するセルが空白ならその範囲に値を展開します。
  もし範囲内のセルに値が既にある場合は、上書きはできず #REF エラーになります。
• カスタム関数では、呼び出し元のセルとその隣接セルにしか表示出来ない。
  任意のセルに値を渡すには、カスタムメニューを使う。
• カスタム関数は 30 秒以内に値を返さなくてはなりません。30秒以上経過すると、
  `Internal error executing the custom function.`とエラーが表示されます。

データ型

Google Spreadsheet では、データを異なる形で保有している為、 カスタム関数として呼び出された場合とApp Scriptとして呼び出された場合で異なるデータになる場合があります。

• シート上での時刻は、スクリプト上ではDateオブジェクトとして扱われます
• パーセントの値は、シート上では整数で現されますが、
  スクリプト上では decimal 型の少数になります。
  例えば、10% のセルの値は、スクリプトでは 0.1

Autocomplete

入力補完。記述しておくと、セル編集時に、関数名の候補や引数の説明を表示してくれるようになります。

• JsDoc で @customfunction タグをつける。

参考サイト

• Google Spreadsheet - Guidelines for custom functions
• Google Apps Script & API の日本語訳とサンプルスクリプト - スプレッドシートのカスタム関数

---

追記

より汎用的にするなら、JOIN関数と組み合わせて使うことができるように、
複数行の範囲を、一次元の配列にするカスタム関数を作る。

function FLATTEN(range){
  return range;
}

例 =JOIN(",", FLATTEN(A1:B2))