var SPREADSHEET_URL = '[PUT YOUR SPREADSHEET URL HERE]';

var spreadsheetAccess = new SpreadsheetAccess(SPREADSHEET_URL, 'Rules');

var totalColumns;

function main() {
  // Make sure the spreadsheet is using the account's timezone.
  spreadsheetAccess.spreadsheet.setSpreadsheetTimeZone(
      AdsApp.currentAccount().getTimeZone());
  spreadsheetAccess.spreadsheet.getRangeByName('account_id').setValue(
      AdsApp.currentAccount().getCustomerId());

  var columns = spreadsheetAccess.sheet.getRange(5, 2, 5, 100).getValues()[0];
  for (var i = 0; i < columns.length; i++) {
    if (columns[i].length == 0 || columns[i] == 'Results') {
      totalColumns = i;
      break;
    }
  }
  if (columns[totalColumns] != 'Results') {
    spreadsheetAccess.sheet.getRange(5, totalColumns + 2, 1, 1).
        setValue('Results');
  }
  // clear the results column
  spreadsheetAccess.sheet.getRange(6, totalColumns + 2, 1000, 1).clear();

  var row = spreadsheetAccess.nextRow();

  while (row != null) {
    var argument;
    var stopLimit;
    var isCostPerConversionColumn = false;
    var calcCostPerConversion = false;
    var costPerConversionMin = null;
    var costPerConversionMax = null;
    
    try {
      argument = parseArgument(row);
      stopLimit = parseStopLimit(row);
    } catch (ex) {
      logError(ex);
      row = spreadsheetAccess.nextRow();
      continue;
    }
    var selector = AdsApp.keywords();
    for (var i = 3; i < totalColumns; i++) {
      var header = columns[i];
      var value = row[i];
      if (header.indexOf("CostPerConversion") > -1) {
        isCostPerConversionColumn = true;
        if (header.indexOf(">") > -1) {
          costPerConversionMin = value;
        }
        if (header.indexOf("<") > -1) {
          costPerConversionMax = value;
        }
      }
      if (parseInt(costPerConversionMin) >= 0 || 
          parseInt(costPerConversionMax) >= 0){
        calcCostPerConversion = true;
      }
      
      if ((!isNaN(parseFloat(value)) || value.length > 0) 
          					&& !isCostPerConversionColumn ) {
        if (header.indexOf("'") > 0) {
          value = value.replace(/\'/g, "\\'");
        } else if (header.indexOf('\"') > 0) {
          value = value.replace(/"/g, '\\\"');
        }
        var condition = header.replace('?', value);
        selector.withCondition(condition);
      }
    }
    
    Logger.log(	  " calcCostPerConverion: "+calcCostPerConversion
                 +" costPerConversionMin: "+costPerConversionMin
                 +" costPerConversionMax: "+costPerConversionMax);
    
    selector.forDateRange(spreadsheetAccess.spreadsheet.
        getRangeByName('date_range').getValue());

    var keywords = selector.get();

    try {
      keywords.hasNext();
    } catch (ex) {
      logError(ex);
      row = spreadsheetAccess.nextRow();
      continue;
    }

    var fetched = 0;
    var changed = 0;

    while (keywords.hasNext()) {
      var keyword = keywords.next();
      if (calcCostPerConversion) {
        var stats = keyword.getStatsFor(spreadsheetAccess.spreadsheet.
          getRangeByName('date_range').getValue());
      	var cost = stats.getCost();
        var conversions = stats.getConversions();
        var costPerConversion = cost / conversions;
        Logger.log("keyword: "+keyword
                 +" cost: "+cost
                 +" conversions: "+conversions
                 +" costPerConversion: "+costPerConversion);
        
        
        if ( 
        	!(costPerConversion > costPerConversionMin &&
          	costPerConversion < costPerConversionMax)
        ) {
        	continue;
        }
      }
      var oldBid = keyword.bidding().getCpc();
      var action = row[0];
      var newBid;

      fetched++;
      if (action == 'Add') {
        newBid = addToBid(oldBid, argument, stopLimit);
      } else if (action == 'Multiply by') {
        newBid = multiplyBid(oldBid, argument, stopLimit);
      } else if (action == 'Set to First Page Cpc' ||
          action == 'Set to Top of Page Cpc') {
        var newBid = action == 'Set to First Page Cpc' ?
            keyword.getFirstPageCpc() : keyword.getTopOfPageCpc();
        var isPositive = newBid > oldBid;
        newBid = applyStopLimit(newBid, stopLimit, isPositive);
      }
      if (newBid < 0) {
        newBid = 0.01;
      }
      newBid = newBid.toFixed(2);
      if (newBid != oldBid) {
        changed++;
      }
      keyword.bidding().setCpc(newBid);
    }
    logResult('Fetched ' + fetched + '\nChanged ' + changed);

    row = spreadsheetAccess.nextRow();
  }

  spreadsheetAccess.spreadsheet.getRangeByName('last_execution')
      .setValue(new Date());
}

function addToBid(oldBid, argument, stopLimit) {
  return applyStopLimit(oldBid + argument, stopLimit, argument > 0);
}

function multiplyBid(oldBid, argument, stopLimit) {
  return applyStopLimit(oldBid * argument, stopLimit, argument > 1);
}

function applyStopLimit(newBid, stopLimit, isPositive) {
  if (stopLimit) {
    if (isPositive && newBid > stopLimit) {
      newBid = stopLimit;
    } else if (!isPositive && newBid < stopLimit) {
      newBid = stopLimit;
    }
  }
  return newBid;
}

function parseArgument(row) {
  if (row[1].length == 0 && (row[0] == 'Add' || row[0] == 'Multiply by')) {
    throw ('\"Argument\" must be specified.');
  }
  var argument = parseFloat(row[1]);
  if (isNaN(argument)) {
    throw 'Bad Argument: must be a number.';
  }
  return argument;
}
function parseStopLimit(row) {
  if (row[2].length == 0) {
    return null;
  }
  var limit = parseFloat(row[2]);
  if (isNaN(limit)) {
    throw 'Bad Argument: must be a number.';
  }
  return limit;
}
function logError(error) {
  spreadsheetAccess.sheet.getRange(spreadsheetAccess.currentRow(),
      totalColumns + 2, 1, 1)
  .setValue(error)
  .setFontColor('#c00')
  .setFontSize(8)
  .setFontWeight('bold');
}
function logResult(result) {
  spreadsheetAccess.sheet.getRange(spreadsheetAccess.currentRow(),
      totalColumns + 2, 1, 1)
  .setValue(result)
  .setFontColor('#444')
  .setFontSize(8)
  .setFontWeight('normal');
}

function SpreadsheetAccess(spreadsheetUrl, sheetName) {
  Logger.log('Using spreadsheet - %s.', spreadsheetUrl);
  this.spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl);

  this.sheet = this.spreadsheet.getSheetByName(sheetName);
  this.cells = this.sheet.getRange(6, 2, this.sheet.getMaxRows(),
      this.sheet.getMaxColumns()).getValues();
  this.rowIndex = 0;

  this.nextRow = function() {
    for (; this.rowIndex < this.cells.length; this.rowIndex++) {
      if (this.cells[this.rowIndex][0]) {
        return this.cells[this.rowIndex++];
      }
    }
    return null;
  };
  this.currentRow = function() {
    return this.rowIndex + 5;
  };
}

/**
 * Validates the provided spreadsheet URL
 * to make sure that it's set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);
}