Simple Automation — Spreadsheet Math and Text Cleanup in Code

1.What to automate — how to decide

Automation means letting code do the repetitive work a person used to do by hand. It does not have to be a big program. A few dozen lines that add up per-item totals from a weekly sales record, or that fix the spacing and duplicates in a list gathered from several places, is perfectly good automation.

That said, not every job needs to become code. If you do something once and it takes ten minutes, writing and checking the code may take longer. On the other hand, a ten-minute job done every week adds up to 520 minutes a year, about nine hours. The more a task repeats under the same rules, the larger the volume, and the more often mistakes creep in when done by hand, the more automation is worth.

It also matters whether the rule is clear. "Keep only the digits of a phone number and reshape it as 010-0000-0000" is easy to write as code. But a job that needs judgment, such as "make awkward sentences sound natural," is better reviewed by a person or an AI assistant than by code. The idea from Lesson 1 still applies here: if you can write the rule down clearly in words, you can write it as code.

2.Turning comma-separated text into a table

Data downloaded from a spreadsheet program, or a list someone sends you in a chat, often has one record per line with fields separated by commas. This format is called CSV (Comma-Separated Values). The first line is usually the column names (the header), and the actual records start on the second line.

The steps to turn it into code are simple. First split it into lines with split("\n") from Lesson 7, then split each line into fields with split(","). Next, give each field a name using the objects from Lesson 6, and you have an "array of objects" — a table inside your code. It is a good idea to call trim() first so blank lines at the start or end do not create empty records.

There is one important trap. Every field you get from split is a string. Even if it looks like a number, "10" is a string, so adding it directly joins the text, as we saw in Lesson 2. Convert quantity and price fields with Number() so the math comes out right.

const text = `date,item,qty,price
2026-03-01,pencil,10,500
2026-03-01,notebook,3,2000
2026-03-02,pencil,5,500
2026-03-02,eraser,4,300`;

const lines = text.trim().split("\n");
console.log(lines[0].split(","));

const rows = lines.slice(1).map((line) => {
  const c = line.split(",");
  return {
    date: c[0],
    item: c[1],
    qty: Number(c[2]),
    price: Number(c[3]),
  };
});
console.log(rows.length);
console.log(rows[0]);

[ 'date', 'item', 'qty', 'price' ]
4
{ date: '2026-03-01', item: 'pencil', qty: 10, price: 500 }

const c = "2026-03-01,pencil,10,500".split(",");
console.log(c[2] + c[3]);
console.log(Number(c[2]) * Number(c[3]));
console.log(Number("10pcs"));

10500
5000
NaN

3.Totals, averages and per-group sums

Once you have a table, you get the total with the accumulator pattern from Lesson 4. Set one variable to 0, go through the records one by one, and add. The average is the total divided by the number of records, but if there are zero records you divide by 0 and get NaN, so check that first. If the decimals run long, decide on a rounding place, as in Math.round(x * 10) / 10.

Adding up separately for each group, such as "total per item" or "expenses per department," is called a per-group sum. Create one empty object, and for each record use the group name as the key and add the value. A key you have not seen yet has the value undefined, so the trick is to start from 0 with (sums[key] || 0) + value. It is like doing in a few lines what a spreadsheet's pivot table does.

When the calculation is done, always check one number by hand. In the example below, pencils are 10 × 500 won plus 5 × 500 won, so the total should be 7,500 won. Checking even one group yourself immediately catches mistakes like picking the wrong column or forgetting to convert strings to numbers.

const rows = [
  { item: "pencil", qty: 10, price: 500 },
  { item: "notebook", qty: 3, price: 2000 },
  { item: "pencil", qty: 5, price: 500 },
  { item: "eraser", qty: 4, price: 300 },
];

let total = 0;
let qtySum = 0;
const byItem = {};
for (const r of rows) {
  const amount = r.qty * r.price;
  total += amount;
  qtySum += r.qty;
  byItem[r.item] = (byItem[r.item] || 0) + amount;
}
console.log("Total sales", total);
console.log("Average qty", qtySum / rows.length);
console.log(byItem);

Total sales 14700
Average qty 5.5
{ pencil: 7500, notebook: 6000, eraser: 1200 }

