The Loop: Moving Form Responses Into a Tracker

Welcome to The Loop. Every Wednesday we take one real, repetitive workflow that someone does by hand and unwind it into something you can run. This week it's the quietest one on the list: the form collects responses beautifully, and then a person opens two tabs and moves them across, field by field, because the sheet the form fills is not the sheet the team works from.

Here's the reframe. Retyping feels like the price of having two tools instead of one, so it gets treated as unavoidable overhead. Watch yourself do it for a week and something plainer shows up. You always skip the rows you already carried. You always turn what somebody typed into the box into one of your five categories. You always assign the same kind of request to the same person. You always set the new row to Not started with a follow-up date a week out. Those aren't chores. Those are rules, and you have been running them from memory because nobody ever asked you to write them down. Write them down once and they become a spec. Describing replaces doing.

So let's watch the carrying loop run at full speed, price it honestly, and turn it into something that fills the tracker before you have opened the tab.

The Loop, in Full

You can't automate a loop you haven't watched closely, so here it is start to finish. It's Monday, eight forty in the morning, at a trade association with fourteen staff and about nine hundred member companies.

Nadia runs member services, which at that size means she owns the front door. The front door is a form: a membership application with eleven questions, linked from three places on the website. Over the weekend it collected thirty-one submissions. She opens the response sheet, which is a perfect, immutable record of what thirty-one people typed, sorted oldest first, with column headers that are the full text of her own questions. Then she opens the tracker, which is where her team lives all week: a board with columns for status, owner, member tier, renewal date, and the note field that everybody actually reads. Nothing in the response sheet maps cleanly onto anything in the tracker. So she starts carrying. Company name across. Contact name across. The free-text answer to "what are you hoping to get out of membership" gets read, then squashed into one of five tags, because "we want to stop guessing about regulation" is Advocacy and "we need somewhere to send new hires" is Training. Two of the thirty-one are the same firm applying twice, four days apart, with slightly different contact people. One email address has a space in it. She assigns an owner based on region, sets each row to Not started, sets a follow-up date, and pastes the original answer into the note field so the owner has the actual words. It's nine fifty. Thirty-one rows exist. Nothing has been decided about any of them.

Written out as steps, the by-hand version looks like this:

1. Open the response sheet and find the submissions that arrived since the last pass.
2. Check each one against the tracker so nothing gets carried twice and nothing gets carried never.
3. Map each answer to the field it belongs in, renaming as you go, because the form asked a question and the tracker wants a value.
4. Turn the free-text answers into the categories your team filters on, and clean up whatever a human typed at eleven at night.
5. Set the fields the form never asked about: status, owner, follow-up date, then log that the row has been carried.

Nothing there is hard. That's the tell. Four of those five steps are pure mechanics, and the fifth is a judgment you make the same way every time. This is the exact shape of work that swells to fill a Monday, and it's what it looks like to automate the data transfer between the tools you already use instead of paying a person to be the bridge between them.

The Manual Tax

The cost of a loop is never the clock alone. It's the minutes, plus the mistakes that only appear when a human is the copy mechanism, plus the days a submission sits in an archive that nobody works from. Add those together and you get the manual tax.

The underlying problem is not niche. In Zapier's survey of 1,000 US knowledge workers, the single most dreaded task named was data entry, defined exactly as moving information from one piece of software to another, and the mindless tasks in that family were reported as taking up an average of 17.3 hours a week. Nearly half the work week, spent being the connective tissue between two applications that could talk to each other directly.

Then there are the two costs hiding underneath the minutes:

• Errors. Not dramatic ones. Quiet ones. The email address that picked up a trailing space and now bounces every automated message you will ever send that company. The duplicate row that turns into two owners calling the same person on Thursday. The category that got tagged Training on a tired Monday and Advocacy on a fresh one, which means every report your team builds on that column is subtly wrong and nobody can tell.
• Latency. This is the expensive one. Somebody applied on Friday afternoon, at the exact moment they cared most, and the first human response reaches them on Tuesday because Monday was spent carrying rows. Interest has a half-life. That gap is the same reason teams eventually automate the feedback that arrives faster than you can read it rather than promising to get to it sooner.

A response sheet is a place where good intentions go to wait. Every hour a submission spends in one is an hour the person who sent it spends deciding you weren't that interested.

