Google Apps Script优化：无新数据时禁用指定代码块执行

Let's work through this issue together. The core problem is that your current code doesn't properly validate whether the startDate and endDate actually exist in the sheet's date column. When either date can't be found, indexOf()/lastIndexOf() returns -1, which calculates firstRow or lastRow as 1 (since startRow = 2). This leads to the code accidentally targeting rows like A1:F2 even when there's no matching data.

Here's the optimized version of your onePeriod() function with robust checks to ensure the marked code block only runs when valid date-range data exists:

function onePeriod(){ // For a single Period Class
  var spreadsheet = SpreadsheetApp.getActive();
  var dashboard = spreadsheet.getSheetByName("Dashboard");
  var sheetName = dashboard.getRange("A4").getValue();
  var startDate = dashboard.getRange("C4").getDisplayValue();
  var endDate = dashboard.getRange("D4").getDisplayValue();
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  var startRow = 2;
  // Fix: Get only rows with data starting from startRow, avoid empty bottom rows
  var dateColumn = sheet.getRange(startRow, 1, sheet.getLastRow() - startRow + 1, 1);
  var dates = dateColumn.getDisplayValues().flat();
  
  // First validate if start and end dates exist in the date column
  var startIndex = dates.indexOf(startDate);
  var endIndex = dates.lastIndexOf(endDate);
  
  // Exit immediately if dates are missing or end date is before start date
  if (startIndex === -1 || endIndex === -1 || endIndex < startIndex) {
    return;
  }
  
  var firstRow = startIndex + startRow;
  var lastRow = endIndex + startRow;
  var range = sheet.getRange(firstRow, 1, lastRow - firstRow + 1, sheet.getLastColumn());
  var newRows = lastRow - firstRow;

  // Only run the logic if we have a valid, non-empty date range
  if (newRows >= 0) {
    // Sorting and removing duplicates
    var column = 3; // Target column C
    range.sort({column: column, ascending:true});
    range.removeDuplicates([column]);
    
    // Delete empty rows if any
    var deleteRows = 0;
    for (var i = range.getHeight(); i >= 1; i--){
      if(range.getCell(i, 1).isBlank()){
        sheet.deleteRow(range.getCell(i, 1).getRow());
        deleteRows++;
      }
    }
    
    // Protecting data
    var timeZone = Session.getScriptTimeZone();
    var stringDate = Utilities.formatDate(new Date(), timeZone, 'dd/MM/yy HH:mm');
    var me = Session.getEffectiveUser();
    var description = 'Protected on ' + stringDate + ' by ' + me;
    var height = range.getHeight();
    var newHeight = height + 1;
    var newRange = sheet.getRange(firstRow, 1, newHeight - deleteRows, sheet.getLastColumn());
    var protection = newRange.protect().setDescription(description);
    
    newRange.getCell(newHeight - deleteRows, 2)
            .setValue(height - deleteRows + ' Students, Signed by ' + me)
            .offset(0, -1, 1, 6)
            .setBackground('#e6b8af');
    
    // Final protection settings
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  }
}

Key Changes Explained: #

• Date Existence Check: We first verify that both startDate and endDate exist in the date column, and that endDate doesn't come before startDate. If any check fails, the function exits right away.
• Fixed Date Range: The dateColumn now only includes rows from startRow (A2) to the last row with data, preventing empty bottom rows from skewing the date array.
• Adjusted Execution Condition: Changed newRows > 0 to newRows >= 0 to handle single valid rows (if needed), but the earlier validation ensures this only runs for legitimate date ranges.

This ensures the marked code block will never execute when there's no matching data in the specified date range, eliminating the accidental modification of A1:F2.

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