如何修改Google Apps Script函数，仅将日期插入到表格有数据的最后一行对应列中

Got it, let's tweak your function so it only fills the date into rows that actually have data in your sheet, instead of the entire column.

The Problem with Your Current Code #

Your original line ss1.getSheetByName('Sheet1').getRange('B:B').setValue(date) selects the entire B column, which is why every cell in that column gets the date—even empty rows below your data.

Modified Solution #

Here's the updated function that targets only the rows with existing data (we'll assume your main data is in column A, since that's where your testHeader is):

function createTodayDate() { 
  const ss1 = SpreadsheetApp.getActiveSpreadsheet(); 
  const sheet = ss1.getSheetByName('Sheet1');
  
  // Get the number of rows with actual data in column A (ignores empty cells)
  const dataRowsCount = sheet.getRange("A:A").getValues().filter(String).length;
  
  // Exit if there's only the header (no data rows)
  if (dataRowsCount <= 1) {
    return;
  }
  
  const date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
  // Target only rows 2 to dataRowsCount in column B (skip header row)
  sheet.getRange(2, 2, dataRowsCount - 1, 1).setValue(date);
}

How This Works #

• dataRowsCount: This counts how many cells in column A have text/values (using filter(String) to remove empty entries). This ensures we only target rows that have actual data.
• Range Selection: getRange(2, 2, dataRowsCount - 1, 1) breaks down to:
  • 2: Start at row 2 (skipping the header row)
  • 2: Column B (since columns are numbered starting at 1)
  • dataRowsCount - 1: Number of rows to target (total data rows minus the header)
  • 1: Only 1 column (column B)
• Edge Case Handling: If there's only the header row (no data), the function exits early to avoid unnecessary actions.

Alternative Using getLastRow() #

If your sheet doesn't have empty gaps in column A, you can also use sheet.getLastRow() for a simpler approach:

function createTodayDate() { 
  const ss1 = SpreadsheetApp.getActiveSpreadsheet(); 
  const sheet = ss1.getSheetByName('Sheet1');
  const lastRow = sheet.getLastRow();
  
  if (lastRow <= 1) {
    return;
  }
  
  const date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
  sheet.getRange(2, 2, lastRow - 1, 1).setValue(date);
}

This works because getLastRow() returns the last row with any data in the sheet. Just note that if other columns have data below your main column A, this will include those rows—so use the first method if you need strict targeting of column A's data rows.

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