Chatworkに登録されているタスクをまとめて確認したい場合に便利な機能です。今回はChatworkに登録されているタスクをスプレッドシートに一覧で出力する処理をご紹介します。
目次
タスク一覧出力イメージ
Chatworkに下図のようなタスクが登録されていたとします。
スプレッドシートのメニューからタスク出力をクリックします。
このようにタスクの一覧が出力されます。
注意点としては、タスク一覧は出力したファイルの2つ目のシートに追加されますので、シート名の出力日時で当該シートを確認してください。
スプレッドシートにパラメーター入力フォーマットを作成する
Chatworkからタスクを出力するためには、APIトークン、ルームIDを設定する必要があります。入力するためのフォーマットを下図のように作成してください。処理の中でC列の値を使用していますので、C列の行番号を間違えずに作成をお願いします。また、シート名は「パラメーター」としてください。
スプレッドシートから入力パラメーターを取得する
それでは、スプレッドシートから「タスク出力」をクリックした場合にどのような処理が行われているか見ていきましょう。
まず、スプレッドシートから入力値を取得します。SpreadsheetApp.getActiveSpreadsheet()で、現在開いているスプレッドシートファイル全体を取得できます。getSheetByName(‘パラメーター’)で「パラメーター」シートを取得しています。シートに対してgetRange(‘C2’).getValue()のようにセル番号を指定することで特定のセルの値を取得できます。
//パラメーターシートから入力パラメーター取得
const paraSS = SpreadsheetApp.getActiveSpreadsheet();
const paraSheet = paraSS.getSheetByName('パラメーター');
if (paraSheet === null){
Browser.msgBox('パラメーターシートが見つかりません。');
return;
}
const token = paraSheet.getRange('C2').getValue(); //チャットワークAPIトークン
const roomid = paraSheet.getRange('C3').getValue(); //ルームID
const fileName = paraSheet.getRange('C4').getValue(); //出力ファイル名
const headerColor = paraSheet.getRange('C5').getBackground(); //タスク一覧ヘッダーの色
//必須入力チェック
if(token === ''){
Browser.msgBox('「APIトークン」が入力されていません。');
return;
}else if(roomid === ''){
Browser.msgBox('「ルームID」が入力されていません。');
return;
}
Chatworkからタスクを取得する
get_Task関数でChatworkからタスクを取得する処理を行っています。ChatworkAPIにはタスクを取得するためのURLが用意されており、上記パラメーターシートから取得したAPIトークン、ルームIDを指定することで、指定ルームのタスクを取得することができます。
function get_Task(token,roomid,autoFlg) {
//指定されたルームのタスクを取得
const params = {
headers : {"X-ChatWorkToken" : token}, //APIトークン
method : "get"
};
const url = "https://api.chatwork.com/v2/rooms/" + roomid + "/tasks";
let res;
try{
res = UrlFetchApp.fetch(url, params);
}catch(e){
if(autoFlg === true){
console.log('「APIトークン」または「ルームID」が誤っています。');
}else{
Browser.msgBox('「APIトークン」または「ルームID」が誤っています。');
}
return false;
}
Chatworkから返却されるタスクはJSON形式です。GASで扱えるようJSON.parse(res.getContentText())とし、オブジェクトに変換します。
//タスクがなければ処理終了
let json;
if(res.getContentText().length != 0){
json=JSON.parse(res.getContentText());
}else{
if(autoFlg === true){
console.log('タスクがありません。');
}else{
Browser.msgBox('タスクがありません。');
}
return false;
}
取得したタスクをソートする
JSONのsort関数を使用してタスク一覧出力時の並び順にソートします。
a、bにはChatworkから取得した順番にデータが設定されます。if文の結果がtrueの場合、returnに0より大きい値を設定すると、aはbの後ろとなり、b→aの順番になります。returnに0より小さい値を設定すると、aはbの前となり、a→bの順番になります。降順にしたい場合はreturnの値を逆にします。returnに0を設定した場合は変更されません。
今回は期限日、期限時間、担当者IDの昇順で並び替えをしています。Chatworkでタスクを登録する際、期限を設定しないこともできます。期限の設定状態はChatworkから取得したJSONの中のlimit_typeで確認できます。 limit_typeが「none」の場合は期限設定がありません。期限が設定されていないタスクは出力順序を下にしたいため、ソート前に期限日時であるlimit_timeに最大値を設定します。limit_timeはUNIXTIMEで取得されており、最大値は「9999999999」です。
//タスクを期限+担当者IDでソート
let calcLimit;
let maxTime;
json.sort(function(a,b){
//期限が設定されていない場合、UNIXTIME最大値をセット
if(a.limit_type === 'none'){
a.limit_time = 9999999999;
}else if(b.limit_type === 'none'){
b.limit_time = 9999999999;
}
limit_typeが「date」の場合、期限日は設定されていますが、期限時刻が設定されていません。期限日が同じだった場合、時刻が設定されていないタスクの順番を下にするため、時刻に最大値をセットしています。UNIXTIMEに1000を掛けた後にformatdate()でJSTに変換することができます。時刻に「23:59:59」をセットした後、ソート処理のため、UNIXTIMEに戻します。JSTを1000で割った後、toFixed()で小数点以下を丸めることでUNIXTIMEに変換できます。
//期限が日付のみの場合、時間を23:59:59に変更
if(a.limit_type === 'date'){
calcLimit = new Date(a.limit_time * 1000 ); // UNIXTIMEを変換
maxTime = new Date(Utilities.formatDate(calcLimit,'Asia/Tokyo','YYYY/MM/dd'+' 23:59:59'));
maxTime = maxTime.getTime() / 1000;
a.limit_time = maxTime.toFixed();
}else if(b.limit_type === 'date'){
calcLimit = new Date(b.limit_time * 1000 ); // UNIXTIMEを変換
maxTime = new Date(Utilities.formatDate(calcLimit,'Asia/Tokyo','YYYY/MM/dd'+' 23:59:59'));
maxTime = maxTime.getTime() / 1000;
b.limit_time = maxTime.toFixed();
}
並び替えをします。まず、期限日時(limit_time)をa、bと順番に比較して、大きいaが大きい場合はb→aの順に、bが大きい場合はa→bの順番にします。次に、担当者ID(account_id)を順番に比較して、こちらも昇順に並び替えます。
if(a.limit_time > b.limit_time) return 1;
if(a.limit_time< b.limit_time) return -1;
if(a.account.account_id > b.account.account_id) return 1;
if(a.account.account_id < b.account.account_id) return -1;
return 0;
})
指定したファイルがカレントフォルダにあるか確認する
タスク一覧をカレントフォルダに出力します。出力ファイル名が指定されている場合は指定ファイルに、設定されていない場合は開いているパラメーターファイルに出力します。
paraFileId に開いているパラメーターファイルのファイルIDを設定し、DriveApp.getFileById(paraFileId).getParents()とすることで、カレントフォルダのイテレーターを取得します。parentFolder.next()でフォルダを取得し、folder.getId()でカレントフォルダのIDを取得できます。
//カレントフォルダに指定の「出力ファイル名」があるか確認
const paraFileId = paraSS.getId(); //パラメーターファイルID
const parentFolder = DriveApp.getFileById(paraFileId).getParents(); //パラメータファイルのカレントフォルダ
const folder = parentFolder.next(); //カレントフォルダ取得
const folderId = folder.getId(); //カレントフォルダID
fileNameにスプレッドシートから取得したファイル名が入っているため、folder.getFilesByName(fileName)、files.hasNext()で指定フォルダ内でファイル名が合致するファイルを取得することができます。ファイルがない場合はcreate_SS関数を呼び出し、指定ファイル名でファイルを作成します。
let fileId;
let files;
//「出力ファイル名」に指定がなければカレントファイルに出力
//「出力ファイル名」ファイルがあれば「出力ファイル名」ファイルに出力、なければ新規作成
if (fileName === ''){
fileId = paraFileId;
}else{
files = folder.getFilesByName(fileName);
if (files.hasNext()){
fileId = files.next().getId();
}else{
fileId = create_SS(folderId,fileName);
}
}
指定フォルダに新しいスプレッドシートファイルを作成する
ファイルがない場合は、新しくスプレッドシートを作成します。ファイルを作成する際、作成場所の指定をすることができないため、少し面倒ですが、3ステップで作成していきます。まず①Googleマイドライブ直下にファイルを作成、②作成したファイルを指定フォルダに追加、③Googleドライブ直下のファイルを削除、という順番で処理をしていきます。①SpreSpreadsheetApp.create(fileName).getId()でマイドライブ直下にfileNameで指定した名前のファイルを作成し、作成したファイルのファイルIDを取得しています。そして、DriveApp.getFileById(fileid)で作成したファイルを取得、DriveApp.getFolderById(folderId)で指定フォルダを取得しています。②folder.addFile(file)で指定フォルダに作成したファイルを追加します。③DriveApp.getRootFolder().removeFile(file)でマイドライブ直下のファイルを削除します。
function create_SS(folderId,fileName) {
const fileid = SpreadsheetApp.create(fileName).getId();
const file = DriveApp.getFileById(fileid);
const folder = DriveApp.getFolderById(folderId);
folder.addFile(file);
DriveApp.getRootFolder().removeFile(file);
return fileid;
}
ファイルにシートを追加する
これまでの処理でタスクを出力するファイルを確定することができました。次はoutput_Task関数を呼び出し、ファイルに新しくシートを追加していきます。
今回はシート名を「日付+時分」にしています。連続で出力した時など、ファイルにすでに同じ名称のシートがある場合は「日付+時分秒」のファイル名としています。
SpreadsheetApp.openById(ファイルID)で開いていないスプレッドシートを指定できます。insertSheet(現在日時分)で指定したシート名のシートを追加しています。
function output_Task(json,fileId,headerColor) {
//出力ファイルに日付+時間のシート追加
const today = new Date();
const ss = SpreadsheetApp.openById(fileId);
const current = Utilities.formatDate(today,'Asia/Tokyo','YYYY/MM/dd HH:mm');
let SHEET;
if(ss.getSheetByName(current)){
SHEET = ss.insertSheet(Utilities.formatDate(today,'Asia/Tokyo','YYYY/MM/dd HH:mm:ss'));
}else{
SHEET = ss.insertSheet(current);
}
スプレッドシートにタスクを出力する
output_Task関数内で行っています。「取得したタスクをソートする」で並び替えたタスクをシートに出力していきます。SHEET.appendRow([name,body,limit_date,limit_time])でスプレッドシートに行追加しながら値を出力しています。
//シートにデータ出力
let name; //担当者
let body; //タスク内容
let limit; //期限
let limit_date; //期限日
let limit_time; //期限時間
SHEET.appendRow(['担当者','タスク','期限日','期限時間']);
for(i in json){
name = json[i].account.name;
body = json[i].body;
//期限の設定状況によって分岐
switch(json[i].limit_type){
case 'time': //日付、時間があるとき
limit = new Date(json[i].limit_time * 1000 ); // UNIXTIMEを変換してdateにセット
limit_date = Utilities.formatDate(limit,'Asia/Tokyo','YYYY/MM/dd');
limit_time = Utilities.formatDate(limit,'Asia/Tokyo','HH:mm');
break;
case 'date': //日付のみのとき
limit = new Date(json[i].limit_time * 1000 ); // UNIXTIMEを変換してdateにセット
limit_date = Utilities.formatDate(limit,'Asia/Tokyo','YYYY/MM/dd');
limit_time = '';
break;
case 'none': //期限の設定がないとき
limit_date = '';
limit_time = '';
break;
}
SHEET.appendRow([name,body,limit_date,limit_time]);
}
スプレッドシートに罫線と色をつける
出力したタスク一覧に罫線と色をつけていきます。SHEET.getLastRow()で値が入力されている最終行番号を取得、SHEET.getLastColumn()で最終列番号を取得します。SHEET.getRange(1, 1, lastRow, lastColumn)で値が入力されているセル範囲を取得します。range.setBorder(true, true, true, true, true, true)で罫線の有無を指定します。falseを指定すると線が無しとなります。 setBorder (上, 左, 下, 右, 垂直, 水平)の順番で各線の有無を指定できます。
//罫線と色
const lastRow = SHEET.getLastRow();
const lastColumn = SHEET.getLastColumn();
const range = SHEET.getRange(1, 1, lastRow, lastColumn);
range.setBorder(true, true, true, true, true, true);
SHEET.getRange(1,1,1,4).setBackground(headerColor);
シートにタスク出力メニューを追加する
タスクの出力は、シートのメニューバーから実行できるようにしています。
スプレッドシートを開いた時にonOpen()というイベントが自動実行されますので、onOpen()の中に下記のようなメニューを追加する処理を書いていくことで、シートにメニューを表示することができます。
//罫線と色
const lastRow = SHEET.getLastRow();
const lastColumn = SHEET.getLastColumn();
const range = SHEET.getRange(1, 1, lastRow, lastColumn);
range.setBorder(true, true, true, true, true, true);
SHEET.getRange(1,1,1,4).setBackground(headerColor);
ソースコードのダウンロード
下記からソースコード全体をダウンロードしていただけます。