【連載】CPIエバンジェリストのお悩み相談室⑤
Googleスプレッドシートで課題管理表を作成し、メール通知などの処理を自動化しよう
ガントチャートやタスク管理、課題管理など、Web制作では作業の効率化や品質担保にツールは必須です。でも、たくさんあって「どのツールを使えばいいのかわからない」という声をセミナー時にもらうことがあります。
私も過去さまざまなツールを試してきましたが、その中で学んだツール選びの重要なポイントは、“誰もが使えて、ぱっと見でわかりやすいこと”だと思います。
そこで私がオススメしたいのが、Googleスプレッドシートです。クラウド上でチームと共有・編集を行いたい場合に利用している人は多いと思いますが、直接プログラミングできるGoogle Apps Scriptと合わせて使うことで、他のGoogleアプリケーションとの連携や、入力の自動化などのカスタマイズが可能になります。Web制作では、エンジニアだけでなくサイトオーナーやディレクターといったノンプログラマーとチームになって作業することがあります。そうしたときに、“誰でも扱えて、確認できる“のは、作業を円滑に進める上で非常に重要なポイントです。
今回は、課題管理表を例に、GoogleスプレッドシートとGoogle Apps Scriptの使い方をご紹介します。自動化できるところは自動化していきましょう。
最後に全ソースコードを掲載しているので、活用いただければ幸いです。
GmailやGoogleドライブなど、11個ものGoogleアプリケーションをクラウド上で操作できるサービスです。JavaScript言語を用いて、テキスト入力を自動化したり、他のアプリケーションと連携させたりできます。今回は、スプレッドシートの入力自動化と、メール(Gmail)通知を行います。
今回作成するプログラムのサンプル
https://docs.google.com/spreadsheets/d/1R0usuQiXVSEWd3gvUXM8pLpReCs5lMQ1QESjgq8l0yM/edit?usp=sharing
・「依頼者」を入力すると、自動で「依頼日」が挿入される
・「担当者」を入力すると、自動で担当者にメール通知される
・「対応内容」を入力すると、自動で「対応日」が挿入される
・ステータスを「完了」にすると、完了にしたレコードの背景色が自動でグレーに変わる
Google スプレッドシートにアクセスし、新しいスプレッドシートを立ち上げます。タイトルは「課題管理表」とし、項目は左から「No、ステータス、依頼者、依頼日、問い合わせ内容、担当者、通知、対応内容、対応日付」とします。
ステータスの項目は、プルダウンで「依頼、作業中、完了」を選択できるようにします。範囲選択後、右クリックし「データ入力規則」をクリックします。
条件「リストを直接指定」を選択し、「依頼,作業中,完了」を入力後「保存」をクリックします。(各項目はカンマ区切りで入力)
ステータスがプルダウンメニューより選択できるようになりました。
課題管理表の準備ができたら、入力や通知の自動化を実装していきます。はじめに「依頼者」と「対応内容」を入力したら、自動で日付を挿入します。「ツール」をクリックし「スクリプトエディタ」を選択します。
新規のスクリプトエディタが起動するので、プロジェクト名を適当に変更します。今回は「QAProject」としました。
リファレンス:
https://developers.google.com/apps-script/reference/spreadsheet/
function onEdit(e) {
Logger.log('スプレッドシートが編集されました');
入力が終わったら、ファイルメニューから「保存」(
スプレッドシートを編集し、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'));
}
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();
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('通知済');
}
メール通知は 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;
};
[筆者プロフィール]
阿部 正幸(あべ まさゆき) | 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.