如果所有单元格都具有Apps Script值，清除行的最有效方法是什么?

问题描述

我正在尝试提出一个函数，如果范围中的所有单元格都具有值，则该函数将清除内容(而不是删除行).以下脚本无法正常运行，非常感谢大家提供的帮助/建议.它目前仅清除一行，并且似乎没有遍历整个数据集.我的想法是遍历行，并分别检查每个单元格.如果每个变量都有一个值，请清除该范围并转到下一行.

I'm trying to come up with a function that will clear contents (not delete row) if all cells in a range have values. The script below isn't functioning as expected, and I would really appreciate any help/advice you all have. It's currently only clearing out a single line, and doesn't appear to be iterating over the whole dataset. My thought was to iterate over the rows, and check each cell individually. If each of the variables has a value, clear that range and go to the next row.

这是一个链接到示例Google表格，以及脚本编辑器中的数据和脚本.

Here's a link to a sample Google Sheet, with data and the script in Script Editor.

function MassRDDChange() {
       // Google Sheet Record Details
       var ss = SpreadsheetApp.openById('1bcrEZo3IkXiKeyD47C_k2LIRy9N9M6SI2h2MGK1Cj-w');
       var dataSheet = ss.getSheetByName('Data Entry');

       // Initial Sheet Values
       var newLastColumn = dataSheet.getLastColumn();
       var newLastRow = dataSheet.getLastRow();
       var dataToProcess = dataSheet.getRange(2, 1, newLastRow, newLastColumn).getValues().filter(function(row) {
           return row[0]
       }).sort();
       var dLen = dataToProcess.length;

       // Clear intiial sheet
       for (var i = 0; i < dLen; ++i) {
           var row = 2;
           var orderNumber = dataToProcess[i][0].toString();
           var rdd = dataToProcess[i][1].toString();
           var submittedBy = dataToProcess[i][2].toString();
           var submittedOn = dataToProcess[i][3].toString();
           if (orderNumber && rdd && submittedBy && submittedOn) {
               dataSheet.getRange(row, 1, 1, newLastColumn).clear();
               row++;
           } else {
               row++; // Go to the next row
               continue;
           }
       }
   }

谢谢！

推荐答案

由于您不想删除行，只需clear()将它们删除，并且它们都位于同一工作表选项卡上，因此这是一个很好的用途 RangeList s 的情况，您可以申请不连续的Range的特定Range方法.目前，创建RangeList 来自一系列引用符号(即RangeList与 Range对象的数组的数组)，因此我们的首要目标是为工作表数据的JavaScript数组添加前缀，以使用可用的参考字符串进行检查.我们可以编写一个函数来将数组索引从0基整数转换为A1表示法，但是R1C1引用完全有效，可以传递给RangeList构造函数，因此我们只需要考虑标头行和0基vs 1-基本索引差异.

Since you don't want to delete the rows, just clear() them, and they're all on the same worksheet tab, this is a great use case for RangeLists, which allow you to apply specific Range methods to non-contiguous Ranges. Currently, the only way to create a RangeList is from a an array of reference notations (i.e. a RangeList is different than an array of Range objects), so the first goal we have is to prefix our JavaScript array of sheet data to inspect with a usable reference string. We could write a function to convert array indices from 0-base integers to A1 notation, but R1C1 referencing is perfectly valid to pass to the RangeList constructor, so we just need to account for header rows and the 0-base vs 1-base indexing difference.

那么，策略是:

1. 批量读取工作表数据到JavaScript Array
2. 用R1C1字符串标记数组的每个元素(即每一行)，该字符串标识该元素的来源位置.
3. Filter基于每个元素内容的工作表数据数组
   • 保留将每个子元素(该行中的列值)转换为布尔值(即与空单元格的值不同)的元素

1. Batch-read sheet data into a JavaScript Array
2. Label each element of the array (i.e. each row) with an R1C1 string that identifies the location where this element came from.
3. Filter the sheet data array based on the contents of each element
   • Keep elements where each sub-element (the column values in that row) converts to a boolean (i.e., does not have the same value as an empty cell)

由于此方法仅使用3个电子表格调用(除了用于批量读取的初始设置)，而每行只需要1个，因此它应该要快得多.

Because this approach uses only 3 Spreadsheet calls (besides the initial setup for a batch read), vs 1 per row to clear, it should be considerably faster.

function clearFullyFilledRows() {
  // Helper function that counts the number of populated elements of the input array.
  function _countValues(row) {
    return row.reduce(function (acc, val) {
      var hasValue = !!(val || val === false || val === 0); // Coerce to boolean
      return acc + hasValue; // true == 1, false == 0
    }, 0);
  }
  const sheet = SpreadsheetApp.getActiveSheet();
  const numHeaderRows = 1,
      numRows = sheet.getLastRow() - numHeaderRows;
  const startCol = 1,
      numCols = sheet.getLastColumn();

  // Read all non-header sheet values into a JavaScript array.
  const values = sheet.getSheetValues(1 + numHeaderRows, startCol, numRows, numCols);

  // From these values, return a new array where each row is the origin
  // label and the count of elements in the original row with values.
  const labeledCounts = values.map(function(row, index) {
    var rNc = "R" + (numHeaderRows + 1 + index) + "C";
    return [
      rNc + startCol + ":" + rNc + (startCol + numCols - 1),
      _countValues(row)
    ];
  });

  // Filter out any row that is missing a value.
  const toClear = labeledCounts.filter(function (row) { return row[1] === numCols; });

  // Create a RangeList from the first index of each row (the R1C1 label):
  const rangeList = sheet.getRangeList(toClear.map(function (row) { return row[0]; }));
  // Clear them all:
  rangeList.clear();
}

请注意，由于这些清除的行可能不相交，因此，结果表可能会被包含数据的行和不包含数据的行乱七八糟.调用sheet.sort(1)会将表中所有非冻结的行排序，将新的空行移到底部(是的，您可以通过编程方式设置冻结的行).视此表在其他地方的引用方式而定，但这可能不是所希望的.

Note that because these cleared rows are possibly disjoint, your resulting sheet may be littered with rows having data, and rows not having data. A call to sheet.sort(1) would sort all the non-frozen rows in the sheet, moving the newly-empty rows to the bottom (yes, you can programmatically set frozen rows). Depending how this sheet is referenced elsewhere, that may not be desirable though.

其他参考:

• Array#filter
• Array#reduce
• Array#map
• JavaScript逻辑运算符
• JavaScript比较运算符

• Array#filter
• Array#reduce
• Array#map
• JavaScript Logical Operators
• JavaScript Comparison Operators

这篇关于如果所有单元格都具有Apps Script值，清除行的最有效方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！