Which reframes the goal. You aren't trying to save the seventy minutes, though you'll save them. You're trying to make sure nothing ever waits three days because the only connection between your form and your team was a person with a full calendar.

Unwinding the Loop

Automating starts with description, not code. Describe the loop tightly enough that something else could run it unsupervised and most of the work is done before anyone opens an editor. The mechanics here are close to nothing: read some rows, write some rows. The decisions are the whole game, and this loop has two good ones: what makes two submissions the same submission, and how you turn a sentence somebody typed into a value your team can filter on.

Here's the same loop written as a spec. Read the right-hand column and notice how much of it is judgment you have already made a hundred times and never said out loud:

That table is the automation. Everything after this is choosing how much of it runs without you in the room. If you would rather build the spec as a conversation than stare at an empty table, that's how BYOBot turns a task into a spec, one question at a time.

The Build: One Loop, Three Ways to Run It

One described loop becomes three builds. The only thing that changes between them is how much runs while you're asleep. Start at the top and move down when the top stops being enough.

The Prompt

The prompt build is zero setup: one saved prompt you paste into any AI chat tool along with the new rows, copied straight out of the response sheet. It won't write to your tracker. It will hand you a clean block you can paste in, already categorized, already deduplicated, already carrying the fields the form never asked for, which on a Monday morning is most of what you wanted.

You are turning raw form responses into tracker rows.
Today's date is [DATE].

For every response below, output one tracker row with these columns:
Submission ID | Company | Contact | Email | Category | Owner | Status | Follow up | Note

Rules:
- Category is exactly one of: Advocacy, Training, Networking, Standards, Other.
  Choose from the open answer. If it fits none of them cleanly, use Other
  and start the Note with NEEDS REVIEW.
- Owner by region: West -> Sam. East -> Priya. Everything else -> Nadia.
- Status is always "Not started".
- Follow up is today plus 7 days, as YYYY-MM-DD.
- Note is the person's own words, trimmed, never paraphrased.
- Clean emails and names: strip spaces, fix obvious casing. Never invent a
  missing value. Leave it blank and say so in the Note.

Then, separately, list:
- DUPLICATES: any two rows sharing an email address, with both submission IDs.
- BLANKS: any row missing Company, Contact, or Email.

RESPONSES:
[paste the new rows from your response sheet, headers included]

That runs the same in ChatGPT, Claude, or Gemini, because a prompt was never locked to one vendor. Run it for two weeks and you'll notice the model is doing something you could describe exactly: the same five categories, the same routing rule, the same seven-day follow-up. Noticing that is the moment you're ready to automate the spreadsheet work you repeat every Monday rather than reformatting it by hand each week.

The Script

Now the honest part. This build barely needs AI at runtime. Fetching rows added since the last run is an API call. Copying an answer into a field is assignment. Checking whether a submission ID already exists is a lookup. Setting status to Not started is a constant. Put a language model in the middle of that and you have added latency, cost, and a small chance of invention to a job that plain code does perfectly on every single run, forever.

So the script is code at both ends. It reads new submissions from wherever the form lives: the Google Forms API if it's a Google Form, or the responses endpoint documented by Typeform if it isn't. Then it writes rows to wherever the team works: a base through the Airtable Web API, a sheet through the Google Sheets API, or a database in Notion. One model call sits in the middle, and it has a precise job.

// pseudo-shape of the script BYOBot generates for you
const responses = await form.listResponses({ since: lastRunAt });
const seen      = await tracker.idIndex();          // submission IDs already carried

for (const r of responses) {
  if (seen.has(r.id)) continue;                     // hard key, no guessing

  const recent = await tracker.findByEmail(r.email, { withinDays: 7 });
  if (recent) { await tracker.appendNote(recent, r); continue; }

  const { category, confident } = await classify(r.openAnswer);  // the one model call

  await tracker.create({
    submissionId: r.id,
    company:  clean(r.company),
    contact:  clean(r.contact),
    email:    clean(r.email),
    category,
    owner:    routeByRegion(r.region),              // plain lookup table
    status:   'Not started',
    followUp: addDays(today, 7),
    note:     confident ? r.openAnswer : 'NEEDS REVIEW: ' + r.openAnswer
  });
}

