You will be able to design a spreadsheet structure that a workflow can write to and a person can read.
Mei Ling runs enquiries for a tuition centre in Tampines. Before she builds the form to spreadsheet to email flow in this module, she needs a sheet that both an automation and a teacher can work from.
Most small business enquiry sheets look alike. There's a header row and a few hundred rows of enquiries. Some rows are highlighted yellow and someone has typed notes in a spare column. "2024 ENQUIRIES" sits in a merged cell across the top, and an "old" tab is still being added to. A person can find their way around a sheet like that, but an automation will choke on it. If you get the structure right now, the next three lessons are much easier.
A record is everything about one submission, from the parent's details to whether anyone has replied. One record is one row. Never split a record across two rows, and never put two records in one row.
Every record needs a unique ID and a timestamp, each in its own column. Lesson 2.2, "Data mapping: passing fields from one step to the next", explained the ID as the thread you use to trace a record through every step of the flow. The form's response ID is a good choice because the form creates it and it never changes. The timestamp records when the enquiry arrived. If you followed lesson 2.3, it is in Singapore time.
Together, the ID and the timestamp let you spot duplicates. If you see the same email address on two rows with timestamps a minute apart, a parent almost certainly clicked submit twice. If you see the same ID on two rows, the flow ran twice on one submission. That is a different problem with a different fix, and Lesson 4.3, "Stop duplicates and missed rows", covers it.
The form fields tell you who asked and what they asked. They don't tell you what has happened since. Status columns record that. The flow fills in some of them and people fill in the rest.
Mei Ling's sheet has 11 columns, in this order: ID, timestamp, parent name, email, phone, child's level, message, confirmation sent, call-back due, called back, outcome. The flow fills in "confirmation sent" with the date, and fills in "call-back due" as two days after the timestamp. Teachers fill in "called back" and "outcome".
Each morning she filters "called back" for empty and "call-back due" for today or earlier. What's left is the day's list of parents to call, so she doesn't need a separate task tracker. When she wants to know how many enquiries enrolled last month, the outcome column gives her the answer.
Keep status columns simple. Give each one a small fixed set of values: yes or no, a date, or a short list such as enrolled, declined, no answer. For the columns people fill in, add dropdowns in the sheet so teachers pick from the list instead of typing their own variations.
Automations read a sheet by its column headers. They expect one header row with the data directly underneath, so anything decorative gets in the way. These are the usual causes of trouble:
merged cells title rows above the header blank spacer rows notes typed into random cells totals at the bottom colours that carry a meaning nobody has written down
These cause real faults. A flow that writes to "the next row" may put new data under a totals line. A lookup may skip half the data because it stopped at a blank row.
The rules are short. Put one header row in row 1, with short, clear names. Put one row per record directly below the header. Don't use merged cells or blank spacer rows. If you want totals, charts or summaries, put them on a separate tab that reads from the data tab, which is the tab holding the raw records. The automation touches only the data tab.
If your old enquiries live in a messy sheet, don't try to clean it in place. Start a fresh sheet for the flow and keep the old one alongside it for reference.
A common failure goes like this. A colleague opens the sheet, renames "Child's level" to "Level", and the flow stops working that night. Depending on the tool, the flow either fails with an error or writes the value into nothing and carries on. Either way, nobody connects the problem to the rename until someone investigates.
Decide now who can edit what. The simplest protection is to lock the header row and the flow-written columns with the sheet's protection settings, so only you as the builder can change them. Teachers keep edit rights on "called back" and "outcome". Then add a short note in the sheet's first tab or its description that lists the columns the flow depends on and asks people not to rename or move them.
Sometimes several people need edit access to the whole sheet. In that case, accept that someone will rename a header eventually, and make sure you will hear about it when they do. Alerts for this are covered in Module 7.
Follow the same order Mei Ling did. Start a fresh sheet if your current one is messy. Put the header row in row 1, then add the ID and timestamp columns, the form field columns, and status columns with small fixed values and dropdowns. Keep summaries on their own tab. Lock the header and the flow-written columns, then leave the note explaining which columns the flow relies on.
You have the diagram from lesson 2.4, Draw your first workflow on paper, with every field your flow passes along. In the activity below you will turn it into a real spreadsheet with an ID column, a timestamp, the form fields and at least two status columns.
Set up your spreadsheet with an ID, timestamp, the form fields and at least two status columns.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).