スプレッドシートに図形でボタンを作成しGASを実行＆トリガーを使ったメニューの追加：Excel VBAプログラマーのためのGoogle Apps Script入門（9）

Googleが提供するGoogle Apps Script（GAS）のプログラミングで、Google Apps（主にスプレッドシート）を操作する方法を解説していく連載。今回は、スプレッドシートに図形でボタンを作成しGASを関連付ける方法、トリガーを使ったメニューの追加について解説する。

» 2017年08月14日 05時00分公開

[薬師寺国安，PROJECT KySS]

今回の主な内容

【登録するスクリプト1】科目の判定をする処理
【登録するスクリプト2】円グラフを作成する処理
スプレッドシートで図形を作成する
図形にスクリプトを関連付ける
Google Apps Scriptの「トリガー」
スプレッドシートが開いたときにトリガーを呼びメニューを追加する
次回は、フォームのデータをスプレッドシートに追加しメールで知らせる方法

　本連載「Excel VBAプログラマーのためのGoogle Apps Script入門」では、Googleが提供する「Google Apps Script」（GAS）のプログラミングで、「Google Apps」を操作する方法を「Google Sheets」（スプレッドシート）を中心に解説していく。

　今回は、作成したスクリプトと、スプレッドシート上に配置した図形を関連付けて、図形をクリックするとスクリプトが実行されるようにしてみる。また、スプレッドシートに「メニュー」という項目を追加して、この項目を選択すると、各種登録されたスクリプトの一覧が表示されて、それを選択するとスクリプトが実行される処理を解説する。

　使用するデータは図1のようなデータを使用する。書式はメニューから手動で行っている。

図1　今回使用する「科目の平均点」のデータ

【登録するスクリプト1】科目の判定をする処理

　今回の処理は連載第6回の「Google Apps Scriptで繰り返しと条件分岐を組み合わせて、データの仕分けを自動化する」の中で作成した、スクリプトを使用している。

　65点以上の場合「◎」、50～60点は「○」、それ以外は「×」を表示させていた処理だ。再掲しておく。

　ツールメニューから「スクリプトエディタ」を起動する。開いたエディタ画面から「ファイル」→「新規作成」とたどって、「科目の判定」ファイルを作成する。「科目の判定.gs」内に、リスト1のコードを記述する。

function myCourseDeterminationFunction() {
  var sheet=SpreadsheetApp.getActiveSheet();
  var value={};
  var values=sheet.getRange("B3:B10").getValues();
  var count=values.length;
  for(var i=0;i<=count-1;i++)
  {
    value[i]=values[i]
    if(value[i]>=65)
    {
      sheet.getRange(i+3,3).setValue("◎");
    }else if(value[i]>=50 && value[i]<65){
      sheet.getRange(i+3,3).setValue("○");
    }else{
       sheet.getRange(i+3,3).setValue("×");
    }
  }
}

リスト1　65点以上の場合「◎」、50～60点は「○」、それ以外は「×」を表示させるコード

　リスト1の詳細については、連載第6回を参照してほしい。

【登録するスクリプト2】円グラフを作成する処理

　次は、このデータからグラフを作るスクリプトだ。

　ツールメニューから「スクリプトエディタ」を起動する。開いたエディタ画面から「円グラフ作成」というファイルを作成する。「円グラフ作成.gs」内に、リスト2のコードを記述する。

function myPIEChartFunction() {
  var sheet=SpreadsheetApp.getActiveSheet();
  var range=sheet.getRange("A2:B10");
  var chart=sheet.newChart()
  .addRange(range)
  .setChartType(Charts.ChartType.PIE)
  .setPosition(2,5,0,0)
  .setOption('title','科目別平均点');
  sheet.insertChart(chart.build());
}

リスト2　円グラフの作成

　リスト2の詳細については、連載第8回の「GASで棒、円、折れ線など各種グラフを作成、変更、削除するための基本」を参照してほしい。

　ここからは、これら2つのスクリプトを、まずは図形と関連付けて実行させてみる。

スプレッドシートで図形を作成する

　スプレッドシートの画面のメニューから「挿入」→「図形描画」と選択する（図2）

図2　図形描画を選択

　すると、図形描画画面が表示されるので、「図形」のアイコンを選択して「図形」→「角丸四角形」（図3）を選択しよう。

図3　角丸四角形を選択した

　するとカーソルが「＋」に変わるので、ドラッグしながら適当な大きさの角丸四角形を作成する。作成した角丸四角形を選択して、マウスの右クリックをすると、「テキストを編集」メニューが表示される。これを選択すると、角丸四角形内が編集状態になるので、「科目の判定」と入力する。「図形描画」のメニューから「太字」と「中央揃え」にしておく。（図4）。

図4　角丸四角形を作成し、「科目の判定」という文字を入力した

　次に、図4の右隅上にある「保存して閉じる」をクリックする。するとスプレッドシート上に、今作成した角丸四角形が表示されるので、適当な位置に移動して配置しておく。

　図2～3の手順で、もう1つ角丸四角形を作成し、名前を「円グラフの作成」としておく。「保存して閉じる」をクリックすると、スプレッドシート上には2つの角丸四角形があることになる。それぞれをドラッグ＆ドロップして適当な位置に配置する。

　筆者は図5のように配置した。

図5　2つの角丸四角形を配置した

