A review of using google-apps-script for data analysis in d3js

Hi, I’ve spent about 6 months working on creating d3js graphs from gSheets data. I want to share that with you. This is a super in-depth tutorial.

Table of contents: I. Intro: Why do this at all? II. How does it work? III. Following the 4 steps. IV. Conclusion, with links to my sample design/gSheet, github codes for the script, and some general thoughts.

Note: if you’re using Observable or otherwise want to embed a d3js graph into a google-apps-script web-app, only follow up steps 1–2. The rest of it is much, much simpler if you understand Runtime. Maybe my second article will be a guide to embedding observable notebooks into google …

I. Why do this at all?

First and foremost, if you are committed to using d3js, chances are you’re willing to spend a lot of time doing “this” (bad pun). So let me be clear: this is nowhere near as efficient as using a pivot table, or downloading gSheets as an excel, or using a third-party app like Tableau that is more user-friendly and less coding intensive, or than printing out the data and drawing a graph by hand with a ruler.

Having said that, there are some advantages to using d3 in google-apps-script. You can make a webapp that takes in gSheets data (that can pull from practically anywhere) and makes a d3 graph; you can concurrently have “buttons” that refresh your data & keep it dynamic; you can design not just the graph, but the entire webapp’s webpage as you see fit. It’s a data analyst’s kingdom all at the price of two things: time and effort (and: struggle, frustration, unrelated hatred of redbean flavor).

So let’s save you as much time as possible — I know there has been talk of this, but I wanted to present my own approach in creating a stacked/group chart (based off Mike Bostock, Ralph Allier).

II. How does it work?

Google-apps-script’s html-service api lets you render html files as a web-app. I sort of had the following conversation with myself:

I want to make a d3js graph.

On what?

A webpage. *step 1: create a webpage file*

From what html code?

index.html. *step 2: write your html code*

Where’s the d3js graph’s code?

index.html, make the thing show my d3js script. *step 3: have your webpage talk to your d3js script*

Want me to run it?

index.html, run my d3js script *step 3.1: run d3js script in index.html*

What data is your graph based off of?

These values on my spreadsheet. *step 4: define data then hand it off to your d3js graph*

III. The 4 Steps

Step 1

Webpage files that are based off apps-scripts and html files are called templated html. This concept is what links our code.gs (apps-scripts) and html (d3js graph) files: without templated html, we’d have no way to combine anything written as a google-apps-script and anything written as a d3js graph.

Google web-apps require a doGet() function like the below to be in your code.gs file. What do I mean by “code.gs” file? If you’re on your gSheet, click Tools → Script editor. Google’ll give you one:)

//Put this into your code.gs file.function doGet(){ return HtmlService.createTemplateFromFile("index.html")
//generates an HtmlTemplate from the index.html file
.evaluate()
//executes printing scriptlets & converts template into an //HtmlOutput file,the latter action being mainly for security //reasons.
}

Note: Don’t get bogged down by .evaluate() sounding complex; it is. It’s meant to make our scriplets work. Scriplets can either be denoted by <? … ?> or <?= … ?> or <?!= … ?>, referred to as “scriplets”, “printing scriplets”, and “force-printing scriplets” respectively.

Scriplets execute code w/out displaying it, sort of like how <p> Hi! </p> on html just shows up as “Hi!”, except for more complicated things like if loops. Not useful for this.

Force-printing scriplets are what we’ll use later in step 3 to run our d3js code, so we need .evaluate().

Step 2

What’s index.html? It’s your html code that your webpage is based off of. In your script editor, go to File → New → html file. Name it index.html. If it interests you, google-apps-script serves pages as html5 with some restrictions.

<!DOCTYPE html>
<style type="text/css">
body{
color: white;
background: #EDE7F6;
font-family: sans-serif;
}
</style>
<head>
<body>
<p> hey </p>
</body>
</head>

Step 3

To have index.html talk to our d3js script, we need to have a d3js script. Go to File → New → html file. Name it “chart.html”, or whatever you want really who am I to stop you :3

If you’re using Observable or some other notebook, the process will look much, much simpler after this, so disregard Steps 3/4/5. Don’t try to paste your observable code, or outputted javascript code into chart.html — you will not even need the chart.html file. I’ll go over that in maybe my next article.

Sorry for the detour. Let’s get back to chart.html. What’ll it look like?

<script>function drawChart(data) { //Your d3js code. drawChart takes in our data variable, which
//we will outline how to create and give onto this function
//in step 4.
}</script>

We use the <script>tag to embed client-side script (JavaScript) — but how do we make sure this is read as d3 javascript, as d3js? Back to index.html we go!

In our index.html, we need to reference both d3js (the classic <script src=></script> stuff) and our chart.html file, our d3js code. Remember how I said we’d use force-printing scriplets to run our d3js code, that’s why we need .evaluate() in our doGet()? Boom, full-circle.

