• ログインログイン
  • 新規登録新規登録

MENU

Google Apps Scriptでプログラミングを学ぶ 最終回

連載Google Apps Scriptでプログラミングを学ぶ

Google Apps Scriptは、開発環境の設定がなくてもすぐにコーディングができる優れもののサービスです。初心者でもすぐに取り掛かれる事例を用いながら、実践に耐えうるレベルまで徐々にステップアップして学習していきます。

この記事は、株式会社らしく様からの寄稿記事となります。前回から引き続きで、プログラミング学習コラム最終回の勉強内容となります。これまでに学んできたことをベースに、実際にプライベートや仕事で役立つ例題を学んでいきましょう。

  • セクション1 フォームからのお問い合わせに自動返信メール
  • セクション2 5秒で消えるメセージ
  • セクション3 住所を緯度経度に変換し画像を取得
  • セクション4 Webサイトのソーシャルメディアでの拡散数
  • セクション5 家計簿

例題のスプレッドシートとGASのダウンロード

ブラウザから、こちらのリンクを実行いただくと、あなたのGoogleドライブに「GASで学ぶ本のサンプルプログラム」というフォルダが作成され、GAS付きのスプレッドシートがコピーされます。Googleアカウントが必要で、コピーするプログラムを承認する必要があります。

こちらからダウンロードをお願いします。

セクション1 フォームからのお問い合わせに自動返信メール

例題の内容

多くのWebサイトでお問い合わせ内容を登録するフォームを見かけることがあると思います。Google Spreadsheet formではそのようなフォームを簡単に作成できますが、そのお問い合わせフォームから登録があった場合に、自動でメールを返信する機能は準備されていません。それをGASで実現します。

仕様

以下のお問い合わせフォームへの登録時、入力されたEmailアドレス宛とお問い合わせ管理者メールアドレス宛に、フォームに入力されたデータを含むメールを送信します。

送信されるメールのサンプルは以下のとおりです。

登録されたお問い合わせ内容は以下のような形でGoogleスプレッドシートに記録されます。

トリガーの設定

onFormSubmitSendMail()について、GASエディタの設定画面で、formへの登録時(onFormSubmit時)に呼び出されるよう、「リソース」メニューより「現在のGASのトリガー」を設定しています。

form登録時に、引数のEventからform登録データを抜き出し、メール送信のための件名・本文を作成し、formに登録されたメールアドレスとあらかじめ設定された管理者メールアドレスに、メールを送信します。

プログラム

/*
* 定数宣言
*/
//管理者メールアドレス ※フォーム作成側の管理者のメールアドレスを設定します。
var ADMINISTRATOR_MAIL_ADDRESS = "sample@company.com";

//フォーム項目名
var COL_FIRST_NAME = "姓(Last Name)";
var COL_LAST_NAME = "名(First Name)";
var COL_COMPANY_NAME = "会社名(Company Name)";
var COL_EMAIL = "Emailアドレス(Email Address)";
var COL_CONTENT_OF_INQUIRY = "ご相談・お問い合わせ内容(Content of Inquiry)";

/*
* フォームからの登録された直後に
* 登録内容に応じたメールをフォーム登録者に送信
*
* @param e {Event} イベントオブジェクト
*/
// フォームから登録された内容はイベントオブジェクト(e)から取得

function onFormSubmitSendMail(e) {
var admin_address = ADMINISTRATOR_MAIL_ADDRESS;
var to_adress = e.namedValues[COL_EMAIL];
var subject = "お問合せいただき、ありがとうございました";
var body = "以下の内容でお問合せを受け付け致しました。\n\n";
body += '○' + COL_FIRST_NAME + ':' + e.namedValues[COL_FIRST_NAME] + '\n';
body += '○' + COL_LAST_NAME + ':' + e.namedValues[COL_LAST_NAME] + '\n';
body += '○' + COL_COMPANY_NAME + ':' + e.namedValues[COL_COMPANY_NAME] + '\n';
body += '○' + COL_EMAIL + ':' + e.namedValues[COL_EMAIL] + '\n\n';
body += '○' + COL_CONTENT_OF_INQUIRY + ':\n' + e.namedValues[COL_CONTENT_OF_INQUIRY] + '\n';
var options = {};
options.bcc = admin_address;

/*
* bcc以外にもccやreplyTo等も指定可能
* メール送付は必要なパラメータを準備して、MailApp.sendEmail()を呼び出す
*/
Logger.log("\nto_adress:" + to_adress + "\nsubject:" + subject + "\nbody:" + body + "\noptions:" + options + "\n");
MailApp.sendEmail(to_adress, subject, body, options);
}