　この2つの角丸四角形にスクリプトを関連付けることになる。

Excelとの違い

　Excelで図形を作成する場合は、メニューの「挿入」→「図形」とたどって角丸四角形などを選択する。GASでは、「挿入」→「図形描画」→「図形」から、角丸四角形を選択している。GASの方がひと手間多いだけだ。

図形にスクリプトを関連付ける

　まず、「科目の判定」の角丸四角形を選択状態にすると、右隅上に「▼」アイコンが表示されるので、これをクリックする。すると各種メニューが表示される（図6）。

図6　メニューが表示された

　図6から「スクリプトを割り当て」を選択する。すると「どのスクリプトを割り当てますか？」と表示されるので、リスト1の「myCourseDeterminationFunction」を指定する（図7）。これは「科目の判定」のスクリプトだ。

図7　「myCourseDeterminationFunction」スクリプトを指定する

　図7から「OK」ボタンをクリックする。

　同じ手順で、今度は「円グラフの作成」の角丸四角形に、リスト2の「myPIEChartFunction」を指定する。これで、2つの角丸四角形にはスクリプトが関連付けられた。

　実行してみよう。まず、「科目の判定」をクリックすると、画面の右上に「スクリプトを実行しています」と表示され、図8のように表示される。

図8　科目の「判定」欄に「◎」「○」「×」が表示された

　次に、「円グラフの作成」をクリックすると図9のように表示される。

図9　「円グラフ」が表示された

　これらのスクリプトを再度実行する場合、削除機能は付けていないので、手動で「判定」欄の値を削除したり、手動で「円グラフ」を削除したりしてから再実行をしていただきたい。特に「円グラフ」の場合は、新規に作成するようにしているので、既存の「円グラフ」を削除してから、スクリプトの再実行を行わないと、何重にも重なって「円グラフ」が表示されるので、注意してほしい。

Excel VBAとの違い

　Excel VBAで図形にマクロを関連付ける場合は、図形を選択してマウスの右クリックから表示される「マクロ」を選択するとマクロの一覧が表示されて、その中から選択するようになる。この点に関してもExcel VBAとGASでは、見栄えが異なるだけで、処理の手順としては大差がない。

Google Apps Scriptの「トリガー」

　次に、スプレッドシートのメニューに「メニュー」項目を追加して、前述のスクリプトを実行させる処理を解説する。

　スプレッドシートにメニューを追加するには、スプレッドシートが開いたときにメニューを追加するスクリプトを実行する必要がある。

　そのようなときは、下記の「トリガー」（イベントハンドラ）を使用する。

onOpen(event)

　スプレッドシートを開いたときに実行される。開いたWebページをリロードした場合なども実行される。

onEdit(event)

　他にも、スプレッドシートを編集したときに実行されるトリガー「onEdit」がある。例えば、いろいろなセルを書き換えるたびにイベントが発生し、このトリガーが呼び出されることになる。

その他のトリガー

　これらの外にも、トリガーの種類があるが、本稿では省略する。気になる方は、下記のURLを参照してほしい。

https://developers.google.com/apps-script/guides/triggers/

Excel VBAとの比較

　Excel VBAでブックが開いたときに自動実行させる方法には、2通りの方法がある。

Workbook_OpenとAuto_Openだ。

　シートが編集されたときのイベントはWorksheet.Changeを使用することになるだろう。

スプレッドシートが開いたときにトリガーを呼びメニューを追加する

　ツールメニューから「スクリプトエディタ」を起動する。開いたエディタ画面から「onOpenイベント」というファイルを作成する。「onOpenイベント.gs」内に、リスト3のコードを記述する。

function onOpen(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var myMenu= [];
  myMenu.push({name: "科目の判定", functionName: "myCourseDeterminationFunction"});
  myMenu.push({name: "円グラフの作成", functionName: "myPIEChartFunction"});
  sheet.addMenu("メニュー", myMenu);
}

リスト3　スプレッドシートが開いたとき、「メニュー」項目を追加するコード

　変数sheetに現在のアクティブなスプレッドシートを取得して参照する（2行目）。

　変数myMenuで空の配列変数を作成する（3行目）。

　空の配列変数myMenuのpushメソッドで、メニューの名前と実行するスクリプトの名前を指定していく（4～5行目）。

　最後に、addMenuメソッドで、「メニュー」という項目を追加し、内容に配列変数myMenuに追加された項目名とスクリプトを追加する（6行目）。

　これを実行すると図10のように、スプレッドシートのメニューに「メニュー」という項目が追加されるが、最初に実行した場合は、追加されていないことが多い。その場合はWebページをリロードすると追加される。

図10　スプレッドシートに「メニュー」が追加され、クリックすると内容が表示される

　図10の「メニュー」から各項目を選択すると、図8や図9のように表示される。

Excel VBAとの違い

　Excelに独自のメニューを追加する場合は、「CommandBar」オブジェクトを使用する。Excelのメニューバーは「Worksheet Menu Bar」という名前のCommandBarオブジェクトである。下記のようなタイプがある。

表　VBAのCommandBarオブジェクトの形式
コントロール名	説明
msoControlButton	ボタン
msoControlEdit	テキストボックス
msoControlDropdown	ドロップダウンリストボックス
msoControlComboBox	コンボボックス
msoControlPopup	ポップアップメニュー