There's exactly one place a model earns its seat, and it deserves a precise name: classify. Deciding that "we keep losing track of who signed what" belongs in the Standards bucket is a language judgment, not a string match, and that one call is worth making. Everything around it stays code. You don't start from an empty file either. BYOBot writes the full version: the file to create, the tokens to paste, the field mapping wired to your own column names, and the same plumbing that lets you automate the Airtable base your team works from without touching it by hand.

The Schedule

The schedule is the smallest leap left, and this loop has an unusually good version of it. A timer is fine: a cron job costs nothing, and a GitHub Actions scheduled workflow costs nothing either. But forms can do better than a timer, because they know the exact moment a submission arrives. A form-submit trigger, the kind documented in Google's Apps Script triggers guide, fires your job on arrival instead of on a clock, and the row appears in the tracker before the person who filled the form has closed the tab. Keep a nightly sweep anyway as a safety net, because triggers occasionally miss and a sweep that finds nothing costs you nothing. Add one more line while you're there: a short daily digest of what got carried and what got flagged, which is the same habit that makes it worth learning to automate the records that move between two systems on a schedule.

Here's how the three stack up, so you can pick your stopping point:

Steal This Build

Here's the whole loop as a spec you can hand to BYOBot or build yourself this afternoon. Copy it, swap in your own categories and your own routing rule, and it's your build. If you want the wider version that covers what happens to those records after they land, our guide on automating customer data workflows picks up where this leaves off.

• Trigger: a new submission arrives, with a nightly sweep for anything the trigger missed.
• Dedupe: submission ID is the hard key, email inside seven days is the soft one, and anything ambiguous gets flagged rather than guessed.
• Map: every question to its destination field, cleaned but never invented.
• Classify: the free text into exactly one category, with Needs review as an honest answer.
• Create: one tracker row carrying status, owner, follow-up date, and the person's own words, and never edit the response sheet.

Membership applications are the example, but look at the shape underneath: something structured arrives, and somebody hand-carries it into the place where work actually happens. Job applications into a hiring board. Support forms into a queue. Event signups into a run sheet. Expense claims into a finance sheet. Contractor onboarding questionnaires into a compliance list. It's the same loop with different nouns every time, which is why it pays to learn once and then automate the data entry that arrives in batches with the build you already have.

Frequently Asked Questions

• Why not just work directly out of the form's response sheet? +
  Because a response sheet and a tracker are two different objects doing two different jobs. A response row is a record of what somebody typed at a moment in time, and it should never change again. A tracker row is a live thing with a status, an owner, and a next action, and it changes every day until the work is finished. The moment you start editing response rows you have destroyed your own audit trail, and the moment you add status columns to the response sheet the form pushes new rows in and knocks your columns out of alignment. Keep the archive clean, build the tracker beside it, and let a job carry rows across, the same way you would automate the Notion database your team plans in.
• Does moving form responses into a tracker actually need AI? +
  Mostly no, and it is worth being blunt about that. Reading new rows, copying fields, setting a default status, and stamping a submission ID are all plain code that will be correct on every single run. There is exactly one place a model earns its seat: turning what somebody typed into a free-text box into one of the categories your team actually works from. Deciding that "we keep losing track of who signed what" belongs in the Contracts bucket is a language judgment. Everything wrapped around that judgment should stay boring and deterministic.
• How do I stop duplicate rows when the same person submits the form twice? +
  Pick a key and enforce it in the tracker, not in your memory. Most form tools give every submission a stable response ID, so write that ID into a column on the tracker row and refuse to create a second row for an ID you have already seen. That handles reruns and retries. The other duplicate is human: the same person filling out the form twice in an hour because they forgot they had. Catch that with a second check on email address plus a time window, then attach the newer submission to the existing row as an update rather than opening a rival one. Deciding which submission wins is a rule you write once, and our walkthrough of how to write a workflow spec covers how to pin down rules like that before you build.
• What if the form and the tracker are different tools entirely? +
  That is the normal case and it changes nothing structural. A form in Typeform feeding a base in Airtable, a Google Form feeding a Notion database, an embedded intake form feeding a support queue: it is the same three moves every time. Read the new submissions, map each field to the destination's field, write a row with the state your team needs. The only thing that varies is which API you call at each end, and the mapping table you wrote in the spec is what makes swapping either end a ten-minute change instead of a rebuild. That portability is the whole reason to automate the form responses your team collects at the spec level rather than wiring two specific tools together and hoping neither one changes.