修改Google Apps Script函数：复制数据时过滤指定行（不改动原表）

Background #

The original function copies data from Sheet1 (range A4:AL428) to Sheet2, but we need to exclude rows where column B is empty or equals "XYZ"—without altering any data in Sheet1.

Updated Code #

function ABCDEF() {
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = ss.getSheetByName('Sheet1');
  var destSheet = ss.getSheetByName("Sheet2");
  var simDataList = ['A4:AL428'];
  var filteredRows = [];

  for (var i = 0; i < simDataList.length; i++) {
    var rangeValues = sourceSheet.getRange(simDataList[i]).getValues();
    // Filter out rows where column B (index 1) is empty or "XYZ"
    rangeValues.forEach(row => {
      var columnBValue = row[1];
      if (columnBValue !== "" && columnBValue !== "XYZ") {
        filteredRows.push(row);
      }
    });
  }

  // Paste filtered data to Sheet2 only if there are valid rows
  if (filteredRows.length > 0) {
    destSheet.getRange(destSheet.getLastRow() + 1, 1, filteredRows.length, filteredRows[0].length).setValues(filteredRows);
  }
}

Key Adjustments #

• Descriptive variable names: Renamed variables like s to sourceSheet for better readability.
• In-memory filtering: All data processing happens on copied values in memory, so Sheet1 remains completely untouched.
• Row exclusion logic: Checks each row's column B value (zero-indexed at position 1) and only keeps rows where the value is neither empty nor "XYZ".
• Safety check: Adds data to Sheet2 only if there are filtered rows available, preventing errors from trying to paste an empty array.

内容的提问来源于stack exchange，提问作者DCC