At ALT we use Google Sheets as an easy way to share and collaborate on draft event timetables. Recent examples are the ALT Annual Conference 2014 and the OER15 timetables. One of the reasons for publishing draft timetables using Google Sheets is we can get a static url for people to download it as PDFs but the contents can be dynamically updated (see recent post on doing this). The template we use for conferences is continually evolving which isn’t an issue as it’s easy to copy the last version. One headache is that our theme colour usually changes. This can be a bit fiddly change as we use empty cells to create a thicker grid:
Faced with another cell background switch it made sense to actually do this with code rather than clicks and thanks to Google Apps Script possible in 19 lines of code and a couple of minutes:
function colorReplace() { var doc = SpreadsheetApp.getActiveSheet(); // get all the existing active sheet background colours var cells = doc.getRange(1, 1, doc.getLastRow(), doc.getLastColumn()).getBackgrounds(); var rows = cells.length; var cols = cells[0].length; // iterate accross for (var i = 0; i < rows; i++){ for (var j = 0; j < cols; j++){ if (cells[i][j] == '#feeff8'){ // first color to change cells[i][j] = '#f3f3f3'; // first color change to } else if (cells[i][j] == '#bf0875'){ // second color to change cells[i][j] = '#079948'; // second color to change } } } // update backgound colours doc.getRange(1, 1, doc.getLastRow(), doc.getLastColumn()).setBackgrounds(cells); }
Tip
To get the existing cell background colour I used the debugger setting a breakpoint before the loop to see the existing cell colour HEX codes: