【連載】CPIエバンジェリストのお悩み相談室
2017年9月7日
TEXT:阿部 正幸(KDDIウェブコミュニケーションズ)

Web制作でかなり便利なGoogle Apps Scriptの活用方法!

ガントチャートやタスク管理、課題管理など、Web制作では作業の効率化や品質担保にツールは必須です。でも、たくさんあって「どのツールを使えばいいのかわからない」という声をセミナー時にもらうことがあります。

私も過去さまざまなツールを試してきましたが、その中で学んだツール選びの重要なポイントは、“誰もが使えて、ぱっと見でわかりやすいこと”だと思います。

そこで私がオススメしたいのが、Googleスプレッドシートです。クラウド上でチームと共有・編集を行いたい場合に利用している人は多いと思いますが、直接プログラミングできるGoogle Apps Scriptと合わせて使うことで、他のGoogleアプリケーションとの連携や、入力の自動化などのカスタマイズが可能になります。Web制作では、エンジニアだけでなくサイトオーナーやディレクターといったノンプログラマーとチームになって作業することがあります。そうしたときに、“誰でも扱えて、確認できる“のは、作業を円滑に進める上で非常に重要なポイントです。

今回は、課題管理表を例に、GoogleスプレッドシートとGoogle Apps Scriptの使い方をご紹介します。自動化できるところは自動化していきましょう。

最後に全ソースコードを掲載しているので、活用いただければ幸いです。

●Google Apps Scriptとは 
GmailやGoogleドライブなど、11個ものGoogleアプリケーションをクラウド上で操作できるサービスです。JavaScript言語を用いて、テキスト入力を自動化したり、他のアプリケーションと連携させたりできます。今回は、スプレッドシートの入力自動化と、メール(Gmail)通知を行います。

今回作成するプログラムのサンプル
https://docs.google.com/spreadsheets/d/1R0usuQiXVSEWd3gvUXM8pLpReCs5lMQ1QESjgq8l0yM/edit?usp=sharing

●仕様 
・「依頼者」を入力すると、自動で「依頼日」が挿入される
・「担当者」を入力すると、自動で担当者にメール通知される
・「対応内容」を入力すると、自動で「対応日」が挿入される
・ステータスを「完了」にすると、完了にしたレコードの背景色が自動でグレーに変わる

●Google スプレッドシート新規作成 
Google スプレッドシートにアクセスし、新しいスプレッドシートを立ち上げます。タイトルは「課題管理表」とし、項目は左から「No、ステータス、依頼者、依頼日、問い合わせ内容、担当者、通知、対応内容、対応日付」とします。



ステータスの項目は、プルダウンで「依頼、作業中、完了」を選択できるようにします。範囲選択後、右クリックし「データ入力規則」をクリックします。



条件「リストを直接指定」を選択し、「依頼,作業中,完了」を入力後「保存」をクリックします。(各項目はカンマ区切りで入力)



ステータスがプルダウンメニューより選択できるようになりました。

●自動で入力日を挿入
課題管理表の準備ができたら、入力や通知の自動化を実装していきます。はじめに「依頼者」と「対応内容」を入力したら、自動で日付を挿入します。「ツール」をクリックし「スクリプトエディタ」を選択します。



新規のスクリプトエディタが起動するので、プロジェクト名を適当に変更します。今回は「QAProject」としました。



リファレンス:
https://developers.google.com/apps-script/reference/spreadsheet/

