如果单元格包含"DEL"，则用于删除行的更有效脚本被删除.

本文介绍了如果单元格包含"DEL"，则用于删除行的更有效脚本被删除.的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有超过50,000行的Google表格，我每天都会使用新数据进行更新.在一天结束时，我运行一个"IF"函数，该函数可以帮助我确定是否要删除该行.

I have a 50,000+ row Google Sheet that I update every day with new data. At the end of the day I run an "IF" function that helps me determine if I want to delete that row.

然后，我编写了一个脚本，该脚本可浏览工作表并删除特定列中具有"DEL"的任何行.问题在于，由于我有很多行，因此脚本花费的时间太长了.是否有人想出一种更有效的方式来删除/清除行(如果单元格中包含字母"DEL")?

I then wrote a script that looks through the sheet and deletes any row that has "DEL" in the specific Column. The problem is that since I have so many rows, the script takes too long to run. Anyone have any ideas of a more efficient way to delete/clear a row if a cell has the letters "DEL"?

    function deleteRows() {
      var sheet = SpreadsheetApp.getActive().getSheetByName('DEL_test');
      var rows = sheet.getDataRange();
      var numRows = rows.getNumRows();
      var values = rows.getValues();

      var rowsDeleted = 0;
      for (var i = 0; i <= numRows - 1; i++) {
        var row = values[i];
        if (row[9] == 'DEL') { // Search cells in Column "J" and delete row if cell is equal to "DEL"
          sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
          rowsDeleted++;
        }
      }
      Browser.msgBox('COMPLETE');
    };

推荐答案

您想有效地删除"J"列中带有DEL的行.

You want to efficiently delete rows with DEL in the column "J".

如何使用Sheets API?使用Sheets API时，一个API调用可以删除几行.这样，将能够降低处理成本.我认为针对您的情况有几种解决方法.因此，请仅考虑其中之一.

How about using Sheets API? When Sheets API is used, several rows can be deleted by one API call. By this, the process cost will be able to be reduced. I think that there are several workarounds for your situation. So please think of this as just one of them.

使用表格API时，请在高级Google服务和API控制台上启用表格API.您可以在此处 中查看如何启用表格API.

When you use Sheets API, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DEL_test');
  var rows = sheet.getDataRange();
  var values = rows.getValues();
  var spreadsheetId = ss.getId();
  var sheetId = sheet.getSheetId();
  var reqs = values.reduceRight(function(ar, e, i) {
    if (e[9] == 'DEL') {
      ar.push({"deleteDimension":{"range":{
        "sheetId": sheetId,
        "dimension": "ROWS",
        "startIndex": values.length - i - 1,
        "endIndex": values.length - i,
      }}});
    }
    return ar;
  }, []);
  Sheets.Spreadsheets.batchUpdate({"requests": reqs}, spreadsheetId);  
  Browser.msgBox('COMPLETE');
}

参考文献:

spreadsheets.batchUpdate
DeleteDimensionRequest

References:

spreadsheets.batchUpdate
DeleteDimensionRequest

如果我误解了您的问题，请告诉我.我想修改它.

If I misunderstood your question, please tell me. I would like to modify it.

如果您不想使用Sheets API，并且想使用clearContent()，那么此示例脚本如何?该示例脚本的流程如下.

If you don't want to use Sheets API and you want to use clearContent(), how about this sample script? The flow of this sample script is as follows.

检索所有值.
仅检索"J"列中没有"DEL"的行.
输入值.

示例脚本:

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DEL_test');
  var rows = sheet.getDataRange();
  var values = rows.getValues();
  sheet.clearContents();
  var val = values.filter(function(e) {return e[9] != 'DEL'});
  sheet.getRange(1, 1, val.length, val[0].length).setValues(val);
  Browser.msgBox('COMPLETE');
}

注意:

我不确定您的实际电子表格.因此，如果这不是您想要的结果，可以提供示例电子表格吗?这样，我想修改脚本.

这篇关于如果单元格包含"DEL"，则用于删除行的更有效脚本被删除.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！