スプレッドシートのオリジナル関数の作成方法～GASによる独自関数の利用手順
Google Apps Script(GAS)で書いたスクリプトの関数は、スプレッドシートのオリジナル関数として利用することができます。
ただ、GASによる独自関数には利用手順や注意すべきポイントがあります。
今回、GASでスプレッドシートのオリジナル関数の作成方法を解説します。
スプレッドシートのオリジナル関数をGASで作成可能
スプレッドシートには様々な関数が用意されていますが、どうしても求めている計算ができる関数がすべて揃っているわけではありません。
スプレッドシートの関数は複数組み合わせることもできますが、組み合わせでは実現できない計算もあります。
そんな時にGoogle Apps Script(GAS)のコードでオリジナル関数を自作することができます。
GASでスクリプトを書くと、必ず関数(function)を準備しますが、その関数がスプレッドシートの関数としても利用できます。
GASであれば、繰り返し処理や条件分岐を細かく設定ができるため、通常の関数では実現できないようなオリジナル関数が実装可能です。
スプレッドシートのGAS自作関数の利用条件
Google Apps Script(GAS)で作成したオリジナル関数を、スプレッドシートで利用するには条件があります。
GASプロジェクトはコンテナバインドスクリプト
GASプロジェクトはスプレッドシートに紐づくコンテナバインドスクリプトで作成することです。
スプレッドシートに紐付かないスタンドアロン型GASスクリプトでは、オリジナル関数にスプレッドシートからアクセスができません。
そのため、スプレッドシートで利用するオリジナル関数を作る場合は、必ずスプレッドシートの「ツール＞スクリプトエディタ」から作成します。
関数には戻り値(return)必須
GASの関数は戻り値は必須ではないため、returnを付けないものもあります。
ただ、スプレッドシートのオリジナル関数としてGAS関数を利用するには、戻り値は必要です。
そのため、必ず関数が終了するタイミングで戻り値を設定しましょう。
ISBNを変換するGAS自作関数のサンプルコード
今回、Google Apps Script(GAS)で作成した自作関数のサンプルコードを解説します。
ISBN13をISBN10に変換する「convertISBN13to10」関数です。
//引数に指定されたISBN13をISBN10(ASIN)に変換する関数
function convertISBN13to10(isbnNumber) {
//ISBN13桁のうち上から3桁と下一桁を取り除いた9桁を取得する
let isbn10=isbnNumber.toString().slice(3,12);
//9桁の数字を1文字ずつ分割して配列に格納する
let data = isbn10.split("");
//計算データの初期化
let calcData=0;
//9桁の各位の数字を数式のように計算して合計する
for(i=0;i<data.length;i++){
calcData += (10-i) * Number(data[i]);
}
//11-(算出した数値を11で割ったときの余り)求める
calcData = 11 - calcData%11;
//計算した数値が11だった場合、0に置き換える
if(calcData == 11){
calcData =0;
}
//それ以外で10だった場合はxで置き換える
else if(calcData == 10){
calcData = "x";
}
//9桁の数字に計算したcalcDataを末尾に付与した値を返却する
return Number(isbn10 + calcData);
}
このGASサンプルコードの関数は、引数に指定したISBN13コードをISBN10に変換します。
今回紹介したような形で実現したい処理をGASコードに組み込み、returnで計算結果を返す関数を用意します。
利用したいスプレッドシートでコンテナバインドスクリプトとして、このGASのサンプルコードを作成すると、オリジナル関数の利用する準備が完了です。
スプレッドシート上でオリジナル関数として利用手順
実際にGoogle Apps Script(GAS)のスクリプトで自作したオリジナル関数をスプレッドシートで利用してみます。
先程も紹介した通り事前準備として、利用したいスプレッドシートのコンテナバインドスクリプトとして、GASコードを用意します。
その後、スプレッドシート上で=(イコール)で用意したオリジナル関数を呼び出します。
するとデータ読み込んでいますというメッセージがセルで表示されたあと、処理結果が表示されます。
なお、GASで作成した独自関数は初回実行時、処理に時間がかかります。
GASによるオリジナル関数の注意点
なお、Google Apps Script(GAS)によるオリジナル関数は、通常のスプレッドシートの組み込み関数と比べると、実行速度は遅くなります。
そのため、GASで自作したオリジナル関数をスプレッドシート上で多用すると、スプレッドシートの負荷が増えます。
特に処理が複雑になればなるほどGAS実行に時間がかかるため、もっと時間がかかるようになります。
複雑な処理に加え、使いすぎるとスプレッドシートの表示が重たくなってしまうので、注意が必要です。
まとめ・終わりに
今回、Google Apps Scriptで作成した関数を、スプレッドシートのオリジナル関数として利用する方法を紹介しました。
あらかじめスプレッドシートに用意されている関数は汎用的なものが多いです。
少し複雑な処理やループ処理などが必要な場合は、組み込み関数だけでは実現できないことがあります。
そんな時にGASスクリプトで自作したオリジナル関数であれば、用途に応じた処理をコードで書いて求める機能を実装できます。
ただ、GASによるオリジナル関数や実行に承認が必要だったり、実行速度は組み込み関数よりも遅いといった制約もあります。
こうした制約も考慮に入れつつ、オリジナル関数が必要なケースではGASで独自関数を作って活用しましょう。