【GAS】Google Apps Script 活用事例 イベント運営・シフト管理、進捗管理で使えるガントチャート制作
GASで作りたいなと思っていたのが、ガントチャートです。Backlogみたいなガントチャートが、分かりやすくて良いなぁと思っていました。誰が何をしているかが分かりやすいので、導入してくれないかなと思っていました。
人事や経理、マーケティングの部署は入っているのに、なぜかウチの部署だけITに弱い人が上をやっているため、そういうのを入れてもらえない。（存在自体を知らないかもしれない）
そして、うちの部署には、工数管理というものが存在しません。管理職は、1日4,5時間、会議していて、自分が生産性が悪いという事に薄々気づいているのか、「工数管理は出来たら理想だけど......今は出来ない」と言って、永遠にやる事がなさそうな気がします。早く転職しなきゃ。
今回も、ソースコードを公開しますが、有料エリアにサンプルファイルのリンクを貼っておきました。そこからファイルをコピーする事が可能です。
ガントチャート → 閲覧用（QUERY関数で同期）
項目詳細 → 入力機能
挙動確認（画面録画）
また一歩、理想に近づいた。あとはサイドバーにHTMLを組み込むぞ！！ pic.twitter.com/ZiYj3FMDNF
— ⅡBASA (@nepia_infinity) April 4, 2020
制作するために、考慮しなきゃいけない事まとめ
G列（7列目）から始まって、イベントの長さに応じて、5分おきに1列が塗られるようにします。
09:00 - 09:15 受付のイベントを例にとると、15分のイベントなので、5分 * 3マス（ G3 : I3 ）を塗り潰す処理を書きます。
09:15 - 09:35 会場設営の場合は、塗り潰しが始まる場所が変わります。
（ J4 : M4 ）全部G列から始まれば、簡単なんですけどね....。
//1番目の項目の場合 09:00 - 09:15 受付 15分のイベントの場合
sheet.getRange(３, 7, 1, 3).setBackground('#ff9900')；
//2番目の項目の場合 09:15 - 09:35 会場設営 20分のイベントの場合
sheet.getRange(4, 10, 1, 4).setBackground('#ff9900')；getRange(行, 起点となる列 + n, 各項目につき1行, 所要時間 5分 * m)みたいな感じ。
イベントでの貸与物の管理、追いかけ業務の自動化
追いかけ業務の自動化で一本記事を書いています。こちらも良かったら、どうぞ
起点となる列 + n は、indexOfを使って求められる。
配列をindexOfを検索し、その返り値を足してあげます。（この5分おきの時刻の配列を作るのが、小さくはない問題だったりもする。）
function indexOf(){
const array = ['09:00','09:05','09:10','09:15'];
const number = array.indexOf('09:15');
console.log(number);//3
}
変数を仮置きして、組み立てる。
//1番目の項目の場合 09:00 - 09:15 受付15分のイベントの場合
//09:00 を０番目とした時、開始時刻が配列の何番目に当たるかを調べる。
const array = ['09:00','09:05','09:10','09:15'];
const number = array.indexOf('09:00');//0
const array2 = ['0:00','0:05','0:10','0:15','0:20'];
const offset = array2.indexOf('0:15');//3
let rowPostion = 3;
const ColumnG = 7;//起点となる列
let startPosition = columnG + number;
sheet.getRange(rowPostion, startPosition, 1, offset).setBackground('#ff9900')；
rowPosition += 1;//塗り潰しが終わった後で、１行ずつ進むこの内容をfor文で回して繰り返していけば、出来そうな感じがします。
時刻の配列を作る。
['09:00','09:05','09:10','09:15'.........'21:00']この配列を手作業で作るのは、ちょっとダルいですね。それもスクリプトで作りましょう！！
function timeArray() {
let requiredTime = '';
let timeArray = [];
for (let i = 0; i <= 60; i += 5) {
console.log(i);
for(let j = 9; j <= 21; j++) {
let hour = ('0' + j).slice(-2);
switch(i){
case 0: requiredTime = hour + ':00';
break;
case 5: requiredTime = hour + ':05';
break;
case 10: requiredTime = hour + ':10';
break;
case 15: requiredTime = hour + ':15';
break;
case 20: requiredTime = hour + ':20';
break;
case 25: requiredTime = hour + ':25';
break;
case 30: requiredTime = hour + ':30';
break;
case 35: requiredTime = hour + ':35';
break;
case 40: requiredTime = hour + ':40';
break;
case 45: requiredTime = hour + ':45';
break;
case 50: requiredTime = hour + ':50';
break;
case 55: requiredTime = hour + ':55';
break;
default: requiredTime = '';
break;
}
if(requiredTime === ''){continue};
timeArray.push(requiredTime);
}//for_j
}//for_i
timeArray.sort(compareFunc);
console.log(timeArray);
return timeArray
}//end
function compareFunc(a, b) {
return a < b ? -1:1;
}
うゲェ〜めちゃ面倒い。このスクリプト
なんかswitch構文使わなくても、mapとか使って、書けそうな気もするけど、ちょっと分からないため、このままいきます。もう直す気力が.....。
データの入力規則を生成する
function buildRule() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('ガントチャート');
const array = timeArray(9, 21, 60);//5分刻みで、09:00 - 21:00までの時刻を生成
sheet.getRange('D3:D').setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(false)
.requireValueInList(array, true)
.build());
const array2 = timeArray(0, 10, 60);//5分刻みで、00:00 - 03:55までの時刻を生成 endTimeの引数が、1桁だと、4:00が生成されない。
sheet.getRange('E3:E').setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(false)
.requireValueInList(array2, true)
.build());
}
10時間未満で作成すると、なぜか最後の9:00とか5:00が抜けてしまうため、サンプルファイルは、10時間で作成しました。
10時間あれば、8h勤務、1h休憩みたいなシフト表にも転用出来るかなと思っています。（残業は無い方がいいですよね.....残業前提の働き方なんて嫌や。）
Javascript 三項演算子
function compareFunc(a, b) {
return a < b ? -1:1;
}要素と要素を比較して、小さい順に並び替えてくれます。この比較する関数がないと、順番が、[9:00,10:00,11:00....9:05,:10:05,11:05]みたいな感じになってしまい、意図する並びで一次元配列が作れません。
解説できるほど理解してない......笑
if(a < b){
return -1
}else{
return 1
}これが元の形、a,bは引数なので、a,bではなくても動くと思います。名前はvalueとかの方が分かりやすいかもしれませんね。
二次元配列の場合は？
if(a[1] < b[1]){
return -1
}else{
return 1
}a[1]とかa[3]みたく配列のindexを指定してあげれば大丈夫です。タスクの締め切りを通知するbotなどで使用しています。
グリッドに着色していくスクリプト
function setBackGroundColor() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('ガントチャート');
const values = sheet.getDataRange().getValues();
console.log('values [i][3] %s indexOfの結果 %s',values[3][3],['09:00','09:05','09:10','09:15','09:20'].indexOf(values[3][3]));
console.log('values [i][4] %s indexOfの結果 %s',values[4][3],['00:00','00:05','00:10','00:15','00:20'].indexOf(values[3][4]));
/*実行時に色を消し、値を削除する*/
const range = spreadsheet.getRange('G3:ET100');
if(range.isBlank() !== true){
range.setBackground(null)
.clear({contentsOnly: true, skipFilteredRows: true});
}
//timeArray(startTime, endTime, minute)
const timeList = timeArray(9, 21, 60);//['０9:00','０9:05'....]のように時刻の配列を生成する
const period = timeArray(0, 10, 60);//['00:00','00:05'....]のように所要時間の配列を生成する。
console.log('timeList %s', timeList);
console.log(typeof timeList);
console.log('period %s',period);
console.log(typeof period);
const columnG = 'G';
const numberColumnG = sheet.getRange(columnG + 1).getColumn();//列
let rowPosition = 3;//行
let offsetNumber = 0;//'00:00がindexOfの０番目になってしまっているため。'
// console.log('G列は、%s 列目です。',numberColumnG);//7
for (let i = 0; i < values.length; i++) {
//所要時間
if(values[i][3] !== '' && i > 1){
let number = timeList.indexOf(values[i][3]);//開始時刻
let startColumn = numberColumnG + number;//開始時刻によって、色を塗る起点が変更になるため
console.log('number %s', number);
console.log('startColumn %s', startColumn);
offsetNumber = period.indexOf(values[i][4]);
console.log('%s 行目 values[i][4] %s offsetNumber %s',i, values[i][4], offsetNumber);
const value = sheet.getRange(rowPosition, 2).getValue();
const value2 = ' 〜 ' + sheet.getRange(rowPosition, 6).getDisplayValue();
/*バーの上に項目名を書き出すための処理*/
const words = value + value2;
sheet.getRange(rowPosition, startColumn)
.setValue(words)
.setFontSize(8)
.setVerticalAlignment('middle');
const person = values[i][2];
const color = detectColor(person);
/*色を塗るための処理*/
sheet.getRange(rowPosition, startColumn, 1, offsetNumber).setBackground(color);
console.log(sheet.getRange(rowPosition, startColumn, 1, offsetNumber).getA1Notation());
rowPosition += 1;
}//if
}//for_i
}//end担当者ごとに塗り分けをしたいなぁ
文字が見やすいように薄めの色を選びました。配色って難しい....5色で限界でした。つまり、現状だと5人までしか担当者ごとの色を設定出来ません。ここは任意で増やして頂ければと思います。
項目詳細のシートに、金田一だったら、薄い青、高遠だったら、オレンジみたいな感じの条件付き書式を設定します。
経験上、一緒くたにすると、必要な事が書いてあるかもしれないけど、残念なシートが出来上がる事が多い.....。そして、今回は、ほとんどの処理をGASで行なっています。
/*詳細項目のシートに担当者の色を、条件付き書式で入力*/
function inChargeColor() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('項目詳細');
const personsList = sheet.getRange(2, 3, sheet.getLastRow()-1, 1).getValues().flat();
/*重複を削除*/
const newArray = personsList.filter(function(value, index, array){ return array.indexOf(value) === index});//重複なしの名前リスト
console.log(newArray);
console.log(newArray.length);
const colorList = ['#c2d3ff','#aef0ff','#ffc3a0','#e1f5c4','#ffebb1'];
let rules = [];
for(let i = 0; i < newArray.length; i++){
if(!newArray[i])continue;
const range = sheet.getRange('D2:D');
const rule = SpreadsheetApp.newConditionalFormatRule()
.setRanges([range])
.whenFormulaSatisfied('=FIND(' +'"' + newArray[i] + '"' + ',C2:C)')
.setBackground(colorList[i])
.build();
rules.push(rule);
}//for_i
sheet.setConditionalFormatRules(rules);
}//end
/*ガントチャートのシートから名前を取得、担当者の色と照合する*/
function detectColor(personName) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('項目詳細');
const columnD = sheet.getRange(1, 3, sheet.getLastRow(), 1).getValues().flat();//indexOfが使えるように、一次元配列化
let index = columnD.indexOf(personName);
index += 1;
const range = sheet.getRange(index, 4);
const color = range.getBackground();
console.log(index);
console.log(range.getA1Notation());//D2
console.log(range.getBackground());//'#c2d3ff'
return color
}
HTMLを書きたいけど、HTMLとGASの連動....
出来たら、良いけどハードルが高いな。これは.....ちょっと骨が折れそう....
B列のアクティブセルを取得して、項目詳細の内容をサイドバーに表示出来ればいいなぁと思っています。出来たら、追記したいと思います。
サンプルファイル
ここから先は
¥ 500
この記事が気に入ったらサポートをしてみませんか？