如何修改Google Apps Script实现将指定单元格复制到另一谷歌表格指定工作表的最后一行

Hey there! Let's get your script working correctly to copy rows from your current sheet to the "catalog" sheet in that separate Google Spreadsheet. I've adjusted your code and added explanations so you understand what changed:

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Copy to catalog')
    .addItem('Copy Stuff', 'copyStuff')
    .addItem('Create Sidebar', 'createSidebar')
    .addToUi();
}

function copyStuff() {
  var ss = SpreadsheetApp.getActive();
  var ssh = ss.getSheetByName('multiple items');
  // Access the target external spreadsheet
  var target = SpreadsheetApp.openById('1QaMmElzcCVVOeO8JD-3uCsuY2hkXAxb1x5d0heXcdWM');
  var dsh = target.getSheetByName("catalog");
  
  // Get the source range you want to copy
  var srg = ssh.getRange('A51:X54');
  // Calculate the next empty row in the target sheet (last row + 1)
  var nextEmptyRow = dsh.getLastRow() + 1;
  
  // Copy the source range to the target's next empty row, matching the source's row/column count
  srg.copyTo(
    dsh.getRange(nextEmptyRow, 1, srg.getNumRows(), srg.getNumColumns()),
    SpreadsheetApp.CopyPasteType.PASTE_VALUES, // Use PASTE_ALL if you want to copy formatting too
    false
  );
}

function createSidebar() {
  var html = '<input type="button" value="Copy Stuff" onClick="jsCopyStuff();" />';
  html += '<script>function jsCopyStuff(){google.script.run.copyStuff();}</script>';
  var userInterface = HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showSidebar(userInterface);
}

Key Changes I Made: #

• Fixed the target range: Instead of just dsh.getRange(lastRow), we now define a range that starts at the next empty row (nextEmptyRow = lastRow + 1) and matches the exact number of rows/columns from your source range (A51:X54). This ensures your data doesn't overwrite existing content in the "catalog" sheet and fits correctly.
• Added explicit paste type: The SpreadsheetApp.CopyPasteType.PASTE_VALUES parameter makes sure only the cell values are copied. If you want to copy formatting, formulas, etc. too, replace this with SpreadsheetApp.CopyPasteType.PASTE_ALL.
• Clarified variable names: Renamed lastRow to nextEmptyRow to make it clearer what the variable is used for.

Just replace your existing script with this one, and it should work exactly how you need it to!

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