How To Add Multiple Columns to Google Spreadsheets

I recently ran into this problem when I was looking at someones Adwords account and wanted to get an idea about all the unique keywords that they were using.  I used the following formula to get an array of all unique keywords:

=UNIQUE(SPLIT(CONCATENATE('Keyword Report'!B2:B)," "))

But then I encountered a problem:

Google Spreadsheets #REF! Error

Error Result was not automatically expanded, please insert more columns ([number-of-columns]).”

Soon, I found that the current best option is to highlight and copy a number of columns and insert the copied columns over and over until there are enough columns. It seemed there should be a better way. After all, you can add an arbitrary number of rows to a spreadsheet. So just like the script to delete multiple sheets, I made the simple functionality.

The script looks like this:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastCol = sheet.getLastColumn();
var maxCols = sheet.getMaxColumns();

function onOpen() { 
 // Try New Google Sheets method
 try{
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sheet Expander')
  .addItem('Add Columns', 'Add Columns')
  .addToUi(); 
 } 
 
// Log the error
 catch (e){Logger.log(e)}
 
// Use old Google Spreadsheet method
 finally{
  var items = [
  {name: 'Add Columns', functionName: 'addColumns'}
 ];
 ss.addMenu('Sheet Expander', items);
 }
}

function addColumns(){
  // Input box asks how many columns
  var numCols = Browser.inputBox("How Many Columns Would You Like to Add? (Max: " + (maxCols - lastCol) + ")");
  // That number of columns are appended to the right side of the sheet
  sheet.insertColumns(lastCol +1 , numCols);
}

Using the Script

  1. Open the Google Spreadsheet Script Editor from the menu: Tools > Script Editor…
  2. Copy and paste the script into a new script file and save: File > Save
  3. Refresh the Google Spreadsheet window.
  4. Once the pages has refreshed, there should be a new menu called “Sheet Expander”
  5. Go to: Sheet Expander > Add Columns, and enter the number of columns you would like to add.

Note: Google Spreadsheets claims to impose a maximum limit on columns within a spreadsheet. They say it is 256 but it seems like it is possible to add many more. Perhaps this depends on number of rows in the spreadsheet.

Hope this saves you some time.

How to Make a Google Apps Script RESTful API or Service

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 public 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 need 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 an Example REST API

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 your 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
}

doGet(): 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. Anytime 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 into 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

[/ultx]

All you have to do is…

  1. Save the script
  2. Click “Publish” in the Google Apps Script Menu
  3. Select “Deploy as web app”
  4. Create a new version of the App
  5. Deploy the App

You will need to specify who has access to your API before you deploy it. Since we are making an API, we must set the access permission to “Everyone (even anonymous).” Otherwise, your client will not be able to access your service unless it is a Google user. For more see the Google Apps Script documentation.

deploy your Google Apps Script API

*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.
[/ultx]

Automate Optimizely Reports With the Results API

“Report automation” is my favorite two-word combo besides “free pizza.” And because of the nature of the testing and optimization game, and the speed it happens (in comparison to something like SEO), reporting can can pile up rather quickly. This is why I was as happy as [see picture below] when Optimizely recently released a test result endpoint from their REST API. To celebrate, I hid in my basement for hours on end and coded an Optimizely + Google Spreadsheet Report Automator that makes it easy to send email reports and import data directly into Google Spreadsheets.

Report Automation Greatness

A REST API, for those of you who haven’t fallen in love with HTTP yet, is essentially a menu of available data that you can request from a service, like Optimizely, Google Analytics, or Moz, based on the service’s URL that you request. (I borrowed that explanation from this great explanation of REST API’s on Quora.)

The Optimizely Results API

The Optimizely Results REST API makes available all the data about your Projects, Experiments, Experiments’ Variations, Goals, and Visitor Segments. If you’re really ambitious you can use the service to create, update, and delete experiments. For me, and hopefully you since you are reading this, the most useful part of the API is the Results endpoint. When you request the Results endpoint, the Optimizely API returns a list of all possible combinations of Goals and Variations. So if you have two variations: ‘Original’ and ‘Variation #1’ and three goals: ‘Engagement’, ‘Users Getting Totally Stoked’, and ‘Revenue,’ the API will return a list of six objects that describe how each variation performed for its corresponding goal.

I thought this was an interesting way of doing this but the awesome @OptimizelyDevs tell me that this is how it must be done in order to keep the service truly RESTful. (See: super-geek read about REST)

Automated Reports > Free Pizza

Back to delicious automation… I started automating reports for myself and team members as soon as I found out that the results endpoint was available and, ever since then, my life has been better. If you think about it, making data more available to your team is going to have two awesome results:

  • Less time wrangling data means more time analyzing and ideating!
  • Promotes an open culture of testing by giving everybody a stronger connection to the experiments and results.

How to Automate Your Optimizely Reporting

