google-ads-stats-in-charts.js

//This script will send you four simple charts per account.
//Clicks, Cost, Conversions, CostPerConversion for the past 28 days.
//or
//Clicks, Cost, AllConversions, CostPerAllConversion for the past 28 days.  

//Enter your email address where you want the emails to be sent:
var EMAIL_ADDRESS_TO_NOTIFY = "[email protected]";

//Enter the label for all the accounts you wish to analyze
var ACCOUNT_LABEL_TO_CHECK = "Monitor";

//Only will process this many accounts
var MAX_ACCOUNTS = 20;

//The URL to the Google Sheet where the data will be dumped
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit#gid=0';

//The days that you don't want the reports to be emailed.
var DONT_RUN_ON_DAYS = ['Sunday','Saturday','Tuesday','Thursday'];

//What type of conversions? false for conversions or true for AllConversions.
var USE_ALL_CONVERSIONS = false;

var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var timestamp = Utilities.formatDate(new Date(), 'America/Chicago', 'yyyy-MM-dd-HH:mm:ss').toString();

function main() {
	Logger.log('Before we start - timestamp = ' + timestamp);

  if (check_day_of_week()) {
    return;
  }
  //Delete all the sheets within it:
  deleteSheets(ss,timestamp);

  var accountSelector = MccApp.accounts();
  accountSelector
    .withLimit(MAX_ACCOUNTS)
    .withCondition('LabelNames CONTAINS "' + ACCOUNT_LABEL_TO_CHECK +'"')
    .executeInParallel('processClientAccount','cleanUp',timestamp);
}

