基于Google Sheets替换规则批量修改Google Drive指定文件夹中文档名称的技术实现问询

Got it, let's fix this script to meet your exact requirement of bulk renaming Google Docs in the 'bvm-hd' folder using the replacement rules from your specified Google Sheet. Here's the corrected and optimized code, plus a breakdown of the changes I made:

First, here's the working script that aligns with your needs:

function bulkRenameDocsInFolder() {
  // Define constants for easy maintenance
  const FOLDER_NAME = 'bvm-hd';
  const SHEET_ID = '1uxEy8cPkNQVBVAFSHPBhvq0aRgRVZ0z2_I1AxK0gcuI';
  const SHEET_NAME = 'Trang tính1'; // Assuming this is your target sheet name

  // Locate the target folder
  const folders = DriveApp.getFoldersByName(FOLDER_NAME);
  if (!folders.hasNext()) {
    console.log(`Folder "${FOLDER_NAME}" not found!`);
    return;
  }
  const folder = folders.next();

  // Fetch and prepare replacement rules from your Google Sheet
  const ss = SpreadsheetApp.openById(SHEET_ID);
  const sh = ss.getSheetByName(SHEET_NAME);
  if (!sh) {
    console.log(`Sheet "${SHEET_NAME}" not found in the spreadsheet!`);
    return;
  }
  const lastRow = sh.getLastRow();
  if (lastRow < 1) {
    console.log("No valid replacement rules found in the sheet!");
    return;
  }
  // Filter out empty rows to avoid invalid replacements
  const replacementRules = sh.getRange(`A1:B${lastRow}`).getValues()
    .filter(row => row[0].toString().trim() !== '' && row[1].toString().trim() !== '');

  // Process every Google Doc in the folder
  const files = folder.getFilesByType("application/vnd.google-apps.document");
  while (files.hasNext()) {
    const file = files.next();
    let updatedFileName = file.getName();

    // Apply each replacement rule to the file name
    replacementRules.forEach(rule => {
      const oldText = rule[0].toString();
      const newText = rule[1].toString();
      // Replace all occurrences (use 'gi' instead of 'g' for case-insensitive matches)
      updatedFileName = updatedFileName.replace(new RegExp(oldText, 'g'), newText);
    });

    // Only rename if the file name actually changed
    if (updatedFileName !== file.getName()) {
      file.setName(updatedFileName);
      console.log(`Renamed: "${file.getName()}" → "${updatedFileName}"`);
    } else {
      console.log(`No changes needed for: "${file.getName()}"`);
    }
  }
}

Key Fixes & Improvements from Your Original Code: #

• Corrected Sheet ID: Swapped the incorrect spreadsheet ID with your specified one (1uxEy8cPkNQVBVAFSHPBhvq0aRgRVZ0z2_I1AxK0gcuI)
• Fixed File Name Logic: The original code tried using replaceText() (which modifies document content, not the file name) — we now use JavaScript's native replace() directly on the file name string.
• Removed Unused Logic: Got rid of the "newest file" variables (like newestFileID) since you need to process all files in the folder, not just the latest one.
• Added Error Checks: Validates that the target folder, sheet, and replacement rules exist to avoid runtime crashes.
• Optimized Rule Handling: Filters out empty rows from the sheet so we don't waste time on invalid rules.
• Conditional Renaming: Only renames files when the name actually changes, reducing unnecessary API calls.
• Clear Logging: Adds console logs to track execution progress (check the "Executions" tab in Apps Script to view these logs).

How to Use: #

1. Open Google Apps Script
2. Create a new project
3. Replace the default code with the script above
4. Save the project (name it something like "Bulk Doc Renamer")
5. Run the bulkRenameDocsInFolder function (you'll need to authorize the script on first run — follow the prompts to grant access to Drive and Sheets)

Extra Notes: #

• Case Sensitivity: By default, replacements are case-sensitive. For case-insensitive matches, change the regex flag from 'g' to 'gi' in the replace() call.
• Special Characters: If your replacement text includes regex special characters (like ., *, +), add this helper function to escape them:

  function escapeRegExp(string) {
    return string.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  }
  

  Then use it like: new RegExp(escapeRegExp(oldText), 'g')

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