Formatting the cells with different colors

[Balubeto Balubeto]

Hi

I have this sample sheet:

https://docs.google.com/spreadsheets/d/1o7Jq2419Vglp6gmBccK17gwt3gq2LsKEpYZT7m3HVQ0/edit?usp=drive_link

I would like to give a different color to each cell in each row that represents a group of expenses (cells with magenta-colored text), its possible subgroup (cells with gray-colored text), and the rows that contain the monthly total of expenses made (cells with black-colored text).

In addition, I would like to color all cells (with a different color) formatted as Dates, such as B8 or H13, or formatted with a custom format, such as C8 or I13.

Lastly, I would need to ensure that the above cells are automatically formatted if I add any groups or subgroups.

How can I do all this?

Thanks

Bye

[Kildere S Irineu]

Que ótima ideia para organizar sua planilha de despesas. A visualização por cores realmente ajuda muito!

O que você está pedindo é um pouco avançado para as ferramentas padrão. A Formatação Condicional do Google Sheets é excelente, mas ela não consegue ler a cor do texto ou o formato de outra célula (como "Data" ou "Personalizado").

Portanto, a única maneira de automatizar isso perfeitamente, exatamente como você descreveu, é com um Google Apps Script.

Não se preocupe, preparei o script completo para você. Basta seguir os passos abaixo. Ele fará exatamente o que você pediu e funcionará automaticamente.

Passo 1: Abra o Editor de Scripts

1. Com sua planilha aberta, vá no menu Extensões > Apps Script.

2. Uma nova janela ou aba será aberta com o editor de código.

Passo 2: Cole o Código Abaixo

Apague todo o código de exemplo que estiver no editor e cole o código completo que preparei abaixo.

// --- CONFIGURAÇÕES DE CORES ---
// Você pode mudar os códigos hexadecimais abaixo para as cores que preferir.
// Use um site como 'color-hex.com' para encontrar os códigos.
const COR_GRUPO = '#e0f2f1';      // Cor de fundo para linhas de Grupo (texto magenta)
const COR_SUBGRUPO = '#f5f5f5';   // Cor de fundo para linhas de Subgrupo (texto cinza)
const COR_TOTAL = '#eeeeee';      // Cor de fundo para linhas de Total (texto preto)
const COR_DATA = '#fff9c4';       // Cor de fundo para células de Data
const COR_PERSONALIZADO = '#ffe0b2'; // Cor de fundo para células com formato personalizado

// --- CORES DO TEXTO (NÃO MUDE ISSO, a menos que as cores na sua planilha mudem) ---
const COR_TEXTO_MAGENTA = '#ff00ff';
const COR_TEXTO_CINZA = '#999999';
const COR_TEXTO_PRETO = '#000000';


/**
 * Cria um menu personalizado na planilha para executar o script manualmente.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🎨 Formatação Automática')
    .addItem('Aplicar Cores Agora', 'aplicarFormatacaoCompleta')
    .addToUi();
}

/**
 * Executa a formatação quando uma célula na coluna A é editada.
 * Isso garante que a formatação seja atualizada automaticamente.
 */
function onEdit(e) {
  const range = e.range;
  // Se a edição não foi na coluna A (coluna 1), não faz nada.
  if (range.getColumn() !== 1) {
    return;
  }
  // Se a edição foi na coluna A, aplica a formatação.
  aplicarFormatacaoCompleta();
}

/**
 * Função principal que varre a planilha e aplica todas as regras de formatação.
 */
