Keep cell colors after deleting conditional formatting in Google Sheets

Question

How to get the same formatting as Fixed colors after deleting conditional formatting in Google Sheets? I tried running the built-in filter in Google Sheets and filtering the column based on color, then coloring the cells with the same color resulting from the conditional formatting, and doing this process for each column. In this way, the color of the cells becomes the same as the color of the conditional formatting and remains after deleting the conditional formatting.

But this is tiring when there are many columns on the page. Is there an easier way to convert conditional formatting to solid colors?

Answer 1

I believe your goal is as follows.

• You want to keep the background colors of cells in a sheet even when the conditional formatting rule is removed.

In this case, Google Apps Script is used. So, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet, set your sheet name of the sheet including the conditional formatting rules, and save the script.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  const backgrounds = range.getBackgrounds();
  sheet.clearConditionalFormatRules();
  range.setBackgrounds(backgrounds);
}

When this script is run, all conditional formatting rules in "Sheet1" are removed while the background colors of cells are kept.

References:

• getBackgrounds()
• setBackgrounds(color)
• clearConditionalFormatRules()