Re: [Apps-Script] Getting Sheet Formats

[Keith Andersen]

Right click on Tab > Copy to > New Spreadsheet or Existing Spreadsheet doesn't work?

Obviously the formulas will break if the formulas get data from other sheets unless it's in the same Workbook. However, if to another workbook, if the Tab names are the same...it should be ok. 

[Keith Andersen]

Per Grok:

function copySheetWithoutFormulas() {

  // Get the source spreadsheet and sheet

  const sourceSS = SpreadsheetApp.getActiveSpreadsheet();

  const sourceSheet = sourceSS.getSheetByName('Sheet1'); // Replace 'Sheet1' with your sheet name

  const destinationSS = SpreadsheetApp.openById('DESTINATION_SPREADSHEET_ID'); // Replace with destination spreadsheet ID

  // Alternatively, create a new spreadsheet: const destinationSS = SpreadsheetApp.create('New Spreadsheet Name');

  // Copy the sheet to the destination spreadsheet

  const copiedSheet = sourceSheet.copyTo(destinationSS);

  copiedSheet.setName('Copied Sheet'); // Optional: Set a name for the copied sheet

  // Get the data range of the copied sheet

  const range = copiedSheet.getDataRange();

  

  // Get the displayed values (converts formulas to static values)

  const values = range.getDisplayValues();

  

  // Clear the range and set only the values (removes formulas)

  range.clearContent();

  range.setValues(values);

}

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

[Google Pony]

Dear Keith,

Thank you for raising this question and sharing your initial approach, Keith! Copying sheet data while preserving formats but removing formulas is a common challenge, and your solution using getDisplayValues() is a great starting point.

To expand on this, I’d like to share an enhanced version of the script that also preserves cell formats (e.g., colors, fonts, borders) while stripping formulas. This addresses a limitation of clearContent(), which removes formatting. Here’s the improved solution:

/**

 * Copies a sheet to another spreadsheet (or new file) while keeping

 * values + formats but removing formulas.

 * @param {string} sourceSheetName - Name of the sheet to copy.

 * @param {string} targetSpreadsheetId - ID of the destination (leave empty to create new).

 */

const copySheetWithFormatsNoFormulas = (sourceSheetName = 'Sheet1', targetSpreadsheetId = '') => {

  const sourceSS = SpreadsheetApp.getActiveSpreadsheet();

  const sourceSheet = sourceSS.getSheetByName(sourceSheetName);

 

  // Create or open destination spreadsheet

  const targetSS = targetSpreadsheetId

    ? SpreadsheetApp.openById(targetSpreadsheetId)

    : SpreadsheetApp.create(`Copy of ${sourceSS.getName()} - ${new Date().toLocaleString()}`);

 

  // Copy sheet and get data ranges

  const copiedSheet = sourceSheet.copyTo(targetSS);

  copiedSheet.setName(`${sourceSheetName} (Copy)`);

 

  const sourceRange = sourceSheet.getDataRange();

  const targetRange = copiedSheet.getDataRange();

 

  // Preserve formats by copying them first

  targetRange.setValues(sourceRange.getDisplayValues()); // Removes formulas

  targetRange.setBackgrounds(sourceRange.getBackgrounds());

  targetRange.setFontColors(sourceRange.getFontColors());

  targetRange.setFontFamilies(sourceRange.getFontFamilies());

  // Add more formats as needed (borders, number formats, etc.)

 

  Logger.log(`Sheet copied successfully to: ${targetSS.getUrl()}`);

  return targetSS;

}

Key Improvements:

1. Preserves formatting (backgrounds, fonts, etc.) while removing formulas.

2. Flexible destination (new or existing spreadsheet).

3. Auto-naming for clarity.

Usage Examples:

copySheetWithFormatsNoFormulas('Data') → Creates a new spreadsheet.

copySheetWithFormatsNoFormulas('Report', 'TARGET_SPREADSHEET_ID') → Copies to an existing file.

For additional robustness, you could add error handling (e.g., invalid sheet names) or extend it to copy conditional formatting.

Would love to hear feedback or if anyone has edge cases to cover!

Sincerely yours,

Sandeep Kumar Vollala

Consultant