title: Google Apps Script: Automated reporting with Google Analytics
body: <p>I am trying to automate some custom Google Analytics reports with Google Apps Script and output the data into a Google docs spreadsheet. I followed a tutorial and got it working with the following code:</p>
  
  <pre><code>    function runDemo() {
    try {
  
      var firstProfile = getFirstProfile();
      var results = getReportDataForProfile(firstProfile);
      outputToSpreadsheet(results);
  
    } catch(error) {
      Browser.msgBox(error.message);
    }
  }
  
  //traverse down the Management API hierarchy - Account &gt;&gt; Property ID &gt;&gt; View ID - returns TJ main site view (profile)
  
  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()[25].getId(); // The property id for TJ is 26 but it is an array so 25 starting from 0
        var profiles = Analytics.Management.Profiles.list(firstAccountId, firstWebPropertyId);
  
        if (profiles.getItems()) {
          var firstProfile = profiles.getItems()[0];
          return firstProfile;
  
        } else {
          throw new Error('No views (profiles) found.');
        }
      } else {
        throw new Error('No webproperties found.');
      }
    } else {
      throw new Error('No accounts found.');
    }
  }
  
  // Querying for Google Analytics report data
  
  function getReportDataForProfile(firstProfile) {
  
    var profileId = firstProfile.getId();
    var tableId = 'ga:' + profileId;
    var startDate = getLastNdays(7);   // Sets the start date to 7 days ago.
    var endDate = getLastNdays(1);      // Yesterday.
  
    var optArgs = {
     //'dimensions': 'ga:keyword',              // Comma separated list of dimensions.
     // 'sort': '-ga:sessions,ga:keyword',       // Sort by sessions descending, then keyword.
     // 'segment': 'dynamic::ga:isMobile==Yes',  // Process only mobile traffic.
     // 'filters': 'ga:source==google',          // Display only google traffic.
     // 'start-index': '1',
     // 'max-results': '250'                     // Display the first 250 results.
    };
  
  
    // Make a request to the API.
    var results = Analytics.Data.Ga.get(
        tableId,                    // Table id (format ga:xxxxxx).
        startDate,                  // Start-date (format yyyy-MM-dd).
        endDate,                    // End-date (format yyyy-MM-dd).
        'ga:sessions,ga:pageviews,ga:sessionDuration,ga:pageviewsPerSession,ga:bounceRate,ga:percentNewSessions', // Comma seperated list of metrics.
        optArgs);
  
  
    if (results.getRows()) {
      return results;
  
    } else {
      throw new Error('No views (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');
  }
  
  
  // Print on the sheet
  
  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());
  }
  </code></pre>
  
  <p>This works just fine but the problem appears when I try to query the Google Analytics API for a second set of results. The reporting I need to do requires various segments and filtering so I assume I need to create multiple sets of results to be written on the sheet but the extra code does not work - there is a second sheet created but the data written on it is the data from var 'results' and not 'results1'. Here is the code:</p>
  
  <pre><code>function runDemo() {
    try {
  
      var firstProfile = getFirstProfile();
      var results = getReportDataForProfile(firstProfile);
      var results1 = getReportDataForProfile(firstProfile);
      outputToSpreadsheet(results);
      outputToSpreadsheet(results1);
  
    } catch(error) {
      Browser.msgBox(error.message);
    }
  }
  
  //traverse down the Management API hierarchy - Account &gt;&gt; Property ID &gt;&gt; View ID - returns TJ main site view (profile)
  
  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()[25].getId(); // The property id for TJ is 26 but it is an array so 25 starting from 0
        var profiles = Analytics.Management.Profiles.list(firstAccountId, firstWebPropertyId);
  
        if (profiles.getItems()) {
          var firstProfile = profiles.getItems()[0];
          return firstProfile;
  
        } else {
          throw new Error('No views (profiles) found.');
        }
      } else {
        throw new Error('No webproperties found.');
      }
    } else {
      throw new Error('No accounts found.');
    }
  }
  
  // Querying for Google Analytics report data
  
  function getReportDataForProfile(firstProfile) {
  
    var profileId = firstProfile.getId();
    var tableId = 'ga:' + profileId;
    var startDate = getLastNdays(7);   // Sets the start date to 7 days ago.
    var endDate = getLastNdays(1);      // Yesterday.
  
  
    //Optional Arguments - all commented out because the first one is empty
    var optArgs = {
     //'dimensions': 'ga:keyword',              // Comma separated list of dimensions.
     // 'sort': '-ga:sessions,ga:keyword',       // Sort by sessions descending, then keyword.
     // 'segment': 'dynamic::ga:isMobile==Yes',  // Process only mobile traffic.
     // 'filters': 'ga:source==google',          // Display only google traffic.
     // 'start-index': '1',
     // 'max-results': '250'                     // Display the first 250 results.
    };
  
    //Optional arguments - this time I need the organic only
  
    var optArgs1 = {
  
      'segment': 'gaid::-5'                   // Process only Non paid search traffic.
  
    };
  
  
    // Make the fist request to the API.
    var results = Analytics.Data.Ga.get(
        tableId,                    // Table id (format ga:xxxxxx).
        startDate,                  // Start-date (format yyyy-MM-dd).
        endDate,                    // End-date (format yyyy-MM-dd).
        'ga:sessions,ga:pageviews,ga:sessionDuration,ga:pageviewsPerSession,ga:bounceRate,ga:percentNewSessions', // Comma seperated list of metrics.
        optArgs);  //This time we pass the optArgs - ie the empty one
  
         // Make the second request to the API.
    var results1 = Analytics.Data.Ga.get(
        tableId,                    // Table id (format ga:xxxxxx).
        startDate,                  // Start-date (format yyyy-MM-dd).
        endDate,                    // End-date (format yyyy-MM-dd).
        'ga:sessions', // Comma separated list of metrics.
        optArgs1);  //This time we pass the optArgs1 - ie the one with segment=organic
  
  
    if (results.getRows()) {
      return results;
  
    } else {
      throw new Error('No views (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');
  }
  
  
  // Print on the sheet - 
  
  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());
  }
  
  // Print again, this time the data from results1 - ie the organic visits
  
  function outputToSpreadsheet(results1) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  
    // Print the headers.
    var headerNames = [];
    for (var i = 0, header; header = results1.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, results1.getRows().length, headerNames.length)
        .setValues(results1.getRows());
  }
  </code></pre>
  
  <p>The error message I get is: "results" is not defined and nothing gets written on the sheet. I was expecting to get at least the results of the first query to appear as the code for var 'results' is still the same. It is the 'results1' that is added on.</p>
  
  <p>As you can probably guess, I am not a developer and my exposure to JavaScript is minimal so I would really appreciate any help with that. Thanks in advance!</p>