Creating dynamic, automatically updating reports in Google Sheets | Publications

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 HowWeBrowse.be, 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 HowWeBrowse.be website is the ideal source for that chart.

updatedreport1

Copy the report’s CSV link

Google Sheets needs CSV or TSV data and in HowWeBrowse.be 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 HowWeBrowse.be will nicely format the data as CSV for you.

updatedreport2

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:

=ImportData("http://howwebrowse.be/csv/os/last_12_months/eyJkZXZpY2UiOlsidGFibGV0IiwibW9iaWxlIl19")

Between the brackets and in quotes is the address that points to the CSV data we got from HowWeBrowse.be. 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.

updatedreport3

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.

updatedreport4

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?

updatedreport5

Creating the chart

The last thing we need to do is to create a chart, based on the data from HowWeBrowse.be.
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.

updatedreport6

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

updatedreport7

Other data sources

On the website http://data.gov.be/en you can find more than 5000 other data sources that you can import in Google Sheets in a similar way.

Author: David Peeters

Tags:

Get in touch

Semetis | 44 Rue des Palais, 1030 Brussels - Belgium

+32 (0)2 211 34 50

welcome@semetis.com

Connect with us