Breaking Free from the Endless Cycle of Manual Cell Coloring
One Monday morning, the team's small data management bot muttered to itself, "Do we really have to color this manually every time?"
Here's what was happening. Order status data flowing from Windows devices into Google Sheets ("Pending," "In Progress," "Completed") needed to be color-coded. While conditional formatting provided a basic setup, keeping up with real-time status changes meant manually re-specifying ranges each time.
"Can even this small task be automated?" Curiosity sparked the experiment.
Meeting Claude Code: A 5-Minute Miracle
Opening Claude Code, a simple request was submitted.
"Can you create a script that automatically fills background colors in a Google Sheet's specific columns whenever data is added or updated, based on status values?"
The code Claude returned was surprisingly brief, around 20 lines.
function autoColorStatus() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getRange('A2:F').getValues();
const statusIndex = 2; // Status column position
for (let i = 0; i < data.length; i++) {
const status = data[i][statusIndex];
let color = '#ffffff';
if (status === 'Pending') color = '#ffe6e6';
if (status === 'In Progress') color = '#fff4e6';
if (status === 'Completed') color = '#e6f3ff';
sheet.getRange(i + 2, statusIndex + 1).setBackground(color);
}
}
After pasting this code into the spreadsheet's Apps Script editor and setting a trigger to "execute on change," cells literally started coloring themselves.
Step-by-Step Implementation
Step 1: Open the Apps Script Editor
In your Google Sheet, click «Tools» → «Apps Script».
Step 2: Paste the Code
Copy Claude Code's provided function into the editor (adjust column positions, status values, and colors to match your sheet).
Step 3: Set Up a Trigger
In the left menu under «Triggers», create a new trigger.
• Function: «autoColorStatus»
• Deployment: «Head»
• Event Source: «Spreadsheet»
• Event Type: «On change»
Step 4: Authorize and Execute
On first run, Google will request account access permissions. Grant them and you're done.
Three Surprising Discoveries
First, the speed is genuinely impressive.
Even with over 1,000 rows of data, coloring completes within 3 seconds. The sluggishness of conditional formatting disappeared.
Second, team members started asking questions.
"This cell was gray yesterday, why is it red today?" The auto-coloring effect captured everyone's attention. When told "Right, the bot is coloring it itself," faces lit up with excitement.
Third, scaling is remarkably simple.
When status categories later expanded to five types, just adding two more conditional lines to the code handled it.
Mistakes from the First Attempt
• Forgot that column indices start at 0, causing everything to shift one column.
• Didn't know color values must use hexadecimal format (#rgb), resulting in errors.
• Set the range to «A:F» without limits, which colored even empty cells. Precise range definition is essential.
What's Now Possible
Applying this pattern unlocks even more automation.
• Auto-adjust font weight based on priority levels
• Automatically strike through overdue items
• Multi-stage coloring based on amount ranges
• Periodically change background colors by time of day
The Final Realization
We kept searching for "complex automation." But the biggest time savings actually came from automating these "small recurring tasks."
When Google Sheet cells started coloring themselves, the team was using that time for more meaningful work.
That's the true meaning of automation.