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.

Motivation

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.

scviz datastudio report

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.

connector as a library

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.isAdminUser())
// true

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' } ] }
	   
*/
console.log(bmScrvizConnector.getSchema())
/*
{ schema: 
   [ { name: 'ownerName',
       label: 'Developer',
       dataType: 'STRING',
       semantics: [Object] },
     { semantics: [Object],
       dataType: 'BOOLEAN',
       name: 'ownerHireable',
       label: 'Hireable' },
     { semantics: [Object],
       label: 'Location',
       dataType: 'STRING',
       name: 'ownerLocation' },
     { semantics: [Object],
       label: 'Owner Id',
       name: 'ownerId',
       dataType: 'NUMBER' },
     { label: 'Followers',
       name: 'ownerFollowers',
       defaultAggregationType: 'MAX',
       semantics: [Object],
       dataType: 'NUMBER' },... etc
*/
console.log(bmScrvizConnector.getAuthType())
/*
{ type: 'NONE' }
*/
testing connector as a library

Getting the data as a library call

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.
    
const request =
  {
    scriptParams: {
      lastRefresh: '1628897778779',
      noCache: false,
      noCacheStudio: false
    },
    fields: [
      { name: 'ownerName' },
      { name: 'ownerId' },
      { name: 'ownerAppsScriptRepos' },
    ]
  }
console.log(bmScrvizConnector.getData(request))
/*
{ schema: 
   [ { name: 'ownerName',
       dataType: 'STRING',
       semantics: [Object],
       label: 'Developer' },
     { label: 'Owner Id',
       dataType: 'NUMBER',
       name: 'ownerId',
       semantics: [Object] },
     { semantics: [Object],
       name: 'ownerAppsScriptRepos',
       dataType: 'NUMBER',
       label: 'GAS repos',
       defaultAggregationType: 'MAX' } ],
  rows: 
   [ { values: [Object] },
     { values: [Object] },
     { values: [Object] },... etc
*/
getData, request and response

The response

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'

  Object.keys(data).forEach (key=> {
    const d = data[key]
    console.log('writing sheet', d)
    bmPreFiddler.PreFiddler().getFiddler({
      id,
      sheetName: key,
      createIfMissing: true
    }).setData(d.rows.map(row => {
      return d.schema.reduce((p, c, i) => {
        p[c.name] = row.values[i]
        return p
      }, {})
    }))
    .dumpValues()
  })
}
dump to sheet

Dump the getData result

  const request =
  {
    scriptParams: {
      lastRefresh: '1628897778779',
      noCache: false,
      noCacheStudio: false
    },
    fields: [
      { name: 'ownerName' },
      { name: 'ownerId' },
      { name: 'ownerAppsScriptRepos' },
    ]
  }
  const data = bmScrvizConnector.getData(request)
  sheetDump({
    owner: data
  })
dump data to sheet

Sheet result

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

Links

bmPreFiddler

github: https://github.com/brucemcpherson/bmPreFiddler

library key: 13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR

scrviz : https://scrviz.web.app?manifest=brucemcpherson%2FbmPreFiddler%2Fappsscript.json

bmScrvizConnector

github: https://github.com/brucemcpherson/bmScrvizConnector

library key: 1sEEcPeh7GZ6QoGIRFP6rbbFU89SIM9DxPTO_bKbDIYWNFD1cZ5n6T3tK

consumeScrvizconnector

github: https://github.com/brucemcpherson/consumeScrvizconnector

