I need help writing a Google Apps Script function that will automatically handle formatting transfer when I create new worksheets. Here’s what I’m trying to accomplish:
Whenever I add a fresh worksheet to my existing Google Sheets document, I want the script to grab all the conditional formatting rules from column “E” in my first sheet and apply them to the same column in the new worksheet. I don’t need the actual data copied over, just the conditional formatting rules.
I’ve been trying to figure this out but I’m getting stuck on the paste special functionality for conditional formatting only. Has anyone dealt with something similar before? Any code examples would be really helpful.
use ConditionalFormatBuilder - beats copying rules manually. loop through your original rules from column E, recreate them with .copy(), then apply to the new sheet. i’ve been doing this for months with zero problems.
I hit this same issue about six months ago building an automated reporting system. Don’t try to copy-paste - use getConditionalFormatRules() and setConditionalFormatRules() instead. Here’s what worked: First, grab the rules from your source sheet with sourceSheet.getConditionalFormatRules(). Filter them to just the column E rules by checking rule.getRanges(). Then create new rules for your target sheet using SpreadsheetApp.newConditionalFormatRule() and copy the criteria and formatting from each original rule. Key part: update the range references to point to column E in your new sheet. Watch out for range definitions - they won’t always transfer cleanly if your sheets have different dimensions.
You can do this with a trigger function that runs when you add a new sheet. I’ve done something similar for budget spreadsheets where each month gets its own sheet with the same formatting rules. Use an onEdit or onChange trigger to catch when sheets get created, then grab the conditional format rules from your source column with getConditionalFormatRules(). The tricky bit is mapping the ranges correctly - you’ll need to loop through each rule and rebuild the ranges for your new sheet using getA1Notation() and updating the sheet reference. Just make sure you clear any existing conditional formatting on the target column first with clearConditionalFormatRules() before adding the new ones. This avoids conflicts and keeps everything clean.