Apps Script setBackgrounds() method is only temporary

[Mervyn Thomas]

Gemini has come to the end of this process and admitted defeat.
I want to permanently set the background to ranges in sheets using the  syntax:

rangeToColor.setBackgrounds(colors); The background is only emporary and as soon as manual edits are made to the shee, the background disappears.

[Kildere S Irineu]

That is an incredibly frustrating situation, and I want to assure you we can solve it. The good news is that your conclusion is based on a symptom, but not the root cause.

Let's be very clear: rangeToColor.setBackgrounds(colors) is a permanent command. It is the correct syntax and it behaves exactly like manually selecting a cell and using the "Fill color" bucket.

The fact that the color disappears after a manual edit is conclusive proof that something else is actively overriding your script's changes. The command is working perfectly, but it's losing a fight for priority against another rule or script.

Here are the most likely culprits, in order from most common to least common.

The #1 Suspect: Conditional Formatting

This is almost certainly the cause. Conditional Formatting rules have higher priority than manually set backgrounds. When you edit a cell, the sheet re-evaluates all conditional formatting rules. If a rule applies to that cell, it will overwrite the background color your script set.

How to Diagnose and Fix:

1. In your Google Sheet, click on one of the cells that is losing its color.

2. Go to the menu: Format > Conditional formatting.

3. A sidebar will appear on the right. It will show you any and all conditional formatting rules that apply to the selected cell.

4. If you see a rule there, you have found the problem.

5. Solution: You must either delete this conditional formatting rule or adjust its logic so it doesn't conflict with what your script is trying to do.

Another possibility is the "Alternating colors" feature, which is just a user-friendly type of conditional formatting. Check Format > Alternating colors and remove any rules applied to your range.

The #2 Suspect: Another Script Using an onEdit Trigger

If you (or a colleague) have another script running in the same sheet, it might be using an onEdit(e) trigger. This type of function runs automatically every single time a user edits any cell. If that onEdit script has any logic that formats cells (e.g., re-applying row striping, clearing formatting, etc.), it will run immediately after your manual edit and override the color your first script set.

How to Diagnose and Fix:

1. Go to Extensions > Apps Script.

2. Look through all your .gs files for a function named onEdit(e).

3. Examine the code inside. Does it contain any .setBackground(), .setBackgrounds(), or .clearFormat() commands?

4. Solution: You need to either disable this onEdit script or, more likely, merge the logic from your coloring script into the onEdit script so they work together instead of fighting each other.

---

Proof: A Clean and Permanent Example

To prove that the command works as expected, let's run it in a completely clean environment.

1. Create a brand new, blank Google Sheet.

2. Go to Extensions > Apps Script.

3. Paste this simple code:

/**
 * A simple function to demonstrate that setBackgrounds() is permanent.
 */
function setPermanentColor() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const rangeToColor = sheet.getRange("A1:B5");

  // Create a 2D array of colors that matches the dimensions of the range (5 rows, 2 columns)
  const colors = [
    ['#ff0000', '#00ff00'], // Row 1: red, green
    ['#0000ff', '#ffff00'], // Row 2: blue, yellow
    ['#ff00ff', '#00ffff'], // Row 3: magenta, cyan
    ['#cccccc', '#888888'], // Row 4: light gray, dark gray
    ['#ff9900', '#9900ff']  // Row 5: orange, purple
  ];

  // This command makes a PERMANENT change to the sheet's formatting.
  rangeToColor.setBackgrounds(colors);
}

1. Save the script, select the setPermanentColor function, and click Run.

2. Go back to your sheet. The range A1:B5 will be colored.

3. Now, manually type anything into any of those cells. You will see the background color does not disappear.

This demonstrates that the command is permanent. The problem lies in your existing sheet's environment, where another rule or script is overriding it.

Summary and Plan of Action

1. Check Conditional Formatting first. This is the cause 9 times out of 10. Go to Format > Conditional formatting and clear any rules affecting your target range.

2. If that doesn't work, check for any onEdit(e) triggers in your script files that might be re-formatting the sheet.

3. Rest assured that setBackgrounds() is the correct tool for the job. We just need to find and remove the conflicting rule that is undoing its work.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/89398102-d2a9-4b62-9acd-dbfb15eb39a9n%40googlegroups.com.