sharing scrviz data studio report
This series of articles will work through how to create a connector for Data Studio in Apps Script. For an ...
Read More
info card on hover or click
Info Card customization By default the info card appears when you hover over a node in the scrviz visualization. Although ...
Read More
copy the library id
You want to include an Apps Script library, and you know it's name, but not its id. A pain right? ...
Read More
scrviz profiles on github
A few scrviz updates today, with more flexibility around the depth of the repo visualization and more options to enrich ...
Read More
In Enrich your developer profile on scrviz I showed how scrviz could be used to show off your Apps Script work to ...
Read More
In Showcase your Apps Script work and get hired via vizzy I showed how scrviz could be used to show ...
Read More
Motivation You'll know from Every Google Apps Script project on Github visualized that you can get a very large diagram of ...
Read More
Apps script github to ide
Motivation Every Google Apps Script project on Github visualized describes how to use https://scrviz.web.app to find and visualize public Apps Script ...
Read More
sheets google apps script
Most years I do a post on 'a year in Apps Script', looking back over the changes in the platform ...
Read More
This series of articles will work through how to create a connector for Data Studio in Apps Script. For an ...
Read More
scviz datastudio report
This series of articles will work through how to create a connector for Data Studio in Apps Script. The source ...
Read More
vizzycache project
In Every Google Apps Script project on Github visualized  I demonstrated an app that could be used to explore what every ...
Read More
exchange apps script foriegn currency
This article will cover the translation of the Sheets workbook filter type functions for  Apps Script. All of them will ...
Read More
sheets google apps script
This article will cover the translation of the Sheets workbook database type functions for  Apps Script. All of them will ...
Read More
sheets google apps script
This article will cover the translation of the Sheets workbook Array type functions for  Apps Script. Most of them will ...
Read More
apps script drive pile of files
The method for doing this is actually part of the bmFolderFun library documented in A handier way of accessing Google ...
Read More
sheets google apps script
In Blistering fast file streaming between Drive and Cloud Storage using Cloud Run I showed how you could use Cloud ...
Read More
apps script skewed distribution
Sometimes you need to generate some fake data for a spreadsheet. In this post I'll cover a few utilities in ...
Read More
Motivation You've written a great Apps Script library and you want to know how many people are using it, and ...
Read More
crusher files on drive
Another quick demo of data sharing Here's a challenge that shares the data in a spreadsheet with node, set up ...
Read More
admin.google.com
This is part of the series on sharing data between Apps Script and Node on various backends, Apps script library with ...
Read More
Another quick demo of data sharing There are many ways to accomplish this of course, but using Caching across multiple Apps ...
Read More
Quick demo of data sharing There are many ways to accomplish this of course, but using Caching across multiple Apps Script ...
Read More
Quick demo of data sharing There are many ways to accomplish this of course, but using Caching across multiple Apps Script ...
Read More
apps script crusher on github
Github as an Apps Script cache platform Another plugin available for Apps script library with plugins for multiple backend cache ...
Read More
upstash graphql explorer
Upstash as an Apps Script cache platform Upstash is a brand new service offering a serverless redis over https  via ...
Read More
upstash graphql console
Apps Script, Redis and GraphQL - together I'm a great fan of both Redis and GraphQL. You'll find plenty of ...
Read More
Cache data on Microsoft OneDrive
OneDrive as Cache platform In Apps script library with plugins for multiple backend cache platforms I covered a way to get ...
Read More
crusher on google cloud storage
Google Cloud Storage as Cache platform In Apps script library with plugins for multiple backend cache platforms I covered a way ...
Read More
cache drive apps script
Motivation This library used to be part of my cUseful collection, but I've decided to pull out into a library ...
Read More
gapi and vuex
Motivation JavaScript authentication with Gapi is both impressive and frustrating. Frustrating because in most of the examples you come across, ...
Read More
Apps Script Vizzy update Every Google Apps Script project on Github visualized introduces a way of visualizing  public Apps Script ...
Read More
vizzy profile info
Get yourself found as an Apps Script consultant Every Google Apps Script project on Github visualized  introduced this vizzy app  ...
Read More
scrviz - vizzy - manifests
In Every Google Apps Script project on Github visualized I introduced Vizzy  which uses GitHub data as its source. That same ...
Read More
scrviz - vizzy - libraries
In Every Google Apps Script project on Github visualized  I demonstrated an app that could be used to explore what every ...
Read More
scrviz -vizzy repo owners
Motivation There are so many Apps Script projects out there where the source code is published on Github, but it's ...
Read More