Retrieve Dynamic Web Data in Google Sheets or Microsoft Excel

Background

Spreadsheet software like Google Sheets and Microsoft Excel are great for organizing and visualizing data, but sometimes getting data into the worksheet can be burdensome or the data may quickly become out-of-date. Being able to import dynamic content into these spreadsheets simplifies visualization tasks and allows you to more easily retrieve up-to-date data.

Task

Build some visualizations in Google Sheets or Microsoft Excel, but use data that is available on the web in such a way that the visualization will update if the web data updates.

Example Data Sources

• https://edtechbooks.org/impact 
• https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)
• https://www.byu.edu/facts-figures 

Hints

Google Sheets

The simplest way to do this with Google Sheets is to use one of the following functions, depending upon the format of your source data:

• ImportHTML
• ImportData
• ImportXML

Microsoft Excel

Excel can be scripted using TypeScript to import from web sources, such as JSON or CSV files. See LilxAPI for an example.