連載
» 2017年08月14日 05時00分 UPDATE

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

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

[薬師寺国安,PROJECT KySS]

 本連載「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を参照してほしい。

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 ポップアップメニュー

 VBAでは、メニューバーに指定する形式(Type)を、表のように、いろいろ選択できる。しかしGASでは、テキストだけの表示になる点が、VBAと異なる。

次回は、フォームのデータをスプレッドシートに追加しメールで知らせる方法

 今回はこれで終わりだ。次回は、最終回。フォームのデータをスプレッドシートに追加しメールで知らせる方法を紹介する。

参考文献

著者プロフィール

PROJECT KySS 薬師寺 国安(やくしじ くにやす)

1950年生まれ。フリーVBプログラマー。高級婦人服メーカーの事務職に在職中、趣味でVBやActiveXに取り組み、記事を執筆。2003年よりフリー。.NETやRIAに関する執筆多数。Windowsストアアプリも多数公開中(約270本)。

 

Microsoft MVP for Development Platforms - Client App Dev (Oct 2003-Sep 2012)。

Microsoft MVP for Development Platforms - Windows Phone Development(Oct 2012-Sep 2013)。

Microsoft MVP for Development Platforms - Client Development(Oct 2013-Sep 2014)。

PROJECT KySSは、1997年に薬師寺聖と結成したコラボレーション・ユニット


Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。