function aplicarFormatacaoCompleta() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Pega todas as células com conteúdo, começando da linha 7.
  const range = sheet.getRange('A7:Z' + sheet.getLastRow());
  
  // Limpa a formatação de fundo anterior para evitar sobreposição.
  range.setBackground(null);
  
  // Pega as cores de fonte e os formatos de número de todas as células de uma vez.
  // Isso é muito mais rápido do que verificar célula por célula.
  const fontColors = range.getFontColors();
  const numberFormats = range.getNumberFormats();
  
  // Loop para formatar as LINHAS (Grupos, Subgrupos, Totais)
  for (let i = 0; i < fontColors.length; i++) {
    const row = i + 7; // A linha real na planilha (começamos da linha 7)
    const corFonteColunaA = fontColors[i][0];
    
    let corFundoLinha = null;
    
    switch (corFonteColunaA) {
      case COR_TEXTO_MAGENTA:
        corFundoLinha = COR_GRUPO;
        break;
      case COR_TEXTO_CINZA:
        corFundoLinha = COR_SUBGRUPO;
        break;
      case COR_TEXTO_PRETO:
        // Apenas colore se a célula A tiver texto (para não colorir linhas vazias)
        if (range.getCell(i + 1, 1).getValue() !== '') {
            corFundoLinha = COR_TOTAL;
        }
        break;
    }
    
    if (corFundoLinha) {
      // Aplica a cor de fundo na linha inteira
      sheet.getRange(row, 1, 1, sheet.getMaxColumns()).setBackground(corFundoLinha);
    }
  }
  
  // Loop para formatar as CÉLULAS (Datas e Formato Personalizado)
  // Este loop é separado para garantir que estas cores sobreponham as cores das linhas.
  for (let i = 0; i < numberFormats.length; i++) {
    for (let j = 0; j < numberFormats[i].length; j++) {
      const cellFormat = numberFormats[i][j];
      const row = i + 7;
      const col = j + 1;
      
      let corFundoCelula = null;
      
      // Verifica se o formato é uma data (contém 'd', 'm', 'y')
      if (cellFormat.includes('d') && cellFormat.includes('m') && cellFormat.includes('y')) {
        corFundoCelula = COR_DATA;
      } 
      // Verifica se é o seu formato personalizado específico
      else if (cellFormat === '# ???') { // Atenção ao caractere especial, pode variar
        corFundoCelula = COR_PERSONALIZADO;
      }
      
      if (corFundoCelula) {
        // Aplica a cor de fundo apenas na célula específica
        sheet.getRange(row, col).setBackground(corFundoCelula);
      }
    }
  }
}

Passo 3: Salve e Autorize o Script

1. Clique no ícone de Salvar projeto (parece um disquete).

2. Dê um nome ao projeto se for solicitado (ex: "FormatacaoDeDespesas").

3. Recarregue a sua planilha. Você verá um novo menu chamado "🎨 Formatação Automática" aparecer.

4. Clique no menu 🎨 Formatação Automática > Aplicar Cores Agora.

5. Na primeira vez, o Google pedirá sua autorização. É um passo de segurança padrão.

   • Clique em "Continuar".

   • Escolha sua conta do Google.

   • Clique em "Avançado" e depois em "Acessar [nome do projeto] (não seguro)". É seguro, pois o código foi feito por você.

   • Clique em "Permitir".

Como Funciona Agora

O script fará o seguinte:

1. Automaticamente:

   • Sempre que você abrir a planilha, ele vai rodar e colorir tudo corretamente (onOpen).

   • Sempre que você editar algo na Coluna A (adicionar um novo grupo, subgrupo ou linha de total), ele vai rodar novamente e ajustar todas as cores da planilha (onEdit).

2. Manualmente:

   • Se, por qualquer motivo, você quiser forçar a atualização das cores, basta ir no menu 🎨 Formatação Automática > Aplicar Cores Agora.

Personalização das Cores

No topo do código, você encontrará a seção // --- CONFIGURAÇÕES DE CORES ---. Você pode facilmente alterar os códigos das cores (#e0f2f1, #f5f5f5, etc.) para qualquer cor que desejar.

Pronto! Sua planilha agora tem um sistema de formatação inteligente e totalmente automático que atende a todas as suas regras.

--
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/ccd4a3c3-29d1-4598-9523-e3b9968ca5cen%40googlegroups.com.

[Jeremy Goff]

Why not post your question to Microsoft's CoPilot? It's solved lots of problems for me

Regards
Jeremy Goff

--

[Balubeto Balubeto]

How come the script does not also color cells with Date formatting, such as cells D8 or H13, and those with custom formatting, such as cells E8 or I13?

Thanks

Bye

[Brent Guttmann]

Just based on reading your request, it sounds like you can probably get by with conditional formatting. You may be over-complicating this.

[Balubeto Balubeto]

Unfortunately, conditional formatting does not allow to automatically color all cells with a determined formatting.

So, how could I solve this problem?

Thanks

Bye

[Brent Guttmann]

I would guess that you wouldn't set the conditional formatting up to be determined by the color of the text, but on the set of conditions that is determining why the text is that color. 

