时间戳

首页 > 解决方案 > 如何将大型数组拆分为较小的数组以进行批处理，以便 setValues() 不会超时？

google-apps-script - 如何将大型数组拆分为较小的数组以进行批处理，以便 setValues() 不会超时？

问题描述

以下代码改编自其他地方：

function importCSVFromWeb() {
  var csvUrl = "https://drive.google.com/uc?export=download&id=xxxxxxxxxxxxxxxxxxxxxxxxxx";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('data');
  sheet.clear();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

快速执行，直到最后一行中的 SetValues()：

[19-12-07 13:45:09:093 PST] Starting execution
[19-12-07 13:45:10:463 PST] UrlFetchApp.fetch([https://drive.google.com/uc?export=download&id=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx]) [1.36 seconds]
[19-12-07 13:45:10:538 PST] UrlFetchApp.HTTPResponse.getContentText() [0.074 seconds]
[19-12-07 13:45:10:976 PST] Utilities.parseCsv([ColAHeader,ColBHeader,ColCHeader,ColDHeader,ColEHeader,ColFHeader,ColGHeader,ColHHeader,ColIHeader,ColJHeader,ColKHeader,ColLHeader,ColMHeader
aaa,bbb,ccc,ddd,eee,fff,ggg,hhh,...) [0.426 seconds]
[19-12-07 13:45:11:504 PST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-12-07 13:45:11:715 PST] SpreadsheetApp.Spreadsheet.getSheetByName([data]) [0.21 seconds]
[19-12-07 13:45:11:716 PST] SpreadsheetApp.Sheet.clear() [0 seconds]
[19-12-07 13:45:11:716 PST] SpreadsheetApp.Sheet.getRange([1, 1, 106974, 13]) [0 seconds]
[19-12-07 13:51:23:063 PST] SpreadsheetApp.Range.setValues([[[ColAHeader,ColBHeader,ColCHeader,ColDHeader,ColEHeader,ColFHeader,ColGHeader,ColHHeader,ColIHeader,ColJHeader,ColKHeader,ColLHeader,ColMHeader], [aaa, bbb, ccc, ddd...) [370.621 seconds]
[19-12-07 13:51:23:241 PST] Execution failed: Exceeded maximum execution time [373.963 seconds total runtime]

其他信息：

超时是想要拆分数组的原因。我想处理 20,000 行然后退出。然后创建一个处理第 20,001-40,000 行的新脚本。

我曾尝试手动将 getRange 的第三个参数更改为 20,000，但我显然不太了解该命令，因为它给了我一个错误：

数据中的行数与范围内的行数不匹配。数据有 106974，但范围有 20000。

有没有办法拆分数组以便我可以批量处理它？

标签： google-apps-scriptgoogle-sheets

解决方案

  • 您想将 CSV 值放入活动的电子表格中。
    • 在您的情况下，CSV 值很大，即 106,974 行和 13 列。
  • 您想使用 Google Apps 脚本实现此目的。

如果我的理解是正确的，这个答案怎么样？在这个答案中，我想提出 2 种模式。请认为这只是几个可能的答案之一。

模式一：

在此模式中，CSV 数据使用 Sheets API 直接放入活动的电子表格中。在测量使用 Google Apps 脚本的 CSV 数据到电子表格的基准测试时，发现 Sheets API 的处理速度比您脚本中使用的电子表格服务的处理速度快。所以作为模式之一，我提出了这种方式。

示例脚本：

当您的脚本被修改为使用 Sheets API 时，它变为如下。在运行脚本之前，请在高级 Google 服务中启用 Sheets API。

function importCSVFromWeb() {
  var csvUrl = "https://drive.google.com/uc?export=download&id=xxxxxxxxxxxxxxxxxxxxxxxxxx";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = ss.getSheetByName('data').getSheetId();
  var resource = {requests: [
    {updateCells: {range: {sheetId: sheetId}, fields: "*"}},
    {pasteData: {data: csvContent, coordinate: {sheetId: sheetId}, delimiter: ","}}
  ]};
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
}
  • 请将 CSV 文件的文件 ID 设置为csvUrl.

模式二：

在此模式中，CSV 数据使用文件的方法转换为电子表格：Drive API 的副本并将工作表复制到活动电子表格。

示例脚本：

当你的脚本被修改后，它变成如下。在运行脚本之前，请在高级 Google 服务中启用 Drive API。

function importCSVFromWeb() {
  var fileIdofCSVFile = "xxxxxxxxxxxxxxxxxxxxxxxxxx";
  var id = Drive.Files.copy({title: "temp", mimeType: MimeType.GOOGLE_SHEETS}, fileIdofCSVFile).id;
  var sheetName = "data";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName).setName(sheetName + "_temp");
  var tempSheet = SpreadsheetApp.openById(id).getSheets()[0];
  var copiedSheet = tempSheet.copyTo(ss).setName(sheetName);
  ss.deleteSheet(sheet);
  DriveApp.getFileById(id).setTrashed(true);
}
  • 请将 CSV 文件的文件 ID 设置为fileIdofCSVFile.

笔记：

  • 在您的脚本中，它假定 CSV 文件https://drive.google.com/uc?export=download&id=xxxxxxxxxxxxxxxxxxxxxxxxxx是公开共享的。请注意这一点。

参考：

补充：模式3

在此示例脚本中，修改了模式 2 的脚本。在这种情况下，使用 Drive API 和 Sheets API。因此，请在高级 Google 服务中启用这两个 API。

示例脚本：

function importCSVFromWeb() {
  var fileIdofCSVFile = "xxxxxxxxxxxxxxxxxxxxxxxxxx";
  var id = Drive.Files.copy({title: "temp", mimeType: MimeType.GOOGLE_SHEETS}, fileIdofCSVFile).id;
  var sheetName = "data";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  sheet.clear();
  SpreadsheetApp.flush();
  var tempSheet = SpreadsheetApp.openById(id).getSheets()[0];
  var copiedSheet = tempSheet.copyTo(ss).getSheetId();
  var resource = {requests: [
    {copyPaste: {
      source: {sheetId: copiedSheet, startRowIndex: 0, startColumnIndex: 0},
      destination: {sheetId: sheet.getSheetId(), startRowIndex: 0, startColumnIndex: 0}
    }},
    {deleteSheet: {sheetId: copiedSheet}}
  ]};
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
  DriveApp.getFileById(id).setTrashed(true);
}

推荐阅读