試行錯誤ダイアリー

新卒エンジニアが日々の技術的な学び,働き方,日々感じたこと等を書きます

【Google Apps Script】スプレッドシート連携してタスク管理

今の部署では,それぞれのタスクをグーグルのスプレッドシートで管理していて,毎週の会議でそれを参照して進捗管理しているのですが,記載しているタスクの多さと,やったタスクが複数の案件をまたがったりしていて分かりづらいです.なので,ボタンを押したらその週の完了したタスクなどが表示されたら楽だなーと思ったのでやってみました.

実現したいこと

ボタンを押したら

  • 今週完了したタスク
  • 進捗中のタスク
  • 締切の近いタスク

の3つの項目についてダイアログにこのように表示したいと思います.

f:id:appli-in:20180618012444p:plain:w400

スプレッドシートの項目とイメージ

作ったスプレッドシートはこんな感じです.

f:id:appli-in:20180618013543p:plain

左上の青い「今週の進捗」と書かれた図形がボタンです. 残日数は”期限日”から”今日の日付”を引いた日数になっているので,すでに期限の過ぎたタスクについては"マイナス表示"になります.

また,開始日,期限日,完了日については,データの入力規則で日付に変更してあります.

実装

先に実装したコードを紹介して,その詳細について書いていきたいと思います.

コード

var sheet=SpreadsheetApp.getActiveSheet();//シートの情報を取得
var completed = "";//完了のタスク
var ongoing = "";//進捗中のタスク
var deadline = "";//締切が近いタスク
var day = 86400000; //一日をミリ秒(ms)換算した値

function checkTask(){
  //表を上から見ていってそれぞれ進捗中,完了にわけて文字列に追加.
  for (var i=3; i <= sheet.getLastRow(); i++){
    //完了したタスク(先週の報告から)
    if(sheet.getRange(i,8).getValue()=="完了" && (sheet.getRange(1,3).getValue() - sheet.getRange(i,5).getValue()) <= day * 7){
      completed = completed + sheet.getRange(i,2).getValue() + "\\n";
    }
    //進捗中のタスク
    if(sheet.getRange(i,8).getValue()!="完了" && (sheet.getRange(i,3).getValue() != null || sheet.getRange(i,3).getValue() != "")){
      ongoing = ongoing + sheet.getRange(i,2).getValue() + " : " + sheet.getRange(i,9).getValue() + "\\n";
    }
    //締切が近いタスク(2週間以内に締め切りがある)
    if(sheet.getRange(i,7).getValue() < 14 && sheet.getRange(i,8).getValue() != "完了"){
      deadline = deadline + sheet.getRange(i,2).getValue() + " : " + sheet.getRange(i,9).getValue() + "\\n";
    }
  }
}

function showProgress(){
  checkTask();
  Browser.msgBox("【完了したタスク】\\n"+ completed + "【進捗中のタスク】\\n" + ongoing + "【締切が近いタスク】\\n" + deadline);
}

タスクの区分判定

今回はタスクを「今週完了したタスク」,「進捗中のタスク」,「締切が近いタスク」にわけます.

今週完了したタスク

今週完了したタスクの定義はステータスが”完了”になっていることと,今週中に完了したことなので,条件文を以下のようにしました.

    //完了したタスク(先週の報告から)
    if(sheet.getRange(i,8).getValue()=="完了" && (sheet.getRange(1,3).getValue() - sheet.getRange(i,5).getValue()) <= day * 7){
      completed = completed + sheet.getRange(i,2).getValue() + "\\n";
    }

この関数では事前に定義したcompletedの文字列に,条件を満たしたタスク名を文字列として足していく処理をしています.これ以降の他のタスクの区分でも同じ事をしています.

if分の中のsheet.getRange(1,3).getValue()は常にセルで=today()によって今日の日付を表示しているセルを表してます.

ここで注意したいは,スプレッドシート上でセルでtoday関数=today()を使って表示した日時とセルの入力規則で表示した日時を単純に引き算するとミリ秒で返ってきます(セルの入力規則で日時にしたセル同士の引き算では日数が返ってくる),なので,事前に一日のミリ秒を定義しそれを日数分かけたものと比較することで,日数の基準を計算しています.この条件を設定しないと一週間以上前に完了したタスクまで表示されてしまい大変なことになります.

関連記事:【google apps script】スプレッドシートと連携時の日付の引き算について - 試行錯誤ダイアリー

またgoogle apps script(javascriptも?)ではダイアログ中の改行は"\n"ではなく"\\n"なので注意します.

進捗中のタスク

進捗中のタスクでは,ステータスが完了ではないもので,タスクに取り掛かっている状態,つまり,開始日が"null"でもなく""(空白)でもないタスクを加えていきます.

    //進捗中のタスク
    if(sheet.getRange(i,8).getValue()!="完了" && (sheet.getRange(i,3).getValue() != null || sheet.getRange(i,3).getValue() != "")){
      ongoing = ongoing + sheet.getRange(i,2).getValue() + " : " + sheet.getRange(i,9).getValue() + "\\n";
    }

どうやらスプレッドシートでは,一度も文字を入力してないセルは"null"と判定されますが一度なにかの文字や数値が入ったセルは消したあとは""(空白)と判定されてしまうようなので注意してください.

締切が近いタスク

こちらは簡単で純粋に"期限日"から"今日の日付"を引いた”残日数”が14日以内のものを集計して表示します.

    //締切が近いタスク(2週間以内に締め切りがある)
    if(sheet.getRange(i,7).getValue() < 14 && sheet.getRange(i,8).getValue() != "完了"){
      deadline = deadline + sheet.getRange(i,2).getValue() + " : " + sheet.getRange(i,9).getValue() + "\\n";
    }

ここではセルの入力規則での日時同士なので日数の「14」で判定します.

ボタンの設置

タブメニューの"挿入"->"図形描写"から適当に図形を生成します. そして,生成した図形を右クリックすると図形の右上にメニューを開けそうなボタンが出てきます.そこをクリックすると,「スクリプト割り当て」というメニューがあるのでクリックしてスクリプト上の関数名を入力します.今回の例だとshowProgressです.括弧は入力する必要はありません.

これだけで図形をクリックすると指定した関数を実行することができます.

showProgress関数では,checkTask関数を実行してそれぞれの文字列変数にタスク名を追加させた上で,ダイアログ出力(Browser.msgBox())しています.

function showProgress(){
  checkTask();
  Browser.msgBox("【完了したタスク】\\n"+ completed + "【進捗中のタスク】\\n" + ongoing + "【締切が近いタスク】\\n" + deadline);
}

まとめ

スプレッドシートでタスク管理をして,一週間のタスク状況を出力するコードをgoogle apps scriptで書きました. これで,自分の完了したタスクを少し管理しやすくなったんじゃないでしょうか.

大変だった点としては,today関数の内部の数値とスプレッドシートの値の差分がミリ秒で返ってくることで,事前に一日のミリ秒を定義しなければいけない所で少しつまづきました.

詳しくはこちらの記事で,

blog.trial-and-error-diary.fun