【GAS活用術⑥】スプレッドシートに独自メニューを追加して関数実行

Hoshi-nok（ほしのっく）

2023年11月9日 23:39

Google Apps Script (GAS)を、もっと身近に、日々の暮らしに。

ここしばらく長文投稿が続いたので、今回は小ネタでいきます。

これまで紹介したGAS活用術で、いろいろなGASを作成しました。実行方法としては、トリガーを設定し、「毎日定時に」とか、「データがフォームから送信される度に」など、GASが自動実行されるようにしてきました。

トリガーによる自動実行は大変便利ですが、作成した関数を自分のタイミングで実行したいこともあります。実は、↓こんな方法でGASの関数を実行することができるのです。

画像でおわかりでしょうか？スプレッドシートに、独自メニュー「GAS活用術スペシャル」が追加されていますよね？

その独自メニューの項目に、それぞれGASのfunction（関数）を紐づけることで、GASをスプレッドシートのメニューから実行することができます。

このメニュー追加からのGAS関数の実行、結構短いスクリプトで実現できるので、今回はこの小ネタを紹介します。

onOpen関数にちょちょいと記述すれば

さっそくスクリプトです。

function onOpen() {
  let ui = SpreadsheetApp.getUi(); 
  ui.createMenu('GAS活用術スペシャル') 
    .addItem('タイムスタンプで並べ替え', 'sortByTimestamp') 
    .addItem('読み聞かせ日・年・組で並べ替え', 'sortByClass')
    .addSeparator()
    .addItem('今日の登録内容をLINEで通知', 'notifyDailyRegs')
    .addToUi();
}

function sortByTimestamp() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //一番左のシートを取得
  let range = sheet.getDataRange();
  let dRange = range.offset(1, 0, range.getNumRows() - 1); //見出し行を除く

  // sort({ソートをかける列の指定}, {true: 昇順, false: 降順})
  dRange.sort([{column: 1, ascending: true} //第1キー：タイムスタンプ, 昇順
              ])
}

onOpen関数は、スプレッドシートを起動すると同時に実行される特殊な関数です。ここに処理を記述しておくことで、スプレッドシートを起動すると独自メニューが追加されるわけです。

実行される関数として、タイムスタンプで並び替えするためのsortByTimestamp関数は上記に記述していますが、sortByClass関数は【GAS活用術③】、notifyDailyRegs関数は【GAS活用術⑤-2】で定義したものです。

onOpen関数、短いですね！そして、実際に追加されたメニューと比較すれば、ほぼコード解説は不要なくらい、わかりやすいかと思います。

上記のように定義後、次にスプレッドシートを起動すると、「GAS活用術スペシャル」メニューが追加されて、メニューから項目を選択すると紐づいたGAS関数が実行できます。

もちろん、メニュー名や項目名、実行したい関数などは自由に設定可能です。区切り線もつけてもつけなくても。

簡単便利

GASの関数を手動で実行する方法としては、もちろん、これまで動作確認・テストでやってきたように、スクリプトエディタを起動して、実行する関数を選択して、実行ボタンをクリックすることで可能です。

ただGASをトリガーで実行し始めたら、スクリプトエディタを起動する機会もあまりなく、わざわざエディタから実行するのは面倒な上、間違って一番上の関数をうっかり実行しがちなんですね。

こんな風にスプレッドシートのメニューから実行できると楽です。とっても簡単ですしね。

他にもスプレッドシート上にボタンを作って、クリックすると、GASの関数を実行する、なんてこともできるようです。

私はメニュー派なので、この方法をご紹介しましたが、興味があれば、ボタンでGASの関数を実行する方法も調べてみてくださいね。

今後ご紹介する活用術でも、ちょいちょいこの独自メニューから関数を起動する方法を活用していく予定です。

** これ以降は、2024/07/11に追記したものです **

onOpen関数の魔法が効かないことも

onOpen関数は、「スプレッドシートを起動すると同時に実行される特殊な関数」と上記で説明しました。

onOpenは、シンプルトリガーの一つで、onOpenとあらかじめ決められた関数名を指定すれば、トリガーを別途設定しないでも、スプレッドシートを起動すれば自動実行される、といういわば魔法のトリガーです。

ところが、この魔法のトリガー、万能ではありませんでした。魔法が効かない時があったのです。その件について、補足しておきます。

onOpen関数を定義したのにメニューが表示されない？？

実際にonOpen関数を定義しても、うまく動作しなかったのが、以下のメニューです。

【GAS活用システム⑧】と【GAS活用システム⑧-2】で、Googleカレンダーにスプレッドシートから予定を一括登録したり、月単位で予定を一括削除したりする関数を紹介しました。

それらの関数をメニューから実行できるように、onOpen関数にcreateMenuメソッドを使ったスクリプトを記述したのに、スプレッドシートを再オープンしてもカスタムメニューが表示されないのです。

ログを確認したら、シンプルなトリガーonOpenが失敗していて、以下のようなエラーが表示されていました。

Exception: The script does not have permission to perform that action. Required permissions: (https://www.googleapis.com/auth/calendar || https://www.googleapis.com/auth/calendar.readonly || https://www.google.com/calendar/feeds) at [unknown function](コード:3:20)

どうやらpermission（権限）の問題のようです。

実は、onOpenのようなシンプルトリガーは、権限が低く設定されていて、実行できる関数にも制限があるとのこと。。

公式リファレンスのシンプルなトリガーの制限事項に、「認証を必要とするサービスにはアクセスできません。」とあります。

どうやらカレンダーもこれに該当しているようで、onOpenトリガーにはカレンダー絡みの関数は記述できないようです。残念。

魔法が効かない時は地道に、関数登録→トリガー設定

onOpenの魔法が効かなくても、他にも方法はあります。

地道に、①別名で関数を定義して、②その関数をスプレッドシート起動時に自動実行されるようトリガーを設定すればよいのです。

①addSpecialMenu関数を、以下のように定義します。
（実行する関数については、【GAS活用システム⑧】および【GAS活用システム⑧-2】を参照）

function addSpecialMenu() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('GAS活用術スペシャル')
    .addItem('シートの予定をカレンダーに一括登録', 'createCalendarEvent')
    .addSeparator()
    .addItem('カレンダーの今月の予定をすべて削除', 'deleteAllEvents_thisMonth')
    .addSeparator()
    .addItem('カレンダーの来月の予定をすべて削除', 'deleteAllEvents_nextMonth')
    .addToUi();
}

②定義したaddSpecialMenu関数を以下の手順でトリガーとして登録します。

スクリプトエディタの画面左端のメニューバーから「トリガー」を選択します。

右下の「トリガーを追加」ボタンをクリックします。

実行する関数として「addSpecialMenu」、イベントのソースとして「スプレッドシートから」、イベントの種類として「起動時」を選択したら、「保存」ボタンをクリックします。

これで、スプレッドシートを閉じて、再度、起動すれば、addSpecialMenu関数が実行され、カスタムメニューが表示されるようになりました！

以上、簡単便利なシンプルトリガー、onOpenが万能ではない件についての追記でした。

以下の記事もよろしければご参照ください。

この記事が気に入ったらサポートをしてみませんか？