●編集時のイベントを取得
// スプレッドシート編集時
function onEdit(e) {
 Logger.log('スプレッドシートが編集されました');

編集時のイベントは onEdit(){ } 関数を使用します。上記コードをスクリプトエディタに入力します。



入力が終わったら、ファイルメニューから「保存」(あるいは保存アイコン)をクリックします。

●ログの確認
スプレッドシートを編集し、onEdit() が動作するか確認しましょう。表示メニューの「ログ」を選択します。





編集時に onEdit() 関数が実行されるのが確認できました。

●編集時に日付を挿入
「依頼者」と「対応内容」が編集できたら、その日付を挿入します。

// スプレットシートのアクティブを取得
var SS = SpreadsheetApp.getActive();
// アクティブなシートを取得
var SHEET = SS.getActiveSheet();

onEdit(e){
// 変更行の取得
 e.range.getLastColumn();
 // 変更列の取得
 e.range.getLastRow();

 // 依頼者が入力されたら
 if(e.range.getLastColumn() === 3){
  // 何か入力があれば
  if(e.range.getValue()){
    addEditDate(e.range.getLastColumn()+1,e.range.getLastRow());
  }
 }

 // 対応内容が入力されたら
 if(e.range.getLastColumn() === 8){
  // 何か入力があれば
  if(e.range.getValue()){
    addEditDate(e.range.getLastColumn()+1,e.range.getLastRow());
  }
 }
}

// 現在の日付を返す
function formatDate(date) {
 var returnDate;
 returnDate = date.getFullYear() + '/';
 returnDate += date.getMonth() + 1 + '/';
 returnDate += date.getDate();
 return returnDate;
}

// 現在の日付を指定のセルに代入する
function addEditDate(c,r){
 range = SHEET.getRange(r,c);
 // 日付の代入
 range.setValue(formatDate(new Date(), 'YYYY/MM/DD'));

}

スプレッドシートが編集されると、「onEdit()」関数が実行されます。関数の実行時にイベントの引数が渡されます。今回は「e」変数に代入しています。変更した行は「e.range.getLastColumn();」列は「e.range.getLastRow();」で取得できます。

 if(e.range.getLastColumn() === 3){ } で、3行目の「依頼者」が編集された場合、 if(e.range.getLastColumn() === 8){ } で、8行目の「対応内容」が編集された場合です。

日付の挿入は function addEditDate(c,r){ } 関数で行っています。まずグローバル変数(SHEET)に現在編集中のスプレッドシートのシートオブジェクトを代入しています。

// スプレットシートのアクティブを取得
var SS = SpreadsheetApp.getActive();
// アクティブなシートを取得
var SHEET = SS.getActiveSheet();

次に addeditDate() 関数内の下記で現在の日付を挿入しています。

range = SHEET.getRange(r,c);
range.setValue(formatDate(new Date()));

●担当者が指定されたらメール通知
担当者欄に担当者が指定されたらメール通知をおこないます。動きとしては下記の通りです。
 1. 担当者が入力される
 2. 通知欄に「通知予定」を代入
 3. 定期実行でプログラムが実行され「通知予定」があった場合メールを送信する

onEdit(){ } 関数に下記を追記
// 担当者が入力されたら
if(e.range.getLastColumn() === 6){
 range =
 SHEET.getRange(e.range.getLastRow(),e.range.getLastColumn()+1);
 range.setValue('通知予定');
}

function checkNotification(){

 // 複数行のセルを取得 (列, 行, 何列, 何行)
 var ranges = SHEET.getRange(2, 7, 30 , 1).getValues();

 for (var i = 0; i < ranges.length; i++) {

  // 通知予定フラグが立っていたら
  if(ranges[i] == '通知予定'){
   var editRow = i + 2;
   // 通知する担当者を取得
   var editorName = SHEET.getRange(6,editRow).getValue();
   // 担当者に合わせて通知を行う
   if(editorName === '阿部'){
    notificationSendEmail('sample@example.com','阿部','タスクが追加されました',7,editRow);
   }else{
    range = SHEET.getRange(7,editRow);
    range.setValue('通知失敗');
   }
  }
 }
}

function notificationSendEmail(address,name,countData,col,row){
 var mailBody = name + '様\n\n' + countData;
 GmailApp.sendEmail(address,"タスクが追加されました" ,mailBody);

 // ステータスの変更
 range = SHEET.getRange(row,col);
 range.setValue('通知済');
}

checkNotification() 関数は「通知予定」フラグが立った行を取得し、各担当者へ通知を行うための定期実行関数です。定期実行の登録は「編集 > すべてのトリガー」を選択し、定期実行する間隔を指定します。





メール通知は GmailApp.sendEmail("メールアドレス","タイトル" , "本文"); 関数を使い送信します。こちらの関数の実行については、2017年5月にGoogleの規約が変更されたので、下記のフォーラムに参加し送信することでできるようになります。

Allow Risky Access Permissions By Unreviewed Apps
https://groups.google.com/forum/#!forum/risky-access-by-unreviewed-apps
(2017年9月現在)

●ステータスが「完了」で背景色を変更
ステータスに「完了」フラグが立ったら、その行の背景色をグレーに変更します。
onEdit() 関数に下記を追記します。

// ステータスが変更されたら
if(e.range.getLastColumn() === 2){
 // 完了の場合
 if(e.range.getValue()=='完了'){
  // 背景色を変更するレンジを指定
  var changeRnages = "A" + e.range.getLastRow() + ":" + "I" + e.range.getLastRow();
  var range = SHEET.getRange(changeRnages);
  range.setBackground("#cccccc");
 }
}

●最後に(見出し)
以上でGoogleスプレッドシートとGoogle App Scriptを使った自動化の完成です。Google Apps Scriptを使えば、各Google アプリケーションの連携がJavaScriptだけで簡単にできます。例えば、アンケートフォームを作成したい場合、Googleフォームと連携し、入力された内容をスプレッドシートに反映させられます。さらに、入力内容の集計や集計結果をGmailで通知、Googleカレンダーに予定を立てるといった組み合わせも可能です。
一度試してみてはいかがでしょうか。

Google Apps Scriptのリファレンス
https://developers.google.com/apps-script/

全ソースコード
// メールアドレスの設定
var MAILADD = {
 '阿部':'abe@example.com',
 '佐藤':'sato@example.com'
};

// スプレットシートのアクティブを取得
var SS = SpreadsheetApp.getActive();
// アクティブなシートを取得
var SHEET = SS.getActiveSheet();

// 編集時
function onEdit(e) {

 // 変更行
 e.range.getLastColumn();
 // 変更列
 e.range.getLastRow();

 // 依頼者が入力されたら
 if(e.range.getLastColumn() === 3){
  // 何か入力があれば
  if(e.range.getValue()){
    addEditDate(e.range.getLastColumn()+1,e.range.getLastRow());
  }
 }
 
 // 対応内容が入力されたら
 if(e.range.getLastColumn() === 8){
  // 何か入力があれば
  if(e.range.getValue()){
    addEditDate(e.range.getLastColumn()+1,e.range.getLastRow());
  }
 }
 
 // 担当者が入力されたら
 if(e.range.getLastColumn() === 6){
  range = SHEET.getRange(e.range.getLastRow(),e.range.getLastColumn()+1);
  range.setValue('通知予定');
 }
 
 // ステータスが変更されたら
 if(e.range.getLastColumn() === 2){
  // 完了の場合
  if(e.range.getValue()=='完了'){

   var changeRnages = "A" + e.range.getLastRow() + ":" + "I" + e.range.getLastRow();
   var range = SHEET.getRange(changeRnages);
   range.setBackground("#cccccc");
   
  }
 }
 
 // 日付の差分
 // dateDiff(new Date('2014/1/1'), new Date('2012/1/2')); // 1

 // コメントを付与
 //e.range.setComment("Edited by: " + e.user + new Date());
}

// 現在の日付を指定のセルに代入する
function addEditDate(c,r){
 range = SHEET.getRange(r,c);
 // 日付の代入
 range.setValue(formatDate(new Date()));
 
}

function checkNotification(){

 // 複数行のセルを取得 (列, 行, 何列, 何行)
 var ranges = SHEET.getRange(2, 7, 30 , 1).getValues();

 for (var i = 0; i < ranges.length; i++) {

  if(ranges[i] == '通知予定'){
   var editRow = i + 2;
   var editorName = SHEET.getRange(editRow,6).getValue();
   Logger.log(editRow);
   Logger.log(editorName);
   
   if(MAILADD[editorName]){
    notificationSendEmail(MAILADD[editorName],editorName,'タスクが追加されました',7,editRow);
   }else{
     range = SHEET.getRange(editRow,7);
     range.setValue('通知失敗');
   }
  }
 }
}

// Emailの送信
// メールの送信は規約により1か、2を実行する必要がある
// 1.OAuth Developer Verification Formで承認を受ける
// https://support.google.com/code/contact/oauth_app_verification
// 2.Allow Risky Access Permissions By Unreviewed Appsのフォーラムに参加する
// https://groups.google.com/forum/#!forum/risky-access-by-unreviewed-apps
function notificationSendEmail(address,name,countData,col,row){
 var mailBody = name + '様\n\n' + countData;
 GmailApp.sendEmail(address,"タスクが追加されました" ,mailBody);

 // ステータスの変更
 range = SHEET.getRange(row,col);
 range.setValue('通知済');
}

// 現在の日付を返す
function formatDate(date) {
 var returnDate;
 returnDate = date.getFullYear() + '/';
 returnDate += date.getMonth() + 1 + '/';
 returnDate += date.getDate();
 return returnDate;
};

>>>「CPIエバンジェリストのお悩み相談室」の目次はこちら


[筆者プロフィール]
阿部 正幸(あべ まさゆき) | KDDIウェブコミュニケーションズ/エバンジェリスト
システム開発会社で大規模なシステム開発を経験後、Web制作会社でプログラマー兼ディレクターとして従事。その後、KDDIウェブコミュニケーションズに入社、レンタルサーバーCPIのプロダクトマネージャーに就任。ACE01、SmartReleaseをリリース後、現職の「エバンジェリスト」としてWeb制作に関する様々なイベントに登壇。Drupal(g.d.o Japan)日本コミュニティー、HTML5 funなどに所属し、OSSを世に広げる活動やWeb制作に関する情報を発信している。

●CPIフタッフブログ
 URL:http://shared-blog.kddi-web.com/

● CPI LINE@のご案内
http://shared-blog.kddi-web.com/other/215