今天用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(); }