How to Make a RESTful API or Service with Google Apps Script

You know what’s cooler than calling API’s and services with Google Apps Script? Making your own API with Google Apps Script. After playing around with my first REST API with Python and Bottle, I wondered how this could be done even cheaper and quicker; without worrying about the complexities of databases and hosting.

When it’s simple and easy you are looking for, the answer is usually Google Apps Script. REST API’s and web services can be written in Google Apps Script in a matter of a few lines. It can then be easily deployed to the pubic as a web app. This post shows how to make a RESTful API for interacting with a Google Spreadsheet.

Planning Your REST API

First we’ll have to have an application, In this case we are going to use a Google Spreadsheet with a product list as our application. We are going to allow clients to retrieve data from the spreadsheet.Google Spreadsheet

Then we’ll have to decide how people can access and interact with the data from our application based on a URI schema. There is some limitation to request URIs with Google Apps Script. Namely, you cannot use URI extensions like “/products/get/” or “/products/list/”. Instead, everything must be done with query string parameters.  In this case our REST service only has one method so we will be using the following schema to get information about the product:

?action=get&prodid=<product id>

For great insight into forming you URI schema see this StackOverflow question.

The API could also have methods to add or delete products and update quantities but for simplicity, I will let you think about what that looks like.

Finally, we will have to decide how data is structured when it is retrieved from the API. In this case I will be using JSON, but it could be XML or Plain Text if you wanted. The Google Apps Script ContentService class has methods for formatting the data output in any way that you choose.

For a much more in depth at designing an API, I would recommend reading these best practices.

Making the RESTful API or Service

doGet(), doPost() and URL Parameters

The most import things to understand are the Google Apps Script doGet() and doPost() methods. These methods take, as an argument, the request URI and decide what you application does with the request. For simplicity, version 1 of our API only accepts GET requests, so there is no need for the doPost() method. But you should know that the doPost() method does have some additional functionality.

When doGet() receives the URI, the Google Apps Script turns it into a request object that, to your application, looks like this:

Request URL:

<script web app url>?action=get&prodid=g1234&prodid=g2434

doGet request Object:

{
  "queryString": "action=get&prodid=1234&prodid=2434",
  "parameter": {
    "action": "get",
    "prodid": "g1234"
  },
  "contextPath": "",
  "parameters": {
    "action": [
      "get"
    ],
    "prodid": [
      "g1234",
      "g2434"
    ]
  },
  "contentLength": -1
}

For more information about the request object, see https://developers.google.com/apps-script/guides/web#url_parameters.

*Its important to note to things: first is the difference between the parameter and parameters array. The parameters array returns a list of all prodid queries while the parameter object only shows the first in the URI and second is that you can use the query string to change your request, but you cannot, unfortunately, use variations the URL extension.

Application Logic and Plumbing

Now that the request URI is neatly formatted into a JavaScript object, all we have to do is translate that into a query for our spreadsheet so that we can return a JSON formatted response to the client. If this is a service, it may be, that all you have to do is run the request object through some logic or an algorithm and return the result. In this case I used two helper functions to interact with data in a Google Spreadsheet.

The function, productQuery, takes a product ID as input and return the row corresponding to that product ID. The function formatProduct, takes a data from a spreadsheet row and turns it into an object with spreadsheet headings as object keys.

/* Take a product ID as input and return the
 * row corresponding to that product ID.*/

function productQuery(prodId){
 for (var i = 1; i < data.length; i++){
  if (prodId === data[i][0]){
   return data[i]
  }
 }
}

/* Take a spreadsheet (product) row and turn it into an object
 with spreadsheet headings as object keys. */

function formatProduct(rowData){
 var product = {}
 for (var i = 0; i < headings.length; i++){
  product[headings[i].toString()] = rowData[i]
 }
 return product
}

 Returning JSON Data

Now that all the intermediate logic is taken care of, all we have to do is write the doGet method so that it takes the request URL and returns our JSON formatted product data.

