Google Analytics API for Google Docs

google_apps_script

var DateTime = function() {
  var now = new Date();
  this.year = now.getFullYear();
  this.month = ('00' + now.getMonth()).slice(-2);
  var monthEndDate = new Date(this.year, this.month, 0);
  this.startDate = this.year + '-' + this.month + '-01';
  this.endDate = this.year + '-' + this.month + '-' + monthEndDate.getDate();
}

var Profile = function(profileName, tableId) {
  this.profileName = profileName;
  this.tableId = tableId;
  this.month = '';
  this.visits = '';
  this.goalCompletionsAll = '';
  this.pageviews = '';
}
    
var Fetcher = function(account, datetime) {
  this.email = account.email;
  this.password = account.password;
  this.datetime = datetime;
  this.authToken = '';
  this.profiles = [];
}
    
Fetcher.prototype = {
  getDatasets: function() {
    this.getAuthToken();
    this.getAccountFeed();
    this.getDataFeed();
    return this.profiles
  },
  getAuthToken: function() {
    var responseStr;
    var response = this.getResponse('https://www.google.com/accounts/ClientLogin', 'post');
    responseStr = response.getContentText();
    this.authToken = responseStr.slice(responseStr.search("Auth=") + 5, responseStr.length).replace(/\n/g, '');
  },
  getAccountFeed: function() {
    var response = this.getResponse(
      'https://www.google.com/analytics/feeds/accounts/default?prettyprint=true',
      'get'
    );
    var xmldoc = Xml.parse(response.getContentText());
    var entry = xmldoc.getElement().getElements("entry");
    for (var i = 0; i < entry.length; i++) {
      var elements = entry[i].getElements();
      var profileName = '';
      var tableId = '';
      for (var j = 0; j < elements.length; j++) {
        if (elements[j].getName().getLocalName() == "title") {
          profileName = elements[j].getText();
        }
        if (elements[j].getName().getLocalName() == "tableId") {
          tableId = elements[j].getText();
          break;
        }
      }
      var profile = new Profile(profileName, tableId);
      // Logger.log(profile);
      this.profiles.push(profile);
    }
  },
  getDataFeed: function() {
    var profiles = this.profiles;
    for (var i = 0; i < profiles.length; i++) {
      try {
        Utilities.sleep(200);
      } catch(e) {
        Logger.log('sleep failed\n' + e.message);
      }
      var response = this.getResponse(
        'https://www.google.com/analytics/feeds/data?ids='
        + profiles[i].tableId
        + '&dimensions=ga:month&metrics=ga:visits,ga:pageviews,ga:goalCompletionsAll&start-date='
        + this.datetime.startDate
        + '&end-date='
        + this.datetime.endDate
        + '&prettyprint=true',
        'get');
      var xmldoc = Xml.parse(response.getContentText());
      var entry = xmldoc.getElement().getElements("entry");
      for (var j = 0; j < entry.length; j++) {
        var elements = entry[j].getElements();
        for (var k = 0; k < elements.length; k++) {
          if (elements[k].getName().getLocalName() == "dimension") {
            if (elements[k].getAttribute("name").getValue()  == "ga:month") {
              profiles[i].month = elements[k].getAttribute("value").getValue();
            }
          }
          if (elements[k].getName().getLocalName() == "metric") {
            if (elements[k].getAttribute("name").getValue()  == "ga:visits") {
              profiles[i].visits = elements[k].getAttribute("value").getValue();
            }
            if (elements[k].getAttribute("name").getValue()  == "ga:pageviews") {
              profiles[i].pageviews = elements[k].getAttribute("value").getValue();
            }
            if (elements[k].getAttribute("name").getValue()  == "ga:goalCompletionsAll") {
              profiles[i].goalCompletionsAll = elements[k].getAttribute("value").getValue();
              break;
            }
          }
        }
      }
    }
    // Logger.log(this.profile);
  },
  getResponse: function (url, method) {
    var postOptions = {
      method: "post",
      payload: "accountType=GOOGLE&Email="
      + this.email
      + "&Passwd="
      + encodeURIComponent(this.password)
      + "&service=analytics&Source=together co. analytics2docs function"
    };
    var getOptions = {
      method: "get",
      headers: {
        "Authorization": "GoogleLogin auth=" + this.authToken,
        "GData-Version": "2"
      }
    };
    var options = (method == "post") ? postOptions : getOptions;
    try {
      return UrlFetchApp.fetch(url, options);
    } catch(e) {
      Logger.log('url fetch failed\n' + e.message);
    }  
  }
}

function getAccountDataList(accountList) {
  var accountData = accountList.getDataRange().getValues().slice(1);
  var accounts = [];
  for (var i = 0; i < accountData.length; i++) {
    var account = {
      email: accountData[i][0],
      password: accountData[i][1]
    };
    accounts.push(account);
  }
  return accounts
}

function getAnalyticsData() {
  var datetime = new DateTime();
  var sheetname = datetime.year + '年' + datetime.month + '月';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var accountList = ss.getSheetByName('AccountList');
  var accountDataList = getAccountDataList(accountList);
  var profiles = [];
  for (var i = 0; i < accountDataList.length; i++) {
    var fetcher = new Fetcher(accountDataList[i], datetime);
    profiles = profiles.concat(fetcher.getDatasets());
  }
  var newsheet = ss.getSheetByName(sheetname) || ss.insertSheet(sheetname);
  newsheet.clear();
  var datasets = [];
  datasets.push(['プロファイル名', '訪問数(セッション数)', 'PV数', '目標合計']);
  for (var i = 0; i < profiles.length; i++) {
    datasets.push([profiles[i].profileName, profiles[i].visits, profiles[i].pageviews, profiles[i].goalCompletionsAll]);
  }
  var dataRange = newsheet.getRange(1, 1, datasets.length, 4);
  dataRange.setValues(datasets);
  newsheet.setColumnWidth(1, 250);
  SpreadsheetApp.setActiveSheet(newsheet);
  SpreadsheetApp.flush();
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Google Analytics", [{name: "Get Analytics Data", functionName: "getAnalyticsData"}]);
}