Google Apps Script: An Unsung Hero

Google Apps Script is a service provided by Google that allows small bits of JavaScript that can interact with other Google Services and other services that provide a web-based API. The Apps Script can be triggered based on web requests, Google Forms, Google Sheets, Google Docs, or, most interestingly, a timer.

The time-based triggers are extremely useful as they allow us to schedule tasks daily, weekly, or monthly at specific times, similar to old-fashioned cron jobs. By doing so, we can run batch jobs to connect datasources, such as SalesForce and Google Analytics, automatically.

Let’s stop and ponder this a moment. Where before we would have needed our own server in order to run scheduled tasks every night, we now have that opportunity for free with Google Apps Script. Where before we would have had to have knowledge of servers and server-side languages, we can now write complicated, automated, cloud solutions using JavaScript.

Apps Script is an extremely powerful and versatile platform. One of the biggest benefits, it lives within the Google Network. This means that it natively integrates with many Google services, including Analytics, Sheets, Docs, and Drive, and can be made to work with many other services, e.g. SalesForce, Dropbox, EventBrite, and Marketo, that have an HTTP API.

Apps Script supports external libraries, such as the OAuth2 Wrapper provided by Google, which can be imported into Apps Script projects to aid in development. While there is, unfortunately, no central directory of published libraries, you are still able to publish your own libraries to minimize code that needs to be duplicated between projects.

Below, I’ll dive into some high-level use cases and examples from how we use the Google Apps Script with our clients and our own website.

Example: SalesForce and BigQuery

One of the most common challenges we face is connecting data from different sources together. The other big challenge is moving data around. Here’s a concrete example from our LunaMetrics website that uses both.

“Can I see someone’s website activity in Salesforce?”

When a potential client reaches out to LunaMetrics in some capacity, most often using the Contact Us box at the bottom of every page, their request ultimately ends up in SalesForce. Similar to Method 1 of our Salesforce integration post, we store the Google Analytics client ID both in Google Analytics as a custom dimension, as well as a custom field with the Salesforce record.

By having the same key in two places (the GA client ID) we now have the ability to connect the data together. Now we need to move everything around. Our hero of the day? Google Apps Script!

We use Apps Script to trigger at night, grab records from SalesForce, run a query in BigQuery using the client ids stored in SalesForce, aggregate the results, and then upload the updates to SalesForce.

Example: SalesForce and Google Analytics

“Can I see someone’s Salesforce information in Google Analytics?”

With the “SalesForce and BigQuery” example above, we’ve already completed most of the work! We already have the data from Salesforce, and we have a reliably way to connect it to the data in Google Analytics. We just need to programmatically upload information into Google Analytics.

Enter the Google Analytics Data Import API. Now, during the same nightly batch, we upload Google Analytics to SalesForce, and then takes other data from SalesForce, such as Industry, Status, and Company name and import that into Google Analytics.

Example: EventBrite and Google Sheets (and more!)

“Can I streamline how I communicate information to our team?”

Every tool these days seems like it comes with their own ‘reporting solution’ and ‘intuitive dashboard.’ This is great for people who log in every day, but not so great for a large team with whom you need to communicate. Luckily, Google Apps Script integrates really nicely with other Google products.

Let’s look again at LunaMetrics. We currently use EventBrite as the ticketing service for our Google Analytics, Google AdWords, and Google Tag Manager trainings and wanted an easy way to aggregate all of the data that they provide us. We needed a better solution to allow trainers to see their future and past attendees as well as monitoring the current attendance for each city.

Using Apps Script on a time-based trigger, we pull information from EventBrite’s API several times throughout the day, process it, and place it into a Google Sheet. This accomplishes the first objective so that our trainers and event coordinators can have all the details they need in a single place.

Next, we use the same information to match attendance with capacity for each upcoming event, as well as the scheduled trainers. We then, using the Google Calendar API, add this information to a shared Google Calendar, so any trainer at any time can pull up their phone or computer and look ahead to see how many people have registered for our Austin, TX training in November or whichever training that have coming up next.

We can also use Apps Script to periodically email team members using the native Gmail integration, on our own schedule or based on specific Triggers.

Parting Thoughts

Google Apps Script is an unsung hero when it comes to moving data around or performing scheduled tasks. Being a free, fairly versatile service makes it an excellent choice for small jobs that need to interact with multiple services online.

While Apps Script, like all services, has some quotas, the majority of them are not ones we often hit. However, the 6 minute execution time does me that large datasets cannot be processed in a single run. While often this isn’t a problem, it is something to keep in mind if you notice your script taking a while.

There are a few other warts to be aware of, but keep in mind all the benefits you get for free!

• Multiple people will have difficulties editing the same project simultaneously
• The built-in version control is lacking the finesse of git, hg, or even svn
• It is difficult to move scripts between projects (the source project is fully deleted)
• Logging errors leaves much to be desired

I hope this post gets your creative juices flowing on how to leverage this awesome tool for your benefit.