Email a PDF snapshot of a dashboard daily

A dashboard is only useful if people look at it, and the reliable way to make people look is to put it in their inbox. A link asks them to remember, open a tab, and find the right sheet. A PDF that arrives every morning asks for nothing — it is just there, the same time every day.

Northwind’s Dashboard tab summarises the things the partners care about each day: pipeline, cash position, and retainer status. This script renders that one tab to a tidy A4 PDF every morning and emails it to the partners. It uses the Sheets export endpoint, so the PDF looks like the dashboard rather than a raw dump of cells, and it runs entirely on the script’s own credentials with no extra authentication to set up.

What you’ll need

• A Google Sheet with a dashboard tab laid out the way you want it to print.
• The spreadsheet’s ID and the tab’s gid, both from the sheet’s URL — the part after /d/ is the ID, the number after gid= is the tab’s gid.
• The list of recipient addresses for the daily email.
• The dashboard must be visible to the account the script runs as — see the “Watch out for” note about sharing.

The script

// The spreadsheet that holds the dashboard tab.
const DASHBOARD_SHEET_ID = '1abcDashboardSheetId';

// The gid of the dashboard tab — the number after gid= in the URL.
const DASHBOARD_GID = 0;

// Who receives the daily snapshot.
const RECIPIENTS = ['[email protected]', '[email protected]'];

/**
 * Renders the dashboard tab to PDF and emails it to the recipients.
 * Designed to run on a daily time-driven trigger.
 */
function emailDailyDashboard() {
  // 1. Today's date, used in the subject line and the file name.
  const today = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd');

  // 2. Render the dashboard tab to a PDF blob.
  const pdf = dashboardAsPdf(today);

  // 3. Email it to the partners with the date in the subject.
  GmailApp.sendEmail(
    RECIPIENTS.join(','),
    `Northwind daily dashboard — ${today}`,
    'Snapshot attached.',
    { attachments: [pdf] }
  );

  Logger.log(`Sent dashboard PDF for ${today} to ${RECIPIENTS.length} recipient(s).`);
}

/**
 * Builds a PDF of a single sheet tab using the Sheets export endpoint.
 * The query params control layout — A4, portrait, fit-to-width, no
 * gridlines. Returns a named PDF blob.
 */
function dashboardAsPdf(label) {
  // Build the export URL for one tab, with print layout baked in.
  const url =
    `https://docs.google.com/spreadsheets/d/${DASHBOARD_SHEET_ID}/export?` +
    `format=pdf&size=A4&portrait=true&fitw=true&gridlines=false&` +
    `printtitle=false&sheetnames=false&gid=${DASHBOARD_GID}`;

  // Fetch it using the script's own OAuth token — no extra auth setup.
  const blob = UrlFetchApp.fetch(url, {
    headers: { Authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
  }).getBlob();

  // Give the file a dated name so attachments are easy to tell apart.
  return blob.setName(`dashboard-${label}.pdf`);
}

How it works

1. emailDailyDashboard formats today’s date once, then reuses it for both the email subject and the PDF file name so the two always match.
2. It calls dashboardAsPdf, which builds a request to the Sheets export endpoint. The endpoint takes layout as query parameters — size=A4, portrait=true, fitw=true to fit the content to the page width, and gridlines=false so the PDF reads like a report, not a spreadsheet.
3. The gid parameter pins the export to one tab. Without it, the endpoint exports the whole workbook.
4. UrlFetchApp.fetch sends the request with an Authorization header carrying ScriptApp.getOAuthToken() — the script’s own credentials. That is what lets the export work without setting up a separate service account or API key.
5. The response is taken as a blob and given a dated name, then returned.
6. Back in emailDailyDashboard, the blob is attached to a short email and sent to the recipient list in one go.

Example run

When the trigger fires on the morning of 19 August 2025, the partners each receive an email:

• Subject: Northwind daily dashboard — 2025-08-19
• Body: Snapshot attached.
• Attachment: dashboard-2025-08-19.pdf — one A4 page showing the Dashboard tab exactly as laid out, in portrait, with no gridlines.

The next morning the same email arrives with 2025-08-20 in both the subject and the file name, so a week of snapshots sits in the inbox as a dated trail rather than a pile of identically named files.

Trigger it

This is a scheduled report — run it once a day before the working day starts:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose emailDailyDashboard, event source Time-driven, type Day timer, and pick the 7am–8am slot.
4. Save, and approve the authorisation prompt the first time it runs.

Watch out for

• The export endpoint only sees what the script account can see. If the dashboard sheet is not shared with the script’s owner, the request returns an HTML error page and that HTML is silently attached as the “PDF”. Make sure the account running the script has at least view access.
• The gid is not the tab’s position — it is the fixed number in the URL after gid=. Reordering tabs does not change it, but pointing at the wrong gid exports the wrong tab.
• The layout is whatever the print settings imply. If the dashboard is wider than an A4 page, fitw=true shrinks it to fit, which can make text small. Design the tab to print well, or switch portrait=true to false for landscape.
• The export reflects the sheet at the moment the script runs. If a formula is still recalculating or an import has not finished, the PDF captures the half-updated state. Schedule the trigger after any feeding jobs have completed.
• Very large or slow sheets can make the export request time out. Keep the dashboard tab focused on summary figures rather than thousands of rows.