インハムシステムの自動化ノート

現役エンジニアが、Excel VBA・Google Apps Scriptによる業務効率化のヒントをお届けします。

【GAS】カスタムメニューを追加してスプレッドシートからスクリプトを実行する方法

作ったスクリプトを他の人に使ってもらう段階で、必ず引っかかるのが実行方法です。「拡張機能 → Apps Script を開いて、この関数を選んで実行してください」という説明は、スクリプトを知らない人には伝わりません。

スプレッドシートの上部メニューに自分の項目を追加すれば、クリックだけで実行できるようになります。この記事では、カスタムメニューの作り方と運用上の注意点を解説します。


最小のカスタムメニュー

onOpen という名前の関数を書くと、スプレッドシートを開いたときに自動で実行されます。

function onOpen() {
  const ui = SpreadsheetApp.getUi();

  ui.createMenu('集計ツール')                      // メニュー名
    .addItem('今月分を集計する', 'aggregateMonth')  // 表示名, 実行する関数名
    .addItem('データを取り込む', 'importData')
    .addToUi();                                    // メニューを登録する
}

function aggregateMonth() {
  SpreadsheetApp.getActiveSpreadsheet().toast('集計を開始します');
  // ここに処理を書く
}

function importData() {
  SpreadsheetApp.getActiveSpreadsheet().toast('取り込みを開始します');
}

保存してスプレッドシートを再読み込みすると、「ヘルプ」の右側に「集計ツール」が追加されます。

addItem の第2引数は関数名を文字列で指定します。aggregateMonth() のようにかっこを付けると動きません。また、この方法では関数に引数を渡せません。処理を切り替えたい場合は、下のように関数を分けて書きます。

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('集計ツール')
    .addItem('先月分を集計', 'aggregateLastMonth')
    .addItem('今月分を集計', 'aggregateThisMonth')
    .addToUi();
}

function aggregateLastMonth() { aggregate(-1); }
function aggregateThisMonth() { aggregate(0); }

function aggregate(offset) {
  const d = new Date();
  d.setMonth(d.getMonth() + offset);
  SpreadsheetApp.getActiveSpreadsheet()
    .toast(Utilities.formatDate(d, 'Asia/Tokyo', 'yyyy年M月') + ' を集計します');
}

項目を整理する(サブメニュー・区切り線)

項目が増えてきたら、グループに分けます。

function onOpen() {
  const ui = SpreadsheetApp.getUi();

  ui.createMenu('業務ツール')
    .addItem('データを取り込む', 'importData')
    .addSeparator()                                  // 区切り線
    .addSubMenu(
      ui.createMenu('集計')                          // サブメニュー
        .addItem('日次集計', 'aggregateDaily')
        .addItem('月次集計', 'aggregateMonthly')
    )
    .addSubMenu(
      ui.createMenu('出力')
        .addItem('PDFで保存', 'exportPdf')
        .addItem('メールで送信', 'sendMail')
    )
    .addSeparator()
    .addItem('使い方を表示', 'showHelp')
    .addToUi();
}

「よく使う項目を上に、危険な項目は区切り線の下に」という並びにすると、誤操作が減ります。


実行前に確認をはさむ

削除や上書きを行う処理は、確認ダイアログを入れます。

function clearData() {
  const ui = SpreadsheetApp.getUi();

  const res = ui.alert(
    'データの削除',                                  // タイトル
    '「作業用」シートの内容をすべて削除します。よろしいですか?',  // 本文
    ui.ButtonSet.YES_NO                              // ボタンの種類
  );

  if (res !== ui.Button.YES) {
    ui.alert('中止しました');
    return;
  }

  SpreadsheetApp.getActive().getSheetByName('作業用').getDataRange().clearContent();
  SpreadsheetApp.getActive().toast('削除しました');
}
ButtonSet 表示されるボタン
OK OK
OK_CANCEL OK / キャンセル
YES_NO はい / いいえ
YES_NO_CANCEL はい / いいえ / キャンセル

値を入力してもらう場合は prompt を使います。

function importByMonth() {
  const ui = SpreadsheetApp.getUi();
  const res = ui.prompt('取り込む月', '1〜12の数値を入力してください', ui.ButtonSet.OK_CANCEL);

  if (res.getSelectedButton() !== ui.Button.OK) return;   // キャンセル

  const text = res.getResponseText().trim();
  const month = Number(text);

  if (!Number.isInteger(month) || month < 1 || month > 12) {
    ui.alert('1〜12の数値を入力してください');
    return;
  }

  SpreadsheetApp.getActive().toast(month + '月分を取り込みます');
}

getSelectedButton() でボタンを、getResponseText() で入力内容を取得します。入力値は必ず文字列で返るため、数値として使う場合は変換とチェックが必要です。


処理中・完了を知らせる

処理が数十秒かかる場合、画面が変わらないと「動いているのか分からない」状態になります。トーストで進捗を出します。