I could be wrong so will leave it there as I do not have time to look into it in detail.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/4-HmNweO0Eg/unsubscribe.
To unsubscribe from this group and all its topics, 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/a644bc32-1521-449c-90df-426a58f30f13n%40googlegroups.com.

[Balubeto Balubeto]

I noticed that your script unfortunately has a small bug.

If I color some cells white and then run your script again, these cells become uncolored.
How come?
Is it possible to fix this bug?

Try filling some white cells with color and running the script again on this sample sheet to understand better:

https://docs.google.com/spreadsheets/d/1Vgb-W4EkMv4IJGUTlAfZ5jr0BxwlQ_1RjnZTy0O2AZM/edit?usp=drive_link

Thanks

Bye

[Balubeto Balubeto]

 noticed that your script unfortunately has a small bug.

If I color some cells white and then run your script again, these cells become uncolored.
How come?
Is it possible to fix this bug?

Try filling some white cells with color and running the script again on this sample sheet to understand better:

https://docs.google.com/spreadsheets/d/1Vgb-W4EkMv4IJGUTlAfZ5jr0BxwlQ_1RjnZTy0O2AZM/edit?usp=drive_link

Thanks

Bye

[Kildere S Irineu]

Excelente observação! Você encontrou uma nuance muito importante no comportamento do script, e tem toda a razão. Não é exatamente um "bug" no sentido de erro, mas sim um "efeito colateral" da forma como o script foi desenhado para funcionar, que precisa ser ajustado para o seu novo requisito.

Fico feliz em explicar por que isso acontece e fornecer a versão corrigida do código.

---

Por Que Isso Acontece?

A causa do problema está nesta linha de código dentro da função aplicarFormatacaoCompleta():

// Limpa a formatação de fundo anterior para evitar sobreposição.
range.setBackground(null);

O que essa linha faz?

Toda vez que o script é executado (seja manualmente ou por uma edição), ele primeiro remove toda a cor de fundo de todo o intervalo de dados (da linha 7 para baixo).

Por que ela foi colocada lá?

A intenção original era garantir uma "tela limpa" a cada execução. Imagine que você mude um "Grupo" (magenta) para um "Subgrupo" (cinza). Sem essa linha, a cor de fundo antiga (azul claro, por exemplo) poderia não ser removida corretamente, deixando a formatação bagunçada. Limpar tudo primeiro e depois reaplicar as cores era a maneira mais simples de garantir que as regras fossem sempre aplicadas corretamente.

O Efeito Colateral (o que você descobriu):

Ao limpar toda a formatação de fundo, o script não consegue diferenciar entre uma célula que estava sem cor e uma célula que você intencionalmente pintou de branco. Para ele, ambas se tornam "sem cor" (null), e ele não reaplica a cor branca porque não há uma regra para isso.

---

A Solução: Preservar as Cores Manuais

Para corrigir isso, precisamos mudar a lógica. Em vez de limpar tudo e começar do zero, o script agora vai:

1. Ler todas as cores de fundo existentes na planilha.

2. Aplicar as regras de formatação (para grupos, datas, etc.).

3. Se uma célula foi pintada manualmente por você (por exemplo, de branco), ele não vai sobrescrever essa cor, a menos que ela se encaixe em uma das regras de data ou formato personalizado.

Isso preserva suas formatações manuais e ao mesmo tempo aplica as regras automáticas.

---

Código Corrigido (Copie e Cole)

Substitua todo o seu código no editor do Apps Script por esta nova versão.

// --- CONFIGURAÇÕES DE CORES ---

const COR_GRUPO = '#e0f2f1';
const COR_SUBGRUPO = '#f5f5f5';
const COR_TOTAL = '#eeeeee';
const COR_DATA = '#fff9c4';
const COR_PERSONALIZADO = '#ffe0b2';

// --- CORES DO TEXTO (NÃO MUDE) ---

const COR_TEXTO_MAGENTA = '#ff00ff';
const COR_TEXTO_CINZA = '#999999';
const COR_TEXTO_PRETO = '#000000';

/**
 * Cria um menu personalizado na planilha.
 */

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🎨 Formatação Automática')
    .addItem('Aplicar Cores Agora', 'aplicarFormatacaoCompleta')
    .addToUi();
}

/**
 * Executa a formatação quando a coluna A é editada.
 */