セクション2 5秒で消えるメッセージ

例題の内容

スパイ大作戦というアメリカのドラマで、スパイに指示を伝えたカセットテープが、再生後に自動消滅する、という設定がありました。ちょっと真似て、”開封されて5秒後にメッセージが跡形も無く消える”という機能を実現したいと思います。Youtubeに、完成イメージをアップロードしています。

仕様

相手に送るメール内に、Googleスプレッドシートのリンクを内包します。スプレッドシートは起動後5秒でメッセージをクリアするよう、GASを記載します。スプレッドシートへのメッセージ記述の仕様は、以下のようにしました。

実行前

 

実行中

 

実行後

 

プログラム

スプレッドシートが開かれた際に実行されるonOpen関数を利用します。トーストメッセージで、5秒で消える注意を促し、5秒処理実行を待った後、メッセージエリアをクリアすることで、メッセージを消去します。

/*
* スプレッドシートが開かれてから5秒後に
* あらかじめセットされているメッセージが消える。
*/
function onOpen() {

// スプレッドシートが開かれた際に、自動的に実行される関数

var ss = SpreadsheetApp.getActiveSpreadsheet() ;
var sheet = ss.getActiveSheet();
var target_range = sheet.getRange("B1:B2");
var target_time = 5;

/*
* 右下にせり上がりのメッセージを5秒間表示
* 5秒待つ
* メッセージをクリア
*/

ss.toast("このメッセージは、5秒で消えます。", "注意!" 5);
Utilities.sleep(target_time*1000);
target_range.clear();
}

セクション3 住所を緯度経度に変換し画像を取得

例題の内容

地域のお店や名所を住所録としてまとめるとともに、ウェブの地図に表示したいというようなことがあると思います。GASのGoogle Mapsサービスを利用して、住所を緯度経度に変換し、Google Mapsか周辺地図画像を取得してGoogleスプレッドシートに表示してみます。

仕様

住所をひととおり入力したのち、カスタムメニューをクリックすると、住所を緯度経度に変換するとともに、周辺地図画像を表示します。

実行前

 

実行後

 

プログラム

/*
* 定数の定義
*/
var ADR_COL ="D"; //住所の列の位置をアルファベットで定義
var LAT_COL = "I"; //緯度(lat)の列の位置をアルファベットで定義
var LNG_COL = "J"; //経度(lng)の列の位置をアルファベットで定義
var MAP_COL = "K"; //マップ画像を表示する列の位置をアルファベットで定義
/*
* 住所から緯度経度情報を取得し、その場所を中心とした
* Googleマップの画像を表示
*/
/*
* スプレッドシートオープン時にカスタムメニューを追加
*/
function onOpen() {
//独自メニューの追加
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menus = [{name: 'Googleマップの表示', functionName: 'showMap'}];
ss.addMenu('Googleマップの表示', menus);
}
/*
* 住所から緯度経度情報を取得し、Googleマップの画像を表示
*/
function showMap() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var last_row = sheet.getLastRow();
for(var i = 2; i <= last_row; i++) {
var address = sheet.getRange(ADR_COL + i).getValue();

//緯度経度情報の取得して表示
var geocode = Maps.newGeocoder().geocode(address);
var lat = geocode.results[0].geometry.location.lat;
var lng = geocode.results[0].geometry.location.lng;
sheet.getRange(LAT_COL + i).setValue(lat);
sheet.getRange(LNG_COL + i).setValue(lng);
//Googleマップの画像を取得して表示

var map = Maps.newStaticMap().setSize(128, 128)
.setCenter(lat, lng).addMarker(lat, lng).setZoom(14).setLanguage('ja')
.setMapType(Maps.StaticMap.Type.HYBRID);

var url = map.getMapUrl();
sheet.getRange(MAP_COL + i).setFormula('=image("' + url + '", 3)');
}
}

