今天用Google Docs做Check List，想對Status欄的Done, In progress, Blocked設不同的background-color，Google Docs竟然有App Scripts可以用，完全是Javascript，超讚的。
下面這個就是我從Google spreadsheets conditional formatting抄來改的。
function colorAll() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var endRow = sheet.getLastRow();
var statusCol = getStatusColumn();
//Browser.msgBox(statusCol);
for (var r = startRow; r <= endRow; r++) {
colorRow(r,statusCol);
}
}
function getStatusColumn() {
var sheet = SpreadsheetApp.getActiveSheet();
var startCol = 1;
var maxCol = sheet.getMaxColumns();
var header = sheet.getRange(1, maxCol);
for ( var col = startCol; col <= maxCol; col++){
var value = sheet.getRange(1, col).getValues();
//Browser.msgBox(value);
if (value == 'Status'){
return col;
}
}
}
function colorRow(r,c){
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange(r, c);
var data = dataRange.getValues();
if(data == "Done"){
dataRange.setBackgroundRGB(0, 255, 0);
}else if(data == "In progress"){
dataRange.setBackgroundRGB(255, 255, 0);
}else if(data == "Blocked"){
dataRange.setBackgroundRGB(255, 153, 0);
}else {
dataRange.setBackgroundRGB(255, 255, 255);
}
SpreadsheetApp.flush();
}
function onEdit(event)
{
var currentCol = event.source.getActiveRange().getColumnIndex();
var statusCol = getStatusColumn();
if (currentCol === statusCol){
colorRow(event.source.getActiveRange().getRowIndex(),statusCol);
}
}
function onOpen(){
colorAll();
}