Wednesday, January 16, 2013

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:

* 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
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')
  while(kwIter.hasNext()) { 
    var kw =;
    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); }


  1. Hi Russ,

    This is a great help thank you.

    Can we set set these Ad Params at ad group level or are they applied to keywords only?

    Thanks again.

  2. It should be pretty easy to modify the script above to update ad params based on ad group name. Follow these steps:

    1. First you need to update the spreadsheet. Currently we have keyword text and matchtype, but if you want to use adgroup instead, just update the column A header to be "adgroup", and keep column be the same. We will just ignore it later.

    2. Now, the logic is going to pull column A in the kw_text parameter. You can change the name if you like, but you don't have to. Now we need to update the Keyword Selector to find all the keywords in a given adgroup. So change the line:

    .withCondition("Text = '"+kw_text+"'")


    .withCondition("AdGroupName = '"+kw_text+"'")

    3. So now you are pulling all the keywords in a given adgroup. The last update would be to remove the check for matchtype. So delete the line:

    if(kw.getMatchType() == mt) {

    And then also the closing } two lines down.

    You should end up with something like this:

    var kw_iter = AdWordsApp.keywords()
    .withCondition("AdGroupName = '"+kw_text+"'")
    .withCondition("Status = ENABLED")
    while(kw_iter.hasNext()) {
    var kw =;
    kw.setAdParam(1, param1);
    kw.setAdParam(2, param2);

    Let me know if you have any issues.

    Thanks for checking out my blog,

    1. I have edited the script to edit at the ad group level as above and it seems to keep throwing up 'script runtime errors'

      Any idea why?

    2. Hi Ashley, Could you post your script here so that I could take a look at it?


    3. Russell, thanks for this great collection of AW Scripts.

      I tried your suggestion above to update prices for all keywords within an ad group and it works fine.

      However, I noticed that it only works when I use a copy of your sample spreadsheet. The moment I create my own, the script generates this error: TypeError: Cannot call method "getRange" of null. (line 11)

      All that's different is the Google Drive URL.

      Do you use any special formatting in the spreadsheet?


    4. Make sure that you name the sheet in your new spreadsheet KeywordParams. By default, it is named Sheet1. Let me know if that solves your issue.

      Thanks for reading,

    5. That was it! Thanks again for the help, Russ.

  3. Great stuff. Thanks!

    Could you help modify the script using regex so that it chooses ad groups containing the word/words in the spreadsheet?



  4. Hi Mads, I think all you need to do is modify the script using the comment above, but instead of saying:

    .withCondition("AdGroupName = '"+kw_text+"'")

    Change it to be

    .withCondition("AdGroupName CONTAINS_IGNORE_CASE '"+kw_text+"'")

    That will match any Ad Groups that contain the word from the spreadsheet. Let me know if that works for you. More information about the operators available for the .withCondition function can be found here:


  5. Does this update business data feed or is it stored elsewhere?

    When i try to use the script i dont get any errors however I don't see it updating the feed either so how can see if and whats updating?

    I am trying to update these ad params hourly but appears I cant do it with bulk upload with csv or spreadsheet and only works if i manually do it from the schedule window of that specific data feed.

    Is there a way to update the feed hourly? Even 2 hours would be fine, 6 hours is too long for what I am hoping to achieve.

  6. Everyone wants to be a 'Google AdWord professional' and who wouldn't? The Google advertising team has given any and every inspiring Internet marketer an open gateway to make money with AdWord advertising. Using AdWords can lead to much success as a marketer.

  7. This ensures the advertising income for the magazine and they are most often willing to cut a good deal. The advertising company will then go out and sell advertising space on these pages that they bought from the magazine.negative keyword ideas

  8. One of the most significant snippets of data among this information is the wellspring of referral. Free Google Ads Audit

  9. On the off chance that you recognize any that you know beyond a shadow of a doubt are phony, banner reviews on google

  10. However, considering the growing trend in Google +, it can be said that it would not be long before people would be in pace with its trend. Best Data Scraping Tools

  11. getting an error "cannot fetch more adwords entities from iterator: exceed limit of 50000)

  12. This article isn't tied in with Bing. This article is about how to rank on the ebb and flow lord of web indexes, Google. Lead generation website

  13. I love this. It is soo informative. Are you also searching for Swedish assignment writing help we are the best solution for you. We are best known for delivering the best services to students without having to break the bank

  14. This post is quite informative. I love reading your blogs. Quite helpful. Are you also searching for Academic content writing services we are the best solution for you. We are best known for delivering the best services to students without having to break the bank.

  15. I love your creativity. Are you also searching for nursing writing services? we are the best solution for you. We are best known for delivering nursing writing services to students without having to break the bank.

  16. Really very useful and Informative information are provided here. Thank you so much for writing keep up like this. [pii_email_e1aa8f4deb45ecd93b2a]

  17. This comment has been removed by the author.

  18. Really very useful and Informative information are provided here. Thank you so much for writing keep up like this.
    rory john gates