Google Apps Scriptでカンバンボードを作成する【GAS】
業務でタスク管理が非常に重要視されてきています。と言っても、これまでのようにタスクをただ並べて、消化したら消すだけでは芸がない上に、効率的で且つ合理的な管理が出来てるとは言えません。そこで使われているのがKanban Board。Microsoft365のPlannerであったり、Backlogであったり、AsanaやTrello等が利用されるようになりました。
しかし、Google WorkspaceにはアドオンとしてはKanbanchi Taskのような外部サービス連携はあったりしますが、タスク管理といえるのはGmail付属のToDoリストやKeepしかありません（しかも、KeepはGoogle Apps Scriptから操作するAPIが用意されておらず、REST APIであるGoogle Keep APIはEnterpriseでしか利用できない）、
そこで今回スプレッドシートをベースにしつつも、kanban方式のタスク管理を作ってみようと考えました。但し、機能は順次追加中の過程なので、ベータ版です。
リンク
図：こんな感じのアプリを作ります
目次
今回利用するスプレッドシート等
- カンバンアプリ - Google Spreadsheet
- Vue.js
- Vuetify
- jQuery
- Vue-Draggable
- Vue-loading-overlay
- Vue-tooltip
- Google Picker API
今回は、Vue-Draggableと呼ばれるVue.jsに対応したプラグインを用いて構築してみたいと思います。VueやVuetifyについては以下のエントリーを参考にしてみてください。今回のサンプルはKanban Boardのアプリですが、様々なGASアプリのノウハウを詰め込んであるので、学習用資材として活用してもらえたらと思います。
尚、現時点でのサンプルアプリはこちらをクリックすると操作する事が可能です。
仕組みと使い方
仕組み
概要
今回のアプリはGASでウェブアプリを構築する極めて標準的なテクニックと、応用テクニックが含まれています。主に列挙すると
- Googleスプレッドシートをデータの入出力の土台として作成
- 複数名同時に書き込みへ対処する為の排他処理
- ウェブアプリケーションとして構築
- VueおよびVuetifyの2つのフレームワークといくつかのプラグインで構成
- 書き込み時のタスクの並び順を記録する為のロジック（sort列）
- ドメイン内のユーザのプロファイルからアイコンURLをAdmin SDKを利用して取得（アサイン先ユーザのアイコンとして使用予定）
- ウィンドウサイズにフィットするようにサイズ自動調整
- 3分置きにGoogleスプレッドシートのデータを自動取得するポーリング機能
- Vue.jsを利用して動的にclass指定を変動させるテクニック
- Draggableで移動時に移動先情報のマージ、自分向けのタスクの背景色変更、期日オーバーの場合の日付色変更など細かなユーザビリティ
- Google Calendarへのイベント登録を行う
ここに順次、新規・編集追加ダイアログの装備やGoogle Calendar連携、アサイン時にメールで通知、トリガーで締め切り前にメールで一括通知などを装備して行こうかなと思います。もちろん、タスク管理のアプリなので、Google Formからの依頼をこのシートに書き出すようにする使い方も非常に良い選択肢だと思います。
queryシート
メインのkanbanとusersにシートを分けて、Query関数で結合したシートをデータ取得元として利用しています。kanbanシートの担当者列の値とusersのメール列の値を連結し、kanbanの全データ + usersの氏名とアイコンURLを1枚にしたテーブルとして表示させています。
数式としては以下のようなものが、A2に入っていますがこのシートは変更禁止です。
//シートを連結して1枚にする数式
=query({kanban!A2:L,iferror(arrayformula(vlookup(kanban!D2:D,users!A2:C2,{2,3},false)))})
使い方
セットアップ
スプレッドシートを開くと、「▶設定」というメニューが開かられるので
- 初期化を実行して、スプレッドシートのIDをスクリプトプロパティに格納する
- Usersシートにドメイン内限定ですが、ユーザを登録し、アイコン取得を実行すると管理者権限があれば、各ユーザのアイコンへのURLを取得して来ます。
- 通知トリガーをクリックすると、3日前〜終了日超過したタスクについてまとめて、毎日朝の5時に対象者にメール通知を送るトリガーが設置されます。
- Google Cloud Consoleで作成しておいたPicker API用のAPIキーをセットする為に、APIキー登録をクリックして、入力して保存する（タスク追加編集時にドライブのファイルを選択する為に利用します）
- アップ先フォルダIDを指定しておくと、添付ファイル選択時にPickerでファイルを直接アップすることが可能になります。
- カレンダーID登録をしておくと、対象のGoogle Calendarにもイベントが登録されるようになります。（タスク名と開始日付、終了日付のみ今は対応）
- ウェブアプリケーションとしてデプロイする
あとは、サンプルにあるようにタスクを登録してゆくことで、Drag可能なTrello風のカンバンボードが表示されるようになります。セクションを移動させる事で、各レコードのステータスおよびそのセクション内に於けるソート順を書き換えてくれるようになっています。
条件付き書式設定にて、完了済みは背景色を黄色、終了のものは灰色となり、終了のデータはカンバンボードには出てこない仕組みになっています。また、担当者のメアドがアクセスしてるユーザと同じメアドの場合、カードの背景は青色になる仕組みになっていますので、ウェブアプリケーションとして公開する場合（デプロイ時）には、「次のユーザとして実行」については、ウェブアプリケーションにアクセスしてるユーザにするのが望ましいでしょう。
特に今回Picker APIを追加したのですが、自分の権限でデプロイしてしまうと、他の人のPickerでも自分のフォルダ構成が表示されてしまうので要注意です。
尚、カレンダーIDですが以下の手順で取得出来ます（xxxxx@group.calendar.google.comというのがIDになります）
- 適当にカレンダーを作る
- カレンダーの「︙」をクリックし、設定と共有を開く
- カレンダーの統合を開く
- カレンダーIDが表示されてるので、これをアプリの設定に登録する
図：カレンダーIDの取得方法
図：イベントがカレンダーに登録された
図：デプロイしないと使えません
図：締切日超過メール
アプリの使い方
現時点で実装済みの内容についてだけとりあえず記載しています。
タスクカードの色
青いタスクカードは自分にアサインされているタスクになります。それ以外は誰かにアサインされているか？アサインされていないタスクとなります。また、赤字の日付は、今日の時点で終了日を超過してるタスクとなります。
図：ひと目で状態を把握する事が可能
タスクカードを移動
タスクカードを掴んで、未着手⇔進行中⇔完了済みの3つのセクションを自由に移動できます。移動が完了すると対象のタスクカードの詳細を開けるようになります（移動中はロックしています）。
複数名同時に同じカードを掴んで移動した場合、最後の人の処理が反映します（排他処理をしている為バッティングはしません）。
タスクカード編集・新規追加
タスクカードをクリックすると編集画面が出ます。また右上の新規追加からは空の状態のタスクカード作成画面が出ます。また、添付ファイルの隣にあるボタンをクリックすると、Google Pickerを利用してGoogle Drive上のファイルを選択して、URLを取得する事が可能です。アップロードで指定する事も可能です。
アップロードの場合はセットアップで指定したフォルダにファイルがアップされ、そのファイルへのURLが取得されるようになっています。このURLがカードのリンクボタンとなります。
保存を実行すると対象のレコードの内容が書き換わります。新規追加の場合には、appendRowにて新規にシートにレコードが追加されます。
図：タスク編集画面
図：Google Pickerを使ったファイル選択
ソースコード
全体のコードのうち、一部分主要な部分のみを掲示しています。
GAS側コード
GAS側はHTML側からの命令にしたがって、シートへの読み書きを中心に装備しています。また、Admin SDKを利用しているのでユーザのアイコンURLを一括で取ってくる関数等も含まれています。
主に、起動時・アイコンURL取得時・タスク移動時のイベント毎の関数を記述しています。
//スプレッドシートデータを取得して返す
function getKanbanData(){
//sheetidを取得する
let Properties = PropertiesService.getScriptProperties();
let sheetid = Properties.getProperty("sheetid");
//Spreadsheetを取得する（queryシートを利用する）
let ss = SpreadsheetApp.openById(sheetid).getSheetByName("query").getRange("A2:L").getValues();
//配列データをソートする(sort列にて)
ss.sort(function(a,b){return(a[11] - b[11]);});
//連想配列に変換して渡す
let array = [];
//現在アクセスしてるユーザ名を取得
let useracc = GetUser();
for(let i = 0;i<ss.length;i++){
//一時配列を用意
let temparr = {};
//レコードを取り出す
let rec = ss[i];
//連想配列に値を入れていく
temparr.no = rec[0];
temparr.bucket = rec[1];
temparr.name = rec[2];
temparr.tantou = rec[3];
temparr.prionum = rec[4];
temparr.step = rec[5];
temparr.memo = rec[6];
temparr.link = rec[7];
temparr.start = getDateman(rec[8]);
temparr.limit = getDateman(rec[9]);
temparr.status = rec[10];
temparr.sortnum = rec[11];
temparr.username = rec[12];
temparr.usericon = rec[13];
//その他の情報を追加
//自分のタスクかどうか？チェック
if(rec[3] == useracc){
temparr.color = "mytask";
}else{
temparr.color = "othertask";
}
//prionumの値に応じてmdi値をセット
temparr.priority = "mdi-numeric-" + rec[4] + "-circle";
//配列に渡す
array.push(temparr);
}
//HTML側に返す
return JSON.stringify(array);
}
//iconurlを取得してマージする
function getUserIconUrl(){
//シートデータを取得する
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("users");
let ss = sheet.getRange("A2:C").getValues();
//シートデータのメアドを元にアイコンサムネイルを取得する
let array = [];
for(var i = 0;i<ss.length;i++){
//レコードを一個取り出す
let rec = ss[i];
//AdminSDKにてサムネイルのURLを取得する
let mail = rec[0];
if(mail == "" || mail == undefined){
continue;
}
//自分のドメインの範囲内しか取得はできません。
let thumbnail = ""
try{
let user = AdminDirectory.Users.get(mail);
thumbnail = user.thumbnailPhotoUrl;
}catch(e){
thumbnail = "";
}
//一時配列と用意してarrayにpush
let temparr = [];
temparr.push(thumbnail);
array.push(temparr);
}
//アイコンURLをシートに書き出す
let lastColumn = array[0].length; //カラムの数を取得する
let lastRow = array.length; //行の数を取得する
sheet.getRange(2,3,lastRow,lastColumn).setValues(array);
//メッセージを表示する
let ui = SpreadsheetApp.getUi();
ui.alert("取得完了しました。")
}
//移動時にGoogleスプレッドシート側のデータを書き換える
function changelog(sortman,arrid){
//ドキュメントロックを使用する
let lock = LockService.getDocumentLock();
//30秒間のロックを実施
if (lock.tryLock(30000)) {
try {
//ここに処理を記述する
//arridからステータスを変更
let sortstatus = "";
switch(arrid){
case "items":
sortstatus = "未着手"
break;
case "items2":
sortstatus = "進行中"
break;
case "items3":
sortstatus = "完了済み"
break;
}
//スプレッドシートの対象のレコードの値を書き換える
//sheetidを取得する
let Properties = PropertiesService.getScriptProperties();
let sheetid = Properties.getProperty("sheetid");
//sheetの対象のレコードの位置を特定する
let sheet = SpreadsheetApp.openById(sheetid).getSheetByName("kanban");
let ss = sheet.getRange("A2:A").getValues();
let targetid = 0;
let editflg = false;
//sortmanを回してスプレッドシートのステータス内のsortnumを書き換える
for(let j = 0;j<sortman.length;j++){
//sortmanからnoとsortnumを取り出す
let sortid = sortman[j].no;
for(let k = 0;k<ss.length;k++){
//レコードを一個取り出す
let rec = ss[k];
//sortidと一致するかチェック
console.log(sortman[j].name + " : " + sortstatus)
if(sortid == rec[0]){
//書き込み行番号を決定
targetid = k + 2;
//シートのデータを書き換える
sheet.getRange("K" + targetid).setValue(sortstatus);
sheet.getRange("L" + targetid).setValue(j);
//editflgを建てる
editflg = true;
//内側のループを抜ける
break;
}
}
}
//editflgの値を元に処理を分岐
if(editflg == true){
return JSON.stringify(["OK","移動完了"]);
}else{
return JSON.stringify(["NG","対象のレコードが見つかりませんでした"]);
}
} catch(e) {
return JSON.stringify(["NG",e.message]);
} finally {
//最期に必ずロックを開放する
lock.releaseLock();
}
}else{
//ロックが取得できずにfalseが返ってきたのでエラーメッセージを表示
return JSON.stringify(["NG","処理がタイムアウトしました。"]);
}
}
- スプレッドシートのデータに基づいて、その全データと加えて自分向けタスクかどうか？等を加えています
- Vueで利用するため、データはこれまでと違い連想配列の形式で構築してから返しています。
- AdminDirectory.Users.getにて、サムネイルアイコンのURLを取得させています。故に、サービスとしてAdminDirectoryを追加しています
- タスク移動時にはそのセクションの全データを渡し、該当タスクのステータス変更だけでなく並び順を保存する為、sort列の値も毎回順番になるよう書き直しています。
- LockServiceによって、同時書き込み等の対応の為、30秒間のロックを実施し排他処理を実現しています。
- 但し、リアルタイム処理はGASではできないので、書き込み前に誰かが書き換えていた場合にはそのレコードは上書きとなるので要注意です。これはGoogle Apps Scriptの仕様なので仕方ないです。
- 移動後はHTML側で最新情報を取得し直してるので、注意すべきはセクション移動対象のレコードだけになります。
HTML側コード
今回のコードはそこそこの量があるため、主要な部分のコードと解説を記載しています。主なポイントは
HTML部分
<span class="box_srcollbar" v-resize="updateBoxHeight" id="scr1">
<draggable id="items" v-model="items" tag="v-col" class="colman" group="items" @end="onEnd" @start="onStart" data-value="items" >
<v-card v-for="item, index in items" :key="item.no" v-bind:class="[item.color]" v-on:click.stop="clickCard(item,this.id)" class="my-5" >
<div class="text--primary" id="toma">
<v-card-text class="text-h5 font-weight-bold"><b>{{ item.name }}</b></v-card-text>
</div>
<v-divider></v-divider>
<v-card-actions >
<v-btn small text v-if="item.limit != ''" v-on:click.stop="alert(item.limit)">
<b class="greenday" v-bind:class="[new Date() > new Date(item.limit) ? 'redocean' : 'greenday']">
<v-icon small>mdi-calendar-clock</v-icon>
{{ item.limit }}
</b>
</v-btn>
<v-spacer></v-spacer>
<v-btn small icon v-if="item.link != ''" v-on:click.stop="openman(item.link,this.id)" id="tera">
<v-icon small>mdi-link-variant</v-icon>
</v-btn>
<v-btn small icon v-on:click.stop="alert(item.priority)">
<v-icon small>{{ item.priority }}</v-icon>
</v-btn>
</v-card-actions>
</v-card>
</draggable>
</span>
- Vueのデザインやスクロールバーの位置などの調整の為のCSSが多数含まれています。
- 未着手、進行中、完了済みの3つの配列をもって、それぞれにDraggableを割り当てています。
- v-ifにてlimitの値が無い場合には、日付は表示されません
- Vue-Draggable自身にはUIを構築する機能を持っていない為、UIやデザインは自身で構築する必要があります（故に今回はVuetifyを利用して各UIを構築しています）
- box_scrollbarを追加し、タスクが枠以上になるとスクロールバー表示が追加されます。
- 今日の日付よりもlimitの値が過去の場合には、日付の文字色は赤色になるようにVueで条件判定させてclassの切り替えをしています。
//クラスを条件判定で切り替え v-bind:class="[new Date() > new Date(item.limit) ? 'redocean' : 'greenday']"
- v-on:click.stopにて各ボタンアクションを設定。親のカードのイベントが発火しないように分けています
JS部分
外部のCDNとして、以下のURLをBody以下で読み込ます必要があります。
<!-- Vue関係 --> <script src="https://cdn.jsdelivr.net/npm/vue@2.6.14/dist/vue.min.js"></script> <script src="https://cdn.jsdelivr.net/npm/vuetify@2.x/dist/vuetify.js"></script> <!-- Draggable関係 --> <script src="https://cdn.jsdelivr.net/npm/sortablejs@1.8.4/Sortable.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/Vue.Draggable/2.23.2/vuedraggable.umd.min.js"></script> <!-- v-tooltip --> <script src="https://unpkg.com/v-tooltip@2.0.2/dist/v-tooltip.min.js"></script> <link href="https://officeforest.org/wp/library/tooltip.css" rel="stylesheet">
VueおよびVuetifyの各関数等は以下の通り。また、ウィンドウサイズ変更時にbox_scrollbarの高さの自動調整用の関数などをリサイズイベント時に発動するようにしてあります。
//指定時間毎にポーリングする処理（ミリ秒指定）
function poll(waitmin) {
//分をミリ秒に変換
let waitmil = waitmin * 1000 * 60;
//インターバル設定
setInterval("onRefresh()", waitmil);
}
//リサイズする高さを計算する
function setGridHeight(){
let layoutHeight = $(window).height() - 230;
let layoutHeight2 = $(window).height()- 98;
$("#scr1").css("height", layoutHeight + "px");
$("#scr2").css("height", layoutHeight + "px");
$("#scr3").css("height", layoutHeight + "px");
$('#gridarea').css('height', layoutHeight2 + 'px');
}
var vm;
var srcarr;
var destarr;
//Vue-Draggableをロードする
const draggable = window['vuedraggable'];
//v-tooltipプラグインを読み込む
Vue.use(VTooltip);
vm = new Vue({
el: '#app',
vuetify: new Vuetify(),
data: {
items:[],
items2:[],
items3:[],
snackbar:false,
text:"",
timeout:3000,
drawer: false,
activeTitle:"タスク一覧",
group: null,
acpanel : 0,
},
components: {
'draggable': draggable,
},
methods:{
//リフレッシュ
onRefresh2(){
onRefresh();
},
//パネルチェンジ
changepane(panel) {
//panelの内容に応じて処理を分岐
switch (panel) {
case "main":
this.acpanel = 0;
this.activeTitle = "タスク一覧";
break;
}
//手動でドロワーを閉じる
this.drawer = false;
setTimeout(() => {
this.drawer = false
}, 0)
},
//resize時にboxscrollbarの高さを調整する
updateBoxHeight(){
setGridHeight();
},
//カードをクリック時に詳細ウィンドウを表示する
clickCard(item,cals){
alert(item.name);
},
//添付ファイルリンクをblankで開く
openman(url,cals){
window.open(url,"_blank");
},
//ドラッグイベント開始
onStart(evt){
//移動元情報を格納する
let srcindex = evt.oldIndex;
let src = evt.from;
let srcid = src.getAttribute("id");
let srcrec = this[srcid][srcindex];
srcarr = [srcindex,srcid,srcrec];
},
//ドラッグイベント完了時
onEnd(evt){
//移動先の配列index
let arrindex = evt.newIndex;
let dest = evt.to;
let destid = dest.getAttribute("id");
let destarr = this[destid];
//GAS側のレコード情報を書き換える
google.script.run.withSuccessHandler(function(ret) {
//返り値を取り出す
let retjson = JSON.parse(ret);
if(retjson[0] == "OK"){
//処理終了
snackman(retjson[1]);
//リフレッシュ
onRefresh();
return;
}else{
//NGだったのでレコードを元に戻す
//srcrecを元の配列に戻す
let srcman = srcarr[1];
let srcrec = srcarr[2];
let srcindex = srcarr[0];
vm[srcman].splice(srcindex, 0, srcrec);
//recordを移動先から消去する
vm[destid].splice(arrindex, 1);
//メッセージを表示
snackman(retjson[1]);
//リフレッシュ
onRefresh();
return;
}
}).changelog(destarr,destid);
},
},
mounted : function(){
//Box Scrollbarの高さ調整
setGridHeight();
//queryシートのデータを取得する
google.script.run.withSuccessHandler(onSuccess).getKanbanData();
//3分毎にデータを自動ポーリング
poll(3);
}
})
- 起動時に、setInterval("onRefresh()", waitmil)をセットして3分毎にGAS側の最新データを自動で取得するようにしてあります。
- itemsにはgetKanbanDataから取得したデータをステータス列の値に応じて、それぞれの配列に連想配列の形で追加するようにしてあります。
- 移動開始時イベントはonStart、終了時イベントはonEndとしてそれぞれ定義しています。
- Draggableの対象となるタグはv-colタグとしています。
-
src.getAttributeにて移動元、移動先のセクションのIDを取得させています。
- 移動時にエラーが出た場合には、元の位置に戻すように仕組みを追加しています。
- 一番重要なのは、onEnd関数。移動が終わった後にGAS側にその結果を伝えて、スプレッドシートのデータを書き換えています。
- 移動したデータだけでなく、移動先セクションに於けるタスクの並び順も変えるchangelog関数を実行する必要があるため、そのためのデータの塊で渡しています。
- 移動と書き換え後にスプレッドシートから最新の全データを再取得しています。他の人の更新内容もリフレッシュする為に。
関連リンク
- 最近盛り上がってきた「かんばん」、ソフトウェア開発における「かんばん」（Kanban）とは何か
- Vue-Trello-Clone - Github
- Vue.jsとVue.Draggableを使ったカンバン式タスク管理システムを作ってみよう
- Building animated draggable interfaces with Vue.js and Tailwind
- Animated draggable Kanban Board with Tailwind and Vue
- mauricius/vue-draggable-resizable
- Vue.jsでドラッグ＆ドロップするなら「Vue.Draggable」がおすすめ
- カンバンをGithub+GASにして進捗を自動計算化/GASをGit管理した話
- KJ法とは？メリットやデメリット、実施方法を紹介
- 【Vue.Draggable】リストやdivタグ以外の要素で、ドラッグアンドドロップで並び替えする方法【Vuetify】
- レイアウト微調整に便利な Spacing Helper (Vuetify) を使ってみる
- How to get a Gmail user's profile picture using Google Apps Script?
- Admin SDK Directory Service
- 【Vue.js】v-forでHTMLを展開するとき、classを動的に付与したい
- とても簡単にドラッグアンドドロップが実現できる Vue-draggable を使ってみた
- 【Vue.js】Vue.Draggableを使って躓いたところ
- clickable v-card with excluded area for the v-menu
- 【Vue】@click.stopのstopメソッドは何をしているか？（v-on:click.stop）
- Spreadsheetに紐付いていない独立したファイルからSpreadsheet上に独自のメニューを作りたい
