Build a resource dashboard with Google Sheets

We will look at how you can build your own resource monitor for your web project inside of Google sheets.

Along the way Google will ask you multiple times if your script is allowed access to certain elements of your spreadsheet.

How I started going down that rabbit hole

For debugging reasons I had a lot historical data on bundle size for the project I am working on. I wanted to make a nice graph and show our customer, how much we have improved bundle size the last 8 months (grey in the example spread sheet). After that I thought, can't I automate that? (It turns out, I can)

Jump into the script editor

If you have your spreadsheet open, go to tools > Script editor to open up the editor. The scripts you write here will run in Googles Apps Script runtime powered by Chrome V8.

First setup:

var url = 'https://example.com';
var websiteContent = '';

function getWebsite() {
  // we will do stuff here
}

Parse the website

If your site ships with it's bundles having the same name with each release, you can skip this point.

So first things first. We need to get the resource links for the bundles we want to include in our dashboard. In my case I load and parse the website into a string and get the links with a regex.

function getWebsite() {
  //fetch site content
  websiteContent = UrlFetchApp.fetch(url).getContentText();
  var mainScriptRegex = /src='(\/resources\/scripts\/main\?v=.*?)'/m;
  var mainScript = prefixWithUrl(
    getMatchCaptureGroup(mainScriptRegex)
  );
  // now we have the URL of our mainScript bundle
  // Feel free to add other resources as desired.
}

function getMatchCaptureGroup(regex) {
  return websiteContent.match(regex)[1];
}

function prefixWithUrl(path) {
  return url + path;
}

Get the resource size

This was the hardest part to figure out. How do I translate my URL into actual kB? It is actually really easy, with the build in functions that come with the script editor. I build a little helper function, as I am reusing it multiple times.

// pass in bundle url and return size in kB
function getResourceSize(url) {
  var content = UrlFetchApp.fetch(url).getContent();
  var byteArray = new Uint8Array(content);
  return byteArray.byteLength / 1024;
}

Debug it

A great tool along the way is the build in Logger.

Logger.log('whatever')

You can access the Logs after running your script via View > Logs.

Set up your spreadsheet

Now is a good time to set up your spreadsheet. The only thing you need to to, is set up your headers. I recommend using a column for a timestamp too.

date	version	size-main-js	size-main-css
data	will	go	here

Insert stuff into Sheets

Now we are at a point where we can add our data into the spreadsheet. I decided to check, if any of the data has changed compared to the last entry, so I'll have a more condensed data table.

function getWebsite() {
  // some stuff before...

  // "yyyy-MM-dd'T'HH:mm:ss", needed this way to be recognized as date
  var date = Utilities.formatDate(new Date(), 'Etc/GMT', "yyyy-MM-dd HH:mm:ss");
  // Object with head row names as key
  insertRowInTracker({
    date,
    version,
    'size-main-js': getResourceSize(mainScript),
    'size-main-css': getResourceSize(mainCss),
  });
}

function insertRowInTracker(rowData) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  // conditional check start
  var rowValues = [];
  var write = false;
  var dataRange = sheet.getDataRange();
  var lastEntries = dataRange.offset(dataRange.getNumRows() - 1, 0, 1).getValues()[0];
  var columnHeaders = dataRange.offset(0, 0, 1).getValues()[0];
  columnHeaders.forEach((header, index) => {
    rowValues.push(rowData[header]);
    // do not check index 0, as it is the timestamp
    if (index) {
      write = write || rowData[header] !== lastEntries[index];
    }
  });
  if (!write) return
  // conditional check end
  sheet.appendRow(rowValues);
}

Finishing up

To make this more useful, lets add another nice feature to our script. We wouldn't want to manually click all the time, so let's add a trigger, who crawls the website for us automatically.

function createTrigger() {  
  // Trigger once a day
  // Frequency is required if you are using atHour() or nearMinute()
  ScriptApp.newTrigger('getWebsite')
      .timeBased()
      .atHour(8)
      .everyDays(1)
      .create();
}

If you run that function once, it will set up the trigger and will call the getWebsite function once a day.

The last thing that needs to be done is to set up a graph for your data. I hope this helps you along and will help you to keep an eye on the script bundles. 😉

Example

here is the link to my example dashboard