如何通过Google Apps Script实现Gsheets跨文件复制并保留源格式？

Hey there, let's sort out this formatting issue you're facing!

Why Your Current Code Loses Formatting #

The getValues() and setValues() methods you're using only handle the raw values of cells—they don't carry over any formatting information. That's why your text-formatted numbers are being automatically converted to number format in the destination sheet; the script isn't preserving the original cell's format.

The Solution: Use copyTo() Instead #

To keep both values and their original formatting (including text-formatted numbers), switch to the Range.copyTo() method. This method lets you explicitly copy values along with their formats, which is exactly what you need.

Here's your revised code:

function Run_function1() {
  var ss = SpreadsheetApp.openById("Destination ID");
  const sk = ss.getSheetByName('Settings WS');
  var cc = sk.getRange("cell with origin file ID").getValue();
  const sh = ss.getSheetByName('Destination WS');
  
  // Clear existing content in the destination sheet
  sh.getRange(2, 1, sh.getLastRow(), 30).clearContent();
  
  var dbss = SpreadsheetApp.openById(cc);
  const dbsh = dbss.getSheetByName('Origin WS');
  const sourceRange = dbsh.getRange(2, 1, dbsh.getLastRow() - 1, 25);
  
  // Copy values AND formats to the destination range
  sourceRange.copyTo(
    sh.getRange(2, 1, sourceRange.getNumRows(), sourceRange.getNumColumns()),
    SpreadsheetApp.CopyPasteType.PASTE_VALUES_AND_FORMATS,
    false
  );
}

Key Details About the Revision #

• SpreadsheetApp.CopyPasteType.PASTE_VALUES_AND_FORMATS: This parameter tells the script to copy both the cell values and their formatting. This ensures your text-formatted numbers stay as text in the destination sheet.
• We first define the full source range, then directly copy it to the destination range—no more converting values to arrays and back, which was causing the format loss.
• If you ever need to copy additional properties (like formulas, data validation rules, or conditional formatting), you can replace the paste type with SpreadsheetApp.CopyPasteType.PASTE_NORMAL instead.

Quick Extra Tip #

If you want to completely reset the destination area before copying, replace clearContent() with clear()—this will wipe both existing content and formatting, so the destination sheet fully inherits the source's style.

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