Real-Time Charts from Spreadsheet

A client needed an interactive chart that shows a selected country’s specific data where they can add it anywhere on the website.

The Challenge

The idea is simple: use the vast countries data hosted on the site and render the chart anywhere they like. But it’s not going to be that easy performance-wise, because it means loading all that data for each user request which strains the server and ultimately slows the website down.

A solution would be to cache the data, but it has its drawbacks: updating a single field would mean waiting for the cache to expire or clear the entire cache manually to update the frontend. There is a delicate balance between improving site performance and how fast you want the updates to reflect.

My Approach & Solution

The data’s single source of truth is on a spreadsheet they share with their subscribers. The data hosted on the site is coming from these spreadsheets they update frequently. The logical thing to do is scrape the spreadsheet and use its data for the chart.

This way they don’t have to manually encode the data to the site while at the same time save server resources. Eliminating both operational and performance friction.

Scraping the spreadsheet has two approaches: do it free but hard or use Google’s APIs which is paid but easy. Obviously, the free but hard option is beneficial for the client. It’s risky and introduces a lot of bugs, but communicating clearly about how the spreadsheet should be formatted helos avoid these issues.

I built the scraper using JavaScript so it would be processed by the visitor’s browser instad of the server. Once the data is scraped, it is transformed into a format the charting library understands. The charting library I used was Observable Plot, it’s a modern and performant data visualization library with a lot of customization options.

The result is an interactive chart with real-time updates coming from the spreadsheet while freeing up server resources.

Tech Stack Used: JavaScript, Google Spreadsheets.