复制和从Google云端硬盘上的CSV文件替换/插入大量数据到SpreadSheet [英] Copy & replace/insert loads of data from CSV file located on Google Drive to SpreadSheet

问题描述

我从另一项服务中获取数据到Google云端硬盘.其格式为CSV，并保存在Google云端硬盘中的.csv中.每个文件的大小约为5MB，> 17K行！.我尝试使用导入"脚本，但是没有用，它只能处理较小的文件，但我只想将整个csv复制到电子表格中，而不必关心旧数据.

I fetch data from another service to Google Drive. Its format is CSV and being saved in .csv on Google Drive. The size for each file around 5MB and >17K rows!. I tried to use Import script but it didn't work, it worked with smaller file but I just want to copy the whole csv to my spreadsheet without caring about old data.

function import() {
   var fSource = DriveApp.getFolderById("****"); // reports_folder_id = id of folder where csv reports are saved
  var fi = fSource.getFilesByName('report.csv'); // latest report file
  var ss = SpreadsheetApp.openById("***"); // data_sheet_id = id of spreadsheet that holds the data to be updated with new report data
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
  if ( fi.hasNext() ) { // proceed if "report.csv" file exists in the reports folder
    var file = fi.next();
    var csv = file.getBlob().getDataAsString();
    var csvData = CSVToArray(csv); // see below for CSVToArray function
        for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
      sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    }
    /*
    ** report data is now in 'NEWDATA' sheet in the spreadsheet - process it as needed,
    ** then delete 'NEWDATA' sheet using ss.deleteSheet(newsheet)
    */
    // rename the report.csv file so it is not processed on next scheduled run
   // file.setName("report-"+(new Date().toString())+".csv");
  }
};

推荐答案

我猜想您的脚本如果正在处理那么多行，可能会超时.您可以做一些优化代码的事情:

I'm guessing your script is probably timing out if it is processing that many lines. Few things you can do to optimise your code:

• 用内置的Utilities.parseCsv()函数替换CSVToArray().
• 将getRange().setValues()移出循环，将所有内容写入数组，然后在一次调用中将其写入工作表.
• 如果仍然太慢，请查看

• Replace CSVToArray() with the built-in Utilities.parseCsv() function.
• Move the getRange().setValues() out of the loop, write everything to an array and then write that to the sheet in one call.
• If it is still too slow look at the Continuous Batch Library which will allow you to break up the processing into smaller chunks

这篇关于复制和从Google云端硬盘上的CSV文件替换/插入大量数据到SpreadSheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！