Over the course of my career, I've found one tool in my toolkit more useful than any other. It's a service called Google Apps Script, a somewhat obscure suite of functionality provided by Google that allows you do, well, almost anything.

I stumbled into the Apps Script world sometime around 2013. At that time, I was working as a Marketing Manager for LunaMetrics (now a member of the Bounteous Corporate Alliance family).

Our services included a hodge-podge of digital marketing things, like SEO/PPC and social media work. Our bread and butter was Google Analytics consulting. We'd help clients to instrument their sites with Google Analytics to generate useful reports and insights.

One thorn in our side was sampling. Google Analytics was crazy flexible - you could combine dozens of dimensions (e.g. traffic source, landing page) with dozens of metrics (e.g. pageviews, users) into custom reports. However, when you strayed outside the standard reports there were some hard limits on how much data the tool would give you. Cross that line and you'd get a sampled report.

Screenshot of the Bounteous blog

A sampled report was the data you asked for, but calculated on a subset of the hits you'd sent in to Google Analytics. In theory, totally reasonable - a statistically valid sample should tell you the same thing as the full report.

In practice, this was a nightmare - clients would regularly get confused or frustrated when things that shouldn't really be estimated (like revenue or transactions) would be wildly different depending on if the report was sampled. For some clients, even two days worth of data would trigger a sampled report. What could be done?

Fortunately, some thoughtful soul at Google heard the wailing and gnashing of teeth and created the Google Analytics Magic Script. Magic Script was a Google Apps Script that allowed you to pull reports into Sheets using the Google Analytics API. If you were clever, you could break reporting ranges into unsampled subsests, then sum those in another separate sheet and presto! unsampled data.

From the Google blog announcing the Magic Script tool

We all were very excited about this and got to work straightaway abusing the heck out of the functionality. The more curious of us began to wonder - what else could we do with this script? Could we make it our own?

Thus began my, geez, like ten year infatuation with Google Apps Script (man is it scary to type those words out). Cracking the hood, I was delighted to find very familiar looking syntax. You see, Google Apps Script was written in Googlescript, a flavor of JavaScript (my first programming language, unless you count Excel functions).

These days it's fully JavaScript (running in the V8 runtime), but it doesn't look all that different. Unlike browser-based JavaScript, everything is synchronous; this may feel a little weird if you're used to e.g. setTimeout or async/await-style programming. After a few weeks of learning the ropes, I had cobbled together a service that would automate all of the data munging to provide unsampled reports (or better still, to provide that data as input for other tools). I was even able to write tools that used the built-in Google Analytics Admin service for automating super tedious tasks, like auditing account configurations.

I mentioned the built-ins before, and they're where GAS shines - you can interoperate with GMail, Google Sheets, Google Docs, Google Analytics, BigQuery, Google Drive, and a boatload more services - 29 in all by my count. Beyond native integrations, you've also got the UrlFetchApp service which allows you to make arbitrary HTTP calls.

You can also write an Apps Script as a server - implement a doGet and doPost call and follow the steps to publish it, and voila. This is how my Contact page works - I post to an Apps Script endpoint, then use the UrlFetchApp to verify the reCaptcha v3 token, then I send myself and the sender an email via the GmailApp service, a whopping 20 lines of code (lightly edited below).

function doPost(e) {
  const {sender, contents, token} = JSON.parse(e.postData.contents);
  if (verifyRecaptcha(token)) {
    GmailApp.sendEmail("notdanwilkerson@gmail.com," + sender, "Contact Us Submission from " + sender, contents);
  }
}

function verifyRecaptcha(token) {
  const resp = UrlFetchApp.fetch("https://www.google.com/recaptcha/api/siteverify", {
    method: "POST",
    payload: {
      secret: RECAPTCHA_SECRET,
      response: token
    },
  });
  return JSON.parse(resp).success;
}

You can also schedule functions via Triggers, which include:

• Events from integrated docs, e.g. form submissions
• Timers

I use a daily trigger to verify that the Contact form works as intended.

function testContactWorks() {
  try {
    const resp = UrlFetchApp.fetch(DEV_URL, {
      method: "POST",
      payload: JSON.stringify({
        "sender": "notdanwilkerson@gmail.com",
        "contents":"foo",
        "token": "bad",
      }),
      headers: {
        "contentType" : "application/json",
      }
    });
    if (resp.toString() !== OKAY) {
      throw new Error(`Invalid response: ${resp}.`);
    }
    GmailApp.sendEmail("notdanwilkerson@gmail.com", "ALERT: Prober worked ", `The prober worked`);
  } catch(e) {
    GmailApp.sendEmail("notdanwilkerson@gmail.com", "ALERT: Prober failed ", `The prober didn't work:\n${e.message}`);
  }
}

There are, of course, some limits on what these scripts can do, but many of these are increased if you're a Workspace customer. Most notable are the limits on storage, execution time, and external calls - but that said, for anything short of a production use case they're very generous. The tooling is pretty good, too - Google provides a local development tool called clasp, and I myself took a crack at a CLI several years back. The async/sync hurdles are a bit annoying, but I'm sure you'll manage.

So go get your hands dirty! The possibilities are endless - in a former life, I've used Apps Script to:

• Automate projection and revenue reporting
• Integrate 30+ APIs into a social media dashboard
• Serve as a backend and server for compicated Angular.js apps
• Operate a shortlink service

And a whole lot more. I hope you love it as much as I do.