csv - 仅为“新”文件将 XLS 转换为 CSV

Question

我有：

• .xlsx 文件每天自动导入到谷歌驱动器文件夹

我想：

• 每个文件都转换为 CSV 并保存到不同的文件夹

• 每个文件都转换为 Google 表格并保存到不同的文件夹

• 原始的 xlsx 文件没有被删除

• 但仅适用于自我上次运行脚本以来添加的文件

我从这篇文章“仅在 Google Apps 脚本中新建文件”和这篇文章“将驱动器文件夹中的所有文件从工作表转换为 CSV 并添加到新文件夹”中将以下代码拼凑在一起。但是我有一个问题——当我尝试运行它时，我收到一条错误消息

GoogleJsonResponseException：对 drive.files.copy 的 API 调用失败并出现错误：找不到文件：[SheetsFolder 的名称]

任何人都可以通过查看此代码来判断错误是什么？

function makeCSV() {
  //Add folder ID to select the folder where excel files are placed
  var SourceFolder = DriveApp.getFolderById("12XqAVzwzgl4a7Z1s6zn5VRcHiZ1vQdsT")
  //Add folder ID to save the where csv files to be placed
  var DestinationFolder = DriveApp.getFolderById("1Z_mUXs3FFDTN7XLk8EN9D5QOxVD8lZfB")
  //Add folder ID to save where the google sheets to be placed
  var SheetsFolder = DriveApp.getFolderById("17k_bl7Of2jH3U_TjaiiY_8BBOds4uSbT")

  //Select the XLS and XLSX files from source folder using the Mimetype of Microsoft Excel
  var searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'";
  var sourceFiles = SourceFolder.searchFiles(searchQuery);

  var now = new Date(); //get current time after you fetch the file list from Drive.

  //Get script properties and check for stored "last_execution_time"
  var properties = PropertiesService.getScriptProperties();
  var cutoff_datetime = properties.getProperty('last_execution_time');

  //if we have last execution date, stored as a string, convert it to a Date object.
  if(cutoff_datetime)
     cutoff_datetime = new Date(cutoff_datetime);

  //Loop over all the XLS files
  while (sourceFiles.hasNext()){
    var sourceFile = sourceFiles.next();

    //if no stored last execution, or file is newer than last execution, process the file.
    if(!cutoff_datetime || file.getDateCreated() > cutoff_datetime){
      var fileId = sourceFile.getId();

      //Copy the XLS file to the Sheets Folder as a google sheet 
      var spreadsheet = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: SheetsFolder}]}, fileId);

      //Get the data from the google sheet using the SpreadsheetApp
      var sheets = SpreadsheetApp.openById(spreadsheet.id).getSheets();sheets[0].getDataRange().getValues()

      //Create a csv file using the data from the google sheet     
      var csv = sheets.map(function(sheet) {return sheet.getDataRange().getValues().reduce(function(csv, row) {return csv += row.join(",") + "\n"}, "")}); DestinationFolder.createFile(spreadsheet.title + ".csv", csv, MimeType.CSV)
    }
  }

  //store "now" as last execution time as a string, to be referenced on next run.
  properties.setProperty('last_execution_time',now.toString());
} 

非常感谢任何帮助！

编辑 1：感谢 Tanaike 帮助解决问题！上面的代码有两个问题：一个是 Tanaike 在他的评论中指出的问题。第二个是“file”应该在if语句中读取“sourceFile”。我在下面发布正确的代码以供后代使用。

function makeCSV() {
  //Add folder ID to select the folder where excel files are placed
  var SourceFolder = DriveApp.getFolderById("12XqAVzwzgl4a7Z1s6zn5VRcHiZ1vQdsT")
  //Add folder ID to save the where csv files to be placed
  var DestinationFolder = DriveApp.getFolderById("1Z_mUXs3FFDTN7XLk8EN9D5QOxVD8lZfB")
  //Add folder ID to save where the google sheets to be placed
  var SheetsFolder = DriveApp.getFolderById("17k_bl7Of2jH3U_TjaiiY_8BBOds4uSbT")

  //Select the XLS and XLSX files from source folder using the Mimetype of Microsoft Excel
  var searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'";
  var sourceFiles = SourceFolder.searchFiles(searchQuery);

  var now = new Date(); //get current time after you fetch the file list from Drive.

  //Get script properties and check for stored "last_execution_time"
  var properties = PropertiesService.getScriptProperties();
  var cutoff_datetime = properties.getProperty('last_execution_time');

  //if we have last execution date, stored as a string, convert it to a Date object.
  if(cutoff_datetime)
     cutoff_datetime = new Date(cutoff_datetime);

  //Loop over all the XLS files
  while (sourceFiles.hasNext()){
    var sourceFile = sourceFiles.next();

    //if no stored last execution, or file is newer than last execution, process the file.
    if(!cutoff_datetime || sourceFile.getDateCreated() > cutoff_datetime){
      var fileId = sourceFile.getId();

      //Copy the XLS file to the Sheets Folder as a google sheet 
      var spreadsheet = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id:"17k_bl7Of2jH3U_TjaiiY_8BBOds4uSbT"}]}, fileId);

      //Get the data from the google sheet using the SpreadsheetApp
      var sheets = SpreadsheetApp.openById(spreadsheet.id).getSheets();sheets[0].getDataRange().getValues()

      //Create a csv file using the data from the google sheet     
      var csv = sheets.map(function(sheet) {return sheet.getDataRange().getValues().reduce(function(csv, row) {return csv += row.join(",") + "\n"}, "")}); DestinationFolder.createFile(spreadsheet.title + ".csv", csv, MimeType.CSV)
    }
  }

  //store "now" as last execution time as a string, to be referenced on next run.
  properties.setProperty('last_execution_time',now.toString());
} 

标签： csvgoogle-apps-scriptgoogle-sheetsgoogle-drive-apixlsx