A generated report that dumps raw data starting in cell A1 is functional but looks unfinished, and a merged, styled header row is often the single change that makes a script's output look like a deliberate report rather than a debug dump.
This tutorial merges a full-width title row across the report's columns, styles it with a background color and white bold text, and adds a smaller italic subtitle row underneath showing the generation date.
Because merge() operates on a Range spanning multiple columns, the same setValue and formatting calls that would normally apply to a single cell apply to the whole merged block at once, keeping the header code short despite covering several columns.
Freezing the first three rows after the header is built keeps the title and column headers visible while a user scrolls through a long report, which matters more as the amount of report data grows.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Range | Role |
|---|---|---|
| Monthly Report | A1:F1 | Merged title header |
| Monthly Report | A2:F2 | Subheader dates |
| Style | center + bold 14pt | Applied after merge |
What it does
buildReportHeader merges the first row across six columns for a title, merges the second row for a generation-date subtitle, and freezes the first three rows so both stay visible while scrolling.
Prerequisites
getRange(1, 1, 1, columnCount) selects a single row spanning every report column, and calling merge() on that range turns it into one large cell that setValue and the formatting methods can then treat as a single unit.
Walkthrough
No special authorization is required beyond normal spreadsheet edit access; this works on any sheet the script already has permission to format.
Edge cases
Chaining setFontSize, setFontWeight, setHorizontalAlignment, setBackground, and setFontColor on the same range applies every style in one readable sequence instead of five separate statements each re-selecting the range.
Testing
Merging a range that already contains merged cells inside it, or that overlaps a merge from a previous run, can throw or produce unexpected results, so a production version should call breakApart() on the target rows before merging if the header is rebuilt more than once.
Hardening
Run buildReportHeader on a fresh Report sheet first to confirm the merged title spans exactly the intended column count, then run it again on a sheet that already has a header to check that it behaves correctly when rebuilt.
Variations
Merged cells complicate sorting and filtering on any row that contains them, so this pattern is best reserved for a title band that sits above the real data table rather than mixed into the same rows a user might later sort.
Full code: buildReportHeader()
Run buildReportHeader on a fresh Report sheet, and call breakApart() on the title and subtitle ranges first if the header is being rebuilt.
function buildReportHeader() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Report') || SpreadsheetApp.getActive().insertSheet('Report');
var columnCount = 6;
var titleRange = sheet.getRange(1, 1, 1, columnCount);
titleRange.merge();
titleRange.setValue('Quarterly Performance Report');
titleRange.setFontSize(16).setFontWeight('bold').setHorizontalAlignment('center').setBackground('#1155cc').setFontColor('#ffffff');
var subtitleRange = sheet.getRange(2, 1, 1, columnCount);
subtitleRange.merge();
subtitleRange.setValue('Generated on ' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MMMM d, yyyy'));
subtitleRange.setFontStyle('italic').setHorizontalAlignment('center');
sheet.setFrozenRows(3);
}- Line 2: Gets or creates the Report sheet before building the header.
- Line 5: Selects a single row spanning every report column.
- Line 6: Merges the selected row into one large title cell.
- Line 8: Chains multiple formatting calls onto the merged range.
- Line 12: Formats the generation date using the script's timezone.
- Line 15: Freezes the header rows so they stay visible while scrolling.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: build a report header
- 1Column count in buildReportHeader matches the report's actual data width
- 2Existing merges broken apart with breakApart() before rebuilding the header
- 3Background and font colors checked for sufficient contrast
- 4Subtitle date format confirmed against the intended locale
- 5Frozen row count covers the full header without hiding the data table's own headers
- 6Header rebuild tested on both a fresh sheet and a sheet with a prior header
- 7Merged rows kept separate from any range a user might sort or filter
Frequently asked questions
A single wide column would leave the other columns awkwardly narrow or misaligned with the data table below, while merging keeps every column's width tied to the data it holds and only the header row visually spans them.
Depending on the exact overlap, Apps Script may throw an error about intersecting merged ranges or silently leave the old merge in an inconsistent state, so calling breakApart() first is the safer approach for a header that gets rebuilt.
Yes, getRange accepts any starting row, starting column, number of rows, and number of columns, so the same merge() call works on a taller block if the header design calls for it.
Each Range formatting method returns the same Range object, so chaining is purely a readability choice; calling them as separate statements on repeated getRange calls would work identically but re-select the range unnecessarily.
A formula referencing a cell inside the merged range still resolves to the merged cell's single value, which is stored in the range's top-left cell, so formulas generally continue working as long as they reference that top-left position.
Call getRange on the same cells and use breakApart() to remove the merge before deleting or overwriting the header, which avoids the overlapping-merge error mentioned above.