New rows get values easily enough. Matching the fonts, number formats, and fill of your template row is the part people do by hand. copyFormattingFromRow(templateRowRange, targetRange) pastes format only from a one-row template onto every row in the target. Values and formulas in the target are left alone.
When to use
- Applying a styled template row to rows you just appended
- Fixing a block of rows that lost number formats or colors
- Keeping currency, date, and percent formats consistent after an import of values
How to use this snippet
/**
* Copy formatting from a single template row onto each row of a target range.
* Uses PASTE_FORMAT only — values and formulas in the target are not changed.
*
* @param {GoogleAppsScript.Spreadsheet.Range} templateRowRange typically one row
* @param {GoogleAppsScript.Spreadsheet.Range} targetRange
* @return {GoogleAppsScript.Spreadsheet.Range} the target range
*/
function copyFormattingFromRow(templateRowRange, targetRange) {
var sheet = targetRange.getSheet();
var cols = Math.min(templateRowRange.getNumColumns(), targetRange.getNumColumns());
var template = templateRowRange.offset(0, 0, 1, cols);
var startRow = targetRange.getRow();
var startCol = targetRange.getColumn();
var numRows = targetRange.getNumRows();
for (var i = 0; i < numRows; i++) {
template.copyTo(
sheet.getRange(startRow + i, startCol, 1, cols),
SpreadsheetApp.CopyPasteType.PASTE_FORMAT,
false
);
}
return targetRange;
}
Example
function formatNewRows() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
var template = sheet.getRange(2, 1, 1, sheet.getLastColumn()); // styled sample row
var firstNew = 50, count = 10;
copyFormattingFromRow(template, sheet.getRange(firstNew, 1, count, template.getNumColumns()));
}
Notes
setValuesalready keeps existing formatting. Use this when the new cells never had the template formats to begin with (blank rows, a fresh tab, pasted values from elsewhere).- Only as many columns as the shorter of the two ranges are copied.
- Conditional formatting rules on the sheet are separate; this copies cell-level formatting.
Happy Coding!
