Pull Google Analytics data into a Google Docs spreadsheet using the API

There are two different ways of calling the script:

• as a spreadsheet function – you can simply place a function in a cell, once the script is set up and authorised.
• Depending on what you ask for, the function may return a single value, or an array from Google Analytics. If you ask for only one metric, the function will return a single value – simple. If you ask for multiple metrics and/or dimensions the function returns an array. In Google spreadsheets array functions are really easy to use.
• The function is:
  getMetric(profile, metric, startdate, enddate, [dimensions], [segment], [filter], [sort], [maxresults])
• any or all the parameters can be cell references
• from the menu. Called this way the script uses query parameters specified in the _settings worksheet, and outputs data to a location also specified in _settings – a new or existing worksheet.
• This results in a table of cells with the metrics in them, with no spreadsheet formulas behind the values, so no unexpected recalculation
• Activated from a custom menu “GA”.
• Values in the _settings sheet may also be references to other cells or formulas (for example to automatically update the target spreadsheet name depending on the date, or to change the filter from a list)

1. Load this shared Google Docs spreadsheet with analytics script
2. Select File|Make a copy (you have to be logged in to a Google account to do this)
3. Update the “ids” value to one of your profile ids – should be something in the form “ga:######” – you can find the list in the query explorer, or look at the id parameter in the query string for a page in Google Analytics (at some point I will ad a feature to retrieve the list)
4. Open the script editor (Tools|Script Editor…),
5. Execute the function Run|getSettingGADataToSheet. This will prompt you to authorise the script to run. – select yes.
6. Run the script again (Run|getSettingGADataToSheet). This will trigger the oAuth process for GA access – you may need to run it twice for all the authorisations. (After all this you won’t have to do this from the script environment again – from then you run it from the spreadsheet menu)
7. Close and re-open the spreadsheet. If you don’t see the GA menu after a minute, go back to the script environment and make any edit (eg add a //comment) to the script and save. Sometimes the spreadsheet copy seems not to recognise the script until it has been edited. Close and re-open again.
8. change the query parameters in _settings as you like, or setup sheets and use the getMetric function

Alternatively, if you want to start from a new or existing spreadsheet of your own, you can

1. copy the script below into a new spreadsheet and
2. (optionally – only needed if you want to invoke from a menu command) copy the table below into a worksheet called _settings, and set your parameters (especially the profile ID). The script expects to find this table in the top left of the _settings sheet, starting from A1. You can change anything in the values column as needed.
3. pick up from step (4) above

/*
Copyright (c) 2012 Justin Gough 
http://35.189.40.227:8000/
Licensed under the MIT license: http://www.opensource.org/licenses/mit-license.php
*/

var settings = getSettings();

function onOpen() {
  var ss, menuEntries;
  ss = SpreadsheetApp.getActiveSpreadsheet();
  menuEntries = [ {
    name : "Activate settings query",
    functionName : "getSettingGADataToSheet"
  } ];
  ss.addMenu("GA", menuEntries);
}

function getMetric(profile, metric, startdate, enddate, dimensions,
  segment, filter, sort, maxresults) {
  // if called with one metric returns a single value
  // if called with multiple metrics returns an array
  var params, query, returnVal, gaData;
  params = {
    "ids" : profile,
    "metrics" : metric,
    "segment" : segment,
    "dimensions" : dimensions,
    "filters" : filter,
    "start-date" : startdate,
    "end-date" : enddate,
    "sort" : sort,
    "max-results" : maxresults
  };
  query = gaQuery(params);
  gaData = query.fetchResult();
  if (gaData.error) {
    returnVal = gaData.errorMsg;
  } else {
    returnVal = gaData.rows;
  }
  return returnVal;
}

function getSettingGADataToSheet() {
  var gaData, query;
  query = gaQuery(settings);
  gaData = query.fetchResult();
  if (gaData.error) {
    Browser.msgBox(gaData.errorMsg);
  } else {
    populateSpreadsheet(gaData, query.info);
  }

  // debug function
  var gaString = Utilities.jsonStringify(gaData);
  Logger.log(gaString);
}

// -----------------------------------------------------------------
// set up request

function gaQuery(params) {
  var baseURL, startDate, endDate, returnObj, query, qryStr, qryInfo, missingParams;
  baseURL = "https://www.googleapis.com/analytics/v3/data/ga?";
  startDate = dateForURL(params["start-date"]);
  endDate = dateForURL(params["end-date"]);
  qryStr = "";
  qryInfo = [ [ "Query info", "" ] ];
  missingParams = [];
  query = {
    addElem : function(elemName, isMandatory, value) {
      if (typeof value === "undefined") {
        value = params[elemName] || "";
      }
      if (isMandatory && (value === "")) {
        missingParams.push(elemName);
      }
      // add to query info even if zero length value
      qryInfo.push([ elemName, value ]);
      // add to query string if there is an actual value
      if (value !== "") {
        if (qryStr !== "") {
          qryStr = qryStr + "&";
        }
        qryStr = qryStr + elemName + "=" + encodeURIComponent(value);
      }
      return this;
    }
  };
  query.addElem("ids", true)
    .addElem("dimensions")
    .addElem("metrics", true)
    .addElem("segment")
    .addElem("filters")
    .addElem("sort")
    .addElem("start-date", true, startDate)
    .addElem("end-date", true, endDate)
    .addElem("start-index")
    .addElem("max-results");
  if (missingParams.length) {
    returnObj =
      {
        uri : "",
        info : [],
        fetchResult : function() {
          return {
            "error" : true,
            "errorMsg" : "Mandatory parameter(s) missing or invalid. Check "
              + missingParams.join(",")
          };
        }
      };
  } else {
    returnObj = {
      uri : baseURL + qryStr,
      info : qryInfo,
      fetchResult : function() {
        var result = getGA(baseURL + qryStr);
        qryInfo.push([ "", "" ]);
        qryInfo.push([ "Result info", "" ]);
        qryInfo.push([ "Total results", result.totalResults ]);
        qryInfo.push([ "sampled", result.containsSampledData ]);
        return result;
      }
    };
  }
  return returnObj;
}

// ------------------------------------------------------------
// interaction with Analytics API
function getGA(URL) {
  var oauthConfig, requestData, result;
  oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig
    .setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oauthConfig
    .setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?" +
    		"scope=https://www.googleapis.com/auth/analytics.readonly");
  oauthConfig
    .setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  requestData = {
    "method" : "GET",
    "oAuthServiceName" : "google",
    "oAuthUseToken" : "always",
  };
  Logger.log("doing fetch: " + URL);

  try {
    result = UrlFetchApp.fetch(URL, requestData);
    result = Utilities.jsonParse(result.getContentText());
  } catch (e) {
    if (e.message) {
      Logger.log(e.message);
      result = {
        "error" : true,
        "errorMsg" : e.message
      };
    }
  }
  Logger.log("done fetch");
  return result;
}