Given that we need to use <?!= … ?>, how would we reference our d3js file? To give time for you to reflect on all up until now, please take five minutes to think on this. You’ll need to understand templated html and scriptlets and creating html outputs from files.

Getting a little stuck? Remember that templated html is just our interface between apps-script and code.gs. It’s not used for our d3js graph itself, so don’t try to create a template html out of “chart”. Instead, create a regular old HtmlOutputFromFile.

Answer is below:

<!DOCTYPE html><style type="text/css">
body{
color: white;
background: #EDE7F6;
font-family: sans-serif;
}
</style>
<head>
<body>
<p> hey </p>
<script src="https://d3js.org/d3.v5.min.js"></script>
<?!= HtmlService.createHtmlOutputFromFile("chart").getContent() ?>
</body>
</head>

Great job for trying it out, regardless of how similar it is to my answer. You might have even come up with, or at least be on the right track, of something totally different and awesome!

We want to output a script, right? We used <script> tags in our d3js code. So, we use the .createHtmlOutputFromFile method of HtmlService. Again, templated html is just used for hybrids of code.js and html. Index.html is our only templated html file. We use .getContent() to serve the content of our html file. Without .getContent(), the html output is word-for-word just whatever is typed in “chart”.

Step 3.1

We’re in shape, we’re healthy, almost ready to run the race (pretend our d3js code is an Olympic runner). We’ve decided we want to run a race & entered in as a contestant (doGet), designed a race map (index.html), now all we need is to actually run (not forgetting to breathe) and bring water.

So how do we run? We have to run index.html, right? And we have to run whatever scripts are in it. We have to run the marathon, but we have to make sure we breathe while doing it. That’s where the google.script.run method comes in handy, coupled with window.onload.

What about the water? We need to make sure our data is eaten up by gluttonous drawChart(). Fancy way of saying this is getChartData() is a callback function we want to run if the server-side function drawChart() runs successfully.

So we need something that looks like this in our index.html file:

<!DOCTYPE html><style type="text/css">
body{
color: white;
background: #EDE7F6;
font-family: sans-serif;
}
</style>
<head>
<body>
<p> hey </p>
<script src="https://d3js.org/d3.v5.min.js"></script>
<?!= HtmlService.createHtmlOutputFromFile("chart").getContent() ?>
<script>

window.onload = function() {
google.script.run
.withSuccessHandler(drawChart)
.getChartData()
}

</script>
</body>
</head>

Step 4

We need to define data & make sure function drawChart(data) gets the message.

How do we define data? Back to code.gs we go!

function getChartData() {//hey pls return data :(}Logger.log(getChartData())

How do we return data? We use methods of the Spreadsheet App stuff to return data as an object.

Note: want to check what data is equal to? Click Run → getChartData(), then click View → Logs. Using Logger.log(getChartData()), we see what data is returned. I’ve done the same for Logger.log(values) in the below, so that you get an idea of how getRange() reads our gSheet.

For my chart I don’t just pull what’s on my gSheet — that’s the whole mapTree stuff (I got help on StackOverflow from user TheMaster, thank you so much once again!).

function getChartData() {   const data = []; //we are about to push an object into here      const sh = SpreadsheetApp.getActive().getSheetByName('Sheet1');
//define sh as Sheet1 basically
const values = sh.getRange(1+1, 2, sh.getLastRow()- 1, 2).getValues()
//uses the getRange method to pull cell values from row 2 down to
//the lastRow, from column 2 to column 3.
Logger.log(values)const mapTree = values.reduce((map, [fruit, orchard]) => {
if (!map.has(orchard)) map.set(orchard, new Map());
let fruitMap = map.get(orchard);
let fruitCount = fruitMap.get(fruit) || 0;
fruitMap.set(fruit, ++fruitCount);
return map;
}, new Map());
mapTree.forEach((fruitMap, orchard) => {
const obj = { orchard };
data.push(obj);
for (const [fruit, fruitCount] of fruitMap) obj[fruit] = fruitCount;
});
return data;}Logger.log(getChartData())

Conclusion

Some cool things can be achieved by this method. Say you have a spreadsheet like this that lists x & y — you want to count the # of occurences of x, grouped by y, and show that in stacked/group graph. Here’s the graph — if you were to add more data to the spreadsheet, it will auto-update in the graph. The “feed me more data” function is for if you’re pulling data from somewhere else and pasting that onto your gSheet, you can have a button that triggers the refresh for you, since getChartData() wouldn’t be able to. Right now I’ve just left an example where it shows today’s date …

Github can be found here. You can see my index.html, code.gs and chart.html files.

Next steps: if you have a more simpler data structure in gSheets that you just need to make into a JSON, or if you are working in Observable and want to embed that into google web-apps, stay tuned for my future articles.

In a world where data flows like water, I do like the idea of learning the dynamics of it — even if it takes more time and effort — not to mention the artistic freedom you get after making it work.

Thanks!

Ye Qiu

叶秋 pianist & data scientist, liamisaacs.com