Google Drive批量上传、重命名及超时：脚本配置与使用咨询

Hey there! Let's break down everything you need to get this Google Script up and running smoothly, including full configuration, step-by-step usage, and fixes for renaming and timeout issues.

1. 完整脚本补充与核心配置 #

First, here's the full, functional version of your script with key improvements for error handling and customization:

function SaveToGoogleDrive(){ 
  var folderID = 'FOLDER_HERE'; // Replace with your target Google Drive folder ID
  var folder = DriveApp.getFolderById(folderID);
  var sheet = SpreadsheetApp.getActiveSheet();
  // Fetch data from columns A (image URL) and B (custom title), starting from row 2
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
  
  // Loop through each row of data
  data.forEach(function(row, index) {
    var imageUrl = row[0];
    var customTitle = row[1] || "Untitled Image"; // Fallback title if B column is empty
    
    try {
      // Fetch the remote image (mute exceptions to handle errors gracefully)
      var response = UrlFetchApp.fetch(imageUrl, {muteHttpExceptions: true});
      if (response.getResponseCode() !== 200) {
        sheet.getRange(index + 2, 3).setValue("Failed: HTTP " + response.getResponseCode());
        return;
      }
      
      // Verify the file is a JPEG
      var contentType = response.getHeaders()['Content-Type'] || 'image/jpeg';
      if (!contentType.includes('image/jpeg')) {
        sheet.getRange(index + 2, 3).setValue("Failed: Not a JPEG file");
        return;
      }
      
      // Create the file in Drive with your custom title
      var blob = response.getBlob().setName(customTitle);
      var file = folder.createFile(blob);
      
      // Log success and add a direct link to the file in column D
      sheet.getRange(index + 2, 3).setValue("Success");
      sheet.getRange(index + 2, 4).setFormula('=HYPERLINK("' + file.getUrl() + '", "View File")');
      
      // Add a small delay to avoid hitting Google's request limits
      Utilities.sleep(1000);
    } catch (e) {
      // Log any errors in column C for debugging
      sheet.getRange(index + 2, 3).setValue("Error: " + e.toString());
    }
  });
}

Key Configuration Steps: #

• Get your Drive Folder ID: Open your target Google Drive folder, copy the string from the address bar (e.g., from https://drive.google.com/drive/folders/abc123XYZ, use abc123XYZ to replace FOLDER_HERE in the script).
• Set up your Sheet: Use column A for remote JPEG URLs, column B for your desired file titles. Add headers like Image URL (A1) and Custom Title (B1) for clarity.

2. Step-by-Step Usage #

1. Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
2. Delete the default code, paste the full script above, and replace the folderID with your target folder's ID.
3. Click the save button 💾, give your script a name (e.g., ImageToDriveImporter).
4. First run authorization: Click the play button ▶️, select SaveToGoogleDrive. You'll see an "unverified app" warning—click Advanced > Go to [your script name] > Allow to grant necessary permissions.
5. Run the script again. Once finished, check columns C (status: Success/Failed/Error) and D (direct link to the Drive file) for results.

3. Fixes for Common Issues #

File Renaming Problems #

• Custom Titles Not Applying: The script already uses column B's value as the file name. If B is empty, it defaults to Untitled Image—adjust this fallback text in the code if needed.
• Handle Duplicate File Names: By default, Drive adds a suffix like (1) to duplicates, but you can customize this logic. Add this code right before creating the blob:

  // Check for existing files with the same title and add a counter suffix
  var existingFiles = folder.getFilesByName(customTitle);
  var counter = 1;
  var finalTitle = customTitle;
  while (existingFiles.hasNext()) {
    finalTitle = customTitle + " (" + counter + ")";
    existingFiles = folder.getFilesByName(finalTitle);
    counter++;
  }
  // Use the final unique title for the blob
  var blob = response.getBlob().setName(finalTitle);
  

Upload Timeouts & Request Limits #

• Add Longer Delays: If you hit rate limits, increase the Utilities.sleep(1000) value to 2000 (2 seconds) or 3000 (3 seconds) to space out requests.
• Batch Processing: For large datasets (100+ rows), split processing into batches to avoid timeouts. Replace the data.forEach loop with this:

  var batchSize = 20; // Process 20 rows at a time
  for (var i = 0; i < data.length; i += batchSize) {
    var batch = data.slice(i, i + batchSize);
    batch.forEach(function(row, index) {
      // Keep your existing row processing logic here
      // Note: Update row references to use i + index + 2 (e.g., sheet.getRange(i + index + 2, 3))
    });
    Utilities.sleep(3000); // Longer pause between batches
  }
  

• Enable Drive Advanced Service: For better performance with large files, enable the Drive API: In the script editor, go to Services > Add Service > Drive API > Add. Then replace folder.createFile(blob) with Drive.Files.create({title: finalTitle, parents: [{id: folderID}]}, blob)—this is more efficient for bulk operations.

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