Data Studio Report & Connections
I have played with Power BI and Data Studio (DS) is Google’s equivalent, and its Free. Since I’ve been playing with GlideApps I have been working in the Google Docs environment with Sheets and Google App Scripts (GAS) and find the Google environment is versatile and has lots of features. An extension of learning to use Queries in Google Sheets was the use of Data Studio as a Dashboard tool. So I decided to explore DS.
Data Studio can be connected to a number of sources including Google Sheets and some SQL data bases. So I decided to do a little bit of testing.
First Project, to embed a Chart in a web page
My first test was to use some video viewing data that I’d collected in Google Sheets. So I connected that as a Source, made a chart and then wanted to see if I could embed the information on a web page (as you would with a dashboard).
I found DS a bit sluggish in uploading the data but the presentation tools were good. The chap who does the Learning Google Sheets videos has good tutorials on beginner Data Studio and I followed those. See below:
The item below is a iframe of a Google Data Studio report. To set it up you need to go to File – Embed and get link.
The main page is setup as a Portrait and I wanted a landscape format and an A4 or A3 proportion. So I had to look up what pixel ratio is equivalent. For A3 Landscape ratio is 1131 x 800 px for same aspect ratio.
To make sure that fits in with the web page you need to set that up with iFrame size so that scroll bars are not needed. For bigger reports use a number of pages and smaller page size and multiple iframes.
I was pretty pleased with the process, after finding the methods used in Data Studio it went well, easy to set up.
MySQL server connection
I was very excited about this option when I saw it, but it ended up being very frustrating. I decided to link to my WordPress blog site database on my VPS. This was just to test the connection, but after a few days of a couple of hours at a time, I have not been successful in doing so.
I watched the video below and it showed a free MySQL server you could use. The link is here: https://www.freesqldatabase.com/
I decided to try using that so loaded up a table from another MySQL database onto a new database on that site. That seemed to connect straight away. That worked. But unless you log in each week they blow the database away. Fair enough, it is free service, intended for testing purposes.
There seems to be a few posts on people having difficulties connecting to their MySQL databases and no clear solution. There are not that many videos about it either. One issue seems to be connecting through the firewall and white listing a lot of IP addresses.
On my website I tried whitelisting all the sites that google Data Studio uses but that didn’t work either.
I then tried writing an App Script to connect to my Database from Google Sheets after I’d white listed sites in Firewall, still no success.
So, DS to MySQL DB on my VPS and GAS connection to the same was a bit of a failure. I’ve just tried to connect to a PostGres DB on my VPS with no success either. A shame as I think linking to a Data base would be really useful for bigger data structures. Google sheets is OK but gets a bit sluggish if there are too many rows of data.
End comment
Some wins and some failures. I’ll do a bit more research on the connections but without too much effort. I can’t see a use for Data Studio if its only connection is to a spreadsheet. I can use the spreadsheet to do the dashboard, and in Google Sheets you can get a link for that on your web page (I think, I should check that). You can definitely embed charts like I do in GlideApps.
