todo.gs

function getSheet() {
  return SpreadsheetApp.getActiveSheet();
}

function getColumnByName(sheet, string) {
  var lastCol = sheet.getLastColumn();  
  var range = sheet.getRange(1, 1, 1, lastCol)  
  var values = range.getValues()[0];  

  for (i=0; i<lastCol; i++) {
    if (values[i] == string) {
      return i + 1;
    }
  }
  throw new Error("column not found");
}

function onEdit(event) {
  var sheet = getSheet();

  var col_base = event.range.getColumn();
  var row_base = event.range.getRow();
  
  for(var colnum=0; colnum<event.range.getNumColumns(); colnum++) {
    for(var rownum=0; rownum<event.range.getNumRows(); rownum++) {
      
      var col = col_base + colnum;
      var row = row_base + rownum;
      // Logger.log("cell: "+col+":"+row );

      if(row==1) continue;
      
      col_done_date = getColumnByName(sheet, "完了日");
      col_gen_date = getColumnByName(sheet, "作成日時");
      col_done = getColumnByName(sheet, "進捗");
      col_item = getColumnByName(sheet, "タスク");
      // Logger.log(event.value);
      
      if (col==col_done) {
        cell_done_date = sheet.getRange(row, col_done_date)
        if(event.value=="TRUE") {
          cell_done_date.setValue(Utilities.formatDate(new Date(), 'Asia/Tokyo', "yyyy/MM/dd HH:mm:ss"));
        } else if (event.value=="FALSE") {
          cell_done_date.clear();
        }
      }
      
      
      if (col==col_item) {
        cell_gen_date = sheet.getRange(row, col_gen_date);
        cell_item = sheet.getRange(row, col_item);
        if (""==cell_item.getValue()) {
          cell_gen_date.clear();
        }
        else if (""==cell_gen_date.getValue()) {
          cell_gen_date.setValue(Utilities.formatDate(new Date(), 'Asia/Tokyo', "yyyy/MM/dd HH:mm:ss"));
        }
      }
    }
  }
}