请求编写脚本：将Drive中xls/xlsm转为Google Sheet并覆盖同名旧文件

I’ve built a Google Apps Script that nails exactly what you need—converts your XLS/XLSM files to Google Sheets, ignores macros (since Google Sheets doesn’t support them anyway), and takes care of duplicate names by overwriting the oldest existing version. Here’s how it works:

Step 1: Full Script Code #

function convertExcelToSheets() {
  // Optional: Replace with your target folder ID to limit conversion to a specific folder
  const targetFolderId = null; // Leave as null to search entire Drive
  const folder = targetFolderId ? DriveApp.getFolderById(targetFolderId) : DriveApp.getRootFolder();
  
  // MIME types for Excel files we want to convert
  const excelMimeTypes = [
    'application/vnd.ms-excel', // .xls
    'application/vnd.ms-excel.sheet.macroEnabled.12' // .xlsm
  ];
  
  // Collect all matching Excel files
  const fileIterator = folder.getFilesByType(excelMimeTypes[0]);
  const excelFiles = [];
  
  // Add files from first MIME type
  while (fileIterator.hasNext()) {
    excelFiles.push(fileIterator.next());
  }
  
  // Add files from remaining MIME types
  excelMimeTypes.slice(1).forEach(mimeType => {
    const iterator = folder.getFilesByType(mimeType);
    while (iterator.hasNext()) {
      excelFiles.push(iterator.next());
    }
  });

  // Process each Excel file
  excelFiles.forEach(excelFile => {
    const baseFileName = excelFile.getName().replace(/\.(xls|xlsm)$/i, '');
    const sheetFileName = `${baseFileName}.gsheet`;
    
    // Find all existing Google Sheets with the same name
    const existingSheetsIterator = DriveApp.getFilesByName(sheetFileName);
    let oldestSheet = null;
    
    // Identify the oldest duplicate
    while (existingSheetsIterator.hasNext()) {
      const sheet = existingSheetsIterator.next();
      if (!oldestSheet || sheet.getLastUpdated() < oldestSheet.getLastUpdated()) {
        oldestSheet = sheet;
      }
    }

    // Convert Excel file to Google Sheets
    const convertedFile = Drive.Files.copy(
      { title: baseFileName, mimeType: 'application/vnd.google-apps.spreadsheet' },
      excelFile.getId(),
      { convert: true }
    );

    // Delete the oldest duplicate if it exists
    if (oldestSheet) {
      DriveApp.getFileById(oldestSheet.getId()).setTrashed(true);
      console.log(`Deleted oldest duplicate: ${oldestSheet.getName()}`);
    }

    console.log(`Converted successfully: ${excelFile.getName()} -> ${convertedFile.title}.gsheet`);
  });
}

Step 2: How to Use This Script #

• Head to Google Apps Script (or go to your Google Drive, click New > More > Google Apps Script)
• Create a new project, delete the default myFunction() code, and paste the script above
• Optional: If you want to limit conversion to a specific folder, replace null in const targetFolderId = null; with your folder's ID (you can grab this from the folder’s URL)
• Save the project (give it a name like "Excel to Sheets Converter")
• Run the script for the first time—you’ll need to authorize it (Google will show an unverified app warning; click Advanced > Go to [Project Name] to proceed)

Key Details to Know #

• Macro Handling: Google Sheets doesn’t support Excel macros, so the conversion process automatically skips them—no extra configuration needed here.
• Duplicate Override: The script scans for all existing Google Sheets with the same base name (without the .xls/xlsm extension). It sends the oldest duplicate to the trash, ensuring your newly converted file stays as the active version.
• Scope Control: Leaving targetFolderId as null makes the script scan your entire Drive. Restricting it to a specific folder is better if you only want to process files in one location.
• Automation: To run this script automatically (e.g., daily to convert new files), go to Edit > Current project's triggers, add a new trigger, and set it to run convertExcelToSheets on a time-driven schedule.

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