GASとスプレッドシートシリーズのパート4はマクロとトリガー関数です。現場で使えるようなシフト作成用スプレッドシートの記事を書こうと思っていたのですが、まだできていないので一旦、GASとスプレッドシートでとても重要なマクロとトリガー関数(onOpen、onEdit、onInstall)についてまとめました。前回までの記事は下記になりますので、参考にして頂ければと思います。
なお、GASの公式リファレンスはこちらになります。この公式リファレンスを参考にして記事を書いています。今回の記事が一人でも多くの方の参考になれば幸いです。
※ 当記事は2019年9月現在の情報に基づいています。今後GAS(Google Apps Script)のアップデート等によって記事内記載のコードが適切に動かないといったことが起こる可能性もあります。あらかじめGASの最新状況について確認下さい。
1. マクロの使い方
まずはマクロの使い方についてです。
1-1. マクロとは
公式リファレンスでマクロが言及されている部分は以下になります。
Google Sheets lets you record macros that duplicate a specific series of UI interactions that you define.
この文章からマクロについてまとめると「ユーザーが行う一連のUI操作を記録する」機能ということです。マクロは、ルーティーンワークのような作業を記録し、それを自動実行できるので、作業を効率化したりできます。なお、記録したマクロはGASに落としこまれるため、改善することも可能です。マクロの記録から利用までの流れはいたってシンプルですが、まとめると次のようになります。
- ツールバーからマクロを選択し、作業を記録
- 記録したマクロを登録
- 記録したマクロを選択し実行(初回実行時のみGAS実行の承認が必要)
- マクロの実行完了
1-2. マクロの記録
マクロの記録の流れについてです。今回は、原本のシフト表から今月分のものを作成するマクロを記録してみました。マクロはツールバーの「ツール」ー「マクロ」を選択することで記録が開始されます。絶対参照と相対参照がありますが、マクロを使用するときの起点となるセルを絶対値とするか相対値とするかの違いです。マクロの記録は長さではなく、作業ごとになるので冷静にゆっくり確認しながら操作すれば良いです。マクロ記録の一連の流れは下記の動画に収めてあります。
マクロに記録するとGASにマクロの動きが落とし込まれます。マクロを適宜修正したい場合はこのGASコードを編集すると良いでしょう。なお、”appsscript.json”というファイルはマクロとスプレッドシートをつなぎ合わせるためのファイルで、マクロを記録すると勝手に作成されます。
1-3. 記録したマクロの実行
上記で記録したマクロを実行してみます。初回の起動時だけはGASファイルに実行権限を付与する必要があるため、権限の承認画面が出ます。これはGASとスプレッドシートについて書いた一番最初の記事(GAS(Google Apps Script)とスプレッドシート Part1(基本編))で画像付きで説明していますので、そちらで確認してみてください。記録されたマクロは次の動画のよう実行されます
以上がマクロの使い方です。マクロで記録し、GASコードを修正することでスプレッドシートの利便性を高めることができます。なお、公式リファレンスでマクロについて記載しているページはこちらになります。当ページではGASコードで自作する方法が書かれていますので参考にしてみて下さい。
2. トリガー関数
次はトリガー関数です。3本目の記事(GAS(Google Apps Script)とスプレッドシート Part3(クイックスタート編))でも触れていますが、GASにはトリガー関数が用意されています。トリガー関数は複数ありますが、使用するグーグルアプリの種類によって利用できるものは変わります。今回はスプレッドシートでデフォルトで利用できる3つのトリガー関数「onOpen」「onEdit」「onInstall」について説明します。
2-1. トリガー関数とは
トリガー関数について公式リファレンスでは次のように記載されています。
Triggers let Apps Script run a function automatically when a certain event, like opening a document, occurs. Simple triggers are a set of reserved functions built into Apps Script, like the function onOpen(e), which executes when a user opens a Google Docs, Sheets, Slides, or Forms file. Installable triggers offer more capabilities than simple triggers but must be activated before use. For both types of triggers, Apps Script passes the triggered function an event object that contains information about the context in which the event occurred.
これを意訳すると次のようになるでしょう。「GASはトリガー関数によって、ドキュメントを開く時などの特定のイベントの際に、自動で関数を呼び出し、実行させることができる。シンプルトリガー関数は、GASにもともと組み込まれているワンセットの関数のことであり、ユーザーがグーグルドキュメントやスプレッドシート、スライド、グーグルフォームを開いた時に実行されるonOpen関数がこれに該当する。インストールを前提とするトリガー関数は、シンプルトリガー関数に対しより高次の機能を提供するが、使用前にアクティベートしなければならない。GASはこの両タイプの関数に対し、イベントに起因するコンテキスト情報をもつイベントオブジェクトを送っている」。これを整理すると次のようになります。
- GASのトリガー関数には2種類ある => シンプルトリガー関数とインストールする必要があるトリガー関数
- シンプルトリガー関数は、GASの組み込み関数
- インストール型のトリガー関数はより高機能だが、事前にアクティベートする必要がある
- GASはトリガー関数に対し、コンテキスト情報をもつイベントオブジェクトを送付する
今回は、シンプルトリガー関数に該当するonOpen関数、onEdit関数、onInstall関数の3つの関数について説明します。
なお、上記の公式リファレンスでは各グーグルアプリに対応するシンプルトリガー関数とインストール可能なトリガー関数が載っているので他のアプリ等で使う場合はそちらのページを確認してみて下さい。
2-2. トリガー関数1: onOpen
onOpen関数は、スプレッドシートを開いた時に実行される関数です。開く時に実行されるため、カスタムメニューの追加のような基本機能の編集に利用されます。
コード
公式リファレンス記載のコードに自作の関数を追加したコードを実験用コードとして準備しました。このコードでonOpen関数はカスタムメニューを追加する役割を担っています。具体的にはツールバーに「Custom Menu」というメニューが追加され、開くと「First item」というサブメニューが設置されています。今回は、このサブメニューを実行すると実行した日の日にちが表示されるようにしています。
// onOpen関数
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Custom Menu')
.addItem('First item', 'menuItem1')
.addToUi();
};
function menuItem1() {
var date = new Date();
var today = date.getYear() + "/" + date.getMonth() + "/" + date.getDate();
Browser.msgBox(today);
}このコードをマクロとは別のGASファイル(デフォルトでは「コード.gs」)に保存することで準備完了です。
実行
onOpen関数を実行してみます。一度スプレッドシートを閉じ、再度開きます。そうするとonOpen関数が自動で実行され、「Custom Menu」がツールバーに追加されます。
次に「First item」を選択します。
このように実行した日の日付が表示されます。onOpen関数を利用すると、独自メニューやウィジェットを追加できるため、より利便性の高いスプレッドシートを作成することができます。
2-3. トリガー関数2: onEdit
onEdit関数は、スプレッドシートの値を変更した時に実行されるトリガー関数です。onOpen関数とは異なり、セルなどの情報を持つイベントオブジェクトを利用します。
コード
コード例は以下になります。このコードは、セルの値が変更されるたびにそのセルに最終更新時間を記載したコメントを追加します。また引数のイベントオブジェクトeからセル情報を取得しています。このコードもGASファイルに保存するだけで、その後自動で機能するようになります。
function onEdit(e) {
var range = e.range;
range.setNote('Last modified: ' + new Date());
};実行
このコードを実行すると次のように最終更新日時の時間が記載されたコメントがセットされていることがわかります。
なお、イベントオブジェクトeに格納される情報はこのような情報になります。
{
"authMode":{},
"range":{
"columnStart":4,
"rowStart":3,
"rowEnd":3,
"columnEnd":5
},
"source":{},
"user":{
"nickname":<グーグルアカウント>,
"email":<Gmailアドレス>
},
"value":"18"
}2-4. トリガー関数3: onInstall
onInstall関数は、アドオンをインストールした時に実行されます。他の2つとは異なり、基本的にはonOpen関数を呼び出すために使われるようです。これは、アドオンをインストールする際ファイルが開いているため、カスタムメニューを追加するためのonOpen関数が実行されないので、その対策としてonInstall関数が用意されたようです。
これは実行はしないので、公式リファレンスに乗っているコードを参考として載せておきます。
function onInstall(e) {
onOpen(e);
}3. 最後に
以上がスプレッドシートのマクロとトリガー関数です。スプレッドシートがエクセルに勝る点は、常にクラウド常にあるのでPCを起動しなくとも自動実行や他のプログラムとの連携が容易にできる点です。その利点に加え、今まで紹介した諸機能を使いこなすことで効率化したり、生産性を向上したりできるようになると思います。この記事を参考に、色々と試してみて頂ければ幸いです。読んで頂きありがとうございました。