使用Google Script将多个CSV文件导入到Google表格中 [英] Importing multiple CSV files to google sheets with Google Script

问题描述

我正在研究一个Google脚本，以将CSV文件从Web URL导入到Google工作表中的各个工作表中.我想在导入新信息之前清除某些列的内容.在导入之前，我很难让脚本清除内容.我还包含了一个CSV的一部分以及另一个在工作表上的CSV.我想我需要在CSV文件之间的脚本中添加一些内容.在导入之前，我还需要添加一些内容以清除内容.我不想只清除工作表，因为有一些公式需要保留.我也不想使用Google IMPORTDATA函数，因为它不可靠.

I am working on a google script to import CSV files from web urls to individual sheets in a google sheet. I would like to have it clear the contents of certain columns before importing the new info. I had had trouble getting the script to clear contents prior to importing. I have also had in include part of one CSV along with another on a sheet. I think I need to add something to the script between CSV files. I also need something added to clear contents prior to importing. I don't want to just clear the sheet because there are formulas that need to remain. I also don't want to use the Google IMPORTDATA function because it is unreliable.

这是我当前的脚本(已删除网址):

Here is my current script (URLs removed):

function importCSVFromWeb() {

  var csvUrl = "http://csvurlhere";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var ss = SpreadsheetApp.openByUrl('spreadsheeturlhere');
  var sheet = SpreadsheetApp.getActiveSheet();
  var ss = sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

  var csvUrl = "http://csvurlhere";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var sheet = SpreadsheetApp.getActive().getSheetByName('RentPaid');
  var ss = sheet.getRange(3, 3, csvData.length, csvData[0].length).setValues(csvData);

  var csvUrl = "http://csvurlhere";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var sheet = SpreadsheetApp.getActive().getSheetByName('Tenants');
  var ss = sheet.getRange(1, 2, csvData.length, csvData[0].length).setValues(csvData);

  var csvUrl = "http://csvurlhere";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var sheet = SpreadsheetApp.getActive().getSheetByName('Owners');
  var ss = sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

推荐答案

要清除工作表的内容或工作表中的一系列单元格，可以使用

To clear the contents of a sheet or a range of cells within a sheet, you can use sheet.clearContents() or range.clearContent(), which will remove only values & leave your formatting & formulas in tact.

我注意到您的行var ss = SpreadsheetApp.openByUrl('spreadsheeturlhere');不执行任何操作，因为您随后重新声明了ss变量.如果脚本已绑定到电子表格，则不需要此行作为对getActive()& getActiveSheet()将为您提供对主电子表格的引用.无论如何，您对SpreadsheetApp.getActive().getSheetByName()的调用都将返回对主电子表格的引用，而不是您通过URL打开的那个电子表格.

I note that your line var ss = SpreadsheetApp.openByUrl('spreadsheeturlhere'); does nothing as you subsequently redeclare the ss variable. If the script is bound to a spreadsheet, you don't need this line as calls to getActive() & getActiveSheet() will give you a reference to the host spreadsheet. Your calls to SpreadsheetApp.getActive().getSheetByName() are returning references to the host spreadsheet anyway, not the one you open by URL.

我还考虑将您的功能更改为接受CSV源URL，工作表名称和&单元锚作为参数，因此代码更易于维护.例如

I would also consider changing your function to accept the CSV source URLs, sheet names & cell anchors as parameters so that the code is easier to maintain. e.g.

function importCSVFromWeb(csvUrl, sheetName, dataAnchor) {
  /* csvUrl:     the CSV source URL
     sheetName:  the name of the sheet to add the data
     dataAnchor: the cell origin for the range.setValues() call as an array 
                 i.e. dataAnchor = [row, col]
  */
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var ss = SpreadsheetApp.getActive();
  // note that you'll need to pass in a name for each sheet, 
  //   including the first one where you used SpreadsheetApp().getActiveSheet() previously
  var sheet = ss.getSheetByName(sheetName);
  sheet.getRange(dataAnchor[0], dataAnchor[1], csvData.length, csvData[0].length).setValues(csvData);
}

function feedCSVImport(){
  var urls_sheets_list = [["csv_source_url_1", "Sheet1", [1,1]],
                          ["csv_source_url_2", "RentPaid", [3,3]],
                          ["csv_source_url_3", "Tenants", [1,2]],
                          ["csv_source_url_4", "Owners", [1,1]]
                         ];
  for(var i = 0, lim = url_sheets_list.length; i < lim; ++i){
    importCSVFromWeb(url_sheets_list[i][0], url_sheets_list[i][1], url_sheets_list[i][2]);
  }
}

这篇关于使用Google Script将多个CSV文件导入到Google表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！