How to automatically transfer column formatting between Google Sheets using Apps Script

[josephk]

I’m working on a Google Sheets project and need help with automation. Here’s what I’m trying to do:

I have a main workbook with multiple sheets. Whenever I create a new sheet in this workbook, I want to automatically grab all the conditional formatting from column “F” in my first sheet and apply it to the same column in the new sheet.

Basically, I need a script that triggers when a new worksheet gets added. The script should locate the source column with all its formatting rules and copy just the conditional formatting (not the actual data) to the corresponding column in the fresh sheet.

Has anyone done something similar before? I’m not sure how to set up the trigger or which methods to use for copying only the formatting rules without the cell values.

[joec]

Had the same requirement about 6 months ago for a project tracker. Main thing I learned - don’t rely on onChange triggers for new sheet detection. They’re super finicky. I switched to ScriptApp.newTrigger().onFormSubmit() or just used a time-based trigger that checks periodically. Way more reliable. The conditional formatting transfer is easy once you nail the trigger part. Watch out though - some rules use relative references that break when you copy them over. Use getA1Notation() to fix the range references for your target sheet. Pro tip: run clearConditionalFormatRules() on the target column first. Saves you from stacking duplicate rules if your script accidentally runs twice on the same sheet.

[priya.in.pixels]

The Problem:

You need to automatically copy conditional formatting from column F of the first sheet in your Google Sheet workbook to column F of any newly added sheets. Manually copying this formatting is time-consuming, and you’re looking for a solution using Google Apps Script or a simpler alternative.

Understanding the “Why” (The Root Cause):

Manually copying conditional formatting is tedious and prone to errors. The core challenge is triggering a script on the creation of a new sheet and safely copying the formatting rules without overwriting existing data or causing conflicts with relative references within the formatting rules themselves. Using a dedicated automation tool like Latenode (mentioned in the original answer) addresses this by providing a visual, no-code way to handle these complexities, eliminating the need for intricate Apps Script debugging. Apps Script solutions, while powerful, can be complex to implement correctly, requiring careful handling of triggers and potential errors.

Step-by-Step Guide:

Using Latenode (as suggested in the original response), the process is significantly streamlined. While the exact steps depend on Latenode’s interface, the general workflow would be:

1. Connect Latenode to Your Google Sheet: Latenode will provide instructions on how to connect securely to your Google Sheet. This typically involves granting necessary permissions.

2. Create a New Workflow: Use Latenode’s visual workflow builder. The key elements of your workflow should be:

   • Trigger: Set a trigger to monitor for the creation of new sheets in your Google Sheet.
   • Data Source: Define the source sheet and column F as the source of the conditional formatting.
   • Conditional Formatting Extraction: A Latenode function (or node) will extract the complete set of conditional formatting rules from the source column F.
   • New Sheet Detection: A node will identify the newly created sheet.
   • Conditional Formatting Application: Another Latenode function will apply the extracted formatting rules to column F of the newly added sheet.

3. Test and Refine: Run the workflow to test its functionality. Latenode’s visual interface makes it easy to debug and adjust the workflow if necessary. You can easily modify the trigger, add logging, and handle potential errors within the visual workflow.

Common Pitfalls & What to Check Next:

• Latenode Permissions: Ensure Latenode has the correct permissions to access and modify your Google Sheet.
• Workflow Logic: Double-check the order of operations in your Latenode workflow to make sure data is extracted and applied correctly.
• Error Handling: Latenode typically offers built-in error handling and logging. Review these logs to identify and troubleshoot any problems.
• Alternative Solutions: If you prefer to use Google Apps Script, focus on using getConditionalFormatRules() and setConditionalFormatRules(). Carefully handle potential range reference issues and consider using ConditionalFormatRuleBuilder for robust rule recreation.

Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!

[JumpingRabbit]

I dealt with this last year. The cleanest way is using getConditionalFormatRules() and setConditionalFormatRules(). Set up an onOpen trigger or use onChange to catch new sheets. Here’s the process: grab the conditional formatting rules from your source column F with getConditionalFormatRules(), then loop through each rule and apply it to the new sheet’s column F. Watch out for range references when copying - they might have absolute references to the original sheet. I hit a snag where some rules reference specific ranges, so you’ll probably need ConditionalFormatRuleBuilder to recreate rules with updated ranges instead of copying them directly. SpreadsheetApp.newConditionalFormatRule() will be your go-to here. For triggers, I used ScriptApp.newTrigger() with onChange, but you’ll need to set it up manually in the script editor first.