Build a print-optimized report web view

Northwind needs to hand clients a printed report now and then, but printing a Google Sheet directly is a mess — columns clip, gridlines clutter the page, and nothing lands where you want it. The usual fix is to copy figures into a Doc and format by hand, which is slow and goes stale the moment the sheet changes.

This automation publishes the sheet as a small web app instead. Open the URL and you get a clean HTML table styled for A4: proper margins, a readable font, and controlled page breaks. Hit print (or “Save as PDF”) and you have a tidy report that always reflects the live sheet — no copy-paste, no reformatting.

What you’ll need

• A Google Sheet whose first tab holds the report data, with a header row in row 1. The script turns each row into a record keyed by those headers.
• The sheet’s ID — the long string in its URL between /d/ and /edit.
• Two files in the Apps Script project: the Code.gs script below and an HTML file named exactly Report (shown as Report.html).
• The script deployed as a web app so the report has a URL to open and print.

The HTML (Report.html)

<style>
  /* Set the printed page to A4 with a 2cm margin all round. */
  @page { size: A4; margin: 2cm; }

  /* A clean, readable body font for both screen and print. */
  body { font: 11pt/1.4 Inter, sans-serif; }

  /* Force a page break after any element with this class. */
  .page-break { page-break-after: always; }

  /* A full-width table with collapsed borders. */
  table { width: 100%; border-collapse: collapse; }
  th, td { padding: 6px; border-bottom: 1px solid #ccc; text-align: left; }
</style>

<h1>Northwind report — <?= date ?></h1>

<table>
  <thead>
    <tr>
      <? for (const k of headers) { ?><th><?= k ?></th><? } ?>
    </tr>
  </thead>
  <tbody>
    <? for (const r of rows) { ?>
      <tr>
        <? for (const k of headers) { ?><td><?= r[k] ?></td><? } ?>
      </tr>
    <? } ?>
  </tbody>
</table>

The script

// The spreadsheet that holds the report data.
const REPORT_SHEET_ID = '1abcDataId';

/**
 * Web app entry point. Reads the sheet, fills the Report template,
 * and returns it as a printable HTML page.
 */
function doGet() {
  // 1. Read the sheet into an array of header-keyed records.
  const data = readSheet(REPORT_SHEET_ID);

  // 2. Build the template and hand it the data it needs.
  const template = HtmlService.createTemplateFromFile('Report');
  template.headers = Object.keys(data[0] || {});  // [] if the sheet is empty.
  template.rows = data;
  template.date = new Date().toLocaleDateString();

  // 3. Evaluate the template into the final HTML page.
  return template.evaluate();
}

/**
 * Reads a sheet's first tab and returns its rows as objects keyed
 * by the header row.
 * @param {string} id - Spreadsheet ID.
 * @return {Object[]} One object per data row.
 */
function readSheet(id) {
  const [headers, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) =>
    Object.fromEntries(headers.map((k, i) => [k, r[i]])));
}

How it works

1. doGet is the web app entry point — it runs whenever someone opens the published URL.
2. It calls readSheet, which reads the whole first tab. The first row becomes the headers; every later row becomes an object keyed by those headers, so the template can refer to columns by name.
3. HtmlService.createTemplateFromFile('Report') loads the HTML file. The script attaches three values to it: headers, rows, and a formatted date. The || {} guard means an empty sheet yields an empty header list instead of an error.
4. Inside Report.html, the <? ?> scriptlets loop over headers to build the table head, then over rows and headers together to build each body row. <?= ?> prints a value into the page.
5. The <style> block does the print work: @page sets A4 with margins, body picks a clean font, and .page-break is available for any element that should start a fresh page.
6. template.evaluate() runs the scriptlets and returns the finished HTML, which the browser shows — and prints — cleanly.

Example run

Say the sheet holds:

Client	Hours	Amount
Riverside Ltd	18	£2,400
Harbour Co	9	£1,150

Opening the web app URL renders a page headed “Northwind report — 01/09/2025” with a bordered, full-width table of those two rows. Pressing Ctrl/Cmd + P shows an A4 print preview with 2cm margins and no spreadsheet gridlines — ready to print or save as PDF.

Run it

The report is on-demand: open the URL whenever you need a fresh copy.

1. Add the Code.gs script and the Report HTML file to one Apps Script project.
2. Deploy it: Deploy → New deployment → Web app. Set Execute as yourself, and Who has access to whoever should see the report.
3. Open the web app URL. The page reflects the sheet as it stands right now.
4. Print from the browser, or choose “Save as PDF” as the destination.

Watch out for

• Page breaks need help on long reports. The .page-break class exists but nothing uses it yet — add <div class="page-break"></div> between sections, or split the table, so rows do not get sliced across pages.
• Browsers differ on print CSS. Chrome honours @page margins well; some browsers ignore parts of it. Test the actual print preview, not just the on-screen view.
• The Inter font may not be installed on every machine. The sans-serif fallback keeps it readable, or @import a web font at the top of the style block for consistency.
• getDataRange reads the entire sheet into one HTML page. Thousands of rows make a heavy page and a long print job — paginate or filter the data first.
• Whoever can open the URL can read the report. Set Who has access carefully, and remember table cells show raw sheet values, including any notes or working columns you would rather not print.