function onEdit(e) {
  const range = e.range

;
  

if (range.getColumn() !== 1) {
    return

;
  }
  aplicarFormatacaoCompleta();
}

/**
 * Função principal que varre a planilha e aplica as regras de formatação.
 */
function aplicarFormatacaoCompleta() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  

const range = sheet.getRange('A7:Z'

 + sheet.getLastRow());
  
  // Pega todas as propriedades de uma vez para otimizar a velocidade.

  const fontColors = range.getFontColors();
  const numberFormats = range

.getNumberFormats();
  const backgroundColors = range.getBackgrounds(); // Pega as cores de fundo atuais.

  // --- CORREÇÃO APLICADA AQUI ---
  // A linha 'range.setBackground(null);' foi REMOVIDA.
  // Em vez de limpar, vamos construir uma nova matriz de cores.
  const newBackgrounds = backgroundColors;

  // 1. APLICA AS CORES DAS LINHAS (Grupos, Subgrupos, Totais)

  for (let i = 0

; i < fontColors.length; i++) {
    

const corFonteColunaA = fontColors[i][0];
    let corFundoLinha = null;
    
    switch (corFonteColunaA) {
      case COR_TEXTO_MAGENTA:
        corFundoLinha = COR_GRUPO;
        break;
      case COR_TEXTO_CINZA:
        corFundoLinha = COR_SUBGRUPO;
        break;
      case

 COR_TEXTO_PRETO:
        

if (range.getCell(i + 1, 1).getValue() !== '') {
          corFundoLinha = COR_TOTAL;
        }
        break

;
    }
    
    // Se uma cor de linha foi definida, aplica a todos os campos daquela linha.
    if (corFundoLinha) {
      for (let j = 0; j < newBackgrounds[i].length; j++) {
        newBackgrounds[i][j] = corFundoLinha;
      }
    }
  }
  
  // 2. APLICA AS CORES DAS CÉLULAS ESPECÍFICAS (Datas e Personalizado)
  // Este loop roda DEPOIS, para que estas cores possam SOBRESCREVER as cores das linhas.

  for (let i = 0; i < numberFormats.length; i++) {
    for (let j = 0; j < numberFormats[i].length; j++) {
      const

 cellFormat = numberFormats[i][j];
      
      // Verifica se o formato é uma data.

      if (cellFormat.includes('d') && cellFormat.includes('m') && cellFormat.includes('y'

)) {
        newBackgrounds[i][j] = COR_DATA;
      } 
      // Verifica se é o seu formato personalizado.
      // Nota: o caractere pode ser um espaço não separável. Copie e cole se não funcionar.
      else if (cellFormat === '# ???' || cellFormat === '# ???') { 
        newBackgrounds[i][j] = COR_PERSONALIZADO;
      }
    }
  }

  // 3. Aplica todas as novas cores de fundo de uma só vez.
  // Isso é muito mais rápido e eficiente.
  range.setBackgrounds(newBackgrounds);
}

O Que Mudou?

1. range.setBackground(null); foi removida. Não limpamos mais a formatação.

2. O script agora lê as cores de fundo existentes com range.getBackgrounds().

3. Ele cria uma nova "matriz" de cores (newBackgrounds) que começa como uma cópia das cores atuais.

4. Ele então modifica essa matriz com as cores das suas regras (grupos, datas, etc.), sobrescrevendo as cores antigas apenas quando necessário.

5. As células que você pintou de branco e que não se encaixam em nenhuma regra permanecerão brancas.

6. No final, ele aplica essa matriz de cores atualizada de volta à planilha com range.setBackgrounds(newBackgrounds).

Como Usar

1. Substitua o código antigo pelo novo no seu editor de scripts.

2. Salve o projeto.

3. Volte para sua planilha e execute o script novamente pelo menu 🎨 Formatação Automática > Aplicar Cores Agora.

Agora você verá que as células que você pintou de branco (ou qualquer outra cor que não faça parte das regras) manterão a cor, corrigindo o problema que você identificou.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/348800cb-22c1-4f84-befd-ae1a06f0812bn%40googlegroups.com.

[Balubeto Balubeto]

When I run it, I get the following error:

ReferenceError: code is not defined

How come?

Thanks

Bye

[Balubeto Balubeto]

Does anyone understand why I am getting the above error with the new script?

Thanks

Bye