What is the most efficient way to clear row if ALL

Question

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.

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;
           }
       }
   }

Thanks!

Answer 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.

The strategy, then, is to:

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)
4. Feed the labels of each of the kept rows to the RangeList constructor
5. Use RangeList methods on the RangeList

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();
}

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.

Additional references:

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