I am working on a template sheet that I can quickly customise before sending out. One of these customisations is changing the heading and subheading cell background colours across multiple tabs in a sheet. IE: on my copy of the sheet these headings are a light and dark yellow, I'd like to quickly change them to a light/dark blue etc without having to individually change each cell manually.
Ideally I would like a script to search current cell hex codes across the whole sheet, and if it encounters the current colour, change it to a new colour hex code.
I cant use conditional formatting for this as the text in these headers will differ, as well as their location on each tab.
The following is an adaptation of the Martin Hawksey solution from 2015.
I've used getMaxRows and getMaxColumns (as opposed to getLastRow/getLastColumn' or even getDataRange`) to define the range because it isn't clear whether there might be cells that don't yet contain data but which have had their background changed.
Though this script doesn't use the more advanced methods such as MAP, it is still very fast.
function colorReplace() {
let ss = SpreadsheetApp.getActiveSpreadsheet()
let sheets = ss.getSheets()
// iterate across sheets, row
for (let s = 0;s<sheets.length;s++){
var sheet = sheets[s]
var cells = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).getBackgrounds();
var rows = cells.length;
var cols = cells[0].length;
for (var i = 0; i < rows; i++){
for (var j = 0; j < cols; j++){
if (cells[i][j] == '#fff2cc'){ // first color to change
cells[i][j] = '#cfe2f3'; // first color change to
} else if (cells[i][j] == '#f1c232'){ // second color to change
cells[i][j] = '#3d85c6'; // second color to change
}
}
}
// update backgound colours
sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).setBackgrounds(cells);
Logger.log("sheet#="+s+", name: "+sheet.getName())
}
}