Google Apps Scriptを使ってGoogleスプレッドシートにユーザー定義関数を作る方法

エクセルの場合、VBAを使用してユーザー定義関数を作ることができます。同様に、Google スプレッドシートもGoogle Apps Scriptを使ってユーザー定義関数が作れます。というわけで、ここではユーザー定義関数をサンプルで作ってみようと思います。

得点表から平均点を計算する関数を作成してみる

まず以下の表を作ってみました。

f:id:tdyu5021:20190504033450p:plain

５人の人がいて、B列に国語の点数、C列に数学の点数が載っています。国語と数学を足した得点の6人の平均点以上であれば“合格”、それに満たなければ“不合格”という文字をD列に表示する関数を作ってみたいと思います。表は現在5人ですが、人数がどんどん増えても成り立つようにします。

Google Apps Scriptを使ったことがある人なら解説不要かと思いますが、念のため順を追って解説します。

ユーザー定義関数の作成方法

まずは、メニューの「ツール」 > 「スクリプトエディタ」をクリックして、スクリプトエディタを開きます。

f:id:tdyu5021:20190504033453p:plain

すると次のような画面が現れます。

f:id:tdyu5021:20190504033500p:plain

デフォルトでは、プロジェクト名は「無題のプロジェクト」で、その中の関数名が「myFunction」とあります。ここは自由に変えてください。

ここにGoogle Apps Scriptを記述していきます。

f:id:tdyu5021:20190504033507p:plain 記述したらスクリプトエディタのメニューの「ファイル」 > 「保存」をクリックしてできあがりです。

これを最初のスプレッドシートで利用するのは簡単です。エクセルのように、作った関数名をセルの中に=をつけて記入し、引数にB列の数字とC列の数字をいれるだけです。

f:id:tdyu5021:20190504033514p:plain

というように、ただスクリプトエディタに関数をつくって、それをスプレッドシートのセルの中に記入しただけです。なのでいちいち解説するほどでもないことなのですが、Google Apps Scriptでユーザー定義関数のことに触れている情報があまりなかったので整理してみました。

最後に今回作った関数のコードを載せておきます。

ソースコード

function myFunction(a,b) {
  var bk = SpreadsheetApp.getActiveSpreadsheet();
  objSheet = bk.getActiveSheet();
  var objCell = objSheet.getActiveCell();
  var finalRow = 100;
  
  //リストの最終行を取得
  for(var i = 2; i <=finalRow ; i++){
     var rng = objSheet.getRange(i,1);
       if(rng.getValue() == ""){
          rowEnd = i-1;
　　　break;
　　}
  }

  //国語と数学の合計を求める  
  var kokugoSum = calcSum(2);  
  var mathSum = calcSum(3);
  //国語と数学の合計の平均を求める
  var avg = (kokugoSum + mathSum) /(rowEnd-1);

  //合格か不合格かを判定
  var sm = a + b;
  if(sm >= avg){
  return "合格";
  }
  else{
  return "不合格";
  }
}

//合計を求める
calcSum =function(x){
  for(var i = 2, sum =0; i <=rowEnd ; i++){
  var rng = objSheet.getRange(i,x);
  var valu = rng.getValue();
  sum += valu;
  }
  return sum;
};