/// ---------------------------------------------------------------
// Name : Get Specified Placement Metrics (Single Account Only)
// 
// Authors : infooptimumclick.co.uk & nick.broad@searchQualified.co.uk
//
// Descripion :
// 
// i) Make a copy of the sample placement sheet found here ("https://docs.google.com/spreadsheets/d/1pso7ChqoLYeVRuNWkNt5lwwurK87g8r6WfTnPiqPKyw/edit?usp=sharing")
// ii) Add your list of domains to the "Domains" tab into column A.
// iii) Set the CONFIG parameters below according to instructions.
// iv) Run the script
// v) Previous results will be cleared and new values added. The list of domains remains intact
// 
// Notes 
//
// *** Contact Us for a hidden feature ***
// 
// *** MCC Version available ***
//
// Change Log : 
// 
// 14th Dec 2016 : Version 1.0 
//
// ---------------------------------------------------------------

// User configuration
// ------------------
var CONFIG={
  // Enter the url of the sheet containing the target placements
  CONTROL_SHEET:"",                      
  
  // Enter the tab name containing the target placements, this tab must exist with the list of placements.
  PLACEMENT_LIST_TAB:"Domains",       
  
  // Reporting start date. Format is YYYYMMDD, blank defaults to 1 year before END_DATE
  START_DATE:””,
 
  // Reporting end date. Format is YYYYMMDD, blank defaults to yesterday. Hence, if both dates are blank the default is last year
  END_DATE:””,
  
  // Include paused campaigns ? Y/N. If left blank the default is Y
  INCLUDE_PAUSED_CAMPAIGNS:"",
 
  // Include conversion metrics ? Y/N. If left blank the default is N              
  INCLUDE_CONVERSIONS:"",
  
// End User configuration
// -------------------------------------------------------------------
  
// Do not change past here, unless you are familiar with AdWords scripts
  METRIC_LIST:['Impressions','Clicks','Cost'],
  DEBUG:false
}

var controlSheet ;
  
function main() 
{
  var controlSheet=SpreadsheetApp.openByUrl(CONFIG.CONTROL_SHEET) ;
  
  if (!controlSheet)
  {
    Logger.log("Cannot open control sheet " + CONFIG.CONTROL_SHEET) ;
    return ;
  }
  
  // Set-up parameters for the placement report
  var parameters={} ;
  
  // Get list of placements
  parameters.placementList=getPlacementList(controlSheet) ;

  if (!parameters.placementList) return ;
  
  // Define list of metrics
  parameters.metricList=CONFIG.METRIC_LIST ;
  
  if (CONFIG.INCLUDE_CONVERSIONS.toLowerCase()=="y") parameters.metricList.push('Conversions') ;
  
  // Define report where clause
  if (CONFIG.INCLUDE_PAUSED_CAMPAIGNS.toLowerCase()!="n") {
    parameters.whereClause="WHERE CampaignStatus != REMOVED " ;
  } else {
    parameters.whereClause="WHERE CampaignStatus = ENABLED " ;
  }
  
  parameters.whereClause+=" AND Impressions > 0 " ;
  
  // Define date range
  if (CONFIG.END_DATE=="")  {
    parameters.endDate=getYesterday() ;
  } else {
    parameters.endDate=CONFIG.END_DATE ;
  }
  
  debugMsg(parameters.endDate) ;

  if (CONFIG.END_DATE=="")  {
    parameters.startDate=getLastYear(parameters.endDate);
  } else {
    parameters.startDate=CONFIG.START_DATE ;
  }
  
  debugMsg(parameters.startDate) ;

  debugMsg(JSON.stringify(parameters)) ;
  
  parameters=processAccount(parameters) ;  
  
  writeResults(controlSheet, parameters) ;
}

function getYesterday()
{
  var today = new Date();
  var yesterday = new Date(today);
  yesterday.setDate(today.getDate() - 1) ;
  
  return (Utilities.formatDate(yesterday, 'TZ', 'yyyyMMdd')) ;
}

function getLastYear(_date)
{
  var oneYearAgo=parseInt(_date.substring(0,4))-1 ; // Basically YYYY - 1
  var month=parseInt(_date.substring(4,6))-1 ;      // Months are 0-11
  var day=parseInt(_date.substring(6)) ;

  var _date=new Date(oneYearAgo.toString(), month, day) ;
  
  _date.setDate(_date.getDate() + 1) ;
  
  return (Utilities.formatDate(_date, 'TZ', 'yyyyMMdd')) ;
}

