This series of articles will work through how to create a connector for Data Studio in Apps Script. The source data will be the github apps script project catalog from Every Google Apps Script project on Github visualized.
This initial article will lay out the motivation and the general structure. We’ll dive into the code in more detail in upcoming articles. You’ll see from this article, that you can do most of the preparation and testing of your connector before going anywhere near Data Studio.
A data studio connector is an Apps Script project that can provide a data resource to Google Data Studio. Here’s a catalog of hundreds of existing connectors. I already have an Apps Script library that knows how to get the data from Apps Script projects on github – here’s a video on what this data is all about and how to use it, and the live scrviz app can be found here. Creating a Data Studio connector will allow a more detailed , customizable analysis of the 4000+ projects scrviz has cataloged.
Example report
What I’m after initially is a simple Data Studio report by owner on the scrviz data, like this.
Structure of connector
There’s a pretty standard recipe for creating connector, so I’ll use that structure. Here’s the official Google documentation.
A connector needs to have the following functions defined. My connector implements them all, and I’ve added a few notes on their purpose.
getConfig – The consumer of the connector sets any connection specific parameters here. Not all connectors have or need user configurable parameters, but my connector provides a few configurable caching options. In addition to the caching built into data studio, the connector will also offer catalog caching (so it doesn’t have to keep going back to the source data), and formatted data caching (so it doesn’t have to rebuild the data if it’s quite recent). These will be configurable via datastudio configuration
getFields – Sets fields and field types that the connector is going to provide to be used to create a schema for DataStudio
getData – Retrieves and formats the data to provide to DataStudio via the connector. It will need a request argument and should return data rows populated with the fields requested along with the schema entries for the requested fields
getAuthType – Sets the kind of Oauth required. This is public data, so no auth is required
getSchema – retrieves the fields from getFields and builds a schema for consumption by DataStudio
isAdminUser – returns whether the current user is allowed to activate debug and other connector feature. The connector’s script properties holds a whitelist of who these are, and isAdminUser tests Session.getEffectiveUser().getEmail() against this whitelist.
The connector as a library
One good way to test the connector is to use it as a library – that’s essentially what it is. Once deployed you can get the library id from the manage deployments menu. The old IDE deployment equivalent is ‘deploy from manifest’ (and frankly it’s a lot more intuitive than the confusing deployment soup of the new IDE). Here it is in the new IDE.
Consuming the library
Create a new project and take the id from the library url. Then you can test each of the connector’s exposed methods just as datastudio will be consuming them.
console.log(bmScrvizConnector.getConfig()) /* { configParams: [ { type: 'CHECKBOX', name: 'noCacheStudio', helpText: 'Data may already be available from recently run report', displayName: 'disable formatted data caching' }, { displayName: 'disable catalog caching', type: 'CHECKBOX', name: 'noCache', helpText: 'Data may be available from recently used scrviz access' } ] }
Making a call to getData is a little more complex as we have to emulate the request it’ll receive when talking to Data Studio and pass it to getData.
The request
This consists of 2 properties
scriptParams – at a minimum these will be the user supplied parameters you set up in getConfig. You’ll also receive a timestamp showing the last time data was refreshed to Data Studio so you can make some data currency and/or caching decisions in the connector.
fields – these are the fields you’d like the connector to populate and return, and will be all or a subset of the fields you set up in getFields and subsequently build in getSchema. My connector schema contains many more fields than shown here, but for brevity I’ve just picked a few fields for testing.
It’s the reponsibility of your getData function to return these 2 properties
schema – the schema entries for the fields in request.fields
rows – an array of values populated with the data for the requested fields
Handling isAdminUser
This function will let Data Studio know if the requestor is able to perform debugging. One way to do this is make a list of admin users in the script property store.
Something like this
function whitelist () { PropertiesService.getScriptProperties().setProperty('adminUsers',JSON.stringify(['bruce@mcpher.com']) ) }
one off setup of admin whitelist
and then your implementation of isAdminUser can use the list to discover if the effective user is an admin, like this
isAdminUser: () => { const adminUsers = PropertiesService.getScriptProperties().getProperty('adminUsers') if (!adminUsers) throw new Error ('couldnt get list for admin user check') const user = Session.getEffectiveUser().getEmail() return Boolean(JSON.parse(adminUsers).find(f=>user===f)) },
checking for admin user
However, since this is a completely public connector that I hope others will enhance, I’m just going to make everybody an admin user.
isAdminUser: () => true
make everybody admin
Testing to a sheet
It’s probably more convenient to test your results to a spreadsheet before getting involved in Data Studio, especially if you’re new to it (as I currently am), so we can easily dump the result from getData to a sheet to make sure it’s what’s expected. Using my Handly helper for fiddler library, along with the connector as another library makes this pretty much a one liner.
Add the libraries
Add these references to your testing script
Add the connector 1sEEcPeh7GZ6QoGIRFP6rbbFU89SIM9DxPTO_bKbDIYWNFD1cZ5n6T3tK
Add bmPreFiddler at 13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR
Code to dump to sheet
function sheetDump(data) { const id = '1WCqfBN-xVH9a6bccCfmLO-jmT__SHX9jx_EQDpGuajg'
Here’s a snippet of some of the results returned – it looks good, so now I can move on to connecting to Data Studio, which I’ll cover in the next post
Resusability
This is my first connector, but probably not my last, so I wanted to make sure that the next ones go smoothly. My approach to this is
use separate namespaces and script files that can be reused with minimal changes
keep out of global namespace as much as possible
only expose functions that might be needed from the library
Apps Script doesn’t have a module system, and since we don’t have control over the order in which const is interpreted by the JavaScript engine, functions that have to be exposed via the library will need to be hoisted (evaluated first) either as vars or as the old style function() syntax. We’ll get into the detail of the code later but my own approach to this is to create these namespaces. It may not work for you, but this is how I try to balance reusability within the constraints of Apps Script V8.
var Connector – contains all the functions a connector will need such as getData etc (as discussed earlier)
const DataManip – contains functions private to the connector and specific to the data to be consumed to manipulate the data for consumption by the Connector. Handles API access to get the data and specific formatting
const flattenVizzy – reduces the data to the dimensions my Data Studio report will be expecting
const Digestive – various utilities for handling caching, compressing and spreading cache across multiple cache items (the data size will be more than the limit for Apps Script caching)
Exposing required functions
Finally we need to expose and hoist some functions from the Connector namespace
// export these globally so that datastidio can see them var getConfig = () => Connector.getConfig(), isAdminUser = () => Connector.isAdminUser(), getSchema = () => Connector.getSchema(), getAuthType = () => Connector.getAuthType(), getData = (request) => Connector.getData(request), getVizzy = () => Connector.getVizzy()
exposing connector functions
What’s next
This article was an introduction to the structure of the connector, and in subsequent articles I’ll go into the full code and how to consume it in Data Studio
Motivation Every Google Apps Script project on Github visualized describes how to use https://scrviz.web.app to find and visualize public Apps Script ...
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines
