Build a slide-based digital signage loop

Northwind has a TV in the office that nobody quite knows what to do with. The obvious use — a rolling display of company numbers — never sticks, because keeping the figures current means someone editing slides by hand every morning. Within a week the screen shows last month’s data, and people stop looking.

This automation keeps the screen honest. A Slides deck holds one slide per KPI, each with {{value}} and {{label}} placeholders. A scheduled script reads the latest figures from a Sheet and rewrites those placeholders on every run. Set the deck to present on a loop and the TV updates itself — no one ever touches it again.

What you’ll need

• A Google Slides deck for the signage display, with one slide per KPI. Each KPI slide should contain the literal text {{value}} and {{label}}.
• A KPIs spreadsheet with a header row containing slideId, value, and label. The slideId ties each row to one slide in the deck.
• The object ID of each KPI slide — read it from the slide URL fragment, or log slide.getObjectId() once to collect them.

The script

// The signage deck shown on the office TV.
const SIGNAGE_DECK = '1abcSignageDeckId';

// The spreadsheet holding the latest KPI figures.
const KPI_SHEET_ID = '1abcKpiSheetId';

/**
 * Reads the latest KPI figures and rewrites the {{value}} and {{label}}
 * placeholders on each matching slide in the signage deck.
 */
function refreshSignage() {
  const deck = SlidesApp.openById(SIGNAGE_DECK);
  const kpis = readSheet(KPI_SHEET_ID);

  // Bail out if there are no figures to display.
  if (!kpis.length) {
    Logger.log('No KPI rows found — leaving the deck unchanged.');
    return;
  }

  let updated = 0;
  for (const slide of deck.getSlides()) {
    // Match this slide to a KPI row by its object ID.
    const id = slide.getObjectId();
    const kpi = kpis.find((k) => k.slideId === id);
    if (!kpi) continue;

    // Swap the placeholders for the current figure and its label.
    slide.replaceAllText('{{value}}', String(kpi.value));
    slide.replaceAllText('{{label}}', kpi.label);
    updated++;
  }
  Logger.log('Refreshed ' + updated + ' KPI slide(s).');
}

/**
 * Reads a sheet and returns its rows as objects keyed by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. refreshSignage opens the signage deck and reads the KPIs sheet into a list of {slideId, value, label} objects.
2. If the sheet has no data rows it logs a message and stops, so the screen keeps its last good figures rather than going blank.
3. It loops over every slide in the deck and reads each slide’s object ID.
4. It looks for a KPI row whose slideId matches that object ID. Slides with no match — a title slide, a divider — are skipped.
5. For a matching slide it calls replaceAllText twice, swapping {{value}} for the figure and {{label}} for its caption.
6. It logs how many slides were updated, which is a quick way to confirm every slideId in the sheet actually points at a real slide.

Example run

The KPIs sheet maps figures to slides:

slideId	value	label
g1a2b3	128	New leads this week
g4c5d6	94%	On-time delivery
g7e8f9	£312k	Pipeline value

Before a run, slide g1a2b3 reads “{{value}} — {{label}}”. After refreshSignage, the same slide reads “128 — New leads this week”, and the other two update in the same way. Running it again an hour later picks up whatever the sheet now says — no manual editing in between.

Trigger it

Run the refresh on a frequent timer so the screen never drifts:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for refreshSignage.
3. Choose a time-driven trigger, minutes timer, every 5 minutes.

To display the deck: open it in a browser on the TV’s machine, choose Present, then enable Auto-advance (every 30 seconds) and Loop from the presenter controls.

Watch out for

• replaceAllText only works if the placeholder text is exact. A stray space or a placeholder split across two text runs by autocorrect will silently fail to replace — type the placeholders carefully.
• The deck must be reopened to pick up changes if the TV’s browser caches it. Most presenter views poll for updates, but check yours; some need a manual reload.
• Object IDs change if you delete and recreate a slide. After any structural edit, re-collect the IDs and update the sheet’s slideId column.
• A 5-minute trigger means up to 288 runs a day. Each is light, but it counts against the daily script-runtime quota — widen the interval if you bump into it.
• replaceAllText consumes the placeholder — after the first run the slide holds a real figure, not {{value}}, so later runs have nothing to replace. To keep the deck refreshable, put each placeholder inside a fixed prefix or suffix the script can target, or rebuild the placeholder text each run.