function getPlacementList(controlSheet)
{
  var placementTab=controlSheet.getSheetByName(CONFIG.PLACEMENT_LIST_TAB) ;

  if (!placementTab)
  {
    Logger.log("Error : Cannot access control sheet Accounts tab " + CONFIG.PLACEMENT_LIST_TAB) ;
    return ;
  }
               
  var rows = placementTab.getDataRange();
  var values = rows.getValues();
  var numRows=values.length ;
  
  if (numRows<2)
  {
    Logger.log("No placements specified") ;
    return ;
  }
  
  // Placements start at row 2.
  var placements={} ;
  
  for(var i = 1; i<numRows; i++) {
    placements[values[i][0]]={} ;
  }
  
  return (placements) ;
}

function debugMsg(str)
{
  if (CONFIG.DEBUG) Logger.log(str) ;
}

function processAccount(parameters)
{
  var queryStr='SELECT Criteria, ' + parameters.metricList.join(",") + ' ' + 
    'FROM PLACEMENT_PERFORMANCE_REPORT ' +
     parameters.whereClause + ' ' +
    'DURING ' + parameters.startDate + ',' + parameters.endDate ;
  
  debugMsg(queryStr) ;
     
  var report = AdWordsApp.report(queryStr) ;
  
  var rows = report.rows();
     
  while(rows.hasNext())
  {
    var row = rows.next();
    
    var placement=row['Criteria'].split(":")[0] ;

    if (parameters.placementList[placement]) {

      debugMsg("Get Stats for " + placement) ;
  
      
      for (var i=0;(i<parameters.metricList.length);i++) {
        var param=parameters.metricList[i] ;
        debugMsg(param + "->" + parseFloat(row[param])) ;
        if (!parameters.placementList[placement][param]) parameters.placementList[placement][param]=0 ;
        
        parameters.placementList[placement][param]+=parseFloat(row[param]) ;
        
      }
    }   
  }
  
  return(parameters) ;
}

function writeResults(controlSheet, parameters)
{
  // Find target tab
  var resultsTab=controlSheet.getSheetByName(CONFIG.PLACEMENT_LIST_TAB) ;
  
  if (!resultsTab)
  {
    // Create tab if it doesn't exist and write the headers
  }
  else
  {
    // Write the headers in
    resultsTab.getRange(1, 2, 1, resultsTab.getMaxColumns()-1).clear() ;
    var headers=[] ;
    headers.push(parameters.metricList) ;
    resultsTab.getRange(1, 2, 1, parameters.metricList.length).setValues(headers) ;
  }
  

  var rows = resultsTab.getDataRange();
  var values = rows.getValues();
  var numRows=values.length ;
  
  if (numRows<2)
  {
    Logger.log("No placements specified") ;
    return ;
  }
  
  // Placements start at row 2.
  for(var i = 1; i<numRows; i++) {
    var row=values[i] ;
    var placement=row[0] ;
    
    debugMsg("Look for " + placement) ;
    
    if (parameters.placementList[placement])
    {
      debugMsg("Apply row " + JSON.stringify(parameters.placementList[placement])) ;
      var spreadSheetRow=i+1 ;
      var metrics=[] ;
      for (var m=0; m<parameters.metricList.length;m++)
      {
        var metric=parameters.metricList[m] ;
        debugMsg(metric) ;
        if (parameters.placementList[placement][metric]!=undefined) {
          metrics.push(parameters.placementList[placement][metric]) ;
        } else {
          metrics.push("") ;
        }
      }
      var metricValues=[] ;
      metricValues.push(metrics) ;
      debugMsg(spreadSheetRow + "/" + resultsTab.getMaxColumns()) ;
      if (resultsTab.getMaxColumns()>1)
      {
        resultsTab.getRange(spreadSheetRow, 2, 1, resultsTab.getMaxColumns()-1).clear() ;
      }
      resultsTab.getRange(1, 2, 1, parameters.metricList.length).setValues(headers) ;
      resultsTab.getRange(spreadSheetRow, 2, 1, parameters.metricList.length).setValues(metricValues) ;
    }
  }
}