How to connect charts with external data sources for automated updates

Vizzlo has an option to update charts automatically. This will save you a lot of time when the data source updates frequently and you want to avoid updating your charts manually. 

 

 

Getting your data into the right shape for a visualization tool is not always easy. The task becomes even more difficult when you want to use data to which you have no direct access.
At Vizzlo, we try to take that burden off your shoulders. You can connect any chart to a public Microsoft Excel file, a public Google Sheet document via a URL. If you have connected your Vizzlo account to a cloud storage provider, you can also schedule automated updates for XLSX, CSV files, and Google sheets documents. You can select arbitrary ranges from the data source, re-order them, duplicate them, and finally import them into Vizzlo.

In this article, we will show you how to connect a Vizzlo chart to external data by examples of

1. a public Microsoft Excel file
2. a Google sheets document hosted on Google Drive

Connect a Vizzlo chart to a public Microsoft Excel file

With the outbreak and spread of the coronavirus, more and more data on case numbers were published. In Germany, the Robert Koch Institute monitors public health and advises the federal government, e.g. on preventing and tackling infectious disease outbreaks such as the COVID-19 pandemic, see rki.de. Its core tasks include the detection, prevention, and combating of infectious diseases and non-communicable diseases in Germany. On their website, the institute publishes data about daily new infections and the reproduction rate of the virus as an Excel file (only in German, unfortunately): https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Projekte_RKI/Nowcasting.html

(The data for the chart is updated daily at 12pm CET)

 

To create the chart above, you need

— to create a Time Series Graph (because the data is time-based)

— open the spreadsheet

— In the top-right corner, click on Connect to open the Sync data dialog

 

You will be asked to 

1. paste a link to a data source, or
2. browse files stored on Box, Dropbox, Google Drive, or One Drive - given you connected your Vizzlo account to those cloud storage providers

We will take the 1. route. You get the link to the Excel file, when you right-click on Tabelle mit Nowcasting-Zahlen zur R-Schätzung > Copy link address.

Or copy the link from here: https://www.rki.de/DE/Content/InfAZ/N/Neuartiges_Coronavirus/Projekte_RKI/Nowcasting_Zahlen.xlsx?__blob=publicationFile

 

Paste this link into the Sync data dialog, and if everything goes well, a green checkmark will indicate that it is a valid link.

 

 

When you now click the Connect button, the data is imported into Vizzlo's spreadsheet. Don't let you distract from the red cells and the warning messages. We'll solve these issue in a minute.

 

 

The data you see in the spreadsheet come from the Excel worksheet "Erläuterungen" (English: Explanations). The data we are interested in is stored in the worksheet called "Nowcast_R". You the input element Worksheet to switch between the different worksheets.

 

 

That looks a lot more like what we would expect. The next and final steps are:

— Select a cell range

— Schedule the updates

 

Instead of importing all the data, we want to select only some columns. The order of the columns we want to import needs to be specified too. We can do this with the input element Optional range.

The data we are interested in for this example is stored in columns A;D;E;F. The Optional range input allows the selection of arbitrary cell ranges from the data source to be imported into Vizzlo. Because different series are drawn on top of each other in the order they are arranged in the Vizzlo spreadsheet, we will import the data in this order A;F;D;E.

Note: Column letter names refer to the data source here and must be separated by a semicolon. A range of columns can be selected by a colon, e.g. A:C;F.

 

Tipp: you can duplicate columns too, simply type the column name twice, e.g. A;A;B. An empty column can be 'imported' with 2 (or more) semicolons, e.g. A;;B (this might be useful when you use a Waterfall chart or a Gantt chart). We will cover more advanced examples below.

 

We are almost there.

 

The column headers say:

B : "Untere Grenze des 95%-Prädiktionsintervalls der Anzahl Neuerkrankungen"

C : "Obere Grenze des 95%-Prädiktionsintervalls der Anzahl Neuerkrankungen (ohne Glättung)"

D : "Punktschätzer der Anzahl Neuerkrankungen"

 

ThatisevenforGermanstoomanylongwords. So we might want to shorten those to something less scientific and less verbose.

B: "Lower bound"

