修改App Script实现Google Sheets导出CSV时更新现有文件

我用下面的App Script把Google Sheets导出为CSV，但每次运行都会在Google Drive里创建新的文件夹/文件。我想要实现的是更新同一文件夹或指定文件夹中的现有CSV文件，而非创建新文件；仅当Google Sheets中新增工作表时才创建新文件。

原代码：

/*
 * script to export data in all sheets in the current spreadsheet as individual csv files
 * files will be named according to the name of the sheet
 * author: Michael Derazon
*/

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "export as csv files", functionName: "saveAsCSV"}];
  ss.addMenu("csv", csvMenuEntries);
};

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  // create a folder from the name of the spreadsheet
 var folder = DriveApp.getFileById(ss.getId()).getParents().next();
  for (var i = 0 ; i < sheets.length ; i++) {
    var sheet = sheets[i];
    // append ".csv" extension to the sheet name
    fileName = sheet.getName() + ".csv";
    // convert all available sheet data to csv format
    var csvFile = convertRangeToCsvFile_(fileName, sheet);
    // create a file in the Docs List with the given name and the csv data
    folder.createFile(fileName, csvFile);
  }
  Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
}

function convertRangeToCsvFile_(csvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;

    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

/*
 * script to export data in all sheets in the current spreadsheet as individual csv files
 * 更新逻辑：存在同名CSV则覆盖内容，不存在则新建；仅新增工作表时创建新文件
 * author: Michael Derazon / modified for update requirement
*/

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "导出为CSV文件", functionName: "saveAsCSV"}];
  ss.addMenu("CSV操作", csvMenuEntries);
};

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  // 获取当前表格所在的文件夹
  var folder = DriveApp.getFileById(ss.getId()).getParents().next();
  
  for (var i = 0 ; i < sheets.length ; i++) {
    var sheet = sheets[i];
    // 生成CSV文件名
    var fileName = sheet.getName() + ".csv";
    // 转换工作表数据为CSV格式
    var csvContent = convertRangeToCsvFile_(fileName, sheet);
    
    // 查找文件夹中是否存在同名CSV文件
    var existingFiles = folder.getFilesByName(fileName);
    if (existingFiles.hasNext()) {
      // 存在则更新文件内容
      var existingFile = existingFiles.next();
      existingFile.setContent(csvContent);
    } else {
      // 不存在则创建新文件
      folder.createFile(fileName, csvContent);
    }
  }
  Browser.msgBox('文件已更新/创建，所在文件夹：' + folder.getName());
}

function convertRangeToCsvFile_(csvFileName, sheet) {
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;

    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") !== -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        if (row < data.length - 1) {
          csv += data[row].join(",") + "\r\n";
        } else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  } catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

• 文件更新逻辑：在创建文件前，通过folder.getFilesByName(fileName)检查目标文件夹中是否存在同名CSV文件。若存在则调用setContent覆盖原有内容，不存在则创建新文件，实现了"更新现有文件、仅新增工作表时创建新文件"的需求。
• 变量作用域修复：给fileName添加var声明，避免全局变量污染。
• 菜单文本优化：将英文菜单改为中文，更符合国内用户使用习惯（可根据需要改回英文）。

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