如何适配Google Script实现从FTP链接或Google Drive文件导入CSV

Let's break down how to modify your existing code to support both sources, plus a combined version that can handle all three (HTTP, FTP, Drive) depending on your needs.

1. Adapting for FTP Links #

Google Apps Script's UrlFetchApp supports FTP URLs, including authenticated ones. You just need to format the FTP URL correctly:

• For anonymous FTP: ftp://ftp.example.com/path/to/your/file.csv
• For authenticated FTP: ftp://username:password@ftp.example.com/path/to/your/file.csv

Here's how to adjust the CSV fetching part of your script:

// Replace the HTTP URL with your FTP URL
const ftpUrl = "ftp://username:password@yourftpserver.com/InnovaCSV.csv";
const res = UrlFetchApp.fetch(ftpUrl);
const csvBlob = res.getBlob();

Note: Some FTP servers may block requests from Google's IP ranges. If you run into issues, check if your server allows external FTP access or consider using a dedicated FTP library (though most basic cases work with UrlFetchApp).

2. Adapting for Google Drive Files #

To import a CSV stored in Google Drive, you'll need to retrieve the file's blob directly from Drive. You can use the file ID (easiest) or search by filename.

Option A: Using the Drive File ID #

1. Get the file ID from the Drive file's URL (it's the long string between /d/ and /edit).
2. Modify the blob retrieval part:

const driveFileId = "YOUR_GOOGLE_DRIVE_FILE_ID";
const driveFile = DriveApp.getFileById(driveFileId);
const csvBlob = driveFile.getBlob();

// Optional: Verify it's a CSV file
if (csvBlob.getContentType() !== "text/csv") {
  throw new Error("The Drive file is not a CSV!");
}

Option B: Searching by Filename #

If you don't want to hardcode the file ID, you can search for the CSV by name (note: this will pick the first matching file):

const fileName = "InnovaCSV.csv";
const files = DriveApp.getFilesByName(fileName);
if (!files.hasNext()) {
  throw new Error(`No file named ${fileName} found in Drive!`);
}
const driveFile = files.next();
const csvBlob = driveFile.getBlob();

3. Combined Script: Support All Three Sources #

Here's a modified version of your original script that lets you choose the source type (HTTP, FTP, DRIVE) and handles each case:

function importCSV(sourceType, sourceIdentifier) {
  // 1. Set required columns (same as original)
  const requiredColumns = [1, 5, 20];
  // 2. Get CSV blob based on source type
  let csvBlob;
  
  switch(sourceType) {
    case "HTTP":
      const res = UrlFetchApp.fetch(sourceIdentifier);
      csvBlob = res.getBlob();
      break;
    case "FTP":
      const ftpRes = UrlFetchApp.fetch(sourceIdentifier);
      csvBlob = ftpRes.getBlob();
      break;
    case "DRIVE":
      const driveFile = DriveApp.getFileById(sourceIdentifier);
      csvBlob = driveFile.getBlob();
      // Verify CSV type
      if (csvBlob.getContentType() !== "text/csv") {
        throw new Error("Drive file is not a CSV!");
      }
      break;
    default:
      throw new Error("Invalid source type. Use 'HTTP', 'FTP', or 'DRIVE'.");
  }

  // 3. Create temp Google Sheet (same as original, using the blob)
  const tempSheetId = Drive.Files.insert(
    {mimeType: MimeType.GOOGLE_SHEETS, title: "tempSpreadsheet"},
    csvBlob
  ).id;

  // 4. Remove unwanted columns (same as original)
  const ss = SpreadsheetApp.openById(tempSheetId);
  const sheet = ss.getSheets()[0];
  const maxColumn = sheet.getMaxColumns();
  const requests = [];
  
  for (let i = 1; i <= maxColumn; i++) {
    if (!requiredColumns.includes(i)) {
      requests.push({
        deleteDimension: {
          range: {
            sheetId: sheet.getSheetId(),
            dimension: "COLUMNS",
            startIndex: i - 1,
            endIndex: i
          }
        }
      });
    }
  }
  
  Sheets.Spreadsheets.batchUpdate({requests: requests.reverse()}, tempSheetId);

  // 5. Copy to destination sheet (same as original)
  const destinationSheetName = "Sheet1";
  const dstss = SpreadsheetApp.getActiveSpreadsheet();
  const values = Sheets.Spreadsheets.Values.get(tempSheetId, sheet.getSheetName()).values;
  
  Sheets.Spreadsheets.Values.update(
    {values: values},
    dstss.getId(),
    destinationSheetName,
    {valueInputOption: "USER_ENTERED"}
  );

  // 6. Delete temp sheet (same as original)
  DriveApp.getFileById(tempSheetId).setTrashed(true);
}

// Example usage:
// importCSV("HTTP", "https://www.stanem.it/csv/InnovaCSV.csv");
// importCSV("FTP", "ftp://username:password@yourftpserver.com/InnovaCSV.csv");
// importCSV("DRIVE", "YOUR_DRIVE_FILE_ID");

Important Notes #

• Permissions: When running the Drive version for the first time, you'll need to authorize the script to access your Google Drive.
• FTP Security: Storing FTP credentials in plain text is not ideal. For production use, consider using Script Properties to store sensitive info securely.
• Error Handling: The combined script includes basic error checking, but you can expand it to handle cases like network timeouts, missing files, or invalid CSV formats.

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