function doGet(request) {
 // Check for a valid request URI
 if (request.paramter.action !== undefined){
  if (request.paramter.prodid !== undefined){
   prodIds = request.parameters.prodid
 
   // The object to be returned as JSON
   response = {
   products : []
  }
  
  // Fill the products array with requested products
  for (var i = 0; i < prodIds.length; i++){ 
   sheetData = productQuery(prodIds[i])
   product = formatProduct(sheetData)
   response.products.push(product)
  }
 
  if (response.products.length > 0){
   return ContentService.createTextOutput(JSON.stringify(response));
  } else {
   return ContentService.createTextOutput('Invalid Request. Product ID(s) not found.');
  } 
 } else {
  return ContentService.createTextOutput('Invalid Request. Use at least one valid "prodid" parameter.');
 } 
 else {
  return ContentService.createTextOutput('Invalid Request. Use a valid "action" parameter.');
 }
}

Now that everything is connected. The request to

<script web app url>?action=get&prodid=g1234&prodid=h2543

returns:

{
  "products": [
    {
      "id": "g1234",
      "name": "Baseball Glove (right)",
      "price": 60,
      "stock": 35
    },
    {
      "id": "h2543",
      "name": "Baseball Hat",
      "price": 40,
      "stock": 52
    }
  ]
}

The next step could be to add some kind of functionality that would allow the client to add a product as a line to the spreadsheet using a different “action” parameter. I’ll leave that to you to explore.

Testing and Debugging

REST service is a bit different than writing a normal script in that the debugging feedback loop is a bit longer. Any time you want to change your code and see how it is really working, you will have to:

  1. Save the script
  2. Create a new version of the App
  3. Deploy the App

To deal with this I found that it was a lot quicker to create a test wrapper function that would call the doGet function with a fake request URL object (shown above). This way you can use the Logger for quick inspection in to your code.

When you’re ready to test your code in action. Try out PostMan, an HTTP client that is great for building queries and viewing the response.

Deploying your REST Service

The most import thing to remember in publishing your API is that the permission must be set to “Everyone (even anonymous)”. Otherwise your client will not be able to access your service unless it is a Google user. Otherwise, all you have to do is follow the three steps of Testing and Debugging. For more see the Google Apps Script documentation.

*Important Note: Because requests to Google Apps Script Web Apps return data from a 302 redirect URL, any client that will be accessing the service, must be capable of following redirects.

Otherwise Google Apps Script provides a quick, cheap and easy way to create simple REST API’s and REST Services. View the full Script on GitHub or make your own! Planning it out will give you a great idea about how to interact with REST services as a client.

Learn Programming and Databases for Digital Marketing | $10k Tech Skills 2/4

This is part three in the $10k Technical Skills for Digital Marketing Series. Part one introduced the importance of learning client-side technologies and offers a plan to learn Javascript, HTML and CSS for digital marketing. This post broadens the picture by introducing server-side programming and databases, which together compose web applications. Understanding how web applications work is a major benefit and should be essential knowledge for digital marketing. Enjoy!

Learning How Web Applications Work

From Google Bot to the Facebook Social Graph, to this WordPress blog; the web as we know it, is a massive system of interconnected applications. All these applications are simply programs and databases that run on servers. And while building these applications is a massive undertaking, learning the underlying processes and concepts is not. It takes nothing more than a bit of effort and time to learn enough about programming and databases to significantly set yourself and your resume apart from the average digital marketer.

While the benefits of learning how to write server-side code and interact with databases are not as immediately useful as many of the skills listed in Part 1, it is actually the process of learning this skill that presents the real value. The learning process will provide and intuition about how applications work and how processes can be scaled. This is key to digital marketing at scale.

If you can understand how search engine bots crawl websites, you can understand what makes a website crawl-friendly and you begin to understand the technical aspects of SEO. If you understand how algorithms work, you can understand Edge Rank and how Facebook decides to distribute content and broaden your reach. If you can understand how your CMS works you can map your analytics platform to it and gain better insight, which you can then use to, automate processes like email and offer personalized experiences. This new intuition about the web will continue to present opportunities.

You will also find many practical opportunities to employ your new programming and databases querying skills for digital marketing tasks and processes. While these skill starts to bleed into the realm of web development and data-science/business intelligence there are still many applications for server side scripting languages, from automation to optimization that can be very powerful for digital marketers.

Programing for the Web

When starting out on the road to learning server-side scripting, it is most realistic to start with PHP, Python or Ruby on Rails. All three are open-source, have strong communities and plenty of free learning resources. They all offer many similar advantages but each is powerful (and practical) in its own way.

programming languages for digital marketing
You see why I chose python…

PHP, for better or worse, has been the defacto server-side language of the web for a long time. PHP is what powers WordPress, Magento, ModX and many other content management systems (CMS’s) and if you are in digital marketing for long you will likely run into at least one CMS powered by PHP. Learning PHP will come in handy when you find yourself wanting to add schematic markup for search engines or scripts for testing or analytics platforms like Optimizely or Google Tag Manager.

Depending on the site(s) and development resources (or lack thereof) that you are planning to work with, PHP may be good choice. It is the easiest code to deploy, as all popular web servers will support PHP.

Python is also used to build websites with frameworks like Django and Flask but more often, sites that are built with Python are apps built with a specific, custom purpose. Unlike, PHP and Ruby, which are designed for, web development; Python is a general-purpose language, which makes it go-to languages for data-science. (The resources featured here are most about how to learn python as that is the language I have focused learning the most. It has been great!)

For the technical marketer, Python is useful for scaling big(er) data science-y processes like web scraping, querying API, interactive analysis and reporting. Many processes that are carried out manually can be programmed using Python and run on a cron job or other triggers. One major benefit of Python is that it is so easy to learn thanks to the number of educational resources and friendly syntax. If you find yourself venturing into the world data science, you will be well prepared with Python as a large and active data science community supports it.

Ruby on Rails, well, I really haven’t played with it much but I have heard it’s very nice. The key, I hear is that it is good for rapid webapp development.

Node and JavaScript were much of the focus of Part 1.

Database Querying and Analysis

Digital marketing without data is not digital marketing and the digital marketer who is not data-literate is just a marketer. I am not arguing that all digital marketers should be become SQL ninjas but learning this skill, like programming, is as much about gaining an intuition about how systems and applications work as it is about developing a practical skill.

databases and analtyics

For a real-world use case that employs this skill as both intuition and a practical skill, look no further than Google Analytics. The Google Analytics web interface is ‘simply’ an elegant way to query, sort, filter and visualize site usage/performance data that is collected in a database. Having a general understanding of how Google Analytics stores data and how different data points/hit types interrelate allows you to be much more precise in your analysis and confident that the data that you pull from Google Analytics is accurate.

SQL knowledge can also help you in times that you need to pull raw data out of Google Analytics for further analysis or to avoid sampling. With Google Spreadsheets’ QUERY function, you can query spreadsheet data using SQL (Structured Query Language). For quick analysis and more complex inspection of data sets, writing SQL queries to explore and form data to your needs can be much quicker and easier to debug than writing a successive set of spreadsheet functions.

When dealing with large amounts of Google Analytics and sampling becomes a significant issue, Google’s BigQuery can be hooked up to Google Analytics to provide SQL-like query functionality with greater speed and scale. When you become comfortable with this GUI-less interface, the ability to query any database become much less daunting. You can then answer question by directly querying databases such as a website’s MySQL database using phpMyAdmin.

“Every question can be distilled into a database query,” Adam Ware of SwellPath told me when I first started learning about databases. The phrase seemed very exciting and has since proven accurate. I have come to realize that databases simply hold all the raw information in a defined structure. By asking the right question in the right way, your digital marketing insights are limited only by your data.

Once you start to understand how databases operate you will notice their appearance in apps across the web from ecommerce stores to analytics platforms to blogs. The understanding of how data is stored and how to extract the data that you want will also significantly improve your ability to use applications to their full potential, ideate optimization for existing apps and learn new applications. This intuition is skill that helps turn data into to knowledge and as you knowing is half the battle.

How to Learn Web Application Programming

Start Here: CodeAcademy.com

This is a great place to start with any web programming language. It is the quickest, easiest and most fun way to get up to speed with a programming language that I have found. Best of all it is free. It offers courses in PHP, Python and Ruby and hosts very helpful Q&A forums for coders who are just starting out.

Get up to Speed: Intro to Programming with Python (Udacity)

Once you have gotten a feel for programming (and a few bumps and bruises to go along with it) the next place to go is to start to understand the real power that programming offers. Udactity’s Inro to Programming in Python picks up where CodeAcademy.com leaves off and introduces capabilities rather than just syntax and style.

For the digital marketer, this course is especially useful because the course is taught through constructing a very rudimentary search engine crawler (or at least the general idea of one). This application opens a window of understanding how big applications work and will make you think differently about how search engines operate.

How the Web Works: Web Development (Udacity)

There is a lot more than just programming that differentiates marketers who can program from web developers. From hosting, to caching to cookies, this course does a good job introducing these concepts.

From my experience, it was a bit too difficult as a follow up from the Intro to Programming in Python course to actual create and deploy a web app, but it does give a substantially understand of technical web terminology to communicate effectively with web developers. (This is a very valuable skill if you ask me.) From this course you will have an understanding of what topics you need to take on in detail to accomplish what you need to do as a technical marketer.

How to Learn Data Analysis with Databases

Become Data-Driven: Intro to Data Science (U. Washington & Coursera)

In my opinion (and I am a bit of a biased data-geek), this is the best online course I have taken. Each lesson offered “aha!” moment after “aha!” moment while teaching really useful skills.

The course assumes only a bit of Python experience and offers a comprehensive introduction to everything from interacting with API’s with Python and to querying databases from the command line to how to think and communicate with data. Taking this course will make any digital marketer more data-driven and will back them up with the skills to take action.

Database Deep Dive: Introduction to Databases (Stanford & Coursera)

Slightly more academic than Intro to Data Science, this course provides a very strong foundation for understanding data and databases. If you are a “why does this work” type of person, this course will be very interesting.

From a practical standpoint, the course offers very good lessons on JSON and XML formats which are everywhere in digital marketing and their understanding is essential for working with API’s. The database portion of the course will take you at least as far as you will need to go for the digital marketing applications of databases.

Put it all Together: MongoDB University

If all these courses have been interesting to you and you have a good handle on programming, then this is the course for you! You will build a real webb app from the ground up while learning MongoDB hotness. Another digital marketing specific benefit to this course is that the app that you build is a blog. Understanding how blog content is retrieved and presented will help you understand a lot about semantic SEO.

I hope you have at least one direction that you are excited about. Leave a comment if you have any questions or follow the rest of the series by signing up for email notifications when new posts are up. API’s, web scraping and “how to learn” are still to come!

How to Opt-Out of Optimizely (cdn.optimizely.com) in One Click

What does “waiting for ‘cdn.optimizely.com’ mean?

In short, ‘cdn’ means Content Distribution Network and Optimizely is a service that provides A/B Testing for websites. Optimizely makes A/B testing possible by swapping images or changing the HTML or CSS styling of a web page. To do this, it has to load additional information on to the webpage being tested.

The reason you are seeing “waiting for cdn.optimizely.com” as you are trying to load a site is most likely because the network you are using is somewhat slow. It just happens to be Optimizely information (likely an image page content for the A/B test) that is coming across the network while you are waiting on a slow network connection.

How to Opt-Out of Optimizely in One Click

The quick solution is to create a bookmarklet to opt out of Optimizely for any page that you don’t want Optimizely to load on. To do this:

  • Create a new bookmark in your browser
  • Instead of adding in a URL add in the following:
javascript:(function(){window.location += '?optimizely_opt_out=true'}())
  • click the bookmarklet when loading sites that are using Optimizely.

Done! You’ve successfully opted out of Optimizely. You should no longer have to wait on cdn.optimizely.com. This should remain in effect until you clear that site’s cookies.

Optimizely is not Bad!

(In fact is pretty awesome)

The reason I wrote this is because a friend tweeted their frustration about waiting on “cdn.optimizely.com.” The unfortunate part is that while Optimizely appears to be the culprit of a slow-loading webpage, its actually far more likely that a slow network connection is to blame.

You probably like Optmizely but you just didn’t know it. Or until now, you didn’t even know about it. Optimizely is used for A/B testing on tons of sites that you visit, from cnn.com to ehow.com. A/B testing is done to improve sites and provide a better user experience for people like you. And 99.999% of the time you don’t notice it because it is making your life better. Only in this rare occasion is it bothering you. (And its because of your network)

How The Bookmarklet Works

The bookmarklet issues some instructions, coded in to javascript, to the browser.

javascript:(function(){window.location += '?optimizely_opt_out=true'}())

This basically says the following:

“Here comes a javascript function”

javascript:(function(){

“Take the current URL (aka window.location) and append to it the parameter to opt out of Optimizely.” This is explained further on optimizely.com.

window.location += '?optimizely_opt_out=true'

“Do these instructions! When the URL is changed, the browser reloads the page with the opt out parameter. This tells Optimizely that no tests should be run on the page and should not load any additional images or information on to the page.” (And you won’t have to wait for it)

}())

I hope this works out for you. Just remember, you don’t hate Optimizely, you hate slow internet connections!

Learn JavaScript, HTML and CSS for Digital Marketing | $10k Tech Skills 1/4

This is part two in the $10k Technical Skills for Digital Marketing series. Being able to code JavaScript, HTML and CSS are the technical skills that enable you to design the presentation and experience of your marketing messaging. JavaScript will also take you much further. These skills will really increase your market value if you are interested in conversion rate optimization, web analytics, email marketing, PPC or SEO. See how these skills are useful and how to learn them.

Learn JavaScript. Love JavaScript.

JavaScript was the first technical skill I really learned and it turned out to be one of the greatest gifts I have ever given myself. JavaScript is arguably the most frequently used, extensible and therefore most valuable tool for digital marketing. It broadens my capabilities, it increases the speed of my work and it adds value to the work I do. How many skills can you offer to an employer (or yourself) that can make that claim?

JavaScript Skills Trends

Let me substantiate the three claims I just made:

JavaScript Adds Value

JavaScript adds value in the form of more and better data. JavaScript is what enables on-page Web analytics data collection. Anything beyond standard pageview tracking such as Google Analytics custom events, dimensions, metrics or product data demands custom JavaScript code.

In digital marketing, better data enables better performance. With a basic understanding of how JavaScript works you can communicate clearly with developers how you want to collect the data that you need to improve marketing decisions. With JavaScript coding capabilities you can employ Google Tag Manager and handle the custom tracking instrumentation yourself.

JavaScript Broadens Capabilities

JavaScript is used everywhere! Web Analytics data collection was the original reason for marketers to learn JavaScript but now, thanks in large part to Google, JavaScript can be used to automate tasks from email marketing reporting with Google Spreadsheet to PPC bidding and account maintenance with Google Adwords Scripts.

Finally, the application of JavaScript that has the most potentially is experience testing and optimization. JavaScript is what testing platforms like Optimizely use to transforms a page into different variations of an A/B test. Along with web analytics tracking this capability offers enormous value and will certainly continue to do so into the future.

JavaScript Increases Speed Through Automation

JavaScript can be used to program repetitive tasks. Automation makes you fast and enables you to work a larger scale. From simple tasks like finding information on a page using a bookmarklet or Chrome Extension to larger jobs like crawling sites and automating task with PhantomJS.

Better, faster, stronger. See why I love it? In addition to the practical benefits, learning and practicing JavaScript is really fun and will introduce a very cool and progressive community. The last bonus about learning JS is that since Node and MongoDB taken hold, it looks like JS is going to be sticking around for a while.

Read and Write HTML and “Get” CSS

HTML is an easy one. Seriously.

HTML literacy is probably the skill that translates to quantifiable value in the shortest amount of time. HTML takes little time to learn, is relatively easy to become proficient with and there are many opportunities to use the skill. It is everywhere! Take, for instance, an organization where there is little existing HTML literacy, you can step in, change and remove ancient images on website’s rotating marquee and Boom!, digital facelift and decreased page load time! Your boss, your site’s visitors, and Google will thank you.

Even if you just become comfortable enough with HTML to be able to visually parse the source code of a webpage, you will have the ability to identify issues and understand their causes. This skill is especially valuable for Search Engine Optimization.

HTML5 Skills Trends

Technical On-Page SEO

Future-facing SEO, along with common meta-tags, integrates structured data such as Schema.org and JSON-LD requires specific syntax to have their intended affect. Page markup can also affect search engine crawlability, page load times, and cross browser compatibility. In this way, valid HTML affects search engine performance and user experience.

HTML literacy also demonstrates its value when implementing analytics or remarketing tags. The code markup and location of the code on the page must be accurate to properly send data to analytics platforms. Being able to debug implementations and communicate with development partners and not fully rely upon them for minor technical support drastically improves the fluidity of technical marketing initiatives and spares you from worrying how or if things will get done.

Email Marketing

Another quick and measurable impact comes in the form of email marketing. You probably already know this but in case you don’t, emails are coded with HTML. You probably also know that email marketing is often one of the most effective digital marketing channels. The ability to test and optimize the look and feel of marketing email can have great impact on the effectiveness of the channel and truthfully of the brand’s identity. From simply swapping out images to writing inline CSS and using email platform template tags, the ability to code HTML is vital to a strong email marketing program.

Conversion Rate Optimization

The final use case for HTML and CSS knowledge is one that offers a remarkably high potential value and that is on-page testing and optimization. Conversion optimization tools like Optimizely and Google Content Experiments allow you to add, remove modify the HTML and CSS (styling, location, spacing) or elements on a page to create different experiment variations of the page. Some of the earliest and biggest wins that come from conversion rate optimization experiments are relatively simple CSS changes so having operational knowledge of CSS can translate to significant value rather quickly.

Conversion Rate Optimization Skills Trends

HTML and CSS knowledge can also translate to a sustainable value beyond the operational, nuts and bolts, knowledge. As your conversion rate optimization-testing plan develops and experiment possibilities become less immediate, having the knowledge of presently untapped methods of displaying a page or newer HTML technologies will open up new testing possibilities. As long as browsers and HTML standards continue to change (this will always) prove its value.

Learning JavaScript

Learn By Doing

CodeAcademy.com is a great place to start. It offers a great introduction to the basics of the JavaScript language; syntax, data structures, expressions, etc. It also offers an intro a course on jQuery. The jQuery course is offered before JavaScript which seems strange to me as jQuery is built on top of JavaScript. I would strong suggest taking both courses if you want to learn jQuery.

Learning From The Work of Others

Learning JavaScript, HTML and CSS is unique in that all the code that makes up a webpage is accessible to you. You just have to know how to find it. Chrome Developer Tools (don’t be intimidated) offers a ton of useful ways to inspect and learn what is happening on any webpage on the Internet. You can inspect and live-edit pages, see what files are coming into the browser across the network and write JavaScript in the context of any webpage with the JavaScript Console. This is a very valuable tool and learning it will also teach you a lot about how browsers, the DOM and webpages work.

Automate Something

Google Apps Script offers an easy interface to try your new skills and accomplish some pretty useful things. It is nice because it abstracts away a lot of complicated things and offers a very simple API to Google Spreadsheets, Google Drive and Google Docs. There is also a lot of good documentation for getting started. If you manage PPC, try Google Adwords Scripts. FreeAdwordsScripts.com offers a lot of good code examples and cool ideas to show how to work with the Adwords Script API.

Books to Read

DOM Enlightenment (Free Online): All you could ever want to learn about interacting with the Document Object Model and more. This is a great one to accompany your exploration of Chrome Developer Tools and the JavaScript console.

Object-Oriented JavaScript: Knowing JavaScript prototypal inheritance is one of the big differences between knowing JS and knowing JS. This is a pretty sweet deep dive into the subject.

Secrets of a JavaScript Ninja: This was written by John Resig who created jQuery. Needless to say, there are a ton of clever patterns to take your JS capabilities to the next level.

Google Apps Script: If you are more interested in the Google Apps Script and simpler forms of automation, this book is great. It will introduce you to a lot of server-side concepts. This stuff is great but just be weary, getting too deep into Google Apps Script may prevent you from learning the really powerful server-side programming languages.

Videos to Watch

Videos can be pretty inspiring. YouTube searches for whatever you are learning can be very helpful but a couple videos that I recommend are:

Learning HTML and CSS

Learn By Doing

CodeAcademy.com again. You have to get repetition with the basics and this is a great intro. Get through it and then read a book and start really practicing.

Learn by Editing

  1. Get to know and love your code editor! Download the code editor of your choice. Atom is free and pretty good.
  2. Write some basic web pages and learn how to use scripts and link style sheets to your webpage. Reference w3schools.com and look at the source code of existing pages for reference. GitHub.com has some nice clean source code. (You will also want to get to know GitHub anyway)
  3. Learn some tools. HTML5 Boilerplate is a great place to start with solid web standards and learning Twitter Bootstrap CSS framework will almost certainly come in handy.

Books to Read

HTML5 for Web Designers is great if you want a good basic intro. It will tell you what you need to know but no necessarily how to do it. This book is as must if you are going to be using Optimizely for testing. http://www.abookapart.com/products/html5-for-web-designers

HTML5 & CSS: Learn to Design and Build Websites is an intro to the medium and teaches how to use it at the same time. This is a great book to read in conjunction with step 2 above.

Now go make something awesome! Or rather learn how to make something awesome and keep practicing until you do. These front-end skills will take you a long way and make you very valuable. Check out the rest of the series to learn more about the Back End and Applications, API’s and Web Scraping and Technical Resources and How to Use Them.

Get notified when each posts is released!

$10k Technical Skills for Digital Marketing + How to Learn Them

Learning the technical aspects of digital marketing has been one of the most gratifying journeys of my career. It has fundamentally changed the way I see the Web and has opened numerous opportunities for creativity and growth. If you have any interest or desire at all to dive into the technical world, don’t’ wait, Jump!

If you are an opportunity seeker, you will find that the demand for technical marketing skills opens up doors that you didn’t even know existed. If you are an ambitious marketer ready to brand yourself as a growth hacker, technical skills are essential. If you are even the least bit curious about how the “magic” of the Web works, a splash into technical waters will remove the constraints of fear and expose new opportunity for creativity. In all cases, learning about Web technologies and how to apply them is likely the best investment you can make into your digital career.
growth hacker Job Trends graph

Where to Start

This is the series I wish I could have read five years ago. It is intended for those, like myself, who are interested in growing a career in digital marketing and want to build an indispensable skillset. The series highlights many of the most valuable technical skills for digital marketing and offers a clear path to learning these skills with minimal time-consuming detours. It includes real use cases and also describes the more conceptual, “higher level” benefits from learning these skills.

This series consists of four posts. Choose a link and enjoy your journey!

The Front End: JavaScript, HTML and CSS

How the technologies that creates the look, feel and experience of the web are used in experience optimization, analytics data collection, email marketing and Google Apps.

The Back End: Web Applications Programming and Database Analysis

How the big systems like Facebook and Google work, how your CMS creates dynamic web pages and how to think like a database.

The Connected Web: Accessing Web API’s and Scraping Websites

How to access data from websites and Web API’s, how to read the data and how to make it useful. Connect all the things!

How to Learn and Where to Find Help

How to read documentation, code repositories and forums, what to do when you get stuck and where to find help.

Get notified when each new posts is released!

Can technical skills earn you an extra $10k a year?

I believe so. There are a ton of college graduates with marketing, communications or business degrees. And for the longest time, the curriculum for these majors lagged far behind the technology. Though these gen X/Y/Z’ers are familiar with the digital environment, few are able to take full advantage of the digital environment, let alone recognize opportunities. If you want to be a growth hacker, digital analyst or just upgrade your digital marketing resume, technical marketing skills are a key differentiator in a wide open market.

Beyond an actionable skillset, the demonstrated ability to learn technical skills is a valuable characteristic in itself. Those who can and do learn, prove their self-motivation, ability to learn and be flexible. From my observations, the people that are driven to learn generally move farther, faster than those who are just good at their job. You cannot learn experience but you can learn skills.

Finally, Jamie Stevens makes a great point about the need for generalists. Not only can these people handle marketing strategies end to end but they can also see the big picture and communicate across a broad spectrum of stakeholders. This glue is indispensable in organizations of any size.

 

 

7 Must-Have Chrome Extensions for Testing and Optimization

Crafting successful tests is as much about clever ideation as it is about skillful execution. These seven Chrome Extensions should be in every optimization craftsperson’s toolbox.  They help simplify the technical tasks, expedite the mundane tasks and optimize the whole creation process.  Add them to your toolbox and work smarter, not harder!

Systematize: UX Check

This brand new UX Chrome Extension is so comprehensive, you can literally build the entire ideation phase of your testing strategy around it. Its benefits are three-fold: it provides a framework for UX evaluation,  it records screenshot/annotation combinations for every element that you may foreseeably test and it packages it into a nice neat .docx file for clean documentation. Learn more about it from the app’s creator.

Pro Tip:  Save the exported .docx files to your Google Drive Folder and keep all your testing and optimization documentation in the same, easily accessible and shareble place.

Collabogreement: Awesome Screenshot & Screencastify

Testing and optimization is collaborative work and communication is key. Before you launch a test, you will likely have to get the approval of other site stakeholders. Screenshots for static elements and screencast for interactive elements offer a quick way to point out optimization opportunities and share mockups. Both of these extensions integrate with and send files  directly to Google Drive which, chances are, you are already using to manage your testing plan and schedule.

Pro Tip: Use Google Chrome’s Developer tools to directly edit page HTML, CSS and Javascript to make quick mockups. Screenshot, share, awesome.

Cookie Info: Edit this Cookie

Cookies are what your browser uses to tell your testing platform what test, variation or segment a given visitor is in. Edit this Cookie allows you to easily set the specific values and expiration dates for cookies for specific sites or clear all the cookies for a site at once.

Pro Tip: Sometimes clearing your cookie is not the best way to go. Use URL parameters to force experiment variations.

Color Picker: ColorZilla

If you are testing the effect of color on your site, don’t let your test fail because your color choices are inconsistent with your site’s branding. Whether you want to draw attention to or away from an element give your test its best chance of winning and your users the best comprehensive experience with consistent color.

Pro Tip: Maybe you can’t find the perfect color on the page or you need a good contrasting color to test. Use paletton.com to find complementary or contrasting colors.

Measure Twice, Cut Once: Page Ruler

How big does your new image have to be? How much space do you have for text? Exactly how far should you move that element? What screen widths should your test run on? Page Ruler allows you to measure everything on your page in pixels. Don’t guess and check. Measure twice and edit once.

Pro Tip: Use the tool’s “Element Mode” to automatically select elements to measure. This provides a good reference point for resizing elements.

Be Selective: jQuery Selector Inspector

Optimizely uses jQuery to make transformations to experiment pages. If you are new to jQuery, this tool can help you define the CSS/jQuery selectors that you need for selecting elements to transform or track clicks for goals.

Pro Tool: Are you spec’ing a site that doesn’t currently have jQuery on it? Use the following code to make a bookmarklet to add jQuery to any page. Just create bookmark a new bookmakr and paste in the following code where the URL would go. Go to the page and click the bookmark. Boom! all the jQuery goodness at your fingertips. 

javascript:(function(){
   var js = document.createElement('script');
   js.src = "//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js";
   document.head.appendChild(js);
 }());

Fixing Breakpoints: Responsive Inspector

Its the 8th year of the mobile web and there are more screens to think about than ever.  The truth is, in order to create effective tests and give your good ideas the best chance of winning, you have to create text experiences that are both flawless and consistent across multiple devices. This tool tells you what breakpoint you need to be aware of in creating a test.

Pro Tip: Consistency is key. Optimizely allows you the capability to unify a user’s experience across devices using a user’s unique id. 

Bonus: Optimizely Extension???

More access to experiment info on the page you are on. I have seen it with my own eyes. I hear its only a matter of time until it gets released. Get excited!

Solving Google Tag Manager Event Race Conditions

Google Tag Manager is a great solution for AJAX-heavy apps but at times it may seem like it adds more complexity than it relieves. Synchronizing data and events in the dataLayer is an example of this. Sometimes you will want to fire a tag that aims to send data that will show up in the dataLayer after an AJAX call and because you don’t have any guarantee about the timing of this AJAX call you may run into a bit of a race condition.

Fire Events With Data

In the world of javascript you might rely on a call back function to solve this problem. This same idea works with Google Tag Manager, but it has its own way of going about it. The Google Tag Manager solution to this type of problem is to push an event to the dataLayer with the new AJAX delivered data and then set firing rules for any beacons that depend on that information to said event. For instance:

 

 dataLayer.push({
  "newDataAboutX" : {
    "thing1" : "xyz",
    "thing2" : 12
  },
  "event" : "dataAboutXReceived"
});
Then you would set all beacons that require data about X to fire when:
 
{{event}} Equals dataAboutXReceived
 
You can then use dataLayer variables, {{newDataAboutX.thing1}} and  {{newDataAboutX.thing2}} in any beacons that fire on dataAboutXReceived and be sure that they are available.  *A note about accessing dataLayer variables: You can access nested dataLayer variables with dot notation. This also extends to arrays. (for example: myDataLayerArray.3).

Google Tag Manager ‘eventCallback’ Function

If that isn’t enough, Google Tag Manager offers an ‘eventCallback’ function for any object that is pushed to the dataLayer. This is a callback function that is called once all tags that are triggered by this rule are fired. This is often used to navigate to another page after capturing data about the event that would normally cause a URL change. ie: outbound link clicks, navigation clicks or Enhanced Ecommerce  promotion clicks, product listing clicks and product adds to cart.
For example:

 

  dataLayer.push({
    'event':'productClick',
    'ecommerce':{
      'click':{
        'actionField':{'list':'Search Results'}, 
        'products':[{
          'name': productObj.name,               
          'id': productObj.id,
          'price': productObj.price,
          'brand': productObj.brand,
          'category': productObj.cat,
          'variant': productObj.variant
         }]
       }
     },
     'eventCallback':function(){
       document.location = productObj.url
     }
  });

Guide to Enhanced Ecommerce Product Data & Collection

Google Analytics Enhanced Ecommerce provides insight into Ecommerce site performance in detail that was previously impossible using standard Google Analytics methods.  In order to provide these insights, data  is collected in a new and unique way. It is important to understand the breadth of Enhanced Ecommerce data collection capabilities in order to fully reach the insights that the feature offers. This guide will take you from Ecommerce product page to Enhanced Ecommerce Product Data.

Enhanced Ecommerce Product Page
A pretty standard Ecommerce Product Page. Thanks to smarthome.com
Enhanced Ecommerce Reporting
Enhanced Ecommerce Product Reporting with Custom Dimensions and Metrics.

Product Data Problem and Solution

The new product schema was created to provide data in a way that answers questions that are specific to products rather than pages or events. Consider event hits; their schema uses a sentence-like structure to describe an action.  This does not map well to a product entity that is not  bound by specific instances in time. The product schema is more similar to pageview hits that collect attributes of a page entity over time. But still,  the data is collected on the page is in a different fashion.

For more, see Carmen Mardiros’ great conceptual explanation of Enhanced Ecommerce Data Layer and my slides on Enhanced Ecommerce schema.

Google Analytics Enhanced Ecommerce assigns a collection or properties to each product .  At least one property is mandatory (either name or id) and other properties are optional: brand, category (up to five tiers), variant, price, quantity, coupon, and position. These properties describe either an attribute of the product or the context in which the product is acted upon. These standard properties are great for providing a holistic understanding of how products are interacted with in the context of an Ecommerce store, but they don’t give the whole picture of how that product is interacted within thecontext of any specific business.

 Understand how a given product is interacted with based on qualitative dimensions like its availability, backorder date, release date or physical dimensions or appearance.

For instance, you may want to understand how a given product is interacted with based on qualitative dimensions like its availability, backorder date, release date or physical dimensions or appearance. Likewise, there is more to understand about products’ quantitative information such as cost of goods sold, previous price, discount or profit. This is where custom dimensions and metrics come in.

Product Data Collection

Data is collected about product entities in a way that very appropriately fits the concept of a product; each product is represented by Javascript Object. An Object is simply a collections of properties and values that describe those properties. For instance a product entity would be represented by an Object that has a name property of  “Clapper Light Switch” and a price property with a value of 30.00. To extent the ability for an Object to describe a product entity we can specify additional properties that further describe the product like “cost of goods sold”, or “back order date.” At the code level, this is done by simply adding one more property “key”:”value” pair to the product entity object. The only difference is that the property name will be a place holder such as “dimension9″ or “metric4″ to be assigned later within the Google Analytics interface. In Universal Analytics it would look like this:

ga("create", "UA-XXXXX-Y");
ga("require", "ec");
ga("ec:addProduct", {
   "id": "81301",
   "name": "Xantech AC1 Controlled AC Outlet",
   "price": "78.55",
   "brand": "Xantech",
   "category": "AC1 Controlled AC Outlet",
   "variant": "white",
   "dimension3": "In stock - Ships Today", // stock status custom dim.
   "metric2": 5,                           // rating stars custom metric
   "quantity": 1 });
ga("ec:setAction", "add");
ga("send", "event", "Product", "Add to Cart", "Xantech AC1 Controlled AC Outlet");

and using the Google Tag Manager data layer it would look like this:

dataLayer.push({ 
   "event": "addToCart",
   "ecommerce": { 
      "currencyCode": "USD",
      "add": {
         "products": [{
            "id": "81301",
            "name": "Xantech AC1 Controlled AC Outlet",    
            "price": "78.55", "brand": "Xantech",            
            "category": "AC1 Controlled AC Outlet", 
            "variant": "white", 
            "dimension3": "In stock - Ships Today",  // stock status
            "metric2": 5,                            // review stars
            "quantity": 1 
         }]
      }
   }
});

For a great working examples of this, see:  https://ga-dev-tools.appspot.com/enhanced-ecommerce/

Setting Custom Dimension & Metric Names

The first thing to note is, if you are using the Google Tag Manager data layer to collect product data, make sure you have checked the “Enable Enhanced Ecommerce Features” and “Use data layer” boxes shown below. Google Tag Manager Add to Cart Event No matter if you are using the data layer or standard Universal Analytics collection code, you will have to do two things:

  1. Ecommerce and Enhanced Ecommerce Reports must be enabled.  Just go into your Admin section > choose a View > Ecommerce Settings  and toggle  Enable Enhanced Ecommerce Reporting to “ON”.
  2.  Custom Dimension and Metric must be activated, named, and configured as you want them to appear in Google Analytics reports. This is also done within the Admin interface. Go to Admin > choose a Property > Custom Definitions and click Custom Dimensions or Custom Metrics. Set the name, the scope to Product, and the state to On. For Custom Metrics, set the appropriate formatting type.

Enhanced Ecommerce Product Custom Metric Setup Note that these hits must be set at the product hit level. Otherwise, the data will not be collected as expected, if at all.

Enhanced Ecommerce Product Data Reports


Great, everything went well up to this point an you are ready to appreciate all your shiny new insights. To find these reports within the Reporting interface go to Conversions > Ecommerce >  select a Product or Sales report .

Enhanced Ecommerce Reporting

From there you can see all your products and sort and filter them by any given property that have been collected. Metrics can also be added to Custom Reports to provide aggregate insights.

Inspecting, Debugging and Perfecting Product Data Collection

You may see something funny in your reports at this point or nothing at. In my experience, Enhanced Ecommerce data is reported as soon as the hit that it was sent with is reported. This is usually relatively quick. (Under 10 minutes) If something looks amiss, don’t worry, there are usually a few simple fixes that can be made to make sure the data is being collected and reported correctly. Assuming you have done everything correctly up to this point, there is may be a few things you need to check and fix. Heres a list:

Debugging Enhanced Ecommerce Hits
This is what you are looking for in the Google Analytics Debugger.
  • Make sure the hit is being sent to right property.
  • Use mandatory product  fields (name or id) as report dimensions.  This is helpful when starting out. If you are looking at a  report with a primary dimension of Product List, but are not yet collecting product list data the report will appear to be empty.
  • Make sure Ecommerce data is sent with a standard Google Analytics Hit. Enhanced Ecommerce data is buffered on the page until a stand Google Analytics hit is sent. Then the Ecommerce data is collected with that hit.
  • Make sure Javascript Object data structure is correct and without errors. Use my data layer debugger bookmarklet to make sure the data is in the data layer. Also, keep an eye on the Javascript console and use jshint.com to make sure there are not errors and everything is formatted correctly.
  • Try this cool method of inspecting each object that is pushed to the data layer is  by using  JSON.stringify to view Object in data layer. Just type the following command into your Javascript console and view the object in JSON (JavaScript Object Notation).
JSON.stringify(dataLayer[0]) 
// where 0 is the index of the first object in the data layer array

Accessing Nested Values with Google Tag Manager Variables

In the case that you are sending an event hit to Google Analytics that is carrying Enhanced Ecommerce info, you many want to use a product’s attributes as the values for the event’s Category, Action, or Label or even Custom Dimension or Metric. Similarly, product data can be applied as a custom dimension on the pageview hit that, for example, is sent to on a product page to carry product detail view Enhanced Ecommerce information. In these case, if you are using Google Tag Manager, you can access the values of the product or actionField data using a Data Layer v2 Variable. These GTM Varialbes allow you to access property values that are nested within Objects or Arrays in the data layer. For instance, if you wanted to access the name of a product that was just added to a shopping cart (as shown above), you would use the following format without the quotes: “ecommerce.add.products.0.name”. Note that the 0 is specifying the index (zero based count) of the Object noted in the array that is enclosed in [brackets].

Thanks Simo for getting me back on track with this.

A Note on Product Hit Scope Dimensions and Metrics

Custom Dimensions and Metrics are product hit level and wont be applied to an event or page on which the happen or to a user that interacts with them. This is done by setting the dimension or metric at the hit level. Just make sure to configure the hit Dimension or Metric accordingly.  Check out this old but good explanation of  hit scope by Justin Cutroni.

Start Collecting and Start Optimizing!

This may seem complicated but the power that it provides is well worth time spent in a detailed implementation. Please leave a comment if you have any questions. Or call us at SwellPath and let’s get this started!

Thank you to SmartHome.com for the pretend data. I want everything in your store.

Agile Strategy for Data Collection and Analytics

This is one of my posts from the Swellpath blog. My original post can be found here.

If you are like most people doing business online, it seems like there is always a long list of digital to-dos that are somewhere between “that will happen in Q4” and “that should have happened by Q4 last year.” Aside from the constant stream of daily hiccups that arise due to the asynchronous nature of our medium, if you are like most others managing a website, you face broader development challenges of slow servers, uncooperative CMS’s, or lame mobile experiences impacting your online success.

This is not failure that you have to accept! Let me introduce you to a little thing that has been bouncing around in the software/web development community that will make your online business operations feel less like swimming in peanut butter. It’s called Agile Development and it’s sexy. It’s fast and sexy like a cheetah wearing high heels.

We can apply these principles of Agile Development to data collection, analytics and optimization to provide two exceptional benefits: rapid access to data and insight, and safeguards against constantly changing web properties.

For data-collection, analytics and optimization:

  • An Agile approach provides action before traditional methods provides insight
  • An Agile approach safeguards against the constant variability of the web medium
Analytics plan and strategy
“If you fail to plan, you are planning to fail!” — Ben Franklin

Learning from Feature Driven Development

The Agile Development concept covers an array of development methodologies and practices, but I would like drill into one especially coherent and efficient method of Agile called Feature Driven Development.

Feature-Driven Development essentially works like this: an overall project is planned as a whole then it is separated into discrete pieces and each of these pieces is designed and developed as a component and added to the whole. This way, instead of having many semi-functional components, the project’s most valuable components are complete and fully functioning.

Phased Implementation (Not Iteration)

Because you might have already heard something about Agile Development, it is important, at this time to dispel the notion that Agile development is defined by iterations upon products. In a sense that is true but mostly it is the complete opposite of the Agile approach. The only iterations that happen are the planning, implementation and completion of a new feature. This is not the same as adding layers upon existing features (more on this with the Definition of Done). The difference here is planning and the ability to see the project and business objectives as a whole.

feature driven development for analytics

Step 1: Develop an Overall Model

You must plan! Planning in an organization can be hard to motivate and difficult to initiate, but these planning steps will actually provide you with better, more actionable data sooner than not.

Understand the system. This is digital. There are a lot of moving parts. It is very important to really know how your digital presence affects your physical business and your overall business strategy and vice versa. Additionally there are likely many components within your business that are (or could be) affected by the data that can be collected. This leads to my next suggestions.

Ask questions and seek multiple perspective. This is time to confront your assumptions about your business, your pain points and your data needs. It is important to really know the processes and decisions that are taking place and how they are (or are not) or could be affected by data. Communicating with those who interact with and make decisions on the data at any level will be extremely insightful.

Be strategic. Look at the big picture of the future, define your goal and work backwards. Agility does not come by luck but rather by being aware of and prepared for all foreseeable possibilities. Consider how things will change and what parts of your digital presence are shifting. How will redesigns, platform changes, and code freezes affect your strategy? This is generally the best way to face an analytics problem so this step applies very well to analytics. Agile was created to solve the problems of being short-sighted and reactive.

Step 2: Define the Parts of Your Plan

This is where the fun starts. There are multiple ways an analytics strategy can be divided and further subdivided into parts. When considering how to divide the project into parts, the goal should be to get to define parts at their most discrete, independent or atomic level. This will be helpful in prioritizing the parts into steps. Ultimately,  these parts can be regrouped based on similarity and development implementation process.

By Web Property and Section

An organization’s web presence is often not limited to a single site or app. There may be different properties or sections of web properties with different intents. Inevitably, some of these properties or sections will have a bigger impact on your organization’s goals and thus would be prioritized differently.

By Data Scope (User, Page/Screen, Event)

Each web properties has layers of data that can be gathered from it. Data about the user in the app or website’s database, information about the content of the page, and information about how the user interacts with the app or website can all be thought of discretely. These differ in terms of intelligence and the actual development work that is required to collect the data.

By Data Use

Another way to divide up the data-collection needs is by end use. For instance, you may be an ecommerce store that has different people or teams who are merchandising, planning and creating content, managing email, social campaigns, or paid media campaigns and/or optimizing the application and user experience. The data needs for each initiative will often overlap with other initiatives but sometimes data needs will be very different from others. These different data needs can be thought of as different parts of your strategy.

By Data Depth

Think 80/20 rule in terms of granularity. Some data is instantly useful. For instance you may not be tracking clicks on your main call-to-actions or “Buy” buttons. These clicks are likely key micro-conversions and having this interaction insight can literally change your strategy overnight. Another layer of depth would be knowing what product was added to the cart as part of that event. A further layer would be configuring Google Analytics’ Enhanced Ecommerce to understand how that specific product flows from the product page to the checkout. Each of these examples provide varying depths of data but also require varying amounts of development time.

Other features like Google Adwords Dynamic Remarketing and Google Analytics Content Groupings can be thought of similarly as they need more information to associate with the user or page.

Step 3: Prioritize

This is the most important step. This is where the unique value of the Agile approach really shines. This can drastically lower the cost and shorten the time to data-driven action. All the planning and foresight that took place before can be leveraged to make the right decisions for the most success.

Consider Goals

Duh. The whole reason you are gathering data is to be data-driven. The parts of your plan that most directly affect your top-line goals should be at the top of the list. Think about every time you have said or heard “If we only knew abc we could achieve xyz.” Now depending on the value of xyz, prioritize data collection.

Consider Time

This is what Agile is all about! With goal impact in mind communicate with relevant parties and your development team or partners to understand how long it will take to implement the code to start gathering data. Sometimes the value of data will scale to the development time, other times it may be as simple as using a Google Tag Manager click listener on calls-to-actions to send events to Google Analytics within a few minutes. Overall, its good to have some data to orient your decisions right away so go for the quick wins first and work with that as code is being implemented to get the real data gold.

Consider Cost

Unfortunately, bottom lines still exist and often development resource cost will have to be justified in implementing code to gather data. Some data collection might be cost prohibitive but it is possible that, by gathering data that is easier to gather, such as standard Ecommerce implementation will give you the rationalization to, in time, get more in depth data. Overall, get the valuable data that comes cheap, squeeze the life out of it until you need more depth.

Step 4: Implementation Cycle (Plan, Implement, QA)

Now, for the moment we’ve all been waiting for, let the collection begin! This is the step that most people think of when they think of Agile development; sprinting to complete a feature and then releasing it.  For Agile analytics, this works the same way. Now that there is a list of analytics “features” or streams of data that have been prioritized each step should be planned, implemented and tested successively.

Plan

This is a more detailed plan than the overall model. This plan defines how the data will be collected. For example this is when Google Analytics Event or Custom Dimension naming conventions would be defined and documented. Be explicit. This will really improve the efficiency of the process.

Implement

Buy your development partner beer and pizza and pass your documentation on to them. Keep them happy and maintain a good relationship. There will be more implementations in the future. Hopefully your documentation is clear but be open and responsive to questions; this is all about speed and accuracy.

Quality Assurance

This should happen in your development environment so that when the code is implemented on the site, the data that is reported is clear and accurate. Be thorough as this implementation should stay this way well into the future. If changes are to be made, be discreet, just as in implementation.

These three steps can happen simultaneously, for example planning can happen on a future part as implementation and QA is happening on the present part.

Start Optimizing!

Agile is not simple but its also not magic. Speeding up the time to data-driven action is made possible by the planning that happens up front. Being proactive is not only a practice of Agile but also general best practice in analytics. It is the planning that makes agile efficiency possible. It may seem difficult, but putting in the effort to plan will put you in a position to act proactively agilely into the future.  Happy optimizing!

Bulk Hide, Show or Delete Multiple Sheets in Google Spreadsheets

Too many sheets! I came across the problem of having a large number of visible or unused sheets in cluttering up my Google Spreadsheets  often enough that I decided to solve it. This problem most often comes up when using Google Apps Scripts that auto-generate multiple sheets. If you have ever used the Google Analytics Magic Script, you know what I mean.

Too many sheets for one Google Spreadsheets

Using the Script:

Copy the code below into a script file in for the Google Spreadsheet you want to use it on, save the script file and reload the spreadsheet. A new menu will appear called “Spreadsheet Cleanup.” This menu has three options:

Hide Sheets, Show Sheets, Delete Sheets

Google Spreadsheets UI Menu

View the Google Spreadsheet Demo

Select an option and and the spreadsheet will prompt you to enter a word or phrase to match the names of the spreadsheets you would like to take action on against. In other words… enter part of the name of the sheets that you would like to take action on into the box and then it will do the job pretty quickly.  Note that you can only use letters in the name. Number will not work. Feel free to edit the code though.  (At first I was using the Javascript match method to search for sheet names but the regex would not allow for the prompt box.) Anyway, that’s all you have to do.

Hope you can find it useful too!

You can also make a copy of the script here:  http://tfox.us/spreadsheet-cleanup

The Google App Script:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetsCount = ss.getNumSheets();
var sheets = ss.getSheets();

function onOpen() { 
 // Try New Google Sheets method
 try{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Spreadsheet Cleanup')
  .addItem('Hide Sheets', 'hideSheets')
  .addItem('Delete Sheets', 'deleteSheets')
  .addToUi(); 
 } 
 
// Log the error
 catch (e){Logger.log(e)}
 
// Use old Google Spreadsheet method
 finally{
  var items = [
  {name: 'Hide Sheets', functionName: 'hideSheets'},
  {name: 'Show Sheets', functionName: 'showSheets'},
  {name: 'Delete Sheets', functionName: 'deleteSheets'}
 ];
 ss.addMenu('Spreadsheet Cleanup', items);
 }
}
function deleteSheets() {
 var deleteSheetsContaining = Browser.inputBox("Delete sheets with names containing:"); 
 for (var i = 0; i < sheetsCount; i++){
  var sheet = sheets[i]; 
  var sheetName = sheet.getName();
  Logger.log(sheetName);
  if (sheetName.indexOf(deleteSheetsContaining.toString()) !== -1){
  Logger.log("DELETE!");
  ss.deleteSheet(sheet);
  }
 }
}
function hideSheets() {
 var hideSheetsContaining = Browser.inputBox("Hide sheets with names containing:"); 
 for (var i = 0; i < sheetsCount; i++){
  var sheet = sheets[i]; 
  var sheetName = sheet.getName();
  Logger.log(sheetName); 
  if (sheetName.indexOf(hideSheetsContaining.toString()) !== -1){
   Logger.log("HIDE!");
   sheet.hideSheet();
  }
 }
}
function showSheets() {
 var showSheetsContaining = Browser.inputBox("Show sheets with names containing:"); 
 for (var i = 0; i < sheetsCount; i++){
  var sheet = sheets[i]; 
  var sheetName = sheet.getName();
  Logger.log(sheetName); 
  if (sheetName.indexOf(showSheetsContaining.toString()) !== -1){
  Logger.log("SHOW!");
  sheet.showSheet();
  }
 }
}

If you are learning how to use Google Apps Script code. Checkout out some useful resources on how to learn javascript for Google Apps Script.