function longProcess() {
  const ss = SpreadsheetApp.getActive();

  ss.toast('処理を開始しました', '実行中', -1);   // -1 で自動で消えない

  const sheet = ss.getSheetByName('データ');
  const values = sheet.getDataRange().getValues();

  // 何らかの処理
  Utilities.sleep(2000);

  ss.toast(values.length - 1 + ' 件を処理しました', '完了', 5);   // 5秒後に消える
}

第3引数の秒数を -1 にすると、次のトーストが出るまで表示が残ります。完了時に上書きすることで、開始と終了が分かる形になります。

ui.alert は処理を止めてクリックを待つため、完了通知には向きません。通知はトースト、確認はalert と使い分けます。


onEditで入力時の処理を自動化する

onEdit も onOpen と同じ「シンプルトリガー」で、セルが編集されたときに自動実行されます。

function onEdit(e) {
  const sheet = e.range.getSheet();

  // 「進捗管理」シートのC列だけを対象にする
  if (sheet.getName() !== '進捗管理') return;
  if (e.range.getColumn() !== 3) return;

  // 「完了」と入力されたら、隣に日付を入れて行に色を付ける
  if (e.value === '完了') {
    e.range.offset(0, 1).setValue(new Date());
    sheet.getRange(e.range.getRow(), 1, 1, 5).setBackground('#e8f0fe');
  }
}

引数 e には編集内容が入っています。

プロパティ 内容
e.range 編集されたセル範囲
e.value 入力後の値(単一セルのみ)
e.oldValue 編集前の値
e.source スプレッドシート本体

最初に対象シート・対象列で早めに抜ける書き方が重要です。これを省くと、関係のない編集のたびに処理が走り、動作が重くなります。


シンプルトリガーの制限を知っておく

onOpen と onEdit は手軽ですが、できないことがあります。

  • 他のファイルやサービスにアクセスできない:メール送信、他のスプレッドシートの読み書き、外部APIの呼び出しは権限エラーになります
  • 編集した本人の権限で動く:閲覧権限しかない人が編集しても、書き込み処理は失敗します
  • 実行時間が短い:長い処理には向きません

メール送信などを行いたい場合は、Apps Scriptの「トリガー」画面からインストール可能なトリガーとして登録します。関数名は onEdit を避け、onEditInstalled のように別名にします(同名にすると両方が動いてしまいます)。

// インストール可能なトリガーとして登録する関数(メール送信ができる)
function onEditInstalled(e) {
  if (e.range.getColumn() !== 3 || e.value !== '完了') return;

  GmailApp.sendEmail('report@example.com', '作業完了',
    e.range.getRow() + '行目の作業が完了しました');
}

なお、インストール可能なトリガーは登録した人の権限で動くため、誰が編集しても登録者のアカウントからメールが送られます。運用上の前提として押さえておきます。


メニューが表示されないときの確認点

  1. 関数名が onOpen になっているか(大文字小文字も一致させる。onopen は動きません)
  2. スプレッドシートを再読み込みしたか(スクリプトを保存しただけでは反映されません)
  3. addToUi() を書き忘れていないか(これが無いとメニューは登録されません)
  4. onOpen の中でエラーが出ていないか(先頭で他のサービスを呼ぶと権限エラーで止まります)
  5. コンテナバインドのスクリプトか(スプレッドシートの「拡張機能 → Apps Script」から作ったものである必要があります。スタンドアロンのスクリプトにはUIがありません)
  6. 共有相手の権限(閲覧のみのユーザーにはメニューが表示されない場合があります)

onOpen の中には、メニューを組み立てる処理だけを書くのが原則です。データの読み込みや初期化をここで行うと、ファイルを開くたびに待たされ、権限エラーの原因にもなります。


ボタンとの使い分け

図形やイラストにスクリプトを割り当てる方法もあります(図形を右クリック →「スクリプトを割り当て」→ 関数名を入力)。

方法 向いている場面
カスタムメニュー 項目が複数ある、シートに関係なく使う、画面を占有したくない
図形のボタン 特定のシートで1つの操作だけ実行する、目につく場所に置きたい

両方を併用することもできます。メニューに全機能を置き、よく使う操作だけシート上のボタンにも割り当てる形が実用的です。


まとめ

  • onOpen に createMenu().addItem().addToUi() を書けばメニューが追加される
  • addItem の第2引数は関数名の文字列。引数は渡せないので関数を分ける
  • 項目が増えたら addSubMenu と addSeparator で整理する
  • 確認は ui.alert、入力は ui.prompt、進捗通知は toast
  • onEdit は対象シート・対象列で早めに抜ける
  • シンプルトリガーではメール送信や外部アクセスができない。必要ならインストール可能なトリガーにする
  • メニューが出ないときは関数名・addToUi()・再読み込みを確認する

「Apps Scriptを開いて実行」という手順が無くなるだけで、ツールは使われるようになります。作ったスクリプトが自分以外にも使われる予定なら、メニュー化まで含めて完成と考えるのがおすすめです。


他の記事でもGASやスプレッドシートの業務効率化テクニックを紹介していますので、ぜひチェックしてみてください。