C: "Upper bound"

D. "New cases"

 

Tipp: Now that you changed the column headers, the next time Vizzlo's servers download the data would be overwritten. To prevent this from happening, uncheck the input element Use source column headers. This setting ensures that column names are taken from the data source (active state of the toggle) or the names are locked in the Vizzlo spreadsheet.

 

We will want to lock the column names for this example.

 

Schedule automated data updates

In the sidebar, switch to tab 2. Auto sync to schedule your updates. You can set the interval to hourly, daily, weekly. Once you are done, click the Okay button at the bottom right and your document will be in sync. 

 

Connect a Vizzlo document to a Google sheets document

We saw how to connect a Vizzlo document to a public Excel file hosted on the web. All you need to do is to paste a public URL into the data sync dialog. Now we want to cover the case to connect your charts to data that is stored in your cloud storage provider of choice. You will have multiple options:

1. You paste a public Linkt to a XLSX file, CSV file, or Google sheets document link into Vizzlo (essentially the same setup as with the public Excel file)
2. Links to private documents (owned by you, the Vizzlo user)
3. Links to documents shared with you (read-only access is sufficient)
4. Links to documents in shared Google Drives (Vizzlo user needs to have at least read-only access)

Note: For the first option you need to make sure that Link Sharing is activated for your Google Sheet, select the option "Anyone with the link" when you copy the link to the data. Then paste this into Vizzlo. This works not only for Google Sheets documents but also Microsoft Excel or CSV files stored in your Google Drive.
For options 2 to 4 to work, at least a single Google Drive cloud storage connection needs to be set up by the user.

 

Let's get started.  We will create a grouped bar chart: vizzlo.com/create/grouped-bar-chart

This chart expects a categorical variable in the first column used as axis labels, followed by numerical  variables.

Here is a link to the data we will use: https://docs.google.com/spreadsheets/d/1zJQpz80XD36HQ-vExfjyuJKHyGtj60IdnPEzCR5hUZw/edit?usp=sharing

 

We will discuss two cases:

— the data is arranged in wide format, and columns need to be re-ordered > worksheet "Wide format"

— the data is arranged in narrow format > worksheet "Narrow format"

(see: https://en.wikipedia.org/wiki/Wide_and_narrow_data)

Connect the chart to the data source, i.e. paste the link into the dialog that opens when you click on "Connect".

 

Import data that is stored in wide format

After you connected your chart to the data source > select the worksheet "Wide format"

 

 

 

The example is, of course, constructed, but it also shows that it is possible to import ranges that not only start in different columns, but also in different rows.

 

As we said earlier Vizzlo's grouped bar chart expects a categorical variable in the first column, which is what we called "label", stored in column F5:F8. The data used to map to height of the bars are stored in B4:D7. Let's add this information to the Optional range input element, i.e. enter F5:F; B4:D.  

Note how we left the bottom end of the ranges undefined. That is we wrote F5:F, and B4:D. This useful if new rows will be added to the source data and you want to import that data into Vizzlo too.

 

Import data that is stored in narrow format

For the second example, we will use the same data, but this time select the worksheet "Narrow format".

 

 

The task here is a bit trickier and requires more labor. The categorical data we need is stored in A2:A4, the values for the bars in B2:B4; B5:B7; B8:B10. We must uncheck the option Use first row as column headers to get:

 

 

Things to note here: 

1. column headers show default names from the Vizzlo chart
2. there are zeros in column E
3. the data is imported correctly

We need to change the column names manually. We also need to 'actively' delete column E. Since we are not using the first row as column headers, the option to use the column headers from the source is disabled. In our example, this is desirable because the Vizzlo server will not overwrite our manually added column headers. 

 

Sidenote: How do I make my Google sheets document public in Google Drive?

— Go to drive.google.com

— Select the document that you want to make Public.

— Right-click on the document and select the Share option.

— Under “Get Link”, click the Down arrow

— From the dropdown menu, make sure to select the option Anyone with the link

— Copy the link and paste it into the Sync data dialog

Reach out to Google support for further info on sharing files, or sent an email to help@vizzlo.com if you have questions or remarks.