With the Results endpoint, there are a number of ways you could set up experiment reporting, like notifications when experiment goals have reached thresholds, notifications when experiments start or end, or time-based reporting to keep your team up-to-date. I tried to make this as broadly accessible to as many experiment driven optimizers as possible so I used the Optizely’s generic export format that you get when you share an experiment’s results. You can either email this to a list of emails or print it to a spread sheet. I made the assumption that you would only want to report on active experiments so that’s what this does. Please comment below to suggest changes. Let’s make this awesome!

Step 1: Enter Your API Token

Go to the Optimizely + Google Spreadsheet Report Automator and make a copy.

To get your Optimizely API token, go to www.optimizely.com/tokens, and click “Generate.” Copy the active token into the Optimizely API Token box then go to the Optimizely Reports menu and click “Test API.”

 

Optimizely Report Automation API Key

This should bring up the following. Please comment if it doesn’t.

Optimizely Report Success

Step 2: Send Email Reports

Enter a list of emails and click “Email Reports” in the Optimizely Reports dropdown. Wait a tick and check your inbox. You have become a reporting spam master!    

Step 3: Auto-create Spreadsheet Reports

Click the Spreadsheet Reports menu item, wait for some HTTP calls and  watch the sheets populate.

Step 4: Set Triggers and Automate Everything!

To set up a time-based trigger, from the spreadsheet click “Tools” > “Script editor…” to open the script editor.

Optimizely Report Menu

Don’t worry, there’s no need to touch the code. In the code editor window click “Resources” > “Current Project’s Triggers.”

Optimizely Report Google Apps Script

Then setup up your time-based trigger for the reporting method that you want.

Optimizely Report Triggers

Test Like Crazy! Report Like a Sane Person!

I hope this works out for you. Please comment with any issues that you might face. If you think this could really be improved. Let me know. Let’s make something great!

Bulk Hide, Show, Delete & Copy Multiple Sheets in Google Spreadsheets

Update: This script now allows you to copy multiple sheets within one spreadsheet to either the same spreadsheet or a different one. If you want to copy the sheet to the same spreadsheet just input you current spreadsheet’s id when prompted. The spreadsheet id can be found in the Spreadsheet’s URL eg: docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0

If you are looking for a way to copy sheets from a template, check out the post on GoogleAppScripting.com

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 Google Spreadsheet Add-On, 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 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. Numbers 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/gscript-on-github

Do not copy the script below. Copy from the Github Repo link.

The HTML formatting will break the Apps Script code.

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('Show Sheets', 'showSheets')
    .addItem('Hide Sheets', 'hideSheets')
    .addItem('Delete Sheets', 'deleteSheets')
    .addItem('Copy Sheets', 'copySheets') 
    .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'},
      {name: 'Copy Sheets', functionName: 'copySheets'},
    ];
   ss.addMenu('Spreadsheet Cleanup', items);
  }
}
 
function deleteSheets() {
  var deleteSheetsContaining = Browser.inputBox("Delete sheets with names containing:"); 
  if (sheetMatch(deleteSheetsContaining)){
    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);
      }
    } 
  } else {
     noMatchAlert();
  }
}

function hideSheets() {
  var hideSheetsContaining = Browser.inputBox("Hide sheets with names containing:");
  if (sheetMatch(hideSheetsContaining)){
    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();
      }
    }
  } else { 
    noMatchAlert();
  }
}

function showSheets() {
  var showSheetsContaining = Browser.inputBox("Show sheets with names containing:"); 
  if (sheetMatch(showSheetsContaining)){
    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();
      }
    } 
  } else {
    noMatchAlert();
  }
}

function copySheets() {
  var copySheetsContaining = Browser.inputBox("Copy sheets with names containing:");
  var destinationId = Browser.inputBox("Enter the destination spreadsheet ID:");
  if (sheetMatch(copySheetsContaining)){
    for (var i = 0; i < sheetsCount; i++){
    var sheet = sheets[i]; 
    var sheetName = sheet.getName();
    Logger.log(sheetName); 
    if (sheetName.indexOf(copySheetsContaining.toString()) !== -1){
      Logger.log("COPY!");
      var destination = SpreadsheetApp.openById(destinationId);
      sheet.copyTo(destination);
    }
  }
   successAlert('copied')
  } else {
    noMatchAlert();
  }
}

// determine if any sheets match the user input
function sheetMatch(sheetMatch){
  for (var i = 0; i < sheetsCount; i++){
    var sheetName = sheets[i].getName(); 
    if (sheetName.indexOf(sheetMatch.toString()) !== -1){
      return true
    }
  }
  return false
}

// alert if no sheets matched the user input
function noMatchAlert() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
   'No Sheets Matched Your Input',
   "Try again and make sure you aren't using quotes.",
   ui.ButtonSet.OK);
}

// alert after succesful action (only used in copy)
function successAlert(action) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert(
   'Success!',
   "You're sheets were " + action + " successfully.",
   ui.ButtonSet.OK);
}

If you love Google Spreadsheets, level up your data visualizations with Sparklines. 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.

Automate MailChimp Reporting with Google Spreadsheets

