SS_URL = 'enter the google sheets url'
EMAILS = ['email@example.com']
//EMAILS = ['x@gmail.com']

function formatDate(date, format){
    return Utilities.formatDate(date, AdWordsApp.currentAccount().getTimeZone(), format)
}

function parseDate(lookback1){


    var oneDay = 1000 * 60 * 60 * 24
    var yesterday = new Date(new Date() - oneDay)
    var xAgo = new Date(new Date() - (oneDay) * (lookback1))

    return formatDate(xAgo,'YYYYMMdd')

}

function getStats(base, exp, startDate, endDate){
  
  var fields = ['campaign.id',
                'campaign.name',
               'metrics.impressions',
               'metrics.clicks',
               'metrics.cost_micros',
               'metrics.conversions',
               'metrics.conversions_value']
  
  var query = "SELECT " + fields.join(',') + " FROM campaign_budget WHERE segments.date >= " + startDate + " AND segments.date <= " + endDate + " AND campaign.id in ( " + base + ',' + exp + ")"
  var report = AdsApp.search(query);
  
   while(report.hasNext()){
     
     var row = report.next()
    // Logger.log(row)
     if(row.campaign.id == base){
       
       var baseStats = [row.metrics.clicks, 
                        row.metrics.impressions, 
                        row.metrics.impressions > 0 ? row.metrics.clicks / row.metrics.impressions : 0,
                        row.metrics.clicks > 0 ? (row.metrics.costMicros / 1000000) / (row.metrics.clicks) : 0,
                        row.metrics.costMicros / 1000000,
                        row.metrics.conversionsValue,
                        row.metrics.costMicros > 0 ? (row.metrics.conversionsValue) / (row.metrics.costMicros / 1000000) : 0,
                        row.metrics.conversions,
                        row.metrics.clicks > 0 ? row.metrics.conversions / row.metrics.clicks : 0,
                        row.metrics.conversions > 0 ? (row.metrics.costMicros / 1000000) / row.metrics.conversions : 0
                        ]
                        
        var baseCampaignName = row.campaign.name
       
     }
     else if(row.campaign.id == exp){
       
       var expStats = [
                        row.metrics.clicks, 
                        row.metrics.impressions, 
                        row.metrics.impressions > 0 ? row.metrics.clicks / row.metrics.impressions : 0,
                        row.metrics.clicks > 0 ? (row.metrics.costMicros / 1000000) / (row.metrics.clicks) : 0,
                        row.metrics.costMicros / 1000000,
                        row.metrics.conversionsValue,
                        row.metrics.costMicros > 0 ? (row.metrics.conversionsValue) / (row.metrics.costMicros / 1000000) : 0,
                        row.metrics.conversions,
                        row.metrics.clicks > 0 ? row.metrics.conversions / row.metrics.clicks : 0,
                        row.metrics.conversions > 0 ? (row.metrics.costMicros / 1000000) / row.metrics.conversions : 0
                        ]
       
       var expCampaignName = row.campaign.name
     }
   }
  
  if(!expStats){
    expStats = [0,0,0,0,0,0,0,0,0,0]
  }
  
  if(!baseStats){
    baseStats = [0,0,0,0,0,0,0,0,0,0]
  }
  
  return {base:baseStats, exp:expStats, baseCampaignName:baseCampaignName, expCampaignName: expCampaignName}
}


function numberWithCommas(x) {
    return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
}

