How to create a tracker like the GitHub contribution graph with Node.js and Google Sheets

By Anne-Laure Le Cunff

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 😊

  • Topic
  • Time
  • Level
  • Notes (optional)
=IF(C2=0,"0",IF(C2<=60,"1",IF(C2<=120,"2","3")))
  • If time <= 60 minutes, level = 1
  • If time <= 120 minutes, level =2
  • Else level = 3

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.

npm install express request ejs googleapis@39 — save
  • 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
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.');});
app.get("/", function(req, res){console.log("Hello!");}

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.

const fs = require('fs');const readline = require('readline');const {google} = require('googleapis');
// 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.');}});}
// 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);});});}
  • Replace the spreadsheet ID with yours — you will find this in the URL of your spreadsheet.
ranges: ['2019!A2:A366', '2019!B2:B366', '2019!C2:C366', '2019!D2:D366']
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];
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.');}

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.

res.render('home', {data: data});
<%= data %>

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!

<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>
<link href="css/styles.css" rel="stylesheet">
<% for (var i = 0; i < 364; i++) { %><li data-level="<%= data[3][i] %>"></li><% } %>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet”>
<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>
$(function () {$('[data-toggle="tooltip"]').tooltip()})
<script src="js/tooltips.js"></script>
<li data-toggle="tooltip" data-placement="bottom" data-animation="false" delay="0" title="<%= data[1][i] %>" data-level="<%= data[3][i] %>"></li>
  • Display the tooltips at the bottom of the squares
  • Not animate them when they appear
  • Make them appear with a delay of 0
.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;}