Creating dynamic, automatically updating reports in Google Sheets | Articles

In Google Sheets it’s super easy to create static reports. But did you know that creating dynamic, automatically updating reports is easy too? Awesome!

As an example I’ll use, a website that shows the Belgian internet usage trends, based on aggregated data of some of Belgium’s most visited websites.

Find the information you need

In my report, I would like to add a chart that shows the usage of the most popular mobile OS. The report Top mobile OS on the website is the ideal source for that chart.


Copy the report’s CSV link

Google Sheets needs CSV or TSV data and in it’s easy to get the CSV data behind any report. Just replace /en/report/ with /csv/ in the address bar of your browser and will nicely format the data as CSV for you.


Importing the data in Google Sheets

Now it’s time to head over to Google Sheets. Click in an empty cell and enter the following formula:


Between the brackets and in quotes is the address that points to the CSV data we got from Hit enter and the data will be loaded instantly.
Even better: this data will be updated every hour bij Google Sheets, so the numbers will always be up to date, whenever you open the sheet.


Formatting the data

Now it’s time to make the data a little more readable. We start with the dates in the first row.
Select the row and choose Format — Number — More formats — More date and time formats…
In the dialog that appears, choose “Month Year” and click Apply.


Next, select all values. As they are percentages, you can format them using the % button in the toolbar. That looks way better, doesn’t it?


Creating the chart

The last thing we need to do is to create a chart, based on the data from
Select all data, including the header rows and click the Insert Chart button. Choose the chart type you like in the chart editor and click the Insert button.


That’s it. You now have a chart in your Google Sheet that’s updated every hour with the latest Mobile OS trends.


Other data sources

On the website you can find more than 5000 other data sources that you can import in Google Sheets in a similar way.

Author: David Peeters


Get in touch

Semetis | 44 Rue des Palais, 1030 Brussels - Belgium

+32 (0)2 211 34 50

Connect with us

Cookie Policy

This website uses cookies that are necessary to its functioning and required to achieve the purposes illustrated in the privacy policy. By accepting this OR scrolling this page OR continuing to browse, you agree to our privacy policy.