In short: A data pipeline is three steps. Extract data from a source, transform it into something usable, load it into a destination. Google Sheets and Apps Script build the first pipeline for most small businesses without a paid tool. The pattern scales from a spreadsheet to Netflix's recommendations engine using the exact same three-step logic.
Key Takeaways
- Every data pipeline follows the same three-step ETL pattern regardless of scale
- Python holds the number one spot on the TIOBE index in 2026 with 18.96% share
- The global marketing automation market hits $15.58 billion by 2030
- Marianne Gatlabayan uses Google Sheets and Apps Script as her default first pipeline
- Quality checks prevent bad data from silently ruining downstream reports
Why every founder eventually needs a data pipeline
Python holds the number one spot on the TIOBE index in 2026 with 18.96% of programming search share. Its dominance comes almost entirely from data work. The global marketing automation market alone reached $6.65 billion in 2024 and will hit $15.58 billion by 2030.
Behind every automation platform, every AI product, and every dashboard sits the same thing: a data pipeline. Data flows from source to storage to a place people can use it. This pattern shows up in every scale of business from a solo founder tracking Stripe payouts to a Fortune 500 running enterprise analytics.
Most founders never learn what one is. They pay for tools that hide the pipeline until the tool breaks and they cannot fix it. That knowledge gap costs money and slows decisions. Marianne Gatlabayan wrote this post to fix the gap so any founder can understand, evaluate, or build a first pipeline before hiring a data engineer to do it for them.
What does ETL actually mean?
Three letters. Three steps. This vocabulary has been stable since the 1970s and applies to every pipeline ever built.
E for Extract. Pull data from a source. An API, a database, a spreadsheet, an email, a file drop. The source can be anything that contains data you want to work with. Extraction is about getting a copy of that data into your control.
T for Transform. Clean, standardize, deduplicate, or reshape the data so it makes sense. Raw data is almost never in the shape you need for analysis. Transformation is where you fix the format problems, remove duplicates, convert units, join related data, and prepare the output for its destination.
L for Load. Write the transformed data into a destination where someone can use it. The destination might be a database, a spreadsheet, a dashboard, or another API. Loading is where the data becomes accessible to the people or systems that need it.
Every data pipeline in the world does these three things. The names change (some people call it ELT, some call it a data flow, some call it a workflow), the tools change, the scale changes. The pattern stays. A pipeline that emails you a daily sales summary and one that runs Netflix's recommendations use the same three-step logic.
When would a founder actually want a data pipeline?
Four common triggers push a founder from "I do it by hand" to "I need a pipeline." Recognizing these triggers early saves months of manual work later.
Reports take too long. Weekly reporting eats a full workday, and there is no room to grow. Every new client or new metric adds hours to the process. The manual workflow that worked at 5 clients breaks at 10.
Numbers do not match. The Ads dashboard says one thing, the CRM says another. The revenue report shows one number, accounting shows a different number. Nobody trusts the executive summary because the source of truth is unclear. A pipeline that pulls from one canonical source and derives everything else fixes this.
Two tools that should share data do not. The email platform does not talk to the CRM. Contacts get manually re-uploaded weekly. Product data lives in one place and marketing data lives in another. Every synchronization gap is a candidate for a pipeline.
A team is about to hire. Growth is coming. Manual data work will not scale to five people. New team members cannot ramp up if the reporting workflow lives only in the founder's head.
Any of these is the moment to build a pipeline. Marianne Gatlabayan has watched founders wait too long on this decision and pay the price in delayed hiring, wrong-strategy decisions from bad numbers, or emergency rebuilds when a tool fails without a backup.
What is the free stack for a first data pipeline?
You do not need Snowflake, dbt, or Airflow for your first pipeline. Enterprise data tools are for enterprise data problems. Small business pipelines start with what you already have.
Google Sheets as the destination. Free. Familiar. Connects to almost every business tool. Every team member already knows how to read a Sheet. No new UI to learn.
Google Apps Script as the extract and transform engine. Free. Runs on Google's servers. No installation. Written in JavaScript, which most technical founders have some familiarity with. The learning curve is a weekend for someone with programming experience.
A scheduled trigger so the pipeline runs itself. Free and built into Apps Script. Set it to run hourly, daily, or weekly. Google runs the script on its infrastructure without any laptop needing to be on.
Total cost: zero. Total time to build the first version: an afternoon. This is the same stack Marianne Gatlabayan uses for the franchise brands she supports and for her own side projects. It scales further than most founders expect before requiring a graduation to real database tools.
What should you design before writing any pipeline code?
Before writing any code, answer four questions in a document. This design step takes 15 minutes and saves hours of rebuild later.
What is the source? A Google Analytics property, a Stripe account, a HubSpot list, a webhook from a form. Name it specifically. "The Stripe payouts API endpoint at /v1/payouts, filtered to the last 30 days." Vague sources ("our sales data") produce vague pipelines that break at the first schema change.
What is the destination? A Google Sheet, a database table, a Looker Studio dashboard. Name that too. "The Revenue tab in the Q4 2026 KPIs Sheet." Same principle: specificity now prevents ambiguity later when someone else has to maintain the pipeline.
What transformations happen in between? Dedupe on email. Convert timezone. Filter to last 30 days. Every transformation gets one line. This list becomes the specification the code implements. Missing transformations become bugs the pipeline produces silently.
How often does the pipeline run? Hourly, daily, weekly. Match the cadence to how fast the source data changes. Running hourly against data that only changes daily wastes API quota. Running weekly against data that changes hourly means every dashboard is stale by six days.
Answering these four in writing takes 15 minutes and saves hours of rebuild later. Marianne Gatlabayan requires this design step from every team member before code review, and estimates it prevents about half the pipeline bugs that would otherwise slip into production.
How do you write the Extract step in Apps Script?
For Google's own tools, Apps Script has native services. Analytics, Ads, Search Console, Gmail, Sheets, Calendar. Enable the service you need in the editor's Services panel by clicking the + icon next to "Services" in the left sidebar. Search for the service, click Add.
For anything else, use UrlFetchApp to call a REST API. This is the universal extract tool.
function extractFromAPI() {
const url = 'https://api.example.com/data';
const options = {
headers: { 'Authorization': 'Bearer YOUR_API_KEY' }
};
const response = UrlFetchApp.fetch(url, options);
return JSON.parse(response.getContentText());
}
That single function fetches JSON from any authenticated API and returns it as a JavaScript object. The Bearer token pattern works for most modern APIs. For older APIs that use API keys as URL parameters, add the key to the URL directly. For OAuth-based APIs, use the built-in OAuth libraries or Apps Script's identity services.
Store API keys in Script Properties, not in code. Click Project Settings, then Script Properties, then add your key with a name like API_KEY. Reference it in code as PropertiesService.getScriptProperties().getProperty('API_KEY'). This prevents API keys from leaking if the script gets shared and lets you rotate keys without editing code.
How do you write the Transform step?
Once you have raw data, clean it before writing to the destination. Transformations happen in memory, which is much faster than transforming after loading and then updating rows.
function transform(rawData) {
return rawData
.filter(row => row.status === 'active')
.map(row => ({
email: row.email.toLowerCase().trim(),
created: new Date(row.created_at),
amount: parseFloat(row.amount) || 0
}));
}
Three lines that filter out inactive rows, normalize emails, and coerce number types. Every pipeline needs a version of this. The pattern uses standard JavaScript array methods (filter and map), which any technical founder can read.
Add more transformations as needed. .filter(row => row.amount > 0) removes zero-dollar entries. .map(row => ({...row, region: getRegion(row.country)})) adds a derived column. Chain as many transformations as the data needs.
For messy CRM data, the Pandas cleaning approach covered in a separate pillar guide applies here too, just written in JavaScript inside Apps Script. The Python Pandas CRM cleaning guide covers the transformations in more depth if the pipeline needs to handle dirty source data.
How do you write the Load step?
Write to your Sheet with setValues, not one cell at a time. This is the most common performance mistake in Apps Script pipelines.
function loadToSheet(cleanData) {
const sheet = SpreadsheetApp.openById('SHEET_ID').getSheetByName('Data');
const rows = cleanData.map(item => [item.email, item.created, item.amount]);
sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 3).setValues(rows);
}
setValues writes the whole array in a single operation. Doing it one row at a time with appendRow in a loop is 100 times slower and blows through execution time limits on pipelines with more than a few hundred rows.
The getRange(startRow, startCol, numRows, numCols) syntax specifies the exact range that receives the data. sheet.getLastRow() + 1 finds the first empty row so new data appends below existing data instead of overwriting it.
For pipelines that should replace existing data instead of appending, clear the range first with sheet.getRange('A2:C').clear() before setValues. This is the pattern for pipelines that produce a rolling window (like "last 30 days of activity") rather than a growing log (like "every payment ever received").
How do you put a full pipeline together?
The full pipeline as one function combining all three steps.
function runPipeline() {
const raw = extractFromAPI();
const clean = transform(raw);
loadToSheet(clean);
Logger.log(`Pipeline complete. Loaded ${clean.length} rows.`);
}
Three function calls. Set a daily trigger on runPipeline. The pipeline now runs itself.
This is the entire pattern professional data engineers use, just at a smaller scale and running on free infrastructure. Enterprise pipelines add orchestration (running multiple pipelines in the right order), monitoring (alerting when things break), and versioning (tracking what changed and when). The core three-step logic stays identical.
The Logger.log line writes the completion status to the Executions log so you can verify the pipeline ran successfully. Check the log occasionally to confirm nothing is silently failing. For pipelines critical to business operations, wrap in the try-catch pattern from the Apps Script beginner guide so failures email you immediately.
How do you add data quality checks?
A pipeline that quietly loads bad data is worse than no pipeline. Silent failures are the pipeline pattern that produces the "numbers do not match" problem from earlier in this guide.
function runPipeline() {
const raw = extractFromAPI();
const clean = transform(raw);
if (clean.length === 0) {
GmailApp.sendEmail('you@example.com', 'Pipeline warning', 'Extracted zero rows. Investigate.');
return;
}
const invalidEmails = clean.filter(r => !r.email.includes('@')).length;
if (invalidEmails > 0) {
Logger.log(`Warning: ${invalidEmails} rows had invalid emails`);
}
loadToSheet(clean);
}
Two checks. Zero rows means something broke upstream (API changed, credentials expired, source system is down). Invalid emails means the source got dirtier and downstream systems are about to receive bad data. Both get flagged before the data lands.
Add more checks based on what "bad data" means for your specific pipeline. Duplicates. Negative numbers where positive is expected. Dates in the future. Missing required fields. Each check catches a specific failure mode before it propagates. Marianne Gatlabayan's production pipelines typically include 5 to 10 quality checks, each targeting a specific historical failure that caused a real problem in the past.
When should you graduate from Apps Script to real data tools?
Apps Script hits real limits at specific thresholds. Recognizing these limits early prevents last-minute scrambles.
6-minute execution cap on free accounts. 30 minutes on Workspace. Batch and chunk to work around this by processing data in smaller runs coordinated with PropertiesService state. If you can't chunk it, graduate. Long-running data processing belongs on Cloud Functions or a real orchestrator.
Sheets slow past 100,000 rows. Formulas start recalculating for minutes. Queries drop rows. Users notice the delay. Time to move to BigQuery, Postgres, or another real database. The transition from Sheets to BigQuery is a common growing-pain moment for small businesses hitting this scale.
Multiple sources need real orchestration. When your pipeline pulls from six APIs and one depends on another, Apps Script triggers get fragile. If API A takes 20 minutes and API B needs API A's output, coordinating this in Apps Script requires PropertiesService state and careful trigger timing. Airflow, Prefect, or n8n handle this properly with dependency graphs.
For most small businesses, Apps Script covers years of growth before any of these limits matter. The Make vs Zapier comparison covers the middle-ground tools that fit between Apps Script and enterprise orchestrators.
What data pipeline vocabulary should every founder know?
Founder conversations with data engineers get easier when you know the words. Bad pipeline hires often trace back to founders who could not evaluate technical claims because the vocabulary was foreign.
| Term | What it means |
|---|---|
| ETL | Extract, transform, load. The classic pattern. |
| ELT | Extract, load, transform. Modern variant where you dump raw data first, then transform in the destination. |
| Data warehouse | A database optimized for analytics. BigQuery, Snowflake, Redshift. |
| Data lake | A store for raw unstructured data. Usually cloud storage. |
| Pipeline orchestration | Software that runs pipelines on schedule and handles failures. Airflow, Prefect. |
| Data quality checks | Automated validation that catches bad data before it flows through. |
| Schema | The structure of your data. Column names, types, constraints. |
| Idempotent | A pipeline that produces the same result when run twice. Prevents duplicate data. |
Learn this vocabulary. Vendors and hires will take you more seriously. More importantly, you can evaluate technical proposals for reasonability. When someone quotes a six-month timeline for a "simple ETL pipeline", you know enough to ask what makes it complex.
What is the one rule that prevents pipeline disasters?
Version everything. Every pipeline. Every transformation. Every schema. This one rule prevents most middle-of-the-night pipeline debugging.
Apps Script has version history built into every file. Use it. Add a comment when you save a meaningful change. "Added filter for cancelled subscriptions. October 15, 2026." When something breaks two months later, the version history tells you exactly what changed and when.
For code kept outside Apps Script, use Git. Even a solo founder benefits from Git. When a pipeline breaks at 2 AM, being able to see what changed in the last commit is the difference between a 10-minute fix and a 3-hour debugging session. GitHub is free for private repositories. Set it up before you write your second script.
Version data too. When a transformation logic changes, keep the old logic accessible for at least 90 days. This lets you re-run historical data with the new logic if needed, and lets you prove to auditors or clients what data they received under the old logic if questions come up later.
Frequently Asked Questions
Do I need to hire an engineer to build my first pipeline?
No. The Apps Script pipeline in this guide takes an afternoon to build with basic JavaScript comfort. Hire an engineer when the pipeline outgrows Sheets or when multiple pipelines need coordination. Founders who build their first pipeline themselves become much better at hiring engineers later because they know what the work involves.
What is the difference between a data pipeline and an automation like Zapier?
Zapier connects two tools with one action each. A data pipeline extracts, transforms, and loads structured data on a schedule. Overlap exists but data pipelines handle larger volumes and more complex logic. Zapier is faster to set up for simple connections. Pipelines scale better for regular data operations.
Can I use Excel instead of Google Sheets?
Yes for the destination. Excel and Power Automate build similar pipelines. Google Sheets and Apps Script win for cloud-first workflows and free scheduling. If your team lives in Microsoft 365 already, the Excel-based version costs no more than you already pay.
How do I know if my pipeline is broken?
Add the quality checks shown above. Send yourself an email whenever the pipeline runs but produces zero rows or catches invalid records. Silence means something is wrong. The absence of a completion notification for a pipeline that normally runs daily is itself an alert.
What happens when my source changes its API?
The extract step breaks. You update the URL, authentication, or response parsing to match the new API. This is why pinning API versions matters when the source supports it. Many APIs offer explicit versioning (v1, v2) precisely so pipelines can stay stable during platform changes.
What to read next
- Clean CRM Data with Python Pandas for a bigger transformation example
- How to Build a Weekly Client Reporting Dashboard in Under an Hour for turning pipeline output into a report
- The Beginner's Guide to Automating Recurring Reports with Google Apps Script for scheduling this pipeline properly
About the Author: Marianne Gatlabayan is a Systems Strategist and Industrial Engineer focused on optimizing operations, automating workflows, and driving business growth. With over a decade of experience in analytics, reporting, and process improvement, she helps organizations build scalable systems that turn complexity into measurable results. In her free time, Marianne enjoys discovering and sipping her favorite coffee bean of the week. Connect on LinkedIn.