I made it! I am finally a nerd. At the top of the MailChimp API there is a clear disclaimer: “Woah, Nerds Only!” Undeterred, I scrolled on to test my nerdy medal. The following is my account of navigating the well-charted waters of the MailChimp API and a tutorial on how to automate a “BARF” report using Google Apps Scripts and Google Spreadsheets.

First, let me tell you how this started. Reporting in digital marketing can be a lot of work. Automate it. Ok done explaining.

The MailChimp API is a Friendly API

MailChimp’s API is nice and clean with great documentation. It has well-defined endpoints and returns JSON objects. Google Apps Scripts is great for parsing JSON objects thanks to the JSON class made specifically for that.

The problem as I defined it was as follows:

  1. MailChimp API has the data I want.
  2. I have to make a call to API using Apps Script’s UrlFetchApp
  3.  The call has to be authenticated by passing along API key.
  4. First I have to make a call to get all the campaigns/list endpoint to get a list of campaigns’ name/id info.
  5. Then I have to iterate through all the selected campaigns and pass their id to the reports/[whichever-stat-I-want] endpoints to get back each campaign’s stats.
  6. Format that data into an array and write it to the spreadsheet.

The Google Apps Script


Yup, so that’s exactly what I did. The interesting part for me was getting a better understanding of the UrlFetchApp class. I had used it before for getting data from APIs or web pages but only the simple way:

UrlFetchApp.fetch(“whateversiteiwant.com/data-from”)

But Mailchimp’s API asks for more. It asks that all requests are made as POST requests and it asks that the “apikey” and additional parameters be passed as JSON objects rather than a query string like: www.site.com/api?param=value.

This meant using the additional “param” parameter in the UrlFetchApp.fetch() method. I am happy that I got it to work. It’s clear in the code how it is used but I still need to know exactly why and how it works because it’s pretty cool. Expect a blog post on that.

Beyond that, the code is pretty self-explanatory. I commented parts that would be dodgy for Codecademy level folks. (I know. I was there not too long ago.)

Writing the report to a Google Spreadsheet

Check it out. It is explained in the comments.

// campaigns list api endpoint docs: apidocs.mailchimp.com/api/2.0/campaigns/list.php
// reports api endpoint: apidocs.mailchimp.com/api/2.0/reports/summary.php
// GAS docs: developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetch(String,Object)
// mailchimp open tracking calculations: kb.mailchimp.com/article/about-open-tracking
  
// your api key can be found at: admin.mailchimp.com/account/api/
// standard "24 hour format in GMT, eg "2013-12-30 20:30:00" - if this is invalid the whole call fails"
// add formated values for start and end date like: var REPORT_START_DATE = "2013-12-30 20:30:00"

function chimpReport() {
  
  var API_KEY = 'yourapikeygoeshere';
  var REPORT_START_DATE;
  var REPORT_END_DATE;
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();  
  
  var dc = API_KEY.slice(-3);
  var api = 'https://'+ dc +'.api.mailchimp.com/2.0';
  var campaignList = '/campaigns/list.json';
  var reports = '/reports/summary.json';
  
  // MC api-specific parameters
  var payload = {
    "apikey": API_KEY,
    "sendtime_start": REPORT_START_DATE,
    "sendtime_end": REPORT_END_DATE
  }; 
  
  // GAS specific parameters: 
  var params = {
    "method": "POST",
    "muteHttpExceptions": true,
    "payload": payload
  };
  
  var apiCall = function(endpoint,cid){
    if(cid){
      payload.cid = cid;
    }
    var apiResponse = UrlFetchApp.fetch(api+endpoint, params);
    var json = JSON.parse(apiResponse);
    return json;
    };
  
  var campaigns = apiCall(campaignList);
  var total = campaigns.total;
  var campaignData = campaigns.data;
  
  for (var i=0; i< campaignData.length; i++){
      
    var c = campaignData[i];
    var cid = c.id;
    var title = c.title;
    var subject = c.subject;
    var send_time = c.send_time;
    
    // send_time values are only present for campaigns that have been sent. otherwise set to null.
    // this if statement will only call for report data and write to the spreadsheet, data from sent campaigns.
    
    if (send_time){
      
      var r = apiCall(reports,cid);
      var emails_sent = r.emails_sent;
      var opens = r.opens;
      var unique_opens = r.unique_opens;
      var clicks = r.clicks;
      var unique_clicks = r.unique_clicks;
      var open_rate = (unique_opens / emails_sent).toFixed(4);
      var click_rate = (unique_clicks / emails_sent).toFixed(4);
      
      // the report array is how each row will appear on the spreadsheet
      var report = [send_time, subject, emails_sent, opens, unique_opens, clicks, unique_clicks, open_rate, click_rate];
      
      Logger.log(report);
      
      // note that this method will append to the bottom of the spread sheet wherever that is.
      // to overwrite a specific range use setValues()
      
      sheet.appendRow(report)
    }    
  }
}

All set!? Next step: make a Google Spreadsheet dashboard with Google Analytics data and Google Spreadsheets Sparklines.