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); }