Data Migration & ETL Magic

Forget expensive enterprise tools. Learn how to use Google Apps Script as a lightweight, serverless ETL engine to move, transform, and clean your data automatically.

The Case for Apps Script ETL

In the world of data engineering, ETL stands for Extract, Transform, and Load. Traditionally, this requires specialized software (like Informatica or Talend) or Python scripts running on rented servers. However, for small-to-medium datasets (up to ~5 million cells), Google Apps Script offers a compelling alternative.

It allows you to build "Serverless Data Pipelines." You don't manage infrastructure; you just define the logic. It's particularly "magical" because it sits directly on top of your data sources (Sheets, Drive, Gmail), removing the complex authentication hurdles usually required to connect these services.

The Core Concept: The Cost of a Call

If you take nothing else from this guide, remember this: JavaScript execution is fast; Google Service calls are slow.

Imagine you are shopping for groceries.

• Bad Approach: Drive to the store, buy one egg, drive home. Drive back to the store, buy one carton of milk, drive home.
• Good Approach (Batching): Drive to the store, buy everything on your list, drive home once.

In Apps Script, calling sheet.getRange(i, 1).getValue() inside a loop is the equivalent of driving to the store for a single egg. It incurs a network round-trip overhead every single time. For 1,000 rows, this might take 2 minutes.

The solution is Batch Operations. We read the entire dataset into memory (an Array) with one command: getValues(). We manipulate that array using standard JavaScript, which happens instantly in the cloud's memory. Then, we write the result back in one shot using setValues().

Phase 1: Extraction (Importing Data)

The first step of migration is getting data in. Apps Script simplifies this via the UrlFetchApp service, which acts like a browser requesting a webpage.

Handling Formats

Data usually arrives as a text string. Your script's job is to parse that string into a 2D array (rows and columns) that Google Sheets understands.

• CSV: Use the built-in Utilities.parseCsv(string). It is robust and handles edge cases, such as commas inside quoted strings (e.g., "New York, NY"), which would break a simple string split.
• JSON: Use JSON.parse(string). This converts the text into JavaScript objects. You will then need to map these objects into arrays (e.g., [obj.id, obj.name, obj.date]) to fit the Sheets structure.

function importCSVFromUrl() {
  // 1. EXTRACT: Fetch the raw text
  const url = "https://example.com/data.csv";
  const csvContent = UrlFetchApp.fetch(url).getContentText();
  
  // 2. TRANSFORM: Parse into 2D Array
  const data = Utilities.parseCsv(csvContent);
  
  // 3. LOAD: Batch write to Sheet
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Phase 2: Transformation (Logic & Hygiene)

Rarely do you move data exactly as is. You usually need to clean it. This is where JavaScript shines.

Common Transformations

Because you are working with Arrays in memory, you can use powerful JavaScript array methods:

• Filtering: Use .filter() to remove rows where a status is "Closed" or a cell is empty.
• Mapping: Use .map() to reformat dates, calculate new columns (e.g., Price * Qty), or sanitize strings (e.g., email.trim().toLowerCase()).
• Deduplication: Create a Set of unique IDs to ensure you don't import the same transaction twice.

The "Smart Append" Strategy

When syncing data from a Source to a Destination, you face a challenge: How do I avoid creating duplicates?

The best pattern is to read the existing IDs in the destination sheet first. Then, as you process the source data, check if the ID already exists. Only add the row to your newRows array if it's truly new.

Phase 3: Loading at Scale (BigQuery)

Google Sheets has a hard limit of 10 million cells, and performance degrades significantly long before that. When your data outgrows Sheets, the natural next step is BigQuery.

Apps Script has an "Advanced Service" for BigQuery. This allows you to treat a BigQuery table essentially like a giant spreadsheet.

The Load Job

Instead of inserting rows one by one (which costs money per insert), the efficient pattern is to create a "Load Job". You convert your JavaScript array into a CSV string in memory, turn that into a "Blob" (a file-like object), and push that Blob to BigQuery. BigQuery then ingests the entire blob in seconds, regardless of whether it's 10 rows or 100,000 rows.

Deep Dive: The 6-Minute Wall (Continuations)

This is the most advanced and critical concept in Apps Script data migration. Google enforces a strict maximum execution time (usually 6 minutes for Workspace accounts).

If your script is migrating 50,000 rows and hits the 6-minute mark, Google kills the process immediately. If you haven't saved your work, you lose progress and don't know where to restart.

The "Watchdog" Pattern

To solve this, we implement a pattern often called "Continuations":

1. Start the Timer: At the beginning of your script, record the startTime.
2. Process in Chunks: Loop through your data. Inside the loop, check the current time against the startTime.
3. The Safety Margin: If the difference approaches the limit (e.g., 5 minutes have passed), stop processing.
4. Save State: Use PropertiesService to save your current index (e.g., "I finished row 4,500").
5. Self-Trigger: Programmatically create a "Trigger" that will start the function again in 1 minute.
6. Graceful Exit: The script ends successfully.
7. Resume: One minute later, the new instance starts. It reads the PropertiesService, sees "Row 4,500", and skips directly to row 4,501 to continue.

Strategic Best Practices

• Use Staging Sheets: Never write raw import data directly to your user-facing dashboard. Import it to a hidden "Staging" sheet first. Verify the data integrity (e.g., check that column counts match), clear the dashboard, and then copy the data over. This prevents users from seeing half-loaded data if the script fails.
• Error Logging: Silent failures are the enemy of data pipelines. Wrap your logic in try...catch blocks. If an error occurs, write it to a "Logs" sheet with a timestamp and the error message.
• DateFormat Hell: Dates are the most common source of migration errors. Always explicitly format dates using Utilities.formatDate() into a standardized string (like YYYY-MM-DD) before moving them between systems to avoid timezone shifts.