セクション4 Webサイトのソーシャルメディアでの拡散数

例題の内容

最近はWebサイトに対する評価をページビューや訪問者数ではなく、ソーシャルメディアでの反響で測定することがよくあります。ここではGASでソーシャルメディアでも代表的なFacebookとTwitterでの反響を測定する処理を実現します。

仕様

以下の画像のようなSpreadsheetに対象のURLを入力し、メニューの「集計実行」→「集計実行」を実行すると、FacebookおよびTwitterのAPIにアクセスして

  • Facebookでのシェア数
  • Twitterでのツイート数

を集計します。

 プログラム

以下の5つのfunctionから構成されています。onOpen()については、GASエディタの設定画面で、「リソース」メニューより「現在のGASのトリガー」を設定しています。

functionの構成は以下の通り。

/**************************************************
[機能] シートオープンイベントトリガ
合計計算のメニューを追加する
* [引数] event イベント
* [戻値] 無し
**************************************************/
function onOpen() {
//独自メニューの追加
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menus = [{name: '集計実行', functionName: 'count'}];
ss.addMenu('集計実行', menus);

}
/**************************************************
* [機能] 集計実施
測定するwebページが編集されたら反響を集計する
* [引数] なし
* [戻値] 無し
**************************************************/
function count(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var count;

// 有効行でループ
var lastRow = sheet.getLastRow();
for( var currentRow=2; currentRow<=lastRow; currentRow++ ){
var url = sheet.getRange("A" + currentRow ).getValue();
if( url == "" ){
continue;
}

// Facebookを集計
var fbResult = sheet.getRange("B" + currentRow );
try{
count = countFBshares( url );
fbResult.setValue( count );
}catch( e ){
fbResult.setValue( e );
}

// Twitterを集計
var twResult = sheet.getRange("C" + currentRow );
try{
count = countTweets( url );
twResult.setValue( count );
}catch( e ){
twResult.setValue( e.getMessage() );
}
}

}

/**************************************************
* [機能] Facebook集計実施
* [引数] 集計対象
* [戻値] 無し
**************************************************/
function countFBshares(url) {
var httpResponse = UrlFetchApp.fetch("http://graph.facebook.com/" + url );
var object = parseResponse( httpResponse );
return object.shares;
}

/**************************************************
* [機能] Twitter集計実施
* [引数] 集計対象
* [戻値] 無し
**************************************************/
function countTweets(url) {
var httpResponse = UrlFetchApp.fetch("http://urls.api.twitter.com/1/urls/count.json?url="+url);
var object = parseResponse( httpResponse );
return object.count;
}
/**************************************************
* [機能] 応答解析
* [引数] 応答
* [戻値] JSONでのパース結果
* [例外] 応答取得失敗
**************************************************/
function parseResponse( httpResponse ){
if( httpResponse.getResponseCode() != 200 ){
throw new Error("url fetch error");
}
return Utilities.jsonParse(httpResponse.getContentText());
}

セクション5 家計簿

例題の内容

Google Spreadsheetで作成した家計簿で、科目ごとの集計をして、支出全体での科目ごとの比率が見える円グラフ表示をGASで実現してみます。

仕様

以下の画像のようなSpreadsheetに支出の内容を入力していきます。

  • Spreadsheetオープン時にメニューの設定と対象月・作成者の値を設定する。
  • 入力行追加時(日付入力時)に科目のプルダウンの設定をサンプルセルよりコピーする。
  • メニューの「合計計算」→「計算実行」を実行すると、科目ごとの集計と科目ごとの金額比率を表す円グラフの表示を行う。

 プログラム

 

// カラム位置定義
var DATE_COLUMN = "A";
var CATEGORY_COLUMN = "D";
var AMOUNT_COLUMN = "E";
var SUM_COLUMN = "E";

