やることになった理由
案件の進捗等を管理するためにチーム内でわかりやすいシートを作って欲しい！とのこと。
GAS使わない方が多いのでわからないのですがやることになりました
シートを作る上で欲しい機能
- 案件がいつから始まり、いつ終わるのか。
- WBSのような表示をしたい。
- 今週がどこかを表示したい。
- 終了まで残り数日となった時に一部色を変えたい。
実際に作ってみた。
注意
これより先のコードについては
・正常系前提であること（異常系は都度修正）
・急ぎで作成した（内部用で一旦作成したので、可読性等の配慮がないです）
上記のためコードが雑になっているかもしれません。
シートのデザインについては添付画像のようにしています。
各列の説明
| 列名 | 要素内容 |
|---|---|
| A | 案件名 |
| B | 担当者 |
| C | 進捗 |
| D | リリース判定 |
| E | 進捗矢印 |
| F | 開始日 |
| G | 納期 |
| H | 残日数 |
| I | 開始週（1月1日から何週目か） |
| J | 納期週（1月1日から何週目か） |
※ K列以降は1年を週で分割してそれぞれを表示するセルとして扱っています。
GASに記述するコード
色変更やWBSのような書き方にする処理をまとめたコード
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const activeSheet = sheet.getActiveSheet(); // activeSheetを宣言
const GValues = sheet.getRange('A4:A300').getValues(); //A列に案件名を記入するため、A列を基準とする。
const lastRow = GValues.filter(String).length;
const resetRange = 'K4:BK' + (lastRow + 3); // resetRangeの計算方法を修正
activeSheet.getRange(resetRange).setBackground(null);
for (let i = 4; i <= lastRow + 3; i++) { // ループの条件を修正
const startRange = activeSheet.getRange(i, 9).getValue()-1;
const lastRange = activeSheet.getRange(i, 10).getValue() >= startRange ? activeSheet.getRange(i, 10).getValue() : 53 ;
for (let j = startRange; j < lastRange; j++) {
activeSheet.getRange(i, 11 + j).setBackground('#6aa84f'); //塗りつぶすときの色を指定しています。
}
}
スプレッドシートを開いた時に呼ばれる処理
function onOpen() {
let activeSheet = SpreadsheetApp.getActiveSheet();
var toweek_range = activeSheet.getRange(1,11,1,53);
toweek_range.clear();
var week_num_range = activeSheet.getRange(3,11,1,53);
week_num_range.setBackground(null);
var range_week = parseInt(Utilities.formatDate(new Date(), "GMT", "w")) + 10;
var to_week = activeSheet.getRange(1,range_week,1,1);
var to_week_num = activeSheet.getRange(3,range_week,1,1);
to_week_num.setBackground('red');
to_week.setValue("今週\n▼");
to_week.setFontColor('red');//今週のテキストをお好みの色に変更してください
to_week.setHorizontalAlignment('center');
}
今後について
以下3点を優先的に修正したいと考えている。
- ボタン形式からメニューバーに追加
コードはわかっているので、あとはカスタムして更新する。
//on Openに追加 let ui = SpreadsheetApp.getUi(); let menu = ui.createMenu('チャート生成'); // メニューをUiクラスに追加する menu.addToUi(); - 年度を跨いだときに現状12/31までしかないので、別シートを生成してそこにデータが更新されるようにしたい。 →シート生成するコードがあると思うので、こちら調査中。
- コードが雑になっているのでこちらの修正を行いたい。 →可読性の低いコードで終わらせるのは申し訳ないので、こちらは要修正。