IMPORTXML for structured data in Google Sheets - Dashboard Guide

Dashboard Mode - IMPORTXML for structured data

Dashboard Goal

Track the latest headlines and their publication dates from a news website using IMPORTXML to fetch structured data automatically.

Sample Data

Headline	Publication Date
Global Markets Rally on Economic Optimism	2024-06-10
New Tech Innovations Unveiled at Conference	2024-06-09
Climate Change Summit Reaches Agreement	2024-06-08
Sports Championship Finals Scheduled for July	2024-06-07
Healthcare Advances Improve Patient Outcomes	2024-06-06

Dashboard Components

• KPI Card: Total Headlines
  Formula: =COUNTA(A2:A6)
  Shows the number of headlines fetched.
• Table: Headlines and Dates
  Formulas:
  =IMPORTXML("https://example-news-site.com", "//h2[@class='headline']") in A2
  =IMPORTXML("https://example-news-site.com", "//time[@class='pubdate']/@datetime") in B2
  Displays headlines and their publication dates fetched live.
• Latest Headline: Most Recent News
  Formula: =INDEX(A2:A6, MATCH(MAX(B2:B6), B2:B6, 0))
  Shows the headline with the newest publication date.

Dashboard Layout

+----------------------+-------------------------+
| Total Headlines (KPI) | Latest Headline (KPI)   |
+----------------------+-------------------------+
|                                          |
|           Headlines and Dates Table       |
|                                          |
+------------------------------------------+

Interactivity

Add a date filter to select a range of publication dates. When the filter changes, the Headlines and Dates Table updates to show only news within that range. The Total Headlines and Latest Headline KPIs update automatically based on the filtered data.

Self Check

If you add a filter to show only headlines published after 2024-06-08, which headlines remain visible? How do the Total Headlines and Latest Headline KPIs update?

Key Result

Dashboard fetches and shows latest news headlines and dates using IMPORTXML with KPIs for total and latest headline.