In order to keep my spreadsheet organized for scheduling purposes, I have implemented a script that changes the font style of rows based on the date. If the date has already passed, the font is changed to italics, greyed out, and with a line-through effect. While the script works well, it unfortunately overrides all formulas in the spreadsheet when applied. Is there a way to modify the formula so that it does not impact specific cells? For example, I do not want it to affect rows that are designated for dates in the future.
Below is the script I am currently using:
function formatOnDate() {
var sh = SpreadsheetApp.getActive().getActiveSheet();
var range = sh.getDataRange();
var data = range.getValues();
var color = '#AAA';// value you want
var style = 'italic';// value you want
var line = 'line-through';// value you want
var fontColors = range.getFontColors();// get all font colors
var fontLines = range.getFontLines();// lines
var fontStyles = range.getFontStyles();//style
var today = new Date();// include today in sheet
//var today = new Date(new Date().setDate(new Date().getDate()-1));// exclude today... uncomment the one you use
for(var n=1 ; n<data.length ; n++){ // start on row 2 so that headers are not changed
if(data[n][0] < today){
for(var c in data[0]){
fontColors[n][c]=color;//set format
fontLines[n][c]=line;//set format
fontStyles[n][c]=style;//set format
}
}
}
//sh.getRange(1,1,data.length,data[0].length).clear();
// now update sheet with new data and style
sh.getRange(1,1,data.length,data[0].length).setValues(data).setFontColors(fontColors).setFontLines(fontLines).setFontStyles(fontStyles);
}
Is there a way to adjust the font styles without interfering with cell formulas? Can we ensure that only past dates are affected, leaving future dates untouched? Alternatively, is there a method to exclude certain columns from this formatting? (Columns N-Q contain formulas)