// ------------------------------------------------------------
// functions to read and manipulate the spreadsheet

function getSettings() {
  // returns a javascript object where each name-value pair in the
  // _settings sheet is represented by a member
  var settingSheetName, ss, sheet, numRows, cells, settingsObj, i;
  // pre-load some default settings
  settingsObj = {
    "start-index" : 1,
    "max-results" : 100,
    "startRow" : 1,
    "startCol" : 4,
    "showParams" : true
  };
  settingSheetName = "_settings";
  ss = SpreadsheetApp.getActiveSpreadsheet();
  sheet = ss.getSheetByName(settingSheetName);
  numRows = sheet.getLastRow() - 1; // data start at row 2
  cells = sheet.getRange(2, 1, numRows, 2).getValues();
  for (i = 0; i < cells.length; ++i) {
    settingsObj[cells[i][0]] = cells[i][1];
  }
  return settingsObj;
}

function populateSpreadsheet(gaData, qryInfo) {
  // gaData is the JSON result from the GA query
  // qryInfo is an array with the query parameters and result metadata
  // the rows object within the json is conveniently compatible with spreadsheet
  // setValues
  var i, rngData, sheet, rngHeadsRow, destinationRange;
  rngData = gaData.rows;
  sheet = targetSheet();
  // pull the column headers out of json and splice into beginning of data array
  rngHeadsRow = [];
  for (i = 0; i < gaData.columnHeaders.length; ++i) {
    rngHeadsRow.push(gaData.columnHeaders[i].name.replace('ga:', ''));
  }
  rngData.splice(0, 0, rngHeadsRow);
  // put the data in and set froze rows if 4 or less would be frozen (any
  // further down and we
  // are likely placing date below soemthing else)
  destinationRange =
    sheet.getRange(settings["startRow"], settings["startCol"],
      rngData.length, rngData[0].length);
  destinationRange.setValues(rngData);
  if (settings["startRow"] < 5) {
    sheet.setFrozenRows(1);
  }
  // put the query info in if settings say so
  if (settings["showParams"]) {
    destinationRange =
      sheet.getRange(settings["startRow"], settings["startCol"]
        + rngData[0].length + 1, qryInfo.length, qryInfo[0].length);
    destinationRange.setValues(qryInfo);
  }
  // format the columns based on the heads info
  for (i = 0; i < gaData.columnHeaders.length; ++i) {
    destinationRange =
      sheet.getRange(settings["startRow"] + 1, settings["startCol"] + i,
        rngData.length - 1, 1);
    switch (gaData.columnHeaders[i].dataType) {
      case "STRING":
        // do nothing - if this is a date and you set the format, strange things
        // happen
        // ga returns dates as strings like yyyymmdd
        break;
      case "INTEGER":
        destinationRange.setNumberFormat("#,##0");
        break;
      case "TIME":
        destinationRange.setNumberFormat("0.00");
        break;
    }
  }
}

function targetSheet() {
  var ss, sheetName, sheet, msg, usrCheckResult;
  ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetName = settings["targetSheetName"];
  if (sheetName === "") {
    Browser
      .msgBox("The setting 'targetSheetName' was not found. A new sheet will be started");
    sheet = ss.insertSheet();
  } else {
    sheet = ss.getSheetByName(sheetName);
    if (sheet == null) {
      sheet = ss.insertSheet(sheetName);
    } else {
      // this is an existing sheet, check if there is data in it
      if (sheet.getLastRow() > 0) {
        msg =
          "The sheet "
            + sheetName
            + " exists and has data in it. "
            + "Cells in the target area will be overwritten. Cells not " 
            +	"in the target area will not. \nProceed? \n(If you say 'no' a new sheet "
            + "will be created instead.)";
        usrCheckResult =
          Browser.msgBox("overwrite sheet?", msg, Browser.Buttons.YES_NO);
        if (usrCheckResult === "no") {
          sheet = ss.insertSheet();
        }
      }
    }
  }
  return sheet;
}

// -------------------------------------------------------------
// utility functions for formatting for URL

function dateForURL(inDate) {
  var dTmp, sTmp, day, year, month;
  dTmp = new Date(inDate);
  if ((Object.prototype.toString.call(dTmp) === '[object Date]')
    && isFinite(dTmp)) {
    year = dTmp.getFullYear();
    month = dTmp.getMonth();
    month++; // js month is zero based
    if (month.toString().length === 1) {
      month = "0" + month;
    }
    day = dTmp.getDate();
    if (day.toString().length === 1) {
      day = "0" + day;
    }
    sTmp = year + "-" + month.toString() + "-" + day.toString();
  }
  return sTmp;

}