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

最近Googleスプレッドシートでプロジェクトの課題や運用タスクをチーム内で共有することが増えた。
Excelに比べて共有に便利なんだけど、細かな調整がVBAのようにできていなかったので少し触ってみる。

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

今回の完成イメージ

１行目のタイトル設定

function setTitle(){

  //１行目にタイトルと背景色を設定
  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(){

  //１行目にタイトルと背景色を設定
  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じゃない「条件付き書式」の方法ご存知の方いらっしゃればご教示下さい。。