Intermediate · I1
Formatting and cleaning data
Data that is correct but hard to read gets ignored. In this chapter scripts make a sheet clear in one click: styled headers, proper number formats, colours that flag problems, and a clean-up routine for messy pasted lists. Use the Class Marks sheet from B4.
Style a header row
function styleHeader() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
const lastCol = sheet.getLastColumn();
const header = sheet.getRange(1, 1, 1, lastCol);
header
.setBackground('#1a5fb4')
.setFontColor('#ffffff')
.setFontWeight('bold')
.setHorizontalAlignment('center');
sheet.setFrozenRows(1); // header stays on screen when you scroll
sheet.autoResizeColumns(1, lastCol);
}
Each set… method returns the range, so you can chain them: one statement, four formats. Put
each call on its own line so it reads like a list.
| Method | Example value |
|---|---|
setBackground | '#fff2cc', 'yellow', or null to clear |
setFontColor | '#cc0000' |
setFontWeight | 'bold' or 'normal' |
setFontStyle | 'italic' or 'normal' |
setFontSize / setFontFamily | 12 / 'Sarabun' |
setHorizontalAlignment | 'left', 'center', 'right' |
setWrap | true to wrap long text |
setBorder | (true, true, true, true, null, null): top, left, bottom, right, inside lines |
Every setter has a plural form (setBackgrounds, setFontColors…) that takes a 2D array, one value per cell.
Same value, different look
A number format changes how a value looks, never the value itself. Run this on an empty tab:
function formatNumbers() {
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1:D1').setValues([[1234.5, 0.825, new Date(), 45000]]);
sheet.getRange('A1').setNumberFormat('#,##0.00'); // 1,234.50
sheet.getRange('B1').setNumberFormat('0.0%'); // 82.5%
sheet.getRange('C1').setNumberFormat('dd/mm/yyyy'); // 03/11/2026
sheet.getRange('D1').setNumberFormat('"฿"#,##0'); // ฿45,000
}What the cells show
A1 1,234.50 B1 82.5% C1 03/11/2026 D1 ฿45,000
| Pattern | Means |
|---|---|
0 / # | A digit that always shows / only if needed |
, | Thousands separator |
% | Show as a percentage (multiplies by 100) |
"text" | Literal text, like a currency sign |
dd mm yyyy hh:mm | Day, month, year, hours, minutes |
@ | Plain text: keeps leading zeros in phone numbers and IDs |
Use getDisplayValue() to read the formatted text, and getValue() for the real number.
Highlight rows by their mark
Green for 80 and over, red for under 50, no fill otherwise. Build the colours in memory, then write them in one call:
// Colour each row by its mark: one read, build colour arrays, one write.
function highlightMarks() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
const n = sheet.getLastRow() - 1;
const width = sheet.getLastColumn();
const marks = sheet.getRange(2, 3, n, 1).getValues();
const colours = marks.map(([m]) => {
const colour = m >= 80 ? '#d9f2d9' : m < 50 ? '#fde0dc' : null; // null = no fill
return new Array(width).fill(colour);
});
sheet.getRange(2, 1, n, width).setBackgrounds(colours);
}| A | B | C | |
|---|---|---|---|
| 1 | Name | Room | Mark |
| 2 | Ploy | M.5/3 | 72 |
| 3 | Ben | M.5/1 | 88 |
| 4 | Mint | M.5/3 | 45 |
Script or conditional formatting?
Sheets' own Format → Conditional formatting updates live as values change. A script colours the sheet only when it runs. Use the built-in feature for simple rules, and a script when the rule needs logic a rule can't express, or when you're cleaning a sheet once.
Sorting
Sort the body of the table, never the header. The column number counts from A = 1 across the whole sheet.
function sortExamples() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
const body = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
body.sort({ column: 3, ascending: false }); // highest mark first
// Several keys: by room, then by name.
// body.sort([{ column: 2, ascending: true }, { column: 1, ascending: true }]);
}Shortcut: sheet.sort(3, false) sorts the whole sheet below the frozen rows by column C, descending.
Checkboxes and drop-downs
function addTickColumn() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
const n = sheet.getLastRow() - 1;
sheet.getRange(1, 5).setValue('Handed in?');
sheet.getRange(2, 5, n, 1).insertCheckboxes();
// A drop-down list in column F.
const rule = SpreadsheetApp.newDataValidation()
.requireValueInList(['Present', 'Late', 'Absent'], true)
.setAllowInvalid(false)
.build();
sheet.getRange(1, 6).setValue('Today');
sheet.getRange(2, 6, n, 1).setDataValidation(rule);
}
A ticked checkbox reads as true, unticked as false. The drop-down rule is built with a
builder: start with newDataValidation(), add settings, finish with .build(). You
will meet the same builder pattern again for triggers.
Clean a messy list
Make a tab called Contacts and paste in a list like this, with all its problems:
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | ploy srisuk | [email protected] |
| 3 | BEN CARTER | [email protected] |
| 4 | ||
| 5 | Ploy Srisuk | [email protected] |
// Fixes the usual problems in a pasted list of names and emails:
// extra spaces, random capitals, blank rows and repeated people.
function cleanContacts() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Contacts');
const [header, ...rows] = sheet.getDataRange().getValues();
const seen = new Set();
const cleaned = [];
for (const [name, email] of rows) {
const n = String(name).trim().replace(/\s+/g, ' ');
const e = String(email).trim().toLowerCase();
if (!n && !e) continue; // skip blank rows
if (seen.has(e)) continue; // skip a repeated email
seen.add(e);
const proper = n.toLowerCase().replace(/\b\w/g, (ch) => ch.toUpperCase());
cleaned.push([proper, e]);
}
sheet.clearContents();
const out = [header, ...cleaned];
sheet.getRange(1, 1, out.length, 2).setValues(out);
console.log(`${rows.length} rows in, ${cleaned.length} rows out`);
}Execution log
4 rows in, 2 rows out
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | Ploy Srisuk | [email protected] |
| 3 | Ben Carter | [email protected] |
trim()removes spaces at the ends, andreplace(/\s+/g, ' ')squeezes double spaces inside.- Emails are compared in lower case, so "Ploy@School" and "ploy@school" count as the same person.
- A
Setremembers which emails have been seen.
Test on a copy
A clean-up script rewrites the sheet. Duplicate the tab first (right-click → Duplicate) until you trust it. Apps Script also has quick built-ins for simple cases: range.trimWhitespace() and range.removeDuplicates().
Practice
1. Zebra stripes Easy
Give the header a blue fill and colour every other row light grey, with one setBackgrounds call.
Show a solution
function zebraStripes() {
const sheet = SpreadsheetApp.getActiveSheet();
const rows = sheet.getLastRow();
const cols = sheet.getLastColumn();
const colours = [];
for (let r = 1; r <= rows; r++) {
const c = r === 1 ? '#c6dafc' : r % 2 === 0 ? '#ffffff' : '#f1f3f4';
colours.push(new Array(cols).fill(c));
}
sheet.getRange(1, 1, rows, cols).setBackgrounds(colours);
}2. Flag duplicate emails Medium
Colour an email cell red if the same email (ignoring case) appears more than once in column B. Don't delete anything.
Show a solution
function markDuplicates() {
const sheet = SpreadsheetApp.getActiveSheet();
const n = sheet.getLastRow() - 1;
const emails = sheet.getRange(2, 2, n, 1).getValues().map(([e]) => String(e).toLowerCase().trim());
const counts = {};
emails.forEach((e) => (counts[e] = (counts[e] || 0) + 1));
const colours = emails.map((e) => [counts[e] > 1 ? '#fde0dc' : null]);
sheet.getRange(2, 2, n, 1).setBackgrounds(colours);
}3. Thai phone numbers Challenge
Column B has phone numbers typed every possible way. Make them all 081-234-5678, and leave any that aren't 10 digits unchanged for a person to check.
Show a solution
// Turns "081 234 5678", "0812345678" and "+66812345678" into "081-234-5678".
function cleanPhones() {
const sheet = SpreadsheetApp.getActiveSheet();
const n = sheet.getLastRow() - 1;
const range = sheet.getRange(2, 2, n, 1);
const out = range.getValues().map(([p]) => {
let d = String(p).replace(/\D/g, ''); // digits only
if (d.startsWith('66')) d = '0' + d.slice(2);
if (d.length !== 10) return [p]; // leave odd ones for a human
return [`${d.slice(0, 3)}-${d.slice(3, 6)}-${d.slice(6)}`];
});
range.setNumberFormat('@').setValues(out); // '@' = plain text, keeps the 0
}