Google Sheets脚本问题：无法复制指定完整数据范围

Hey there! You’re right to suspect line 8 is the issue—let’s break this down and get your script copying the full range as intended.

The Problem with Your Current Code #

Your script uses sheet2.appendRow(values [0]), which creates two limitations that lock you into copying only one row:

1. values[0] only grabs the first row of your A1:C21 range (since getValues() returns a 2D array where each sub-array represents a single row).
2. appendRow() is designed to add just one row at a time—it can’t handle the full 21-row, 3-column dataset you need to copy.

The Fixed Script #

Here’s the updated code that will copy the entire A1:C21 range (with the modified date in the first cell) to your "January 2020" sheet:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Cover");
  var source = sheet.getRange("a1:c21");
  var values = source.getValues();
  
  // Update the first cell to the current date
  values[0][0] = new Date();
  
  var sheet2 = ss.getSheetByName("January 2020");
  // Calculate the first empty row in the target sheet
  var startRow = sheet2.getLastRow() + 1;
  // Write the entire 2D array to the target sheet
  sheet2.getRange(startRow, 1, values.length, values[0].length).setValues(values);
}

Key Changes Explained #

• Replaced appendRow() with setValues(): setValues() is built for bulk writing of 2D arrays—your full A1:C21 range is exactly this structure, so this method can handle all rows and columns in one go.
• Calculated the start row: sheet2.getLastRow() + 1 finds the first empty row at the bottom of your "January 2020" sheet, ensuring your new data doesn’t overwrite existing content.
• Defined the target range: getRange(startRow, 1, values.length, values[0].length) specifies:
  • startRow: The first row to write your data to
  • 1: The starting column (column A)
  • values.length: Number of rows to write (21, matching your source range)
  • values[0].length: Number of columns to write (3, matching columns A:C)

Quick Notes #

• Double-check that the "January 2020" sheet exists in your spreadsheet—if not, the script will throw an error.
• If your target sheet has merged cells spanning multiple rows, getLastRow() might not work correctly. Stick to unmerged rows for the most reliable results.

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