I recently started on a task where our customer wanted to transfer data and statistics from their Silverstripe platform to Google Data Studio. The data would be used to create reports on how many articles was posted each month/day and how many registered members they get every month/day on two of their sites. Data Studio has support for many different ways of connecting both your own applications data and other Googles services. But there wasn’t a good support for REST APIs and if you want to use one you will have to code your own community connector a script in google that can be imported to Data Studio as a data source.
How Data Studio handles data
When transporting data to Data Studio you need to have it well formated and sent in a special format that Data Studio understands. This format is something you create in your community connector but you still have to think about it when you are structuring your API.
Data Studio uses a map scheme pointed to an array with values for example:
This is then pointed to an array of objects with an array of values containing the amount and date:
Creating a REST API
When we are creating a REST API we have to think about how we later want to map this data in our community connector to look like the example above. Creating a normal REST API in PHP is something quite straight forward so I wont go in depth with that here. Instead I will focus on the SQL and how we’re going to get the right data from the SilverStripe database to display in our API. For this case this was the hardest part of the data transfer because we couldn’t use the built in ORM in SilverStripe to get the data we wanted. My approach to get the data was to select the date of when an article or user was created or published then count the amount of times one date appeared to get the amount of published articles or created users for each day. Then we listed this in a simple JSON output to later use in our community connector. We also made multiple endpoints one for each query so when you access the endpoint it wont load all your data in the same output to keep a good performance.
We will also have to think about only grabbing and giving an output between two dates - so when Data Studio sends a request to the API it will always include two dates a start date and end date with what data it wants to grab. So we will need to include a param in our url for the dates for example: https://url.com/api/endpoint/2018-09-01/2018-09-30
Here is an example of our JSON output, we make a JSON object with an array of the values amount and date to keep it simple to use in our app script.
Data Studio Community Connector
After creating a REST endpoint we had to do the formatting to get our data into Data Studio and there is something called a community connector that you can use to create your own data source in Data Studio. So to create a new community connector we had to use googles own app script and deploy it to use in Data Studio. This app script simply takes the data from our REST endpoint and converts it to the format mentioned above. This is also where we will handle the requests and schemas for your data in Data Studio.
When creating a community connector we will build it from 4 functions that are predefined to be used by Data Studio when doing a request. Each function takes the request as an object and we can access the request to se what dates and what config will be used for the specific request.
To learn more about community connectors and how they work you can look through Googles own codelab that is a step to step guide on how to create your own community connector.
Data Studio Creating a Report
When we had a working community connector we could deploy it and use it to create a data source in Data Studio this data source then include all the data that was is grabbed from the our REST API.
We can then start to create a graph or chart using the data source in Data Studio by choosing a graph and dragging it out. This will automatically create a graph with the specified data source that will auto update with new data from your REST API.
