使用 Google Apps 脚本将多个 CSV 文件导入 Google Sheets

Question

所以我正在为我的家庭管理做一个个人项目。我想在打开电子表格 (adminSheet.gs) 时自动导入 .csv 文件。可以手动执行此操作，但我希望自动执行此操作，因为我必须导入 30 多个 .csv 文件，而且还会有更多文件。我已启用谷歌应用程序脚本（GAS）。然而，GAS 对我来说是新的，我对 Javascript 有点熟悉，但是自从我上大学以来已经有一段时间了..我希望你能帮助我！以下是事实。我有多个 .csv 文件，每个文件都有不同的名称。它们位于名为 CSVfolder 的同一文件夹中。此文件夹位于 Google 云端硬盘中的主文件夹 MAINfolder 中。它们由分隔符分隔；（分号）。CSV 文件具有相同的数据结构，并且每个 .csv 文件的第一行相同。第一行已放入工作表 adminSheet.gs 中，作为标题行，称为 HEADER。因此，导入时需要删除每个 .csv 文件的第一行。我将在 CSV 文件夹中上传新的 .csv 文件。这些文件名遵循以下命名结构。ID_01-09-2020_30-09-2020.csv 或 ID_01-10-2020_31-10-2020.csv。ID 后跟更改日期。（.csv 数据将针对该时间段。不会发生重叠或重复的数据，因为我将手动选择数据集）。然而，这当然会让它变得非常整洁。当我打开 adminSheet.gs（位于 MAINfolder 中）时，它需要自动识别任何新的 .csv 文件并将其与所有其他（之前导入的）.csv 数据合并到现有的 adminSheet.gs 中。新的 .csv 文件应合并到固定标题行 HEADER 下方和先前导入的 .csv 文件上方。（这也可以解释为：在 HEADER 和 adminSheet.gs 中已有的数据之间）（这也可以解释为：.csv 中的新数据应位于 adminSheet.gs 中已有的旧数据之上）。什么 Google Apps 脚本可以做到这一点？

Answer 1

以下代码的工作原理如下：

• 从 csv 文件中获取数据作为数组

• 在标题行后插入多行

• 在工作表中设置数组

• 导入 csv 文件后将其移至存档文件夹，这样就不会再次导入它们

• 然后移至下一个文件，直到没有更多文件可供导入

您需要创建一个存档文件夹来存储导入后的 csv 文件。为此，我通常会在包含 csv 文件夹的文件夹中创建一个。

在代码中，您需要提供存档文件夹和根文件夹（包含 CSV 文件夹的文件夹）的文件夹 ID 以及电子表格中目标工作表的名称。这些都可以在代码中的前四个变量中设置。（我已经csvFolderName根据您上面提供的信息设置为“CSVfolder”。

保存此代码后，在其上创建一个触发器以在打开时运行。

这是代码：

function importCSVFiles()

{

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  const csvFolderName = 'CSVfolder';                                       // Folder containing csv files and the Archive folder

  const rootFolderId = 'id_of_folder_containing_CSVfolder';                // Root folder

  const archiveFolderId = 'id_of_archiveFolder';                           // Archive Folder

  const destinationSheetName = 'name_of_destination_sheet_in_spreadsheet'; // destination sheet in spreadsheet

  

  const root = DriveApp.getFolderById(rootFolderId);

  const archiveFolder = DriveApp.getFolderById(archiveFolderId);

  

  const fileName =/ *.csv/;

  

  var rootFolders = root.getFolders();

  

  const sheet = ss.getSheetByName(destinationSheetName);

  var csvArray = [];

  

  while (rootFolders.hasNext()) 

  {

    var folder = rootFolders.next ();

    Logger.log('folder.getName()',folder.getName());

    if (folder.getName() === csvFolderName) 

    {

      importCSV(folder);

    }

    else 

    {

      var csvFolder = folder.getFoldersByName(csvFolderName);

      while (csvFolder.hasNext()) 

      {

        importCSV(csvFolder.next());

      }

    }

  }

  

  function importCSV(folder) 

  {

    var files = folder.getFiles();

    while (files.hasNext()) 

    {

      var file = files.next();

      Logger.log('file.getName()',file.getName());

      csvArray = file.getBlob().getDataAsString("UTF-8");

      csvArray = Utilities.parseCsv(csvArray);

      csvArray.shift();

      sheet.insertRowsAfter(1,csvArray.length);

      sheet.getRange (2,1,csvArray.length,csvArray[0].length).setValues(csvArray);

      

      file.moveTo(archiveFolder);

    }

  }

}

如果您似乎对分隔符有问题，您可能需要通过将上面的代码行替换为来将其专门设置为自定义分隔csvArray = Utilities.parseCsv(csvArray);符csvArray = Utilities.parseCsv(csvArray,';');