根据单元格内的值删除行，进行优化 [英] Delete rows based on values within cell, optimising

问题描述

我每周运行一次脚本，从文档中删除行，然后将这些值粘贴到另一张纸上进行数据分析，但是随着文档的增长(+20,000行)，我的脚本超时了.无论如何如何优化脚本以使用更少的处理/内存并更快地运行?

I have script running on a weekly bases deleting rows from a document and then pasting these values to another sheet for data analysis, however as the document grows (+20,0000 rows) my script times out. Anyway on how to optimise the script to perhaps use less processing/memory and run faster?

var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[0] == 'delete' || row[0] == '') {
data.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}

推荐答案

在您的情况下，我理解如下.

In your situation, I understand as follows.

• 有很多行被删除.
• 行不继续.因此必须使用deleteRow()逐行删除行.

• There are a lot of rows which are deleted.
• The rows are not continued. So the rows have to be deleted row by row using deleteRow().

如果我的理解是正确的，那么该修改如何?

If my understanding is correct, how about this modification?

• 使用Sheets API的batchUpdate.
  • 通过使用Sheets API，可以通过一个API调用删除离散行.
  • Use batchUpdate of Sheets API.
    • By using Sheets API, the discrete rows can be deleted by one API call.

    要使用表格API，请在高级Google服务和API控制台上启用表格API.有关如何启用它们的信息，请在此处查看

    In order to use Sheets API, please enable Sheets API at Advanced Google Services and API console. About the how to enable them, please check here.

    var spreadsheetId = "#####"; // Please input spreadsheet ID.
    var sheetId = "#####"; // Please input sheet ID.
    
    var deleteRows = [];
    for (var i = values.length - 1; i >= 0; i--) {
      if (values[i] == "delete" || values[i] == "") {
        deleteRows.push({
          "deleteRange": {
            "shiftDimension": "ROWS",
            "range": {
              "startRowIndex": i,
              "endRowIndex": i + 1,
              "sheetId": sheetId
            }
          }
        });
      }
    }
    Sheets.Spreadsheets.batchUpdate({"requests": deleteRows}, spreadsheetId);
    

    注意:

    • 在此修改后的脚本中，它假定从Spreadsheet检索的值在values中.
    • 运行此操作之前，请输入包含要删除的行的电子表格ID和工作表ID.

    Note :

    • In this modified script, it supposes that the retrieved values from Spreadsheet is in values.
    • Before you run this, please input spreadsheet ID and sheet ID having the rows you want to delete.

    如果这不是您想要的结果，对不起.

    If this was not result what you want, I'm sorry.

    这篇关于根据单元格内的值删除行，进行优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！