// セル位置定義
var SHEET_NAME_CELL = "B2";
var USER_MAIL_ADDRESS = "B4";

/**************************************************
* [機能] シートオープンイベントトリガ
合計計算のメニューを追加する
* [引数] event イベント
* [戻値] 無し
**************************************************/
function onOpen() {
//独自メニューの追加
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menus = [{name: ‘計算実行’, functionName: 'sum'}];
ss.addMenu('合計計算', menus);

// シート名の設定
var sheet = ss.getActiveSheet();
sheet.getRange(SHEET_NAME_CELL).setValue( sheet.getName() );

// 作成者(メールアドレス)の設定
var user_email = Session.getEffectiveUser().getEmail();
sheet.getRange(USER_MAIL_ADDRESS).setValue(user_email);

}

/**************************************************
* [機能] カラム編集イベントトリガ
日付カラムが編集されたら科目リストをコピーする
* [引数] event イベント
* [戻値] 無し
**************************************************/
function onEdit(event)
{
var sheet = event.source.getActiveSheet();
var currentRow = sheet.getActiveCell().getRow();
var currentColumn = sheet.getActiveCell().getColumn();
var editData = sheet.getRange( DATE_COLUMN + currentRow ).getValue();

if( currentRow >= 22 && currentColumn == 1 && // 有効行内で日付カラムを操作した
editData != "" ) {// 日付カラムはデータ入力されている

var target = sheet.getRange(CATEGORY_COLUMN + currentRow );
var original = sheet.getRange(CATEGORY_COLUMN + 1 );

//データコピー
original.copyTo(target);
}

}

/**************************************************
* [機能] 各項目の合計値を求め、合計値欄に設定する
合計値の比率をパイチャートで表示する
* [引数] event イベント
* [戻値] 無し
**************************************************/
function sum(){

// スプレッドシートを取得する
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var categoryName;// 科目名

// 集計用カウンタ構築
var counter = new Array();
for( var i=3; i<=18; i++ ){
categoryName = sheet.getRange( CATEGORY_COLUMN + i ).getValue();
counter[ categoryName ] = {
"counter" : 0,
"rangeObject" : sheet.getRange( SUM_COLUMN + i )
}
}

// 編集された最後の行を取得する
var last_row = sheet.getLastRow();

// 集計
for( i=22; i<= last_row; i++ ){

// 編集された範囲を取得する
var amount = sheet.getRange( AMOUNT_COLUMN + i ).getValue();
categoryName = sheet.getRange( CATEGORY_COLUMN + i ).getValue();
if (counter[ categoryName ]) {
counter[ categoryName ].counter += amount;
}
}

// 結果設定
for( i in counter ){
counter[ i ].rangeObject.setValue( counter[i].counter );
}

// パイチャートが表示されている状態ならば、いったん削除する
var charts = sheet.getCharts();
for ( i in charts) {
sheet.removeChart(charts[i]);
}

// パイチャートを構築
var chartBuilder = sheet.newChart();
var chartRange = sheet.getRange( CATEGORY_COLUMN + 3 + ":" + SUM_COLUMN + 18);
chartBuilder.addRange( chartRange );
chartBuilder.setChartType( Charts.ChartType.PIE );
chartBuilder.setPosition( 6, 2, 0, 0 );
chartBuilder.setOption("width", 500);
chartBuilder.setOption("height", 250);

// パイチャート表示
sheet.insertChart( chartBuilder.build() );
}

補足、参考資料

参考文献など

カテゴリ

種類

タイトル

解説

画像

