根据日期删除单元格 [英] Delete Cells Based on Date

问题描述

我需要有关单元格删除脚本的帮助.通常，我想运行一个重置脚本，以清除直到运行它之前的所有数据.因为我正在向与过滤器中的信息匹配的那些单元格中静态输入值，所以我相信我需要删除那些单元格，以便在我从导出页面删除过期的行后正确地将输入与过滤器信息的位置对齐

I need a help with a cell-deletion script. In general, I want to run a reset script that clears out all of the data up to the day I run it. Because I am statically inputting values into those cells that are matching up with information from a filter, I believe I need to delete those cells to properly line up my inputs with where the filter information will be after I delete the expired rows from the exporting page.

这是我要在脚本中执行的操作:如果Column F值<今天的日期，然后删除I，J和K中的单元格并将其下方的单元格向上移动.我想我找到了执行此操作的代码，但是运行该程序需要花费很长时间，以至于该程序无法通过多行才超时.我将使用for循环在73页上运行它，因此，如果它滞后于一个页面...是的，我需要帮助！

Here's what I want to do in my script: If the Column F value < today's date, then delete the cells in I, J, and K and shift the cells below them up. I think I found code to do this, but it takes so long to run that the program times out before it can get through more than a few rows. I will use a for loop to run it over 73 pages, so if it is lagging out on one...yeah, I need help!

function deleteEntries() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var datarange = ss.getDataRange();
  var lastrow = datarange.getLastRow();
  var values = datarange.getValues();

  var currentDate = new Date();

  for (i = lastrow; i >= 5; i--) {
    var tempdate = values[i-1][5];

    if (tempdate < currentDate)  
    {
      ss.getRange(i-1, 8).deleteCells(SpreadsheetApp.Dimension.ROWS);
      ss.getRange(i-1, 9).deleteCells(SpreadsheetApp.Dimension.ROWS);
      ss.getRange(i-1, 10).deleteCells(SpreadsheetApp.Dimension.ROWS);
}}}

推荐答案

根据Apps脚本最佳做法" ，您将希望限制电子表格服务的使用以缩短执行时间.可以考虑两种即时"优化:

In accordance with Apps Script "best practices", you will want to limit the use of the Spreadsheet Service to improve execution times. There are two "immediate" optimizations that can be considered:

1. 连续删除多个单元格 为此，只需选择一个1行x 3列范围:ss.getRange(i-1, 8, 1, 3)而不是选择(i-1, 8)，(i-1, 9)，(i-1, 10)并分别在三个Ranges上分别调用deleteCells.
2. 在删除表格之前先对其进行排序，以便仅需要1个删除电话(例如 C ++ stdlib删除"这个习语).如果您的数据是根据F列排序的，则所有应删除的数据都在末尾，那么您只需要迭代内存中的数组(非常快速过程)即可找到首先应删除的日期，然后删除&以下的所有数据.包括它.

1. Delete more than 1 cell at a time in a row To do this, simply select a 1-row x 3-column range: ss.getRange(i-1, 8, 1, 3) instead of selecting (i-1, 8), (i-1, 9), (i-1, 10) and calling deleteCells on each of the three Ranges.
2. Sort your sheet before deleting such that only 1 delete call is necessary (e.g. the C++ stdlib "erase-remove" idiom). If your data is sorted based on column F, such that all data that should be removed is at the end, then you simply need to iterate the in-memory array (a very fast process) to locate the first date that should be removed, and then remove all the data below & including it.

选项2的实现如下所示(我假设您使用冻结的标题，因为在对工作表或范围进行排序时它们不会移动.

An implementation of option 2 would look like this (I assume you use frozen headers, as they do not move when the sheet or range is sorted).

function sortDescAndGetValuesBack_(s, col) {
  return s.getDataRange().sort({column: col, ascending: false}).getValues();
}
function deleteAllOldData() {
  const sheets = SpreadsheetApp.getActive().getSheets()
      .filter(function (sheet) { /** some logic to remove sheets that this shouldn't happen on */});
  const now = new Date();
  const dim = SpreadsheetApp.Dimension.ROWS;

  sheets.forEach(function (sheet) {
    var values = sortDescAndGetValuesBack_(sheet, 6); // Col 6 = Column F
    for (var i = sheet.getFrozenRows(), len = values.length; i < len; ++i) {
      var fVal = values[i][5]; // Array index 5 = Column 6
      if (fVal && fVal < now) { // if equality checked, .getTime() is needed
        console.log({message: "Found first Col F value less than current time",
                     index: i, num2del: len - i, firstDelRow: values[i],
                     currentTime: now, sheet: sheet.getName()});
        var delRange = sheet.getRange(1 + i, 8, sheet.getLastRow() - i, 3);
        console.log({message: "Deleting range '" + sheet.getName() + "!" + delRange.getA1Notation() + "'"});
        delRange.deleteCells(dim);
        break; // nothing left to do on this sheet.
      }
    }
    console.log("Processed sheet '" + sheet.getName() + "'");
  });
}

参考:

• Array#filter
• Array#forEach
• Range#sort
• Range#deleteCells

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