Google Sheets每日追加数据至底部失败：代码覆盖旧行求助

The core issue with your current code is that insertRowAfter(1) adds a new row right after row 1 every time you run the function—this means you're always overwriting the same spot instead of adding to the end of your existing data. Here's how to adjust it to append new rows to the bottom of the sheet:

Corrected Code #

function recordHistory() {
  // Get the active spreadsheet and target sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName("RECEIPT TRACKER");
  
  // Fetch the data you want to record from B33:N33
  var sourceRange = targetSheet.getRange("B33:N33");
  var dataToAppend = sourceRange.getValues()[0]; // Convert 2D range data to a 1D row array
  
  // Append the data as a new row at the bottom of the sheet
  targetSheet.appendRow(dataToAppend);
}

Key Changes Explained #

• Removed forced row insertion: Instead of hardcoding a row after row 1, appendRow() automatically adds a new row at the very end of the sheet's existing data—no more overwriting!
• Simplified data handling: getValues() returns a 2D array (even for a single row), so we use [0] to extract the first (and only) row of data as a 1D array that appendRow() can process directly.
• Clearer variable names: Renamed variables like sheet to targetSheet to make the code easier to follow and maintain.

Optional: Handle Edge Cases #

If your sheet has a header row or might be empty initially, appendRow() still works perfectly—it adds the new row right after the last row with content. If you need more control (e.g., inserting a row explicitly before setting values), you can use this alternative approach:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName("RECEIPT TRACKER");
  
  var sourceRange = targetSheet.getRange("B33:N33");
  var dataToAppend = sourceRange.getValues();
  
  // Get the last row with content and insert a new row after it
  var lastRow = targetSheet.getLastRow();
  targetSheet.insertRowAfter(lastRow);
  
  // Paste the data into the new row (columns B to N = columns 2 to 14)
  targetSheet.getRange(lastRow + 1, 2, 1, 13).setValues(dataToAppend);
}

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