GAS ウェブサイト Google Apps Script公式サイト(英語) Googleが提供するGoogle Apps Scriptの公式サイトです。圧倒的な情報量です。もっとGASを使いたい場合、ここを避けては通れません。英語ですが、Google翻訳などを使って読み解きましょう。  
GAS 書籍 Google Apps Scriptクィックリファレンス 上の公式サイトの情報に負けないくらい、クラスやメソッドの解説が充実しています。  
GAS ウェブサイト ドットインストール Google Apps Script入門 動画でGoogle Apps Scriptを学べます。ひとつの動画は3分にまとまっていて、ご自分のペースで計画的に学習しやすいです。  
JavaScript ウェブサイト ドットインストール JavaScript入門 動画でJavaScriptを学べます。ひとつの動画は3分にまとまっていて、ご自分のペースで計画的に学習しやすいです。※ウェブサイト開発を想定した内容になっています。  
JavaScript 書籍 よくわかるJavaScriptの教科書 初心者向けのJavaScriptの解説本で、わかりやすいと思います。※ウェブサイト開発を想定した内容になっています。  
VBA 書籍 世界でいちばん簡単なExcel VBAのe本 Excel VBAも使う必要がある方は、こちらもお勧めです。この本の作成においても参考にさせていただきました。  

公式Google Apps Script 製品ページの解説の読み方

公式Google Apps Script 製品ページ内にはクラスやメソッドに関する説明のページがあります。ただし英語です。このページを使いこなす上での簡単な解説を書きます。

公式Google Apps Script 製品ページを開き、左側のメニューにある「REFERENCE」がクラスやメソッドの解説です。

参考としえて、SpreadsheetAppクラスのcreate()メソッドの解説を例にとり、使い方をお伝えしてみます。
「Google Apps Services」をクリックします。

「Spreadsheet」サービスをクリックし、「Spreadsheet」クラスをクリックします。

 

 

「SpreadsheetApp」クラスのメソッドの一覧が表示されますので、「create()」メソッドをクリックします。

create()メソッドの解説が表示されます。
メソッドの概要と、サンプルプログラム、引数の解説、戻り値の解説が表示されています。
ブラウザがChromeであれば、翻訳機能がります。英語が苦手な方は、翻訳ボタンを押してみてください。

日本語に翻訳されています。ただし、プログラムまで翻訳されてしまうのと、必ずしも翻訳がわかりやすいとも限りませんので、補助的な目的で使ってください。

記事執筆者のプロフィール

佐藤 純也株式会社らしくにて、「ITを活用した街の魅力アップ支援と」「成長する企業のオフィスワークの”イケてる化”支援」を推進しています。クラウド(SaaS)至上主義者。2児の父。岩手県奥州市出身、東京都品川区在住。blog: http://blog.rashiku1.com/

Website: http://www.rashiku1.com

Google+: http://gplus.to/junyasato/

Facebook: http://facebook.com/junya.sato

 
日下 毅株式会社クエステトラに在籍、営業兼SE兼コンサルタントを担当。その中でGASによる顧客支援も実施。これまでVisualBasic(金融業向けカード発行機の組込ソフト)、JAVA(CRMシステム)、ActionScript(BPMシステムの業務モデラ)等での開発に従事。京都市在住。Google+: http://gplus.to/tsuyoshi.kusaka/Facebook: http://www.facebook.com/tsuyoshi.kusaka

Twitter: https://twitter.com/tsuyoshikusaka

 
松田 優香子育てmama 兼 non-IT女子。ブログ「non-IT女子はGASを使いこなせるようになるか?」を運営。babyを背中におぶり、日夜ブツブツ呻き声を上げながら不定期更新中。子育てママ”でも“non-IT”でも“誰”でもGASは使えるぞ!ということを、我が身を持って体現しています。blog: http://mypleis.blogspot.jp/

Website: https://sites.google.com/a/mypleis.com/gas_mypleis/

Google+: http://gplus.to/matsuda.yuka/

 
米澤 猛士中医薬膳師の資格を持つ異色のフリーSE。体を壊しがちな激務を乗り切るためには、正しい「食」による養生法にある事を体験。これは是非広めねばと目下検討中。実現手段として主にGASやGoogle App Engineを研究しています。google+ :  http://gplus.to/takeshiyonezawa   

株式会社らしくのインターン応募はこちら

オススメ記事一覧

もっと見る
完全無料!

1で登録完了!

エンジニアの仕事・年収や選考ノウハウ記事が読めるほか、
会員にはプログラミング講習やES・面接対策などリアルな無料サポートも充実。
ここだけの求人情報も多数。

今すぐ新規会員登録
Page Top