Moving Data Between Steps: Mapping and Transforms
Map fields from an email into a sheet row, tidy values on the way and keep a visible error list for the records that do not fit.
In this lesson you will
- Map source fields to target columns
- Tidy values with small transforms
- Handle a missing field without crashing
- Avoid date confusion
About 20 minutes
The row that came out wrong
Ravi’s first automation copies each new email into a spreadsheet so he can see all enquiries in one place. On day one the sheet looks like a mess. Sam’s email address is shown in capitals with spaces around it, so the sheet treats it as different from sam@example.com. One subject has two spaces and a gap at the start. The date reads as a long string of numbers and letters. And one row has an empty customer name. Nothing is broken in the platform. The data simply arrived in one shape and the sheet wanted another. Fixing that gap is called mapping, and tidying values on the way is called transforming.
Mapping and transforming
Mapping means saying which source field feeds which target column: the sender’s address goes into the Email column. Transforming means changing the value on the way: lower-casing an address, trimming spaces, turning an ISO date into a short date. Real platforms offer these as little formula or text tools, often called formatters. The idea is always the same: take a value, apply a small function, pass on the result.
Incoming data often has a nested shape, where one field sits inside another, such as the sender’s name inside a ‘from’ block. Platforms usually show this as a path with dots, and the program below reads such paths too.
A field mapper with a safety net
Below, a mapping table lists each target column with a source path and an optional transform after a bar. The loop reads the value, applies the transform and builds a row. If a source field is missing, as the second email has no sender name, the loop does not crash. It leaves the cell empty and writes the problem into an error list, so that a person can see exactly which row and which field failed.
from datetime import datetime
records = [
{"from": {"name": "Sam Lee", "address": " SAM@Example.com "}, "subject": " Quote for flyers ", "date": "2026-11-02T09:15:00"},
{"from": {"address": "ana@example.org"}, "subject": "Where is my order?", "date": "2026-11-02T10:40:00"},
{"from": {"name": "Kim Ray", "address": "kim@example.net"}, "subject": "Invoice 118", "date": "2026-11-03T08:05:00"},
]
transforms = {
"lower": lambda v: v.lower().strip(),
"strip": lambda v: " ".join(v.split()),
"short_date": lambda v: datetime.fromisoformat(v).strftime("%d %b"),
}
mapping = {"customer": "from.name", "email": "from.address|lower",
"subject": "subject|strip", "received": "date|short_date"}
def get(rec, path):
for part in path.split("."):
rec = rec[part]
return rec
rows, errors = [], []
for i, rec in enumerate(records, 1):
row = {}
for target, spec in mapping.items():
path, _, fn = spec.partition("|")
try:
value = get(rec, path)
row[target] = transforms[fn](value) if fn else value
except KeyError as missing:
row[target] = ""
errors.append((i, target, str(missing).strip("'")))
rows.append(row)
print("rows built:", len(rows))
print("errors:", errors)
print("row 1 email:", rows[0]["email"])
print("row 1 subject:", rows[0]["subject"])
print("row 3 received:", rows[2]["received"])
rows built: 3
errors: [(2, 'customer', 'name')]
row 1 email: sam@example.com
row 1 subject: Quote for flyers
row 3 received: 03 Nov
The program built 3 rows and reported one problem: [(2, ‘customer’, ‘name’)]. That means row 2 had no customer name, which is the missing name from the sample data. Row 1’s email came out as sam@example.com, with the capitals and spaces removed, and its subject as Quote for flyers, with the extra spaces squeezed out. Row 3’s date, which started as a long ISO timestamp, became 03 Nov. The safety net matters most. Without it, one odd email would stop the whole run, and every later email would wait in a queue unprocessed.
Habits for clean data
- Trim spaces and lower-case email addresses before comparing them.
- Pick one date style and convert every date to it.
- Decide what to do when a field is missing: skip, fill a default or flag it.
- Keep a column for errors or notes so problems stay visible.
- Test with at least one messy sample, not only a perfect one.
Dates deserve extra care. A date written 03/11 means the third of November in some countries and the eleventh of March in others. If your emails or sheets mix styles, convert to an unambiguous format such as the year, month and day order, then display it however your readers expect.
Assuming every field is always present. Senders leave names blank, forms skip answers and attachments go missing. Decide in advance what your workflow does with an empty value.
The emails, names and dates here are made up, and example.com addresses are reserved for examples. Real platforms offer formatting tools with their own names and options, so look for text and date formatters in yours.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does mapping mean in an automation?
- Drawing a picture of the office
- Sending a message
- Saying which source field feeds which target column
- Deleting old data
Mapping connects a source field to a target field.
What is a transform?
- A new trigger
- A kind of password
- A backup
- A small change applied to a value on the way
Examples are lower-casing, trimming and reformatting dates.
Why did the program write the problem into an error list?
- To stop all runs
- So a person can see which row and field failed
- To hide it
- To change the date
Visible errors let one bad record be fixed without halting the rest.
Why trim spaces and lower-case email addresses?
- To make them longer
- To send them faster
- So the same address always looks the same
- To avoid using a sheet
Otherwise the same person can look like two different people.
What is a safe way to handle a date that could be read two ways?
- Convert to an unambiguous format first
- Guess
- Delete the row
- Use capital letters
A year-month-day format cannot be mistaken for another order.
Summary
- Mapping connects fields; transforming tidies values.
- A missing field should be flagged, not crash the run.
- Trim, lower-case and standardise before comparing.
- Use an unambiguous date format inside the workflow.
Questions
Do I need formulas to transform data?
Many platforms give point-and-click formatters. The lesson shows the logic so that you recognise what those tools do.
What if a field is sometimes empty?
Decide in advance: skip the row, use a default, or flag it for a person. Flagging is the safest choice when you are unsure.
Are those names and emails real?
No. They are invented, and example.com is a domain reserved for examples.
