Update Ad Params from Google Spreadsheet

2013-07-11 - NOTE: For those wanting to update AdParams at an AdGroup level, check out an updated version of this script.

Today we have a little script to help adjust keyword ad params at a large scale.  This is an example of how you can use the integration with Google Spreadsheets to update param1 and param2 values for keywords.  I have provided a sample spreadsheet which you can make a copy of.

As long as the Adwords Account and the Google Docs account use the same login, it will be able to access any spreadsheet you have.  I suggest opening my sample spreadsheet and then making a copy if it in your account.  You can then replace the URL from the script below with the url of your copy of the spreadsheet.

Link to my sample spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Aotb6eheEOpodC1yVjUwc2Y1NDIzUVFLLThJdTFPUnc#gid=0

Thanks,
Russ

/************************************************
* Update Ad Params Using a Google Spreadsheet
* Version 1.1
* ChangeLog v1.1
*  - Added the ability to enable param1 or 2 individually
*  - Looks for Keywords on all sheets
*  - Runs much faster
* Created By: Russ Savage
* FreeAdWordsScripts.com
************************************************/
var SPREADSHEET_URL = "THE URL FOR YOUR GOOGLE SPREADSHEET GOES HERE";
var SET_PARAM1 = true;
var SET_PARAM2 = false;
var DATA_RANGE = 'A:E'; // A - CampaignName, B - AdGroupName, 
                        // C - Keyword, D - Param1, E - Param2

function main() {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var allSheets = spreadsheet.getSheets();
  var allData = [];
  for(var i in allSheets) {
    var sheet = allSheets[i];
    var data = sheet.getRange(DATA_RANGE).getValues();
    data.shift(); //get rid of headers
    allData = allData.concat(data);
  }
  
  var allDataHash = {};
  for(var i in allData) {
    var row = allData[i];
    if(row[0] === '') { continue; }
    var rowKey = [row[0],row[1],row[2]].join('~~!~~');
    allDataHash[rowKey] = { param1 : row[3], param2: row[4] };
  }
  
  var kwIter = AdWordsApp.keywords()
    .withCondition('CampaignStatus = ENABLED')
    .withCondition('AdGroupStatus = ENABLED')
    .withCondition('Status = ENABLED')
    .get();
  
  while(kwIter.hasNext()) { 
    var kw = kwIter.next();
    var campName = kw.getCampaign().getName();
    var adGroupName = kw.getAdGroup().getName();
    var rowKey = [campName,adGroupName,kw.getText()].join('~~!~~');
    if(allDataHash[rowKey]) {
      if(SET_PARAM1) { kw.setAdParam(1, allDataHash[rowKey].param1); }
      if(SET_PARAM2) { kw.setAdParam(2, allDataHash[rowKey].param2); }
    }
  }
}