基于Google Sheet批量重命名Google Drive文件的脚本需求

Hey there! No worries at all—let's break this down step by step so you can get this script up and running even as a total beginner. Here's exactly what you need to do:

First, make sure your Sheet is structured correctly. You'll need 3 core columns (plus an optional status column we'll use later):

• Column A: Full path to the folder in Google Drive (e.g., Projects/2024/Client Reports — skip "My Drive" in the path)
• Column B: Exact original filename (including file extension like .pdf or .txt — case-sensitive!)
• Column C: The new filename you want to use (don't forget the extension here too)

Example row:

1. Open your Google Sheet, click Extensions > Apps Script to open the script editor.
2. Delete any default code in the editor (the myFunction() stuff).
3. Paste this full script into the editor:

function renameFilesFromSheet() {
  // Grab the active sheet in your spreadsheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Get all data starting from row 2 (since row 1 is your header)
  const data = sheet.getDataRange().getValues().slice(1);
  
  // Loop through each row of your data
  data.forEach((row, index) => {
    const folderPath = row[0]; // Column A: Folder path
    const oldFileName = row[1]; // Column B: Original filename
    const newFileName = row[2]; // Column C: New filename
    const rowNumber = index + 2; // Match the actual row number in your Sheet
    
    // Skip rows where original or new filename is empty
    if (!oldFileName || !newFileName) {
      sheet.getRange(rowNumber, 4).setValue("Skipped: Empty filename");
      return;
    }
    
    try {
      // Find the target folder using the path from Column A
      const targetFolder = getFolderByPath(folderPath);
      if (!targetFolder) {
        sheet.getRange(rowNumber, 4).setValue("Error: Folder not found");
        return;
      }
      
      // Look for the file with the original name in the target folder
      const files = targetFolder.getFilesByName(oldFileName);
      if (files.hasNext()) {
        const file = files.next();
        // Rename the file!
        file.setName(newFileName);
        sheet.getRange(rowNumber, 4).setValue("Renamed successfully");
      } else {
        sheet.getRange(rowNumber, 4).setValue("Error: File not found");
      }
    } catch (error) {
      // Catch and log any unexpected errors
      sheet.getRange(rowNumber, 4).setValue(`Error: ${error.message}`);
    }
  });
}

// Helper function: Find a Google Drive folder using a path string
function getFolderByPath(path) {
  // If path is empty, use the root "My Drive" folder
  if (!path || path.trim() === "") {
    return DriveApp.getRootFolder();
  }
  
  // Split the path into individual folder names
  const folderNames = path.split("/").filter(name => name.trim() !== "");
  let currentFolder = DriveApp.getRootFolder();
  
  // Navigate through each folder in the path
  for (const folderName of folderNames) {
    const folders = currentFolder.getFoldersByName(folderName);
    if (folders.hasNext()) {
      currentFolder = folders.next();
    } else {
      // Return null if any folder in the path doesn't exist
      return null;
    }
  }
  
  return currentFolder;
}

1. In the script editor, make sure renameFilesFromSheet is selected in the dropdown next to the run button (▶️).
2. Click the run button. You'll see a prompt asking for authorization (since the script needs access to your Drive and Sheet).
3. Follow these steps to authorize:
   • Select your Google account.
   • Click Advanced (at the bottom left of the "Google hasn't verified this app" screen).
   • Click Go to [Your Script Name] (unsafe) (it's safe—you wrote the script!).
   • Click Allow to grant the necessary permissions.

• Path accuracy matters: Double-check the folder path in Column A. If a file is directly in "My Drive", leave Column A empty.
• Case-sensitive filenames: Log.txt is not the same as log.txt to Google Drive—make sure your Sheet's original filename matches exactly.
• Duplicate files: If there are multiple files with the same original name in a folder, the script will only rename the first one it finds. Try to keep filenames unique in each folder.
• Status updates: The script will automatically add a status message in Column D for each row, so you can see which files worked and which didn't.

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