Automating Google Analytics Data into Google Spreadsheet

[jus...@sweetmetrics.com]

Hi,

I'm a novice developer by all means but I've been able to follow a blog post https://developers.google.com/analytics/solutions/articles/reporting-apps-script?hl=en

to the point where I am SO CLOSE to where I need to be.  I'm trying to automate a query of Analytics Data into a Google Spreadsheet for reporting purposes.

My issue is, I cannot get the first part of code 'getFirstProfile' to work how I need it to.  I know the the account, property and profileID of the data I am trying to export but I cannot figure out how to alter the function 'getFirstProfile' to the correct website.  I do have multiple accounts under my Google analytics username.

Finally, I've attached the script that is in my Google Spreadsheet that I'm working on.  

In summary, I am trying to have this code export data from a specific account/property/profile (aka - website in layman's terms) instead of the 'first one' that this code is designed to do.

Any help would be greatly appreciated!

Thank you! :-)

Justin

[jus...@sweetmetrics.com]

PS - Here is my code (it would not attach)

function runDemo() {

  try {

    var firstProfile = getFirstProfile();

    var results = getReportDataForProfile(firstProfile);

    outputToSpreadsheet(results);

  } catch(error) {

    Browser.msgBox(error.message);

  }

}

function getFirstProfile() {  

  var accounts = Analytics.Management.Accounts.list();

  if (accounts.getItems()) {

    var firstAccountId = accounts.getItems()[0].getId();

    var webProperties = Analytics.Management.Webproperties.list(firstAccountId);

    if (webProperties.getItems()) {

      var firstWebPropertyId = webProperties.getItems()[0].getId();

      var profiles = Analytics.Management.Profiles.list(firstAccountId, firstWebPropertyId);

      if (profiles.getItems()) {

        var firstProfile = profiles.getItems()[0];

        return firstProfile;

      } else {

        throw new Error('No profiles found.');

      }

    } else {

      throw new Error('No webproperties found.');

    }

  } else {

    throw new Error('No accounts found.');

  }

}

function getReportDataForProfile(firstProfile) {

  var profileId = firstProfile.getId();

  var tableId = 'ga:' + profileId;

  var startDate = getLastNdays(2);   

  var endDate = getLastNdays(0);      

  var optArgs = {

    'dimensions': 'ga:date,ga:medium,ga:source,ga:campaign',        

    'sort': '-ga:date',

    'start-index': '1',

    'max-results': '10000'

  };

  // Make a request to the API.

  var results = Analytics.Data.Ga.get(

      tableId,                 

      startDate,               

      endDate,                  

    'ga:visits,ga:transactions,ga:transactionRevenue', 

      optArgs);

  if (results.getRows()) {

    return results;

  } else {

    throw new Error('No profiles found');

  }

}

function getLastNdays(nDaysAgo) {

  var today = new Date(); 

  var before = new Date();

  before.setDate(today.getDate() - nDaysAgo);

  return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');

}

function outputToSpreadsheet(results) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();

  // Print the headers.

  var headerNames = [];

  for (var i = 0, header; header = results.getColumnHeaders()[i]; ++i) {

    headerNames.push(header.getName());

  }

  sheet.getRange(1, 1, 1, headerNames.length)

      .setValues([headerNames]);

  // Print the rows of data.

  sheet.getRange(2, 1, results.getRows().length, headerNames.length)

      .setValues(results.getRows());

}

[Nick]

Hi,

You might just try using the magic script to solve this: http://analytics.blogspot.com/2012/08/automate-google-analytics-reporting.html

If you already know your profileID, just remove the getFirstProfile method,

and set 

> var tableId = 'ga:' + profileId

to your profileId.

-Nick

[jus...@sweetmetrics.com]

Nick,

Thanks for your help!  That little tweak fixed it. I was actually thinking of doing just that, but wasn't sure of how the functions/variables were related.

Thank you again,

Justin

[jus...@sweetmetrics.com]

Follow up question:

I would like to tweak this code to clear/import data into one sheet instead of creating a new one every time   In other words, I want to create a few charts based on this data that are updated daily without having to re-write formulas on another sheet. (I'm also going to join this data with 3rd party sources, hence my reasoning for bringing this out of Google Analytics)

Any idea on how to do this?

Thanks!
Justin