function getData(){
  var fields = [
                "campaign_experiment.start_date",
                "campaign_experiment.end_date",
                "campaign_experiment.name",
                "campaign_experiment.traffic_split_percent",
                "campaign_experiment.status",
                "campaign_experiment.campaign_draft",
                "campaign_experiment.experiment_campaign",
                "campaign_experiment.description"
]


  var query = "SELECT " + fields.join(',') + " FROM campaign_experiment"

  var report = AdsApp.search(query);
  var sheetRows = []
  var emailRows = []
  var statuses = []
  
  while(report.hasNext()){

    var row = report.next()
    
    var baseCampaignId = row.campaignExperiment.campaignDraft.split('/')[3].split('~')[0]
    var experimentCampaignId = row.campaignExperiment.experimentCampaign.split('/')[3]
    
//    var baseCampaign = AdsApp.campaigns().withIds([baseCampaignId]).get().next()
//    var experimentCampaign = AdsApp.campaigns().withIds([experimentCampaignId]).get().next()
    var startDate = row.campaignExperiment.startDate.replace('-','').replace('-','')
    var endDate = row.campaignExperiment.endDate.replace('-','').replace('-','')
    var stats = getStats(baseCampaignId, experimentCampaignId, startDate, endDate)
    var baseStats = stats.base
    var expStats = stats.exp
    var today = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'YYYY-MM-dd')

    
    var header1 = [stats.baseCampaignName,row.campaignExperiment.description ? 'Test details: ' + row.campaignExperiment.description : '','','','','','','','','','','','','']
    var header2 = [row.campaignExperiment.name, 'Traffic Split', 'Start Date', 'End Date', 'Clicks', 'Impr.', 'CTR', 'Avg. CPC', 'Cost', 'Conv. Value', 'ROAS', 'Conv.', 'Conv. Rate', 'CPA']
    var row1 = ['Original', row.campaignExperiment.trafficSplitPercent / 100, row.campaignExperiment.startDate, row.campaignExperiment.endDate].concat(stats.base)
    var row2 = ['Experiment', row.campaignExperiment.trafficSplitPercent / 100, row.campaignExperiment.startDate, row.campaignExperiment.endDate].concat(stats.exp)
    var rowBreak = ['','','','','','','','','','','','','','']
    
    var combined = [header1, header2, row1, row2, rowBreak, rowBreak]
    
    
    var rowe1 = ['Original', row.campaignExperiment.trafficSplitPercent + '%' ,
                 row.campaignExperiment.startDate, 
                 row.campaignExperiment.endDate,
                  numberWithCommas(parseInt(stats.base[0])),
                  numberWithCommas(parseInt(stats.base[1])),
                  parseFloat(stats.base[2] * 100).toFixed(2) + '%',
                  '$' + parseFloat(stats.base[3]).toFixed(2),
                  '$' + numberWithCommas(parseInt(stats.base[4])),
                  '$' + numberWithCommas(parseInt(stats.base[5])),
                  parseFloat(stats.base[6]).toFixed(2),
                    numberWithCommas(parseInt(stats.base[7])),
                    parseFloat(stats.base[8] * 100).toFixed(2) + '%',
                    '$' + parseFloat(stats.base[9]).toFixed(2)]

    var rowe2 = ['Experiment', row.campaignExperiment.trafficSplitPercent + '%', 
                 row.campaignExperiment.startDate, 
                 row.campaignExperiment.endDate,
                   numberWithCommas(parseInt(stats.exp[0])),
                  numberWithCommas(parseInt(stats.exp[1])),
                  parseFloat(stats.exp[2] * 100).toFixed(2) + '%',
                  '$' + parseFloat(stats.exp[3]).toFixed(2),
                  '$' + numberWithCommas(parseInt(stats.exp[4])),
                  '$' + numberWithCommas(parseInt(stats.exp[5])),
                  parseFloat(stats.exp[6]).toFixed(2),
                    numberWithCommas(parseInt(stats.exp[7])),
                    parseFloat(stats.exp[8] * 100).toFixed(2) + '%',
                    '$' + parseFloat(stats.exp[9]).toFixed(2)]
    
    
    
    var combinedEmail = [header1, header2, rowe1, rowe2, rowBreak, rowBreak]
    
    sheetRows = sheetRows.concat(combined)
    emailRows = emailRows.concat(combinedEmail)
    
    
    
    var status = row.campaignExperiment.status
    
    if(status == 'ENABLED' && row.campaignExperiment.endDate < today){
      status = 'FINISHED'
    }
    statuses.push(status)
    
    
    
  }
  
  return {rows:sheetRows,emailRows:emailRows, statuses:statuses}
}

function createHtml(exp){
  
    var tables = []
    for(var i in exp){
      var table = '<table style = "text-align:left; border-collapse: collapse;">{cells}</table>'
      var html_rows = []
      
      var rows = exp[i]
      
      for(var j in rows){
        
        var row = rows[j]
        var html_row = []
        
        for(var z in row){
          
          var cell =  '<td style = "padding:7px; border:1px solid black">' + row[z] + '</td>' 
          html_row.push(cell)
          
        }
        
        
          var html_row = '<tr>' + html_row.join('') + '</tr>'
          html_rows.push(html_row)
      }
      
      table = table.replace('{cells}', html_rows.join(''))
      tables.push(table)
    }
    var html = "<html><head></head><body>Here are the results of all currently active experiments:<br><br><br>{tables}</body></html>".replace('{tables}', tables.join('<br><br>'))
    

    return html
}


function main() {


  

  var data = getData()
  var rows = data.rows
  var emailRows = data.emailRows
  var statuses = data.statuses
  var ss = SpreadsheetApp.openByUrl(SS_URL)
  var tab = ss.getSheetByName('Campaign Experiments')
  var today = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MM/dd/YYYY')

  
  
  
  tab.clearContents()
  tab.getRange(1,1, rows.length, rows[0].length).setValues(rows)

  for(var i in statuses){
    var status = statuses[i]
    var rowNumber = parseInt(i) + 1 + (parseInt(i) * 5)
    var range = tab.getRange(rowNumber, 1, 1, 1)
    
    if(status == 'ENABLED'){
      range.setBackground('#90ee90')
    }
    else{
      range.setBackground('#d9d9d9')
    }
    
    
  }

  
  var activeExperiments = []
  
  
  for(var i in statuses){
    
    var status = statuses[i]
    
    if(status == 'ENABLED'){
      
      var counter = parseInt(i) + (parseInt(i) * 5)
      
     activeExperiments.push([emailRows[counter], emailRows[counter+1], emailRows[counter+2], emailRows[counter+3]])

      
    }
    
  }
  
 
  if(activeExperiments.length > 0){
    var html = createHtml(activeExperiments)
    var subject = today + ' Experiment Results'
    
    Logger.log('Sending emails')

    
    for(var x in EMAILS){
      MailApp.sendEmail({to:EMAILS[x],subject:subject,htmlBody:html})
    }
  }
  
  else{
    Logger.log('Done, nothing to send')
  }


  
  

}