function processClientAccount(timestamp) {

  // Process your client account here.
  var dateStr = Utilities.formatDate(new Date(), AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
  var account = AdsApp.currentAccount();
  var accountName = account.getName();
  var accountId = account.getCustomerId();
  var accountCurrencyCode = getCurrencySymbol(AdsApp.currentAccount().getCurrencyCode());
  var timeZone = AdsApp.currentAccount().getTimeZone();

  Logger.log(accountName + '(' + accountId + ') - ' + 'Starting to process account.');

  //First get the various date ranges
  Logger.log(accountName + '(' + accountId + ') - ' + 'Setting up the date range.');

  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var now = new Date();

  //Get the date range for the last 28 days
  var start_28_days_ago = Utilities.formatDate(new Date(now.getTime() - 28 * MILLIS_PER_DAY), timeZone, 'yyyy-MM-dd');
  var end_28_days_ago = Utilities.formatDate(new Date(now.getTime() - 1 * MILLIS_PER_DAY), timeZone, 'yyyy-MM-dd');
  var dateRange =  start_28_days_ago.replace(/-/g, '') + ',' + end_28_days_ago.replace(/-/g, '');

  var queryText = 'SELECT Date, Clicks, Cost, AllConversions, CostPerAllConversion, Conversions, CostPerConversion FROM ACCOUNT_PERFORMANCE_REPORT DURING ' + dateRange + ' ';

  var result = AdsApp.report(queryText);

  var ssName = accountName + ' (' + accountId + ')';

  Logger.log('Exporting to spreadsheet = ' + ssName);
  result.exportToSheet(ss.insertSheet(ssName));

  Logger.log(accountName + '(' + accountId + ') - ' + 'Building the click chart.');
  var lineChartClicks = BuildChart('Clicks',accountCurrencyCode,ssName);

  Logger.log(accountName + '(' + accountId + ') - ' + 'Building the cost chart.');
  var lineChartCost = BuildChart('Cost',accountCurrencyCode,ssName);

  Logger.log(accountName + '(' + accountId + ') - ' + 'Building the AllConversions chart.');
  var lineChartAllConversions = BuildChart('AllConversions',accountCurrencyCode,ssName);

  Logger.log(accountName + '(' + accountId + ') - ' + 'Building the cost per All Conversions chart.');
  var lineChartCostPerAllConversion = BuildChart('CostPerAllConversion',accountCurrencyCode,ssName);

  Logger.log(accountName + '(' + accountId + ') - ' + 'Building the Conversions chart.');
  var lineChartConversions = BuildChart('Conversions',accountCurrencyCode,ssName);

  Logger.log(accountName + '(' + accountId + ') - ' + 'Building the cost per Conversions chart.');
  var lineChartCostPerConversion = BuildChart('CostPerConversion',accountCurrencyCode,ssName);

  if (USE_ALL_CONVERSIONS) {
    //Send data on All Conversions:
    var strHTML = 'These are the stats for the past 28 days: ('+ start_28_days_ago + ' - ' + end_28_days_ago +') <br/> <br/>' +
        '<img src="cid:Clicks" alt="Clicks over the past 28 days" />' + '<img src="cid:Cost" alt="Cost over the past 28 days" /><br/>' +
        '<img src="cid:AllConversions" alt="AllConversions over the past 28 days" />' + '<img src="cid:CostPerAllConversions" alt="CostPerAllConversions over the past 28 days" /><br/>' +
        '<br /><br />' + SPREADSHEET_URL + '<br /><br />';

    Logger.log(accountName + '(' + accountId + ') - ' + 'Sending the email notification.');

    MailApp.sendEmail({
      to: EMAIL_ADDRESS_TO_NOTIFY,
      subject: 'Google Ads - Past 28 days stats: ' + accountName + '(' + accountId + ') - ' + dateStr,
      htmlBody: strHTML,
      inlineImages: {
        'Clicks': lineChartClicks.getBlob().setName("Clicks over the past 28 days"),
        'Cost': lineChartCost.getBlob().setName("Cost over the past 28 days"),
        'AllConversions': lineChartAllConversions.getBlob().setName("AllConversions over the past 28 days"),
        'CostPerAllConversions': lineChartCostPerAllConversion.getBlob().setName("Cost Per All Conversions over the past 28 days"),
      }
    });
  } else {
    //Send data on Conversions:
    var strHTML = 'These are the stats for the past 28 days: ('+ start_28_days_ago + ' - ' + end_28_days_ago +') <br/> <br/>' +
        '<img src="cid:Clicks" alt="Clicks over the past 28 days" />' + '<img src="cid:Cost" alt="Cost over the past 28 days" /><br/>' +
        '<img src="cid:Conversions" alt="Conversions over the past 28 days" />' + '<img src="cid:CostPerConversion" alt="CostPerConversion over the past 28 days" /><br/>' +
        '<br /><br />' + SPREADSHEET_URL + '<br /><br />';
    Logger.log(accountName + '(' + accountId + ') - ' + 'Sending the email notification.');

    MailApp.sendEmail({
      to: EMAIL_ADDRESS_TO_NOTIFY,
      subject: 'Google Ads - Past 28 days stats: ' + accountName + '(' + accountId + ') - ' + dateStr,
      htmlBody: strHTML,
      inlineImages: {
        'Clicks': lineChartClicks.getBlob().setName("Clicks over the past 28 days"),
        'Cost': lineChartCost.getBlob().setName("Cost over the past 28 days"),
        'Conversions': lineChartConversions.getBlob().setName("Conversions over the past 28 days"),
        'CostPerConversion': lineChartCostPerConversion.getBlob().setName("Cost Per Conversion over the past 28 days"),
      }
    });
  }
  Logger.log(accountName + '(' + accountId + ') - ' + 'Sent email notification.');
  Logger.log(accountName + '(' + accountId + ') - ' + 'Finished processing the account.');
  
  return JSON.stringify({timestamp:timestamp});  
}

function cleanUp(results){
  
  Logger.log('Cleaning up my mess');
  if (results.length > 0) {
    var obj = JSON.parse(results[0].getReturnValue());
    
    Logger.log('Cleaning up the mess - timestamp = ' + obj.timestamp);
    //Remove the first sheet that should be blank.
    if (ss.getNumSheets()>1){
      var sheet = ss.getSheetByName(obj.timestamp);
      ss.deleteSheet(sheet);
    }
  } 
}

function check_day_of_week(){
  var weekday = new Array(7);
  weekday[0] = "Sunday";
  weekday[1] = "Monday";
  weekday[2] = "Tuesday";
  weekday[3] = "Wednesday";
  weekday[4] = "Thursday";
  weekday[5] = "Friday";
  weekday[6] = "Saturday";

  var date = new Date();
  if (DONT_RUN_ON_DAYS.indexOf(weekday[date.getDay()]) >= 0){
    Logger.log('TRUE - Todays day is (%s) date.getDay() is %s - indexOf (%s) ', weekday[date.getDay()], date.getDay(), DONT_RUN_ON_DAYS.indexOf(weekday[date.getDay()]));
    MailApp.sendEmail({
     to: EMAIL_ADDRESS_TO_NOTIFY,
     subject: 'Google Ads - Past 28 days stats: Didn\'t run because it was ' + weekday[date.getDay()],
     htmlBody: '<p>Didn\'t run stats because it was ' + weekday[date.getDay()] + '</p>'
    });

    return true;
  } else {
    Logger.log('FALSE - Todays day is (%s) date.getDay() is %s - indexOf (%s) ', weekday[date.getDay()], date.getDay(), DONT_RUN_ON_DAYS.indexOf(weekday[date.getDay()]));
    return false;
  }
}

function getCurrencySymbol(currency_code){
  var currency_symbols = {
      'USD': '$',  // US Dollar
      'EUR': '€',  // Euro
      'GBP': '£',  // British Pound Sterling
  };

  if(currency_symbols[currency_code]!==undefined){
    return currency_symbols[currency_code];
  }else{
    return '$'
  }
}

function deleteSheets(spreadsheet,timestamp) {
  //Loop through all sheets except for the first sheet.

  var sheets = spreadsheet.getSheets();

  //Add a new sheet at the start.
  spreadsheet.insertSheet(timestamp, 0);

  Logger.log('Number of sheets - before: ' + spreadsheet.getNumSheets());

  //Do we have more than two sheets?
  if (spreadsheet.getNumSheets()>1){
    //Loop through each of the sheets and delete them except for the first one.
    for (i = 0; i < sheets.length; i++) {
      Logger.log('(' + spreadsheet.getNumSheets() + ') - (' + sheets.length + ') - Deleting sheet named = ' + sheets[i].getSheetName());
      spreadsheet.deleteSheet(sheets[i]);
    }
  }

  Logger.log('Number of sheets - after: ' + spreadsheet.getNumSheets());
}

function BuildChart(myMetric, currencyFormat, ssName) {

  //Load the spreadsheeet
  var sheet = ss.getSheetByName(ssName).sort(1, true);


  //Grab all the data in the sheet
  var data = sheet.getRange('A1:G29').getValues();


  //Start Building the data table
  var dataTable = Charts.newDataTable();

  var cssValues = '';
  var currencyFormatOption = '';

  switch (myMetric) {
    case 'Clicks':
      cssValues = 'blue';

      //Add columns
      Logger.log('data[0][0]:'+data[0][0] + ' data[0][1]:'+data[0][1] );
      dataTable.addColumn(Charts.ColumnType.DATE, data[0][0]);
      dataTable.addColumn(Charts.ColumnType.NUMBER, data[0][1]);

      //Add rows
      for(var j=1; j<data.length; j++){
        //Logger.log(data[0][1] + ':' + data[j])
        dataTable.addRow([data[j][0],data[j][1]]);
      }

      break;
    case 'Cost':
      cssValues = 'green';
      currencyFormatOption = currencyFormat + '#,###.##';

      //Add columns
      Logger.log('data[0][0]:'+data[0][0] + ' data[0][2]:'+data[0][2] );
      dataTable.addColumn(Charts.ColumnType.DATE, data[0][0]);
      dataTable.addColumn(Charts.ColumnType.NUMBER, data[0][2]);

      //Add rows
      for(var j=1; j<data.length; j++){
        //Logger.log(data[0][2] + ':' + data[j])
        dataTable.addRow([data[j][0],data[j][2]]);
      }

      break;
    case 'AllConversions':
      cssValues = 'orange';

      //Add columns
      Logger.log('data[0][0]:'+data[0][0] + ' data[0][3]:'+data[0][3] );
      dataTable.addColumn(Charts.ColumnType.DATE, data[0][0]);
      dataTable.addColumn(Charts.ColumnType.NUMBER, data[0][3]);

      //Add rows
      for(var j=1; j<data.length; j++){
        //Logger.log(data[0][3] + ':' + data[j])
        dataTable.addRow([data[j][0],data[j][3]]);
      }

      break;
    case 'CostPerAllConversion':
      cssValues = 'red';
      currencyFormatOption = currencyFormat + '#,###.##';

      //Add columns
      Logger.log('data[0][0]:'+data[0][0] + ' data[0][4]:'+data[0][4] );
      dataTable.addColumn(Charts.ColumnType.DATE, data[0][0]);
      dataTable.addColumn(Charts.ColumnType.NUMBER, data[0][4]);

      //Add rows
      for(var j=1; j<data.length; j++){
        //Logger.log(data[0][4] + ':' + data[j])
        dataTable.addRow([data[j][0],data[j][4]]);
      }

      break;
    case 'Conversions':
      cssValues = 'orange';

      //Add columns
      Logger.log('data[0][0]:'+data[0][0] + ' data[0][5]:'+data[0][5] );
      dataTable.addColumn(Charts.ColumnType.DATE, data[0][0]);
      dataTable.addColumn(Charts.ColumnType.NUMBER, data[0][5]);

      //Add rows
      for(var j=1; j<data.length; j++){
        //Logger.log(data[0][3] + ':' + data[j])
        dataTable.addRow([data[j][0],data[j][5]]);
      }

      break;
    case 'CostPerConversion':
      cssValues = 'red';
      currencyFormatOption = currencyFormat + '#,###.##';

      //Add columns
      Logger.log('data[0][0]:'+data[0][0] + ' data[0][6]:'+data[0][6] );
      dataTable.addColumn(Charts.ColumnType.DATE, data[0][0]);
      dataTable.addColumn(Charts.ColumnType.NUMBER, data[0][6]);

      //Add rows
      for(var j=1; j<data.length; j++){
        //Logger.log(data[0][4] + ':' + data[j])
        dataTable.addRow([data[j][0],data[j][6]]);
      }

      break;
    default:
      MailApp.sendEmail({
        to: EMAIL_ADDRESS_TO_NOTIFY,
        subject: 'Google Ads - ERROR in Past 28 days stats: ' + ssName,
        htmlBody: '<p>There was an error trying to build a chart for this metric (<strong>' + myMetric + '</strong>)</p>'
      });
  }


  var trendlinesoptions = {
    0: {
      color: 'purple',
      //'type':'linear',
      type: 'exponential',
      pointSize: 5,
      opacity: 0.2,
      labelInLegend: myMetric + ' Trendline',
      visibleInLegend: true
    }};

  var minimum_horizontal_data_value_to_render = {
    minValue:0,
    viewWindow: {
      min: 0
    }
  }

  //Create and build chart
  var chartBuilder = Charts.newLineChart()
      .setDataTable(dataTable)
      .setTitle(myMetric + ' over the past 28 days')
      .setXAxisTitle('Date')
      .setYAxisTitle(myMetric)
      .setDimensions(850, 350)
      .setOption('legend.position', 'none')
      .setOption('vAxis', {'format':currencyFormatOption})
      .setOption('vAxis', minimum_horizontal_data_value_to_render)
      .setOption('trendlines', trendlinesoptions)
      .setCurveStyle(Charts.CurveStyle.NORMAL)
      .setPointStyle(Charts.PointStyle.MEDIUM)
      .setColors([cssValues]);

  var chart = chartBuilder.build();

  return chart;
}