I was searching for a possibility to format entire rows or defined cells in condition to a formula or content of another cell. This is a really handy functionality of other spreadsheet programs. So I thought it should also be possible in Google Drive … yeah, but only with a selfwritten function. Here [1] I found the first hints and with the API [2] I finished mine:
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Issues'); // Change to your own
var rows = sheet.getRange('a1:c');
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var i = 0; i <= numRows - 1; i++) {
var n = i + 1;
if (values[i][2] == 'finished') {
sheet.getRange(n + ':' + n).setBackgroundColor('white');
sheet.getRange(n + ':' + n).setFontColor('lightgrey');
}
else if (values[i][2] == 'new') {
sheet.getRange(n + ':' + n).getCell(1,2).setBackgroundColor('yellow');
sheet.getRange(n + ':' + n).setFontColor('black');
}
else if (values[i][2] == 'workingOn') {
sheet.getRange(n + ':' + n).getCell(1,2).setBackgroundColor('red');
sheet.getRange(n + ':' + n).setFontColor('black');
}
else {
sheet.getRange(n + ':' + n).setBackgroundColor('white');
sheet.getRange(n + ':' + n).setFontColor('black');
}
}
};
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Issues'); // Change to your own
var sortRange = sheet.getRange('a2:c');
sortRange.sort([{column: 1, ascending: true}, {column: 3, ascending: true}]);
}
Hope it helps you too!
[1] http://productforums.google.com/forum/#!topic/docs/t6xPcOkjNbQ/discussion
[2] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet