//will create a new spreadsheet with the name :  Campaign_Perf_Dump

var spreadsheet = SpreadsheetApp.create('Campaign_Perf_Dump');
var SPREADSHEET_URL = spreadsheet.getUrl();
    
function main() {
  var results = runQuery();
  writeToSpreadsheet(results);
  Logger.log("completed");
  //the spreadsheet url where the report can be found
  Logger.log('Please find report here :\n%s',SPREADSHEET_URL); 
  
}

function getSheet(){
   
  var s_sheet= SpreadsheetApp.openByUrl(SPREADSHEET_URL);
   s_sheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
  var sheet =s_sheet.getSheetByName('Sheet1');
  sheet.clearContents();
  return sheet;
}

function writeToSpreadsheet(rows){
  
  var to_write = convertRowsToSpreadsheetRows(rows);
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet =getSheet();
 // sheet.clear();
  
  var numRows = sheet.getMaxRows();
  if(numRows < to_write.length){
    
    sheet.insertRows(1,to_write.length-numRows);
  }
  var d = new Date();
  
   
  var range = sheet.getRange(6,1,to_write.length,to_write[0].length);
  
  
  range.setValues(to_write);

   var rangeToSort = sheet.getRange(7,1,to_write.length,to_write[0].length);
   sheet.getRange(7, 1, to_write.length, 1).setNumberFormat('DD/MM/YYYY');
  Logger.log("number of rows : %s", to_write.length);
  rangeToSort.sort([{column: 1, ascending: true}]);
  sheet.getRange(1, 5).setValue("Report Date:");
  sheet.getRange(1, 6).setValue(d.toDateString());
  sheet.getRange(3,5).setValue("Campaign Report:");
   sheet.getRange(3,6).setValue(" Last Month");
   
}
function runQuery() {
  var API_VERSION = {  };
  var cols = getColumns();
  var report = getReport();
  var date_range = getDateRange();
  var where = getWhereClause( );
  var query = ['select',cols.join(','),'from',report,where,'during',date_range].join(' ');
  var report_iter = AdWordsApp.report(query, API_VERSION).rows();
  var rows = [];
  while(report_iter.hasNext()) { 
    rows.push(report_iter.next());
  }
  return rows;
}
function convertRowsToSpreadsheetRows(rows) {
  var cols = getColumns( );
  var ret_val = [cols];
  for(var i in rows) {
    var r = rows[i];
    var ss_row = [];
    for(var x in cols) {
      ss_row.push(r[cols[x]]);
    }
    ret_val.push(ss_row);
  }
  return ret_val;
}

function getReport( ) {
  
    return 'CAMPAIGN_PERFORMANCE_REPORT';
 }

function getWhereClause( ) {
 
    return 'where Impressions >= 0 ';
 }
 

function getColumns( ) {
  var ret_array= [];
 
  ret_array.push("Date");
  
  return ret_array.concat(['CampaignName',
                           'Clicks',
                           'Impressions',
                           'Ctr',
                           'AverageCpc',
                           'Cost',
                           'AveragePosition',
                           'Conversions',
                           'ConversionRate',
                           'ConversionValue']);
}
 

function getDateRange() { 
  
        return 'LAST_MONTH';
  }