如何将指定单元格区域带格式和公式复制到另一工作簿？

Hey Michael, let's work through this problem together—you're right to want to keep formatting and formulas intact, and we can tweak your approach to make that happen for your fixed range!

Why Your Current Code Isn't Delivering What You Need #

• Your first copytosnapshot() function does preserve formatting and formulas, but it copies the entire sheet instead of your targeted range.
• Your second Copy() function uses getValues() and setValues(), which only pull raw cell values—this is why you're losing formatting and formulas entirely.

Working Script for Targeted Range Copy #

Here's a tailored script that copies your fixed A1:AC88 range to the destination workbook, keeping all formulas, cell formatting, and other cell properties:

function copySpecificRangeWithFormat() {
  // 1. Set up source spreadsheet and target range
  const sourceSS = SpreadsheetApp.getActiveSpreadsheet(); // Use openById('Source-ID') if not the active sheet
  const sourceSheet = sourceSS.getSheetByName('LIVE BOARD'); // Replace with your source sheet name
  const sourceRange = sourceSheet.getRange('A1:AC88'); // Your fixed range to copy

  // 2. Set up destination spreadsheet and paste location
  const destinationSS = SpreadsheetApp.openById('Destination_ID'); // Replace with your destination workbook ID
  const destinationSheet = destinationSS.getSheetByName('Sheet11'); // Replace with your target sheet name
  const destinationRange = destinationSheet.getRange('A1'); // Start pasting at this cell (adjust as needed)

  // 3. Copy range with all properties intact
  sourceRange.copyTo(destinationRange, {
    contentsOnly: false, // Retains formulas (set to true if you only want values)
    formatOnly: false, // Retains cell formatting (colors, fonts, borders)
    validationOnly: false // Retains data validation rules if present
  });
}

Quick Adjustments You Might Need #

• Change paste starting position: If you don't want to paste at A1, update destinationSheet.getRange('A1') to your preferred starting cell (e.g., A2 to paste below existing content).
• Paste to next empty row: If you ever need to append instead of overwriting, replace the destination range line with:

  const nextEmptyRow = destinationSheet.getLastRow() + 1;
  const destinationRange = destinationSheet.getRange(nextEmptyRow, 1);
  

• Permissions: Double-check you have edit access to the destination workbook, and authorize the script when prompted to access both spreadsheets.

This should give you the exact result you're looking for—fixed range copy with all formatting and formulas preserved. Let me know if you run into any hiccups!

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