/***************************************************
* Weekly QS Tracker
* Version 1.0
* Created By: Pat Bennett
* PBennett.com
****************************************************/

/***************************************************
* EDITABLE: This is the name of the sheet that will 
* be created in your Google Drive account
****************************************************/
var SHEET_NAME = "My Weekly QS Tracker";

/***************************************************
* EDITABLE: The date rnage of the report you are
* pulling. Possible values here: http://goo.gl/8Q41VG
****************************************************/
var DATE_RANGE = 'LAST_7_DAYS';

/***************************************************
* EDITABLE: Add all the colunns you want to
* pull data from. These are found in the report type
* definition here: http://goo.gl/eEmO0b
****************************************************/
var COLUMNS = ['CampaignName',
                 'AdGroupName',
                 'Id',
                 'KeywordText', 
                 'KeywordMatchType', 
                 'QualityScore'
                ];

/***************************************************
* DO NOT MODIFY BELOW THIS LINE UNLESS YOU KNOW WHAT
* YOU ARE DOING. MAGIC STARTS HERE
****************************************************/
var SHEET_TEMPLATE = "https://docs.google.com/spreadsheets/d/1ecPr82CSzQqoxU8K2-HdRfDkvcB6jlhOiEukyzOFBUo/edit";

function main() {
  //does this sheet already exist? if so, grab the first one
  var files = DriveApp.getFilesByName(SHEET_NAME);
  while (files.hasNext()) { 
    var file = files.next();
    var url = file.getUrl();
    break;
  }
  
  // Create the new spreadsheet if it's not already created
  if(!url) {
    var ss = SpreadsheetApp.openByUrl(SHEET_TEMPLATE);
    var newSpreadsheet = ss.copy(SHEET_NAME);  
    var url = newSpreadsheet.getUrl();
  }
  // Put the URL in the log
  Logger.log("Your KW Report Is Ready: " + url);  
  //var url = SHEET_TEMPLATE; 
  var sheet = SpreadsheetApp.openByUrl(url);
  
  //loop through campaigns and save
  var report = AdWordsApp.report(
    'SELECT CampaignName, CampaignId ' +
    'FROM  CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE Status != "REMOVED" ' +
    'AND AdNetworkType1 = "SEARCH" '+
    'DURING '+DATE_RANGE);  
  var rows = report.rows();
  while(rows.hasNext()) {
    var data = rows.next();
    saveQSforCampaign(data,sheet);
  }
 
}

/**
* Saves the QS data by campaign
*/
function  saveQSforCampaign(data,sheet){
  var campaignName = data.CampaignName;
  var campaignId = data.CampaignId;

  var templateSheet = sheet.getSheetByName('TEMPLATE');
  var sheetCheck = sheet.getSheetByName(campaignName);
  if(sheetCheck != null) {
      var csheet = sheetCheck;
    } else {
      var csheet = sheet.insertSheet(campaignName, {template: templateSheet});      
  }
  
  //Grab the Keyword data and export
  // Format the selected columns for AWQL
  var columnsStr = COLUMNS.join(',') + " ";
  
  // Pull the report with the options defined for all non-deleted campaigns
  var report = AdWordsApp.report(
    'SELECT ' + columnsStr +
    'FROM  KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE CampaignId = ' + campaignId + ' ' +
    'AND Status != "REMOVED" ' +
    'AND QualityScore > 0 ' +
    'DURING ' + DATE_RANGE);
  
  // Export the data to the spreadsheet created
  // TO DO: need to change this to iterate through each one and add a column for the date range
  //report.exportToSheet(csheet);
  var rows = report.rows();

  //Figure out which column we need to add QS to
  var qsCol = findEmptyCol(csheet,1,1);
  var check = qsCol - 1;
  var cDate = csheet.getRange(1,check).getValue();
  
  if(cDate != getCurrentDate()) {
    csheet.getRange(1,qsCol).setValue(getCurrentDate());
  } else {
    qsCol = check;
  }
  
  while(rows.hasNext()) {
    var data = rows.next();
    //find the adgroup
    var matchRow = findMatch(csheet, data.Id, "E1:E1000");
    csheet.getRange(matchRow, 1).setValue(data.AdGroupName);
    csheet.getRange(matchRow, 2).setValue(data.KeywordText);
    csheet.getRange(matchRow, 3).setValue(data.KeywordMatchType);
    csheet.getRange(matchRow, 4).setValue(data.Id);
    csheet.getRange(matchRow, qsCol).setValue(data.QualityScore);
    //Logger.log("Saved " + data.campaignName +" at" + matchRow);
  }
}

/***************************************************
* HELPER FUNCTIONS
****************************************************/
/**
 * Finds the row where a value is matched, if none found it figures out the next emty row
 * @param {sheet} sheet object
 * @param {needle} sring the term we are looking for
 * @param {range} string the range to look in within the spreadsheet
 * @return {matchedRow} string of the row index we should use
 */
function findMatch(sheet, needle, range) {
  var rows = sheet.getRange(range).getValues();
  for (var i = 0; i< rows.length; i++) {
    if(rows[i][0] == needle) {
      var matchRow = i+1;
      //Logger.log("Match found at row " + matchRow);
      break;
    }
  }
  
  if (typeof matchRow === 'undefined') {
    //Logger.log("Match Not Found");
    //get next empty row
    var nextRow = findEmptyRow(sheet,1,2);
    //Logger.log("Adding match at row " + nextRow);
    var matchRow = nextRow;
  }
  return matchRow;
}

/**
 * Finds the next empty row in a spreadsheet
 * @param {sheet} sheet object
 * @param {minRow} string the min row we are starting with
 * @param {column} the column we are basing our decision off of
 * @return {rowindex} the row index that's empty
 */
findEmptyRow = function(sheet, minRow, column) {
  var values = sheet.getRange(minRow, column, sheet.getMaxRows(), 1).getValues();
  for (var i = 0; i < values.length; i ++) {
    if (!values[i][0]) {
      return i + minRow;
    }
  }
  //add a row in case we are at the end
  //Logger.log("MAX: " + sheet.getMaxRows());
  sheet.insertRowsAfter(sheet.getMaxRows(),1);
  return sheet.getMaxRows();
}

/**
 * Finds the next empty col in a spreadsheet
 * @param {sheet} sheet object
 * @param {row} the row we are basing the decision off of
 * @param {minColumn} the min column we will start our search off of
 * @return {colindex} the col index that's empty
 */
findEmptyCol = function(sheet, row, minColumn) {
  var values = sheet.getRange(row, minColumn, 1, sheet.getMaxColumns()).getValues();
  for (var i = 0; i < sheet.getMaxColumns(); i ++) {
    if (!values[0][i]) {
      return i + minColumn;
    }
  }
  //add a col in case we are at the end
  //Logger.log("MAX: " + sheet.getMaxColumns());
  //sheet.insertColumnAfter(sheet.getMaxColumns());
  //return sheet.getMaxColumns();
}

/**
 * Timestamp to help log the date in a good format
 */
function getCurrentDate() {
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "MM/dd/yy"); 
  return formattedDate;
}