Google Drive - Conditional formatting of entire rows

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