A Small Inconvenience in Everyday Work
Yesterday morning, the team's data checking time was getting longer again. We had to manually verify order information in a Google Sheet, color-code rows by status, and hunt for missing data. This repetitive task was eating up 15 minutes a day.
「Could this be automated?」
First Conversation with Claude Code
I opened Claude Code and explained the need simply.
I need a script that reads specific cell values in a Google Sheet and automatically changes the color of entire rows based on conditions. For example, if the 「Status」 column says 「Pending」, color it yellow; if 「Completed」, color it green.
Claude Code immediately suggested an Apps Script solution.
function colorizeByStatus() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getDataRange();
const values = range.getValues();
for (let i = 1; i < values.length; i++) {
const status = values[i][2]; // 「Status」 column (3rd column)
let color;
if (status === "Pending") {
color = "#FFFF00"; // Yellow
} else if (status === "Completed") {
color = "#00FF00"; // Green
} else if (status === "Error") {
color = "#FF0000"; // Red
} else {
continue;
}
sheet.getRange(i + 1, 1, 1, sheet.getLastColumn())
.setBackground(color);
}
}
Execution and Astonishment
I copied the code into Google Sheet's 「Extensions」 > 「Apps Script」 window. Selected the function name and clicked play.
3 seconds later.
The sheet transformed. Some rows turned yellow, others turned green. Colored automatically. I didn't lift a finger.
The most surprising part came when I added a new row. The color appeared instantly. Automation was genuinely «working».
Taking It Further
After tasting the basic automation, I asked Claude Code for more.
「I'd also like to check if data is complete. For instance, if the 「Quantity」 column is empty, could it mark it orange as a warning?」
Claude Code expanded the code. This time, it wasn't just «coloring». It included «validation logic».
function validateAndColorize() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
let bgColor = "#FFFFFF"; // Default white
// Assign color based on status
const status = data[i][2];
if (status === "Pending") bgColor = "#FFFF00";
else if (status === "Completed") bgColor = "#00FF00";
// Flag empty quantity
const quantity = data[i][4];
if (!quantity || quantity === "") {
bgColor = "#FFA500"; // Orange override
}
sheet.getRange(i + 1, 1, 1, sheet.getLastColumn())
.setBackground(bgColor);
}
}
Limitations and Next Steps
What we learned from this process:
1. **Trigger Automation**: Set up Apps Script's «time-based trigger» to run automatically every hour. Now I just add data; the script does the checking.
2. **Debugging Records**: On first try, I mixed up column numbers and colored the wrong column. We worked with Claude Code to double-check the index and fix it.
3. **Color Code Agreement**: Team alignment on what each color means is crucial. The same orange can be interpreted differently by different team members.
40 Minutes, Plus a Day of Gains
The entire process took 40 minutes. Code writing, 10 min; testing, 15 min; trigger setup, 10 min; team briefing, 5 min.
The next day, a team member remarked:
「With colors appearing automatically, data issues pop right out. Our check time is down to 3 minutes.」
Where Claude Code met conditional formatting, a small automation created meaningful convenience.