const text = `dept,amount
Sales,32000
Dev,15000
Admin,9000
Sales,18000
Dev,26000`;

const sums = {};
for (const line of text.split("\n").slice(1)) {
  const [team, money] = line.split(",");
  sums[team] = (sums[team] || 0) + Number(money);
}
console.log(sums);

let best = null;
for (const [team, sum] of Object.entries(sums)) {
  if (best === null || sum > best[1]) {
    best = [team, sum];
  }
}
console.log("Top spender:", best[0]);

{ Sales: 50000, Dev: 41000, Admin: 9000 }
Top spender: Sales

4.Cleaning up text — spaces, blank lines, duplicates

A list typed in by several people usually contains leading and trailing spaces, double spaces, blank lines, and the same person entered twice. They may look identical to you, but to a computer "Alex" and "Alex " are different strings, so without cleanup, removing duplicates and searching do not work properly.

The cleanup rule usually has three steps. Remove leading and trailing spaces with trim(), shrink runs of spaces to a single space with replace(/\s+/g, " "), and drop empty strings with filter. \s+ is a regular expression meaning "one or more whitespace characters," which you got a taste of in Lesson 7. If regular expressions confuse you, put a sample sentence into a regex tester first and see what gets matched.

For removing duplicates, Set is handy. A Set holds each value only once, so spreading it back into an array with [...new Set(array)] drops duplicates while keeping the order in which values first appeared. For values usually treated as case-insensitive, such as email addresses, normalize them with toLowerCase() before removing duplicates. Printing the count before and after cleanup shows at a glance how many records were dropped.

const raw = [
  "  Alex ",
  "Mary  Ann",
  "Alex",
  "",
  "Jay   ",
  "Mary Ann",
];
const cleaned = raw
  .map((s) => s.trim().replace(/\s+/g, " "))
  .filter((s) => s !== "");
console.log(cleaned);

const unique = [...new Set(cleaned)];
console.log(unique);
console.log(raw.length, "→", unique.length);

[ 'Alex', 'Mary Ann', 'Alex', 'Jay', 'Mary Ann' ]
[ 'Alex', 'Mary Ann', 'Jay' ]
6 → 3

const emails = ["[email protected]", "[email protected] "];
const norm = emails.map((e) => e.trim().toLowerCase());
console.log([...new Set(norm)]);

[ '[email protected]' ]

5.Unifying phone number and date formats

The same phone number can arrive in many shapes, such as 010-1234-5678, 01012345678 or 010 1234 5678. (The examples use the Korean mobile format: 11 digits starting with 01.) The sturdiest way to unify the format is "keep only the digits, then reassemble." replace(/\D/g, "") removes every character that is not a digit. If 11 digits remain and they start with 01, cut them 3-4-4 and insert hyphens.

For values that do not fit the rule, do not force a fix; it is better to return something like null meaning "could not process." That way you can collect just those lines later for a person to check. If automation turns an unknown value into something plausible, wrong data quietly mixes in, and that is harder to find than a mistake made by hand.

Dates come in all sorts too, such as 2026.3.5., 2026/03/05 or 2026-3-15 . Remove spaces and the trailing dot, split on any of ., / or -, then pad the month and day to two digits with padStart(2, "0") to get the 2026-03-05 shape. This shape is convenient because sorting it as text also puts the dates in order. The function below only checks month 1–12 and day 1–31, so it cannot catch a date like February 30. Leave limitations like this in a comment.

function formatPhone(s) {
  const d = s.replace(/\D/g, "");
  if (d.length !== 11 || !d.startsWith("01")) {
    return null;
  }
  return d.slice(0, 3) + "-" + d.slice(3, 7) +
    "-" + d.slice(7);
}

const inputs = [
  "010-1234-5678",
  "01012345678",
  "010 1234 5678",
  "(010)1234.5678",
  "1234-5678",
];
for (const p of inputs) {
  console.log(p, "→", formatPhone(p));
}

010-1234-5678 → 010-1234-5678
01012345678 → 010-1234-5678
010 1234 5678 → 010-1234-5678
(010)1234.5678 → 010-1234-5678
1234-5678 → null

