Edited at
Quad incDay 10

Google スプレッドシートで課題管理シートを簡単に作ってみる一歩目

More than 1 year has passed since last update.

最近Googleスプレッドシートでプロジェクトの課題や運用タスクをチーム内で共有することが増えた。

Excelに比べて共有に便利なんだけど、細かな調整がVBAのようにできていなかったので少し触ってみる。

JSONでフォーマット定義作って読み込めるようにするとちょっと便利かも。


今回の完成イメージ

advent.png


1行目のタイトル設定

function setTitle(){

//1行目にタイトルと背景色を設定
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var iColCnt = 1;
var iRowCnt = 1;

for each(var val in arrTitle){
sheet.getRange(1, iColCnt).setValue(val).setBackgroundColor(bgColor);
iColCnt++;
}

//A列にナンバーを設定
while (iRowCnt <= maxRows) {
sheet.getRange(iRowCnt+1, 1).setValue(iRowCnt);
iRowCnt++
}
};


セルの入力項目の制限

function setValidation(){

//F列のバリデーションを設定
var cellPriority = SpreadsheetApp.getActive().getRange('F2:F'+(maxRows+1));
var rulePriority = SpreadsheetApp.newDataValidation().requireValueInList(arrPriority).build();

//H列のバリデーションを設定
var cellStatus = SpreadsheetApp.getActive().getRange('H2:H'+(maxRows+1));
var ruleStatus = SpreadsheetApp.newDataValidation().requireValueInList(arrStatus).build();

cellPriority.setDataValidation(rulePriority);
cellStatus.setDataValidation(ruleStatus);
};


擬似「条件付き書式」の設定

//他の書き方見つけられなかった。。10-Dec-2015

//onEditだと動きが遅い
function onEdit(e)
{
var setCol = 1; //適用開始列
var setEndCol = 11;
var range = e.source.getActiveRange();

var sheet = e.source.getActiveSheet();
if( sheet.getRange(range.getRow(), 8).getValue() == 'クローズ' ){
sheet.getRange(range.getRow(), setCol, 1, setEndCol).setBackgroundColor('#cccccc');
} else {
sheet.getRange(rowidx, setCol, 1, setEndCol).setBackgroundColor('#ffffff');
}
};


コード全体 setUp関数を実行します(汚くてすんません。。)

//設定項目(あとでJSON化かな)

var maxRows = 100;
var bgColor = "#00CCFF";
var arrTitle = ['#','内容','起票日','起票者','区分','優先度','対応内容','ステータス','対応者','対応予定日','対応日'];
var arrPriority = ['','',''];
var arrStatus = ['起票','確認中','対応中','対応済','クローズ'];

function setUp(){

//タイトル設定
setTitle();

//Cellのバリデーション設定
setValidation();

Browser.msgBox('Done!');

}

function setTitle(){

//1行目にタイトルと背景色を設定
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var iColCnt = 1;
var iRowCnt = 1;

for each(var val in arrTitle){
sheet.getRange(1, iColCnt).setValue(val).setBackgroundColor(bgColor);
iColCnt++;
}

//A列にナンバーを設定
while (iRowCnt <= maxRows) {
sheet.getRange(iRowCnt+1, 1).setValue(iRowCnt);
iRowCnt++
}
};

function setValidation(){

//F列のバリデーションを設定
var cellPriority = SpreadsheetApp.getActive().getRange('F2:F'+(maxRows+1));
var rulePriority = SpreadsheetApp.newDataValidation().requireValueInList(arrPriority).build();

//H列のバリデーションを設定
var cellStatus = SpreadsheetApp.getActive().getRange('H2:H'+(maxRows+1));
var ruleStatus = SpreadsheetApp.newDataValidation().requireValueInList(arrStatus).build();

cellPriority.setDataValidation(rulePriority);
cellStatus.setDataValidation(ruleStatus);
};

//他の書き方見つけられなかった。。10-Dec-2015
//onEditだと動きが遅い
function onEdit(e)
{
var setCol = 1; //適用開始列
var setEndCol = 11;
var range = e.source.getActiveRange();

var sheet = e.source.getActiveSheet();
if( sheet.getRange(range.getRow(), 8).getValue() == 'クローズ' ){
sheet.getRange(range.getRow(), setCol, 1, setEndCol).setBackgroundColor('#cccccc');
} else {
sheet.getRange(rowidx, setCol, 1, setEndCol).setBackgroundColor('#ffffff');
}
};

2時間調べながら触ってみたけど、JavascriptとVBAをがっちゃんこした感じかなと思いました。

次は定義のJSON化とセル幅調整かな。

onEditじゃない「条件付き書式」の方法ご存知の方いらっしゃればご教示下さい。。