It’s day two of a four day product launch. You’ve worked hard all year to create a fantastic product, test your sales systems and tell the world about this amazing offer. You know you’ve sold 100 products so far, but…
…you don’t know whether your ads are effective, which affiliates are really killing it versus which have forgotten about your launch, or even whether your own emails are converting.
Looking at your sales log only, and having to decipher what’s happened since the last time you looked an hour ago, is like trying to drive in the dark without headlights.
Thankfully there is a better way to track your sales, so you can see your data, get insights about what’s working and what’s not, and immediately act to increase your bottom line.
This post looks at how to build a real-time dashboard for the E-junkie digital sales platform using Google Sheets:
E-junkie is a digital shopping cart, used for selling digital products and downloads. The system handles the shopping cart mechanics, but does not do any data analytics or visualizations.
You can view a transaction log (i.e. a list of all your sales) but if you want to understand and visualize your sales data, then you’ll need to use another tool to do this. Google Sheets is a perfect tool for that.
You can use a Google Sheet to capture sales data automatically in real-time, and use the built-in charts to create an effective dashboard.
You’d be crazy not to have a tracking system set up, to see and understand what’s going on during sales events or product launches. This E-junkie + Google Sheets solution is effective and incredibly cheap ($5/month for E-junkie and Google Sheets is free).
The Write Life ran a Writer’s Bundle sale this year, during the first week of April. It’s a bundled package of outstanding resources for writers, including ebooks and courses, heavily discounted for a short 4-day sales window.
I created a new dashboard for The Write Life team to track sales and affiliates during the entire event. This year’s dashboard was a much improved evolution of the versions built for the Writer’s Bundle sales in 2014 (which, incidentally, was my first blog post on this website!) and 2015.
Build dashboards with Google Sheets and Data Studio
This course is dashboard-focused and entirely practical. Concepts are taught through real-life examples, starting with a basic static dashboard all the way through to the complex web marketing dashboard you can see in the screenshot above.
The course covers data visualization and dashboard best practices, dynamic dashboards, how to use Google Forms to collect data, how to import external data, how to use Apps Script to automate processes and add extra features, Google’s new dashboard tool, Data Studio, and much, much more.
What you get when you enroll:
> Over 9 hours of video content.
> Lifetime access to all the content, to learn at your own pace.
> Copies of all the finished dashboard templates for the five dashboard examples and the one Data Studio report.
> Access to all the raw data files, copies of the formulas and links to online documentation and other great resources.
> Membership to our dedicated Facebook group where you can post questions and get answers, share insights and interact with other students.
What others say about the course:
I’d recommend this course for anyone trying to make great dashboards in Google Sheets. Ben is a great teacher and he explains everything very thoroughly. Five stars from me. – Jakob R.
This course is a fantastic way to discover how to build dashboards. Ben takes you step by step through what you need to do and his approach is well thought and organised. The information covers the basics and introduces more complex subjects in a simple and easy to follow format. – Marilyn C.
Everyone talks about data culture in companies, but only a few know how to do it. Good dashboards are a great first step to democratize access to data in a simple way, and this course will definitely change the way you see your data today. – Gabriel O.
Are you ready to start building beautiful, functional dashboards?
Google launched a new business intelligence tool called Data Studio in May 2016. It’s a really smart reporting tool for quickly creating powerful, stunning dashboards from multiple Google data sources.
It’s a great option for small/medium businesses already using Google tools, who want to build bespoke dashboards for that 40,000ft view of their business.
Google Data Studio Example Reports
Here are two example reports for a mid-size website (~500k pageviews a month).
Regular readers will know of my enthusiasm for building dashboards, especially using Google apps (like this one or this how-to article).
So I was super excited in May of this year when Google launched Data Studio, a free data visualization and dashboard tool to compete against incumbent dashboard vendors Microsoft PowerBI, Tableau and Qlickview.
Here, I’m excited to share my initial impressions and show you some of the basics steps to build dashboard reports using this tool.
Google Apps Script is a cloud based scripting language for extending the functionality of Google Apps and building lightweight web-based applications.
What does this mean in practice: It’s a coding language where you can write small programs performing custom behaviors that go beyond the standard features of Google Apps. The code is stored and executed on Google’s servers.
It means you can do cool stuff like automating repetitive tasks, creating, modifying and emailing documents to people, and linking up your Google Sheets to other data sources. Heck, you can even build complex web forms, use a Google Sheet as your database, programatically create charts and publish it all to the web. In other words, you can build fully featured, lightweight web applications.
In this Google Sheets script tutorial, we’re going to write a script that is bound to our Google Sheet, or you might say contained within our Google Sheet. This is known in the jargon as a container-bound script.
(If you’re looking for more advanced examples and tutorials, check out my full list of Apps Script articles.)
Hello World in Google Apps Script
Let’s write our first, extremely basic program, the classic “Hello world” program beloved of computer teaching departments the world over.
Begin by creating a new Google Sheet.
Then click the menu Tools > Script editor... to open a new tab with the code editor window.
This will open a new tab in your browser, which is the Google Apps Script editor window:
By default, it’ll open with a single Google Script file (code.gs) and a default code block, myFunction():
In the code window, between the curly braces after the function myFunction() syntax, write the following line of code so you have this in your code window:
Your code window should now look like this:
Google Apps Script Authorization
Google have robust security protections to reduce risk from unverified apps, so we go through the authorization workflow when we first authorize our own apps.
When you hit the run button (the black triangle) for the first time, you will be prompted to authorize the app to run:
Clicking Continue pops up another window in turn, showing what permissions your app needs to run. In this instance the app wants to view and manage your spreadsheets in Google Drive, so click Allow (otherwise your script won’t be able to interact with your spreadsheet or do anything):
❗️When your first run your apps script, you may see the “app isn’t verified” screen and warnings about whether you want to continue.
In our case, since we are the creator of the app, we know it’s safe so we do want to continue. Furthermore, the apps script projects in this post are not intended to be published publicly for other users, so we don’t need to submit it to Google for review (although if you want to do that, here’s more information).
Click the “Advanced” button in the bottom left of the review permissions pop-up, and then click the “Go to Starter Script Code (unsafe)” at the bottom of the next screen to continue. Then type in the words “Continue” on the next screen, click Next, and finally review the permissions and click “ALLOW”, as shown in this image (showing different script):
Next, Google Apps Script will show you two status messages to tell you what’s happening.
First this one:
And then this one:
If anything goes wrong with your code, this is stage when you’d see a warning message (instead of the yellow message, you’ll get a red box with an error message in it).
Now, assuming you got those two yellow status messages and they’ve both automatically disappeared from view, then your program has run successfully. Click back on the browser tab with your spreadsheet (most likely the tab to the left of the one we’re in).
You should see the output of your program, a message box popup with the classic “Hello world!” message:
Click on Ok to dismiss.
Great job! You’ve now written your first apps script program.
Functions in Google Apps Script
We should rename our function to something more meaningful.
At present, it’s called myFunction which is the default, generic name generated by Google. Every time I want to call this function (i.e. run it to do something) I would write myFunction(). This isn’t very descriptive, so let’s rename it to helloWorld(), which gives us some context.
So change your code in line 1 from this:
Note, it’s convention in Apps Script to use the CamelCase naming convention, starting with a lowercase letter. Hence, we name our function helloWorld, with a lowercase h at the start of hello and an uppercase W at the start of World.
Adding a custom menu in Google Apps Script
In its current form, our program is pretty useless for many reasons, not least because we can only run it from the script editor window and not from our spreadsheet.
Let’s fix that by adding a custom menu to the menu bar of our spreadsheet, so that a user can run the script within the spreadsheet without needing to open up the editor window.
This is actually surprisingly easy to do, requiring only a few lines of code. Add the following 6 lines of code into the editor window, above the helloWorld() function we created above, as shown here:
ui.createMenu('My Custom Menu')
If you look back at your spreadsheet tab in the browser now, nothing will have changed. You won’t have the custom menu there yet. We need to re-open our spreadsheet (refresh it) or run our onOpen() script first, for the menu to show up.
To run onOpen() from the editor window, first select the onOpen function as shown in this image:
Once you’ve selected the onOpen function, the small triangle button will change from light gray to black, meaning it can be clicked to run your chosen function:
Now, when you return to your spreadsheet you’ll see a new menu on the right side of the Help option, called My Custom Menu. Click on it and it’ll open up to show a choice to run your Hello World program:
Google Apps Script Examples
Macros in Google Sheets
Another great way to get started with Apps Script is by using Macros. Macros are small programs in your Google Sheets that you record so that you can re-use them (for example applying a standard formatting to a table). They use Apps Script under the hood so are a great way to get started in seeing what you can do.
Let’s create a custom function with Apps Script, and also demonstrate the use of the Maps Service. We’ll be creating a small custom function that calculates the driving distance between two points, based on Google Maps Service driving estimates.
The goal is to be able to have two place-names in our spreadsheet, and type the new function in a new cell to get the distance, as follows:
The solution should be:
Copy the following code into the Apps Script editor window and save. First time, you’ll need to run the script once from the editor window and click “Allow” to ensure the script can interact with your spreadsheet.
In this script, I’ve created a custom menu (as we did above) to run my main function. The main function, saveData(), copies the top row of my spreadsheet (the live data) and pastes it to the next blank line below my current data range as text, thereby “saving” a snapshot in time.
Google Apps Script is by no means confined to Sheets only, and is equally applicable in the Google Docs environment. Here’s a quick example of a script that inserts a specific symbol or text string into your Doc wherever your cursor is:
6. You can change the special character in this line
var element = cursor.insertText('§§');
to whatever you want it to be, e.g.
var element = cursor.insertText('( ͡° ͜ʖ ͡°)');
7. Click Save and give your script project a name (doesn’t affect the running so call it what you want e.g. Insert Symbol)
8. Run the script for the first time by clicking on the menu: Run > onOpen
9. Google will recognize the script is not yet authorized and ask you if you want to continue. Click Continue
10. Since this the first run of the script, Google Docs asks you to authorize the script (I called my script “test” which you can see below):
11. Click Allow
12. Return to your Google Doc now.
13. You’ll have a new menu option, so click on it: My Custom Menu > Insert Symbol
14. Click on Insert Symbol and you should see the symbol inserted wherever your cursor is.
Google Apps Script Tip: Use the Logger class
Use the Logger class to output text messages to the log files, to help debug code.
The log files can be accessed after the program has finished running, by going to View > Show Logs (or Cmd + Enter, or Ctrl + Enter (on PC)).
The syntax in its most basic form is Logger.log(something in here). This records the value(s) of variable(s) at different steps of your program.
For example, add this script to a code file your editor window:
Run the script in the editor window, then View > Show Logs and you should see:
Real world examples from my own work
I’ve only scratched the surface of the outermost epidermis, not even millimeters deep, of what’s possible using GAS to extend the Google Apps experience.
Here’s a couple of interesting projects I’m working on:
1) A Sheets/web-app consisting of a custom web form that feeds data into a Google Sheet (including uploading images to Drive and showing thumbnails in the spreadsheet), then creates a PDF copy of the data in the spreadsheet and automatically emails it to the users. And with all the data in a master Google Sheet, it’s possible to perform data analysis, build dashboards showing data in real-time and share/collaborate with other users.
2) A dashboard that connects to a Google Analytics account, pulls in social media data, checks the website status and emails the user if it goes down, and emails a summary screenshot as a PDF at the end of each day.
Going GAS by Bruce Mcpherson is a newly published (i.e. bang up-to-date as of April 2016) book covering the entire GAS ecosystem, with a specific focus on making the transition from Office/VBA into Google Apps/GAS. Even if you don’t use Office or VBA much or at all, it’s still a very useful resource. It’s been a few years since I’ve done any serious VBA work, but I still found the book very helpful and a great overview of the GAS environment.
Imagination and patience to learn are the only limits to what you can do and where you can go with GAS. I hope you feel inspired to try extending your Sheets and Docs and automate those boring, repetitive tasks!