2021年NI+C Advent Calendar 2021の18日目です。
目次
- はじめに
- Google Apps Scriptとは
- Google Apps Scriptの使い方
- よく使うメソッド（書き方）
- スクリプトを読み解く
- 終わりに
はじめに
Googleスプレッドシートを上手く活用するためのカスタム関数の作り方についての記事になります。
カスタム関数はGoogle Apps Scriptを使って作成していきます。
カスタム関数を作る上で必要となるGoogle Apps Scriptの書き方についてまとめました。
Google Apps Script（GAS）とは
javascriptをベースとしたGoogleによって開発されたプログラミング言語です。
実行環境の設定等の面倒な事前準備が必要がなく、すぐに始められるため、初めてプログラミングをする人でも触りやすいと思います。
カスタム関数を作成する以外にも様々な活用方法がありますが、それらに関しては他記事参照。（Webアプリ、チャットボットetc）
Google Apps Scriptの使い方
1.Googleスプレッドシートを開き、ツールバーの拡張機能からApps Scriptを押下します。
2.関数の{}内に処理スクリプトを追加し、Ctrl + Sで保存します。
3.作成した関数を実行します。
関数の実行方法は３種類あります。
- エディタ画面から実行
- ボタン（図形）を配置して実行
- ツールバーから実行
エディタ画面から実行
エディタ画面上部で実行する関数を選択し実行ボタンを押下します。
ボタンを配置して実行
スプレッドシートで図形を配置し、そこに実行する関数を割り当てて実行します。
ツールバーから実行
スクリプト内にツールバーに関数を表示する処理を追加し、メニューからスプレッドシートの実行します。（詳しくは他記事参照）
よく使うメソッド
スプレッドシートを活用する上で基本となるメソッド（取得・操作方法）を記載します。
データ取得
- スプレッドシートの情報を取得：SpreadsheetApp.getActive()
- まずはこれ。スプレッドシートの情報を取得します。
- シートの情報を取得：.getSheetByName()
- シートを指定してスプレッドシート情報からシート単体の情報に絞り込みます。
- セル範囲の情報を取得：.getRange()
- セル単体や範囲を指定して情報をさらに絞り込みます。
- 値の情報を取得：.getValue()／.getValues()
- getRangeで指定した範囲のセルの値を取得しリストで返します。
データ挿入
- 値を挿入：.getValue()／.getValues()
- 指定したセルに値を挿入します。
合わせて覚えたい
- 値をリスト化：.flat()
- .getValuesで取得した値リストを一次元に整えます。
- 後述のfor文で使う際に便利です。
ここで例を一つ
スプレッドシートのシート名「test」、セルA1に書いてある内容をセルB1に書き写す場合
こんな感じです。これがカスタム関数を作る上での基本となります。
ここにプログラムチックな２つの要素を追加して活用していきます。
それが「if文」と「for文」です。
if文
条件分岐させる場合に使う書き方で、「もし条件1ならパターンAの処理をしたい、条件2ならパターンB、それ以外ならパターンCの処理」といったように設定します。
書き方はこんな感じ
上から順番に条件確認が行われ、最初に条件が合ったものの処理が行われます。
ifだけでも使用可能です。else if、elseは必要なければ設定する必要はありません。
長くなってしまうので、詳しくは「javascript if文 書き方」で検索すると条件式の書き方がわかると思います。
for文
繰り返し処理を行う場合に使う書き方で、「ここからここまでAの処理をしたい」といったように設定します。
()にスタートの値、繰り返し条件、繰り返し毎の値の増減を設定します。
例えば0番目から始まって10番目まで繰り返し処理を行う場合はこのようになります。
長くなってしまうので、詳しくは「javascript for文 書き方」で（略
スクリプトを読み解く
ここで一つ例としてシート名「result」にあるテスト結果からAクラスの人の点数平均を算出し、シート名「average」に挿入するを記載します。それを読み解いてみましょう。
シート「result」
スクリプト
解説
まずはスプレッドシートお作法部分
スプレッドシートの情報を取得し、どのシートを使うか設定します。
次にテスト結果が書いてあるセル範囲を設定し、値を取得します。
for文とif文を組み合わせて、リストを0番目から順番に参照して、「クラスがA」の場合は得点を加算します。それ以外は無視します。
for文の繰り返し処理が終わったら加算した得点から平均点を算出し、averageシートに算出した平均点を挿入します。
終わりに
今回はスプレッドシートのカスタム関数を作成する上で必要となる基本的な部分をまとめてみました。
Google Apps Scriptは開発環境の準備も必要ありませんし、プログラミング初心者の方でも触れやすいかと思います。
今回は単純な関数を作成してみましたが、もしご興味を持っていただけたら、より詳細な内容や注意点をまとめた記事を投稿されていますので、そちらを参考にご自身の業務に合った関数を作ってみてください。