// checks only month 1-12 and day 1-31 (Feb 30 slips through)
function formatDate(s) {
  const t = s.replace(/\s/g, "").replace(/\.$/, "");
  const parts = t.split(/[.\/-]/).map(Number);
  if (parts.length !== 3) return null;
  const y = parts[0], m = parts[1], d = parts[2];
  if (!Number.isInteger(y) || y < 1000) return null;
  if (!(m >= 1 && m <= 12 && d >= 1 && d <= 31)) {
    return null;
  }
  const mm = String(m).padStart(2, "0");
  const dd = String(d).padStart(2, "0");
  return y + "-" + mm + "-" + dd;
}

const dates = [
  "2026.3.5.",
  "2026/03/05",
  " 2026-3-15 ",
  "2026. 12. 1",
  "2026.13.1",
  "3/5/2026",
];
for (const x of dates) {
  console.log(JSON.stringify(x), "→", formatDate(x));
}

"2026.3.5." → 2026-03-05
"2026/03/05" → 2026-03-05
" 2026-3-15 " → 2026-03-15
"2026. 12. 1" → 2026-12-01
"2026.13.1" → null
"3/5/2026" → null

6.Protecting the original and checking the results

The worst accident in automation is overwriting the original so you cannot go back. Make it a rule that code only reads the original and writes results to a new variable or a new file. If you work with files, copy the original somewhere safe before you start, and add a date or "cleaned" to the result file's name to tell them apart. As we saw in Lesson 6, map and filter create new arrays and leave the original array untouched, so they fit this rule well.

Check the results in three ways. First, a count check: print the number of original records, result records and filtered-out records, and see whether they add up. Second, a sample check: pick a few lines from the result and compare them with the original by hand, especially the very first, the very last and any that look odd. Third, a total check: see whether the total from a spreadsheet program matches the total from your code. If you put the text before and after cleanup into a text diff checker, you can see only the changed parts at a glance.

With real CSV files, the simple split(",") in this lesson sometimes breaks. There is a rule that a field containing a comma is wrapped in double quotes, so a single field written as "pencil, blue" gets cut into two. There are also rules for line breaks inside a field and quotes inside quotes. So when you handle real files, open and process them in a spreadsheet program, or use a well-tested library (a parser) that implements the CSV rules properly. This lesson's approach suits simple text whose format you defined yourself.

const line = '2026-03-03,"pencil, blue",2,500';
const cells = line.split(",");
console.log(cells.length);
console.log(cells);

5
[ '2026-03-03', '"pencil', ' blue"', '2', '500' ]

const text = "name,amount\r\nAlex,3000\r\n\r\nSam,abc\r\n";
const lines = text.split(/\r?\n/).slice(1);
const ok = [];
const bad = [];
for (const line of lines) {
  if (line.trim() === "") continue;
  const c = line.split(",");
  const n = Number(c[1]);
  if (c.length === 2 && Number.isFinite(n)) {
    ok.push({ name: c[0], amount: n });
  } else {
    bad.push(line);
  }
}
console.log("Processed", ok.length, "On hold", bad.length);
console.log(bad);

Processed 1 On hold 1
[ 'Sam,abc' ]

• Only read the original; write results to a new variable or new file
• Check that original count = processed count + on-hold count
• Pick the first, last and odd-looking lines and compare them by hand
• Double-check one total another way (a calculator or a spreadsheet program)
• Handle real CSV files with a spreadsheet program or a well-tested CSV parser

7.Building automation with an AI assistant

Automation code is a good job to hand to an AI coding assistant. To get good results, though, you need to state the input shape, the output you want and the rules for exceptions clearly. "Clean up my CSV" gets far less accurate code than "a function that takes text with the header dept,amount and returns an object of totals per department, and collects lines whose amount is not a number separately."

When you show data to an AI, do not paste in a real customer list or contact details; show just a few lines of fake data in the same shape. Knowing the format is enough to write the code. Run the code you receive on the real data on your own computer, and do the same count, sample and total checks described above.

How to read and test the code you receive is covered in detail in the next lesson, Lesson 10. If you have solved this lesson's exercises yourself, you will spot places in AI-written code where the Number conversion is missing or blank lines are not handled much more easily.