var RECIPIENT_EMAIL = 'Email ID';

var SPREADSHEET_URL = spreadsheet.getUrl();

function main(){
  
 var results = runQuery();
 writeToSpreadsheet(results);
 Logger.log("done");

 validateEmail(RECIPIENT_EMAIL);
  
   Logger.log(
      'Search Query report available at\n' + SPREADSHEET_URL);
  if (RECIPIENT_EMAIL) {
    MailApp.sendEmail(
        RECIPIENT_EMAIL, 'search query Performance Report is ready',
        SPREADSHEET_URL);
  } 
}

//Helper function to get or create the spreadsheet 
function getSheet(tab) {
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet;
  try {
    sheet = s_sheet.getSheetByName(tab);
    if(!sheet) {
      sheet = s_sheet.insertSheet(tab, 0);
    }
  } catch(e) {
    sheet = s_sheet.insertSheet(tab, 0);
  }
  return sheet
}
 

function writeToSpreadsheet(rows)
{
  var tab = 'queryreport';
  var to_write = convertRowsToSpreadsheetRows(rows);
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = getSheet(tab);
  sheet.clear();
  
  var numRows = sheet.getMaxRows();
  if(numRows < to_write.length) {
    sheet.insertRows(2,(to_write.length-numRows)); 
  }
  var range = sheet.getRange(4,1,to_write.length,to_write[0].length);
  range.setValues(to_write);
    Logger.log("number of rows : %s", to_write.length);
  
  setFormatAndFormulas(sheet,to_write,tab);

}

function setFormatAndFormulas(sheet,to_write){
   Logger.log("was in the formatting section");
  sheet.setFrozenRows(3);
   var d=new Date();
    sheet.getRange(1, 1).setValue("Report Date:").setBackgroundRGB(255, 255,153);
  sheet.getRange(1, 2).setValue(d.toDateString()).setBackgroundRGB(100, 0, 255);
  sheet.getRange('E2:F2').mergeAcross().setValue("Search Query Report:").setFontWeight("bold").setWrap(true);
   sheet.getRange(2,7)
      .setValue(AdWordsApp.currentAccount().getCustomerId()).setFontWeight("bold");
    var headlinerange = sheet.getRange(4,1,1,to_write[0].length).setFontWeight("bold").setWrap(true);
    var table = sheet.getRange(4,1,to_write.length,to_write[0].length);
    table.setBorder(true, true, true, true, true, true, "blue", SpreadsheetApp.BorderStyle.SOLID);
	 table.setWrap(true);	 
   return sheet;
   
   }
function runQuery(){
   
// list of keywords to be excluded  
  var results = [];
  var excludeThem = ['repair', 'repairing','replace', 'replacement',
        'service', 'price', 'cost', 'broken', 'change', 
        'not working', 'damage','crack'];
 //var regex = new RegExp( '([A-Z])\w+/g' + excludeThem.join('\\b|\\b') + '([A-Z])\w+/g');
  //var regex = new RegExp("(^|[^a-zA-Z0-9])(" + excludeThem.join("|") + ")([^a-zA-Z0-9]|$)", "g");

 var reg = /repair|repairing|replace|replacement|service|price|cost|broken|change|not working|damage|crack/g  
  

  var queryReport = AdWordsApp.report(
   " SELECT Query,CampaignName,AdGroupName,KeywordId," +
   " Clicks, Impressions, Conversions, Cost, CostPerConversion" +                           
   " FROM SEARCH_QUERY_PERFORMANCE_REPORT "  +
   " DURING YESTERDAY");

  var rowNum = 0;
  var includedRows = 0;
  var excludedRows = 0;
  var queryRows = queryReport.rows();

  while (queryRows.hasNext()) {
  var queryRow = queryRows.next();
    rowNum++;
  var keywordText = queryRow["Query"];
  var testCamp = keywordText.toLowerCase();
  if(!!testCamp.match(reg))
      {
      excludedRows++;
      continue;
  }
 else{
    includedRows++;
    results.push(queryRow);
  }
 }
  Logger.log('total num of rows fetched by report =  %s',rowNum);
  Logger.log('total num of rows excluded =  %s',excludedRows);
  Logger.log('total num of rows included =  %s',includedRows);
  return results;
  
}


//This function will convert row data into a format easily pushed into a spreadsheet
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 getColumns( ) {
  var ret_array = [];
  
    Logger.log('Fetched the columns'); 
 
  return ret_array.concat(['Query',
                           'CampaignName',
                           'AdGroupName',
                           'KeywordId',                            
                           'Clicks', 
                           'Impressions',
                           'Conversions',
			   'Cost',
			   'CostPerConversion'			 
                          ]);
   }

function validateEmail(email) {
  if (email == 'PLEASE INSERT EMAIL ID HERE') {
    throw new Error('Please use a valid email address.');
  }
}