如何高效从电子表格导入数据至Google SQL数据库？

Great question! Let's break down the best approaches to get your two 4000-row spreadsheets into Google Cloud SQL, plus dive into optimizing that App Script code that's taking nearly 5 minutes to run.

1. Cloud SQL原生导入工具（最快最省心，优先推荐） #

If this is a one-time import, skip the code entirely—Google Cloud SQL has a built-in import tool that's way faster than any script you can write. Here's how it works:

• Step 1: Export your Google Sheets as CSV files (File > Download > Comma-separated values). Clean up any extra blank rows first to avoid importing junk.
• Step 2: Upload the CSVs to Google Cloud Storage. Make sure your Cloud SQL instance has the storage.objectViewer permission for the Storage bucket (you can set this in IAM).
• Step 3: Head to the Cloud SQL Console, select your instance, go to the Import tab, pick the CSV from Storage, specify your target table, and choose whether to append or replace data.

This method runs entirely on Google's servers—no App Script execution limits, no network latency from your script to the database. A 4000-row CSV will import in seconds, not minutes.

2. Optimize Your Existing App Script Code #

If you need to keep using App Script (e.g., for automated recurring imports), the main issue with your current code is almost certainly row-by-row database inserts. Each execute() call makes a separate network request to Cloud SQL, and those add up fast for 4000 rows. Here's how to fix it:

a. Batch Inserts Instead of Row-by-Row #

Group rows into batches (50-100 rows per batch) and run a single INSERT statement for each batch. This cuts down on network requests drastically. For safety, use parameterized queries to avoid SQL injection (never concatenate user/data directly into SQL strings):

function batchInsertData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YourSheet");
  const data = sheet.getDataRange().getValues();
  // Skip header row if needed
  const rowsToInsert = data.slice(1);
  
  const db = getDbConnection(); // Reuse your existing connection function
  const batchSize = 100;
  const stmt = db.prepareStatement(
    "INSERT INTO your_table (col1, col2, col3) VALUES (?, ?, ?)"
  );

  for (let i = 0; i < rowsToInsert.length; i++) {
    const row = rowsToInsert[i];
    // Set parameters (adjust types based on your columns)
    stmt.setString(1, row[0]);
    stmt.setString(2, row[1]);
    stmt.setInt(3, row[2]);
    stmt.addBatch();

    // Execute batch when we hit batch size or end of data
    if ((i + 1) % batchSize === 0 || i === rowsToInsert.length - 1) {
      stmt.executeBatch();
      stmt.clearBatch();
    }
  }

  stmt.close();
  db.close();
}

b. Minimize Sheets API Calls #

Stop reading cells one at a time—fetch all data in a single call with getDataRange().getValues(). This reduces the number of requests to the Sheets API, which is a major source of slowdown in scripts. Also, filter out empty rows upfront to avoid processing unnecessary data:

const filteredRows = rowsToInsert.filter(row => row.some(cell => cell !== ""));

c. Optimize Database Connection #

• Reuse a single database connection for all inserts, don't open/close it inside your loop. Opening a connection has overhead, so doing it once per script run saves time.
• If your script still hits timeouts, split the data into two separate batches (e.g., process first 2000 rows, then the next 2000) and run them as separate function calls, or use PropertiesService to track progress if needed.

d. Cut Unnecessary Overhead #

• Remove any console.log() calls inside loops—logging adds unexpected latency when repeated 4000 times.
• Temporarily disable spreadsheet recalculation during the import (re-enable afterward):

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setSpreadsheetCalculation(SpreadsheetApp.Calculation.MANUAL);
  // Run your import code here
  ss.setSpreadsheetCalculation(SpreadsheetApp.Calculation.AUTOMATIC);
  

3. Cloud Functions + Sheets API (For Automated Workflows) #

If you need recurring automated imports, consider moving the logic to Google Cloud Functions instead of App Script. Cloud Functions have longer execution limits (up to 9 minutes for Node.js) and can use more efficient database libraries (like pg for PostgreSQL or mysql2 for MySQL). You can trigger the function when your spreadsheet is updated, or on a schedule via Cloud Scheduler.

For a one-time import, go with the Cloud SQL native tool—it's the fastest and least error-prone. If you need to automate, optimize your App Script with batch inserts first, or upgrade to Cloud Functions for better performance and scalability.

内容的提问来源于stack exchange，提问作者M277