Set AdParams at the AdGroup Level from a Google Spreadsheet

I've had a few people ask me how to update my previous script about Setting AdParams at the Keyword Level to be able to set them at the AdGroup level. Here is an update to that script that does just that.

To use it, you will need to create a new Google Spreadsheet and load it with the adgroup name, param 1 value, and param 2 value in columns A, B, and C respectively (with column headers). Then copy that url into the script and you should be good to go.

Thanks,
Russ

/************************************************
* Update Ad Params by Ad Groups
* 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 = "PUT YOUR SPREADSHEET URL HERE";
var SET_PARAM1 = true;
var SET_PARAM2 = false;
var DATA_RANGE = 'A:D'; // A - CampaignName, B - AdGroupName, 
                        // C - Param1, D - 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]].join('~~!~~');
    allDataHash[rowKey] = { param1 : row[2], param2: row[3] };
  }
  
  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].join('~~!~~');
    if(allDataHash[rowKey]) {
      if(SET_PARAM1) { kw.setAdParam(1, allDataHash[rowKey].param1); }
      if(SET_PARAM2) { kw.setAdParam(2, allDataHash[rowKey].param2); }
    }
  }
}

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