How to recreate the GitHub contribution graph with Node.js and Google Sheets
This year, I decided to focus on personal growth, with two main challenges: learning how to code, and getting in better shape. Both involve creating daily habits. Since January, I’ve been using a spreadsheet as a tracker to keep myself accountable on both fronts.
But I’ve noticed a format that I really like and that several products use: the grid of coloured squares GitHub popularised with its contribution graph.
You can see it on Makerlog for tracking daily tasks, Puregym for tracking gym sessions, and Steph Smith recently created a web app using this graph to track coding sessions.
I wanted to challenge myself and see if I could build such a dynamic graph based on my spreadsheet.
This tutorial uses Node.js, Express, the Google Sheets API, and Bootstrap. I’ll go through each step from pulling data from the spreadsheet to styling it. You can see a demo here and the final files are here. Feel free to use them however you wish.
1) Create the Google Sheet
I already had a spreadsheet, but created a copy to start with a clean file. If you want to skip this section, you can make a copy of this spreadsheet. This is data from my actual tracker if you’re curious about my coding journey 😊
If you want to tag along, let’s go through each step together.
First, add the following headers:
- Date
- Topic
- Time
- Level
- Notes (optional)
We will not pull data from the “Notes” column in the web app, it’s just a place for you to easily write anything worth remembering about a particular day.
Then, freeze the headers by clicking on View > Freeze > 1 Row.
Fill the first column with the dates from January 1st to December 31st of the current year.
Add some dummy data in the second and third column. For example, add “jQuery” as a topic you studied in the “Topic” column, and “120” as the number of minutes you studied in the “Time” column. Do this for a few dates so we can see later if things are working correctly or not.
Finally, add the following formula in the “Level” column, starting with cell D2:
=IF(C2=0,"0",IF(C2<=60,"1",IF(C2<=120,"2","3")))What this formula does is that it calculates your level of activity based on the input from the “Time” column.
- If time = 0, level = 0
- If time <= 60 minutes, level = 1
- If time <= 120 minutes, level =2
- Else level = 3
These will be helpful to allow us to pick a different colour for the squares based on how much time we dedicated to the chosen activity, in this case, learning how to code. You can edit the formula above to make the levels correspond to different times that make more sense for you and your chosen activity.
That’s it! Our spreadsheet is ready. And no, we don’t need to publish it.
2) Build your application
You can skip the first steps of this section if you’re comfortable with Node.js already. I want to write this part as I often struggle with tutorials that skip on some parts the author deems obvious.
If it’s your first time using Node.js, we’ll first need to install it. Download the installer and follow the instructions.
Then, open your terminal. I highly recommend installing Hyper, but it’s absolutely not necessary for this tutorial. If you want to stick to the default Terminal, press WinKey+R on Windows, then type cmd and press Enter, or on Mac, just search for “Terminal”.
First, let’s create a directory for our application. Type mkdir pixel-progress and press enter. mkdir stands for “make directory” and this command creates a directory called “pixel-progress”.
Let’s now go into this directory. Type cd pixel-progress (cd stands for “change directory”) and press enter so we’re now inside our directory.
We’re going to create two folders inside this directory. Type mkdir public views, which will create two directories respectively called “public” and “views”. I will explain later what these are for, but they basically hold all of the front-end part of our application.
Now type touch app.js — while mkdir is to create folders, touch is to create files, so this command will create a file called “app.js”, which will be the backbone of our application.
Okay, we have all of the necessary files to get started! Now, let’s make them a bit smarter.
Node.js comes packaged with something called npm, which is basically a super easy way to reuse code from other JavaScript developers, so you don’t have to always do everything from scratch.
For this tutorial, we are going to use a few npm packages (i.e. code written by other awesome developers) that are going to make our life easier.
First, let’s initialise npm in our directory so we can use it. Type npm init in the Terminal and press enter. You are going to see a series of prompts asking you questions such as the licence for your app, the author, etc. It’s okay to keep everything as default by just continually pressing enter until it stops asking anything.
Then, let’s install those packages we will need in our application. Type the following in the Terminal and press enter:
npm install express request ejs googleapis@39 — saveLet’s unpack this.
We’re asking npm to install the following packages:
- Express: a framework for Node.js that will help us create dynamic pages
- Request: so the client can request information from the server
- EJS: a templating language that will let us generate HTML with JavaScript
- googleapis@39: the Google Sheets API
The first three ones are basic packages you usually install anytime you create a new Node.js application. The last one I found in the Google Sheets API documentation, which we will review in more depth later.
Phew. That was a lot to get started. Now let’s actually get cracking.
In the Terminal, type {name of your programming text editor} . including the full stop. If you use VS Code, that would be code ., for Atom, it’s atom . and for Sublime, sublime . — the . tells the terminal to open this folder.
Type the following inside app.js and save the changes:
const express = require('express');const request = require('request');
app = express();
app.set('view engine', 'ejs');
app.use(express.static('public'));
app.listen(process.env.PORT || 3000, function() {console.log('Server running on port 3000.');});
The first two lines tell our app to require the “Express” and “Request” modules we installed earlier. We then create a new Express instance, set our templating engine to EJS, and tell Express to serve static files from the “Public” directory we created earlier.
Finally, we tell our server which port to listen on. Since we haven’t deployed our app yet, we will use port 3000, but the process.env.PORT bit will let our environment (for example Heroku) set the variable for us later.
Let’s check if everything is working by creating our first route. Between the app.use and the app.listen parts, type the following:
app.get("/", function(req, res){console.log("Hello!");}
Now open your browser, and type localhost:3000 into the URL bar. Then, open your terminal. If everything has gone well so far, you should see “Hello!” printed in the Terminal.
3) Connect your Google Sheet and your Node.js application
For this part, we’re simply going to follow the Google Sheets API documentation. You should make sure you’re logged in with your Google account.
First, click on “Enable the Google Sheets API”. In the pop up window that appears, click on “Download client configuration”. Place the credentials.json file that you just downloaded in your working directory, the same one as where your app.js file is.
As we’ve already installed the library, we can skip to copying and pasting the code sample provided in the documentation into our app.js file.
First, add this part below your other required packages at the top of your file:
const fs = require('fs');const readline = require('readline');const {google} = require('googleapis');
Then, replace the console.log("Hello!") line which was for testing purposes with this part:
// Load client secrets from a local file.fs.readFile('credentials.json', (err, content) => {if (err) return console.log('Error loading client secret file:', err);// Authorize a client with credentials, then call the Google Sheets API.authorize(JSON.parse(content), listMajors);});/*** Prints the names and majors of students in a sample spreadsheet:* @see https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit* @param {google.auth.OAuth2} auth The authenticated Google OAuth client.*/function listMajors(auth) {const sheets = google.sheets({version: 'v4', auth});sheets.spreadsheets.values.get({spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',range: 'Class Data!A2:E',}, (err, res) => {if (err) return console.log('The API returned an error: ' + err);const rows = res.data.values;if (rows.length) {console.log('Name, Major:');// Print columns A and E, which correspond to indices 0 and 4.rows.map((row) => {console.log(`${row[0]}, ${row[4]}`);});} else {console.log('No data found.');}});}
Finally, copy and paste all of this just before the app.listen part:
// If modifying these scopes, delete token.json.const SCOPES = [‘https://www.googleapis.com/auth/spreadsheets.readonly'];// The file token.json stores the user’s access and refresh tokens, and is// created automatically when the authorization flow completes for the first// time.const TOKEN_PATH = 'token.json';/*** Create an OAuth2 client with the given credentials, and then execute the* given callback function.* @param {Object} credentials The authorization client credentials.* @param {function} callback The callback to call with the authorized client.*/function authorize(credentials, callback) {const {client_secret, client_id, redirect_uris} = credentials.installed;const oAuth2Client = new google.auth.OAuth2(client_id, client_secret, redirect_uris[0]);// Check if we have previously stored a token.fs.readFile(TOKEN_PATH, (err, token) => {if (err) return getNewToken(oAuth2Client, callback);oAuth2Client.setCredentials(JSON.parse(token));callback(oAuth2Client);});}/*** Get and store new token after prompting for user authorization, and then* execute the given callback with the authorized OAuth2 client.* @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.* @param {getEventsCallback} callback The callback for the authorized client.*/function getNewToken(oAuth2Client, callback) {const authUrl = oAuth2Client.generateAuthUrl({access_type: 'offline',scope: SCOPES,});console.log('Authorize this app by visiting this url:', authUrl);const rl = readline.createInterface({input: process.stdin,output: process.stdout,});rl.question('Enter the code from that page here: ', (code) => {rl.close();oAuth2Client.getToken(code, (err, token) => {if (err) return console.error('Error while trying to retrieve access token', err);oAuth2Client.setCredentials(token);// Store the token to disk for later program executionsfs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {if (err) return console.error(err);console.log('Token stored to', TOKEN_PATH);});callback(oAuth2Client);});});}
Run the sample by typing node . inside the terminal, and follow the instructions to authenticate yourself. You will obtain an authorisation code which you will need to paste into the Terminal. This step is important as this is what allows your app to access your spreadsheet even though you haven’t made it public.
Now that everything is working, let’s modify the code so we’re actually using our own spreadsheet rather than the sample one.
- Rename the function from
listMajorstopullData— be careful as it appears twice inapp.js, first to authorise access to our spreadsheet, then to actually run the function. - Replace the spreadsheet ID with yours — you will find this in the URL of your spreadsheet.
The current function pulls data from only one range. We want to pull data from several ranges in our spreadsheet. Lucky for us, there is a method provided by the Google Sheets API called batchGet which allows us to “return one or more ranges of values from a spreadsheet.”
First, let’s replace sheets.spreadsheets.values.get with sheets.spreadsheets.values.batchGet to use this method.
Then, replace range with ranges, and enter the ranges we want to access from our spreadsheet:
ranges: ['2019!A2:A366', '2019!B2:B366', '2019!C2:C366', '2019!D2:D366']In this case, “2019” is the name of the specific sheet inside the spreadsheet, which name you can see on the tab at the bottom.
Now, let’s create variables for these so we can access them and do things with them. Replace the line const rows = res.data.values with the following:
const date = response.data.valueRanges[0].values;const topic = response.data.valueRanges[1].values;const time = response.data.valueRanges[2].values;const level = response.data.valueRanges[3].values;
const data = [date, topic, time, level];
This creates four variables to store our data — date, topic, time, and level — and then puts them in a data array which will make them easier to manipulate. You can test that everything works by console logging the resulting data in the same fashion as shown in the documentation:
if (data.length) {console.log('Data:');// Print columns A to C, which correspond to indices 0 to 2.data.map((row) => {console.log(`${row[0]}, ${row[1]}, ${row[2]}`);});} else {console.log('No data found.');}
This should print the data from your spreadsheet in the Terminal.
That’s it! We’re now connected and pulling data from our spreadsheet. Now, let’s actually display it in our app.
4) Display the data from the Google Sheet in the app
It’s now time to use EJS, our templating engine of choice. Templating allows us to use special tags in our HTML markup to insert variables or run programming logic.
Replace the console log bit we just used to test everything was working with the following:
res.render('home', {data: data});If we take a step back and look at our code, what we tell our app is “If someone accesses the / route, run all of that code that accesses my Google Spreadsheet, and render the home page while including the resulting data.”
Okay, but we don’t have a home page, so let’s create it. Inside your views folder, create a home.ejs file. Fill it with your regular HTML boilerplate.
Inside a div, add the following:
<%= data %>This tells the file to display the data we passed earlier. Now, refresh the page in your browser at localhost:3000 to see if everything is working. You should see something really ugly with basically all of the placeholder data you included in your spreadsheet at the beginning of this tutorial.
Awesome! Now, let’s make this pretty.
5) Style the data to make it look like the GitHub contributions graph
I’m not great at CSS and didn’t want to reinvent the wheel here, so I used this cool tutorial by Ire Aderinokun. There are probably better ways to go about it, and this is not the most accessible approach (tables would have been better) but it did the job. Please do share if you go about it another way!
Here is the code pen where you can copy the HTML and CSS.
First, paste this part into your home.ejs file:
<div class="graph"><ul class="months"><li>Jan</li><li>Feb</li><li>Mar</li><li>Apr</li><li>May</li><li>Jun</li><li>Jul</li><li>Aug</li><li>Sep</li><li>Oct</li><li>Nov</li><li>Dec</li></ul><ul class="days"><li>Sun</li><li>Mon</li><li>Tue</li><li>Wed</li><li>Thu</li><li>Fri</li><li>Sat</li></ul><ul class="squares"><! -- added via javascript --></ul></div>
Then, go to your public folder, create a css folder, and then inside this folder create a styles.css file to paste the CSS provided in the Code Pen above.
Inside the <head> tags in your home.ejs file, add the following to link to the spreadsheet:
<link href="css/styles.css" rel="stylesheet">Let’s ignore the JavaScript in the Code Pen as it generates random values to fill the grid, and let’s write our own. Replace <! -- added via javascript --> with the following:
<% for (var i = 0; i < 364; i++) { %><li data-level="<%= data[3][i] %>"></li><% } %>
This pulls data from the fourth position in our data array (the const data = [date, topic, time, level] bit in our app.js file), which corresponds to the level of activity we defined before. We loop through day 1 to day 365 of the year.
Refresh your page, which should now display the graph, as well as coloured squares for the days you filled with placeholder data!
Another bit of data that would be nice to display is what you actually did on that day, such as a gym class or a specific topic you studied. Let’s use the Tooltips component in Bootstrap.
First, let’s add this inside the <head> tags in our home.ejs file:
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet”>And this at the bottom of the file, just before the closing </body> tag:
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script><script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script><script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.bundle.min.js"></script>
These scripts are just jQuery, as well as the basic Bootstrap script and Popper.js, which the Tooltips documentation asks us to include in order to display tooltips.
The documentation also tells us to initialise our tooltips with the following:
$(function () {$('[data-toggle="tooltip"]').tooltip()})
Let’s create a tooltips.js file inside a public/js folder containing the code above, and include it like so, just before the Bootstrap script in our home.ejs folder:
<script src="js/tooltips.js"></script>Finally, let’s actually display tooltips on hover with the “Topic” data from our spreadsheet.
Replace <li data-level="<%= data[3][i] %>"></li> with the following:
<li data-toggle="tooltip" data-placement="bottom" data-animation="false" delay="0" title="<%= data[1][i] %>" data-level="<%= data[3][i] %>"></li>This tells our app to:
- Toggle the tooltips on hover
- Display the tooltips at the bottom of the squares
- Not animate them when they appear
- Make them appear with a delay of 0
The content of the tooltip is pulled via <%= data[1][i] %>, which is the second column of our spreadsheet.
Refresh your page, and hover over some of the coloured squares to see if it’s working. You can play with the CSS to style the tooltips or change the colours of the squares.
This bit in particular allows you to change the colours of the squares:
.squares li {background-color: #D7DDF2;}.squares li[data-level="1"] {background-color: #577AF9;}.squares li[data-level="2"] {background-color: #3960EF;}.squares li[data-level="3"] {background-color: #1B3699;}
For example, I decided to use variations of blue instead of green.
Voilà! I hope you found this fun. You can find the final files here and the demo here.
p.s. I don’t usually write tutorials, but if you’re interested in my other articles about productivity, creativity, and product design, you can subscribe to my newsletter.
