【連載】CPIエバンジェリストのお悩み相談室⑤
Googleスプレッドシートで課題管理表を作成し、メール通知などの処理を自動化しよう

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