如何修改Google脚本实现多页分号分隔CSV批量导入谷歌表格？

Hey there! Let's extend your existing Google Apps Script to handle those paginated CSV imports with semicolon delimiters. I'll walk you through a complete solution that loops through each page, imports the data, and stores each page's content in its own sheet.

First, here's the full script that does exactly what you need. I've added comments to explain each part so you can tweak it to fit your exact setup:

function importPagedCSVs() {
  // Configure these variables to match your setup
  const baseUrl = "https://example.com/feed?page="; // Base URL without the page number
  const maxPages = 10; // Total number of pages you want to import
  const csvDelimiter = ";"; // Your CSV uses semicolons instead of commas

  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Loop through each page from 1 to maxPages
  for (let pageNumber = 1; pageNumber <= maxPages; pageNumber++) {
    try {
      // Build the full URL for the current page
      const fullPageUrl = `${baseUrl}${pageNumber}&format=csv`;
      
      // Fetch the CSV content from the URL
      const response = UrlFetchApp.fetch(fullPageUrl);
      const csvText = response.getContentText();

      // Parse the CSV (handles semicolon delimiters)
      // Split into rows first, then split each row by semicolon
      const rows = csvText.split("\n").filter(row => row.trim() !== "");
      const parsedData = rows.map(row => row.split(csvDelimiter));

      // Get or create a sheet for this page
      let pageSheet = activeSpreadsheet.getSheetByName(`Page ${pageNumber}`);
      if (!pageSheet) {
        // Create a new sheet if it doesn't exist
        pageSheet = activeSpreadsheet.insertSheet(`Page ${pageNumber}`);
      } else {
        // Optional: Clear existing content if the sheet already exists
        // Remove this line if you want to append new data instead of overwriting
        pageSheet.clearContents();
      }

      // Write the parsed data to the sheet
      if (parsedData.length > 0) {
        pageSheet.getRange(1, 1, parsedData.length, parsedData[0].length).setValues(parsedData);
        console.log(`Successfully imported Page ${pageNumber}`);
      } else {
        console.log(`Page ${pageNumber} has no data to import`);
      }

    } catch (error) {
      // Log errors but keep the script running for other pages
      console.error(`Failed to import Page ${pageNumber}: ${error.message}`);
      continue;
    }
  }

  // Show a confirmation alert when done
  SpreadsheetApp.getUi().alert("Paged CSV import finished! Check the script logs for details.");
}

Key Customizations & Notes #

• Robust CSV Parsing: The basic split works for most simple CSVs, but if your data has quoted fields that contain semicolons (like "Smith, Jane; Marketing"), use this improved parser function instead. Replace the parsing lines in the main script with this:

  // Helper function to handle quoted fields with delimiters
  function parseCSV(csvText, delimiter) {
    const regex = new RegExp(`(${delimiter})(?=(?:[^"]*"[^"]*")*(?![^"]*"))`, "g");
    return csvText.split("\n")
      .filter(row => row.trim() !== "")
      .map(row => row.replace(regex, "\t").split("\t").map(cell => cell.replace(/^"|"$/g, "")));
  }
  
  // Use it like this in the main function:
  const parsedData = parseCSV(csvText, csvDelimiter);
  

• Sheet Behavior: By default, the script clears existing content in a sheet if it already exists. If you want to append new data instead of overwriting, just delete the pageSheet.clearContents(); line.

• Error Handling: The try/catch block ensures that if one page fails (e.g., a broken link or empty data), the script will keep importing the other pages. You can check the errors by going to View > Logs in the Apps Script editor.

• Rate Limits: For 10 pages, you won't hit Google's rate limits, but if you're importing more pages, add a small delay inside the loop to avoid issues:

  Utilities.sleep(1000); // Wait 1 second between requests
  

How to Implement This #

1. Open your Google Spreadsheet.
2. Go to Extensions > Apps Script to open the script editor.
3. Replace the default code with the script above.
4. Update the baseUrl, maxPages, and csvDelimiter variables to match your actual setup.
5. Save the script (click the floppy disk icon) and give it a name like "ImportPagedCSVs".
6. Run the function by clicking the play button next to importPagedCSVs. The first time you run it, you'll need to authorize the script—follow the prompts, and click "Advanced" to allow access (it's safe, you're running your own script).

That's it! This script will handle all your paginated CSV imports and organize each page into its own sheet.

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