Bulk Shopping Ad Group Creator

Bulk Shopping Ad Group Creator provides a way to bulk create ad groups and product groups in existing Shopping Campaigns. It reads ad group settings (such as default bid and status) and product group hierarchies along with bids from a spreadsheet, and creates them in AdWords. The product group hierarchy must be specified in AdWords Editor format for Shopping Campaigns.

How it works

The script takes the input spreadsheet and processes it row-by-row. The campaign name(s) specified in the spreadsheet must already exist in AdWords or an error will be thrown. The following will happen if:

  • A specified ad group doesn't exist
    • Ad group is created with specified settings (default bid, status, promotion) along with its new product groups
  • A specified ad group already exists and does not have product groups (unlikely)
    • Ad group settings are unchanged, and the new product groups are created
  • A specified ad group already exists and has product groups
    • Ad group settings are unchanged, and its existing product groups are unchanged

The script will append the result of processing each row to the end of that row.

Note: Preview is not currently working with this script. It creates product groups and subdivides them further, but because the product groups are not actually created in preview mode, attempting to operate on them fails. The script will still work when run normally.

Spreadsheet specifications

The script requires the following column headers to be present in the spreadsheet:

  • Campaign - campaign name
  • Ad Group - ad group name
  • Max CPC - default ad group bid if in ad group row, product group bid if in row with product group
  • Product Group - product group hierarchy specification in AdWords Editor format
  • AdGroup Status - status of the ad group. Can be "enabled" or "paused"
  • Promotion - promotional text for the ad group

It will ignore all other columns. This means that only these settings will be copied, and other ad group settings will NOT be in the newly created ad group.

The ad group settings row must come before any product group rows for the same ad group.

If “everything else” product groups are explicitly specified in the spreadsheet, the row must come after all product groups of the same level in the same ad group. If they are not explicitly specified, they will be created with the ad group default bid.

Setup

  • Create a spreadsheet with the above column headers and fill out your ad group and product group details. It should look similar to this sample.
    • Alternatively, you can download an existing campaign with AdWords Editor export to .csv as a template, make modifications to the .csv, then upload it to Google Spreadsheets. If you do this, please make sure the ad groups in the spreadsheet are new (non-existent).
  • Be sure to check that the destination campaign(s) exists in AdWords.
  • Create a new AdWords script with the source code below.
  • Don't forget to update the following parameters in the script:
    • SPREADSHEET_URL - URL for the spreadsheet to process
    • SHEET_NAME - Name of the sheet with data to process

Source code

// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Bulk Shopping AdGroup Creator
 *
 * @overview The Bulk Shopping AdGroup Creator provides a way to bulk create ad
 *     groups and product groups in existing Shopping Campaigns. See
 *     https://developers.google.com/adwords/scripts/docs/solutions/bulk-shopping-ad-group-creator
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.0.1
 *
 * @changelog
 * - version 1.0.1
 *   - Added validation for external spreadsheet setup.
 * - version 1.0
 *   - Released initial version.
 */

/**
 * SPREADSHEET_URL: URL for spreadsheet to read
 * SHEET_NAME: Name of sheet in spreadsheet to read
 */
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
var SHEET_NAME = 'YOUR_SHEET_NAME';

/**
 * Column header specification
 * These are the actual text the script looks for in the spreadsheet header.
 */
var CAMPAIGN_COLUMN = 'Campaign';
var AD_GROUP_COLUMN = 'Ad Group';
var MAX_CPC_COLUMN = 'Max CPC';

var PRODUCT_GROUP_COLUMN = 'Product Group';
var PROMOTION_COLUMN = 'Promotion';
var AD_GROUP_STATUS_COLUMN = 'AdGroup Status';

var REQUIRED_COLUMN_NAMES = {};
REQUIRED_COLUMN_NAMES[CAMPAIGN_COLUMN] = true;
REQUIRED_COLUMN_NAMES[AD_GROUP_COLUMN] = true;
REQUIRED_COLUMN_NAMES[MAX_CPC_COLUMN] = true;
REQUIRED_COLUMN_NAMES[PRODUCT_GROUP_COLUMN] = true;
REQUIRED_COLUMN_NAMES[PROMOTION_COLUMN] = true;
REQUIRED_COLUMN_NAMES[AD_GROUP_STATUS_COLUMN] = true;
/** End of column header specification */

/**
 * Reads campaign and bid data from spreadsheet and writes it to AdWords.
 */
function main() {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  var sheet = validateAndGetSpreadsheet(SPREADSHEET_URL, SHEET_NAME);
  parseSheetAndConstructProductGroups(sheet);

  Logger.log('Parsed spreadsheet and completed shopping campaign ' +
      'construction.');
}

//Stores results of each row, along with formatting details
var resultsByRow = ['Result', 'Notes'];
var resultColors = ['Black', 'Black'];

/**
 * Validates header of sheet to make sure that header matches expected format.
 * Throws exception if problems are found. Saves the column number of each
 * expected column to global variable.
 */
function validateHeader(headerRow) {
  var result = {};

  var missingColumns = Object.keys(REQUIRED_COLUMN_NAMES);

  // Grab the column # for each expected input column.
  for (var columnIndex = 0; columnIndex < headerRow.length; columnIndex++) {
    var columnName = headerRow[columnIndex];
    if (columnName in REQUIRED_COLUMN_NAMES) {
      result[columnName] = columnIndex;
      var index = missingColumns.indexOf(columnName);
      if (index >= 0) {
        missingColumns.splice(index, 1);
      }
    }
  }

  if (missingColumns.length > 0) {
    throw 'Bid sheet data format doesn\'t match expected format. ' +
        'Missing columns: ' + missingColumns.join();
  }
  return result;
}

/**
 * Converts a spreadsheet row into map representation.
 */
function parseRow(row, headers) {
  var parsedRow = {};

  for (var header in headers) {
    var colNum = headers[header];
    var val = row[colNum].toString().trim();
    if (!val) {
      continue;
    }
    // AWEditor will add double quotes (") around string if it contains
    // commas or double quotes, so we need to strip those out.
    if (val.charAt(0) == '"' && val.charAt(val.length - 1) == '"') {
      val = val.substring(1, val.length - 1);
    }
    // AWEditor escapes double quotes (") with another double quote ("").
    val = val.replace(/""/g, '"');
    if (header == PRODUCT_GROUP_COLUMN) {
      var productGroups = [];
      var parsedProductGroups =
          parseAWEditorFormat(val);
      if (parsedProductGroups.error) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] = parsedProductGroups.error;
        resultColors[0] = 'Red';
        return null;
      }

      for (var x = 0; x < parsedProductGroups.length; x++) {
        // Product group type and value indices are level-1
        // (e.g. for L1, x=0).
        productGroups[x] = {
          type: parsedProductGroups[x][0],
          value: parsedProductGroups[x][1]
        };
      }
      parsedRow[header] = productGroups;
    } else {
      parsedRow[header] = val;
    }
  }

  // Ignore rows that don't have any useful information.
  var testRow = [];
  for (var k in parsedRow) {
    // Remove campaign, ad group columns and test if the rest is empty.
    if (k == CAMPAIGN_COLUMN || k == AD_GROUP_COLUMN) {
      continue;
    }
    testRow.push(parsedRow[k]);
  }
  if (testRow.toString().replace(/[,]+/g, '') == '') {
    resultsByRow[0] = 'SKIPPED';
    resultsByRow[1] = 'Superfluous row';
    return null;
  }
  return parsedRow;
}


/**
 * Parses spreadsheet and constructs ad groups and product groups in AdWords.
 */
function parseSheetAndConstructProductGroups(sheet) {
  var headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  var headers = validateHeader(headerRow);

  var values = sheet.getRange(2, 1, sheet.getLastRow() - 1,
      sheet.getLastColumn()).getValues();

  var campaigns = {};

  var outputColumn = sheet.getLastColumn() + 1;
  var resultHeaderRange = sheet.getRange(1, outputColumn, 1, 2);
  resultHeaderRange.setValues([resultsByRow]);
  resultHeaderRange.setFontColors([resultColors]);
  resultHeaderRange.setFontWeights([['Bold', 'Bold']]);

  // Iterate through rows.
  for (var r = 0; r < values.length; r++) {
    resultsByRow = ['', ''];
    resultColors = ['Black', 'Black'];
    var row = values[r];

    var parsedRow = parseRow(row, headers);

    if (parsedRow) {
      var bid = parsedRow[MAX_CPC_COLUMN];
      if (bid) {
        // For European locale, decimal points are commas.
        bid = bid.toString().toLowerCase().replace(/,/g, '.');
        if (bid != 'excluded' && (isNaN(parseFloat(bid)) || !isFinite(bid))) {
          resultsByRow[0] = 'ERROR';
          resultsByRow[1] = 'Invalid bid';
        }
      }
      var campaignName = parsedRow[CAMPAIGN_COLUMN];
      campaigns[campaignName] =
          fetchCampaignIfNecessary(parsedRow, campaigns, campaignName);
      if (campaigns[campaignName]) {
        var adGroupName = parsedRow[AD_GROUP_COLUMN];
        campaigns[campaignName].createdAdGroups[adGroupName] =
            buildAdGroupIfNecessary(parsedRow, campaigns[campaignName],
                adGroupName, bid);
        if (campaigns[campaignName].createdAdGroups[adGroupName]) {
          if (!campaigns[campaignName].createdAdGroups[adGroupName].skip) {
            buildProductGroups(parsedRow,
                campaigns[campaignName].createdAdGroups[adGroupName], bid);
          } else {
            resultsByRow[0] = 'SKIPPED';
            resultsByRow[1] =
                'Ad group already exists with product groups';
          }
        }
      }

      if (!resultsByRow[0]) {
        resultsByRow[0] = 'SUCCESS';
      }
    }
    switch (resultsByRow[0]) {
      case 'ERROR':
        resultColors[0] = 'Red';
        break;
      case 'SUCCESS':
        resultColors[0] = 'Green';
        break;
      case 'WARNING':
        resultColors[0] = 'Yellow';
    }
    var resultRange = sheet.getRange(r + 2, outputColumn, 1, 2);
    resultRange.setValues([resultsByRow]);
    resultRange.setFontColors([resultColors]);
    resultRange.setFontWeights([['Bold', 'Normal']]);
  }
}


/**
 * Fetches campaign from AdWords if it hasn't already been done.
 */
function fetchCampaignIfNecessary(row, campaigns, campaignName) {
  //Find the campaign
  if (!campaignName) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Missing campaign name';
    return null;
  }
  var campaign = campaigns[campaignName];
  if (!campaign) {
    campaign = findCampaignByName(campaignName);
    campaign.createdAdGroups = {};
    if (!campaign) {
      resultsByRow[0] = 'ERROR';
      resultsByRow[1] = 'Could not find campaign';
    }
  }
  return campaign;
}

/**
 * Builds ad group if necessary, otherwise returns existing ad group.
 */
function buildAdGroupIfNecessary(row, campaign, adGroupName, bid) {
  if (!adGroupName) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Missing ad group name';
    return null;
  }
  // See if we already fetched/created the ad group.
  var adGroup = campaign.createdAdGroups[adGroupName];
  if (!adGroup) {
    // Only use the bid on this line for the ad group default bid if there are
    // no product groups specified for it. Ad group default bid must be
    // specified.
    if (row[PRODUCT_GROUP_COLUMN]) {
      resultsByRow[0] = 'ERROR';
      resultsByRow[1] = 'Ad Group is missing a default bid.';
      return null;
    }
    // Set default status to enabled.
    var status = 'ENABLED';
    var promotion;
    // If ad group status is specified, make sure it's "active", "enabled", or
    // "paused", and set status. Ad group status must be set on the first row
    // that the ad group appears in.
    if (row[AD_GROUP_STATUS_COLUMN]) {
      status = row[AD_GROUP_STATUS_COLUMN].toUpperCase();
      if (status == 'ACTIVE') {
        status = 'ENABLED';
      }
    }
    // Ad group promotional text must be set on the first row that the ad group
    // appears in.
    if (row[PROMOTION_COLUMN]) {
      promotion = row[PROMOTION_COLUMN];
    }
    adGroup = createAdGroup(adGroupName, status, bid, promotion, campaign);
    if (adGroup) {
      adGroup.rootProductGroup = adGroup.rootProductGroup();
      adGroup.rootProductGroup.childMap = {};
    }
  }
  return adGroup;
}

/**
 * Builds product groups from row.
 */
function buildProductGroups(row, adGroup, bid) {
  if (!row[PRODUCT_GROUP_COLUMN]) {
    return;
  }
  // Iterate through product groups in row.
  var maxLevel = -1;
  var productGroupsToAdd = row[PRODUCT_GROUP_COLUMN];
  var productGroup = adGroup.rootProductGroup;
  var newProductGroups = [];
  for (var i = 0; i < productGroupsToAdd.length; i++) {
    var type =
        productGroupsToAdd[i].type.toString().toLowerCase()
        .replace(/[ ]+/g, '');
    var val = productGroupsToAdd[i].value.toString().trim();
    if (type) {
      //For each Group level n, row must contain values for 1...n-1
      if (i - maxLevel > 1) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] = 'Every level of the product ' +
            'group type must have all higher ' +
            'level values. L' + i + ' is filled but missing L' +
            (maxLevel + 1);
        return;
      }
      maxLevel = i;

      // Each row must have matching # of bidding attribute type and value.
      if (!val) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] =
            'Every product group type must have an associated value. L' +
            i + ' has a type but no value';
        return;
      }

      // Build product groups.
      if (!productGroup.childMap[val.toLowerCase()]) {
        if (val == '*') {
          if (Object.keys(productGroup.childMap).length == 0) {
            resultsByRow[0] = 'ERROR';
            resultsByRow[1] =
                '"Everything else" product group must come after all others';
            return;
          } else {
            var child = productGroup.childMap[val];
            if (!child) {
              var children = productGroup.children().get();
              while (children.hasNext()) {
                child = children.next();
                if (child.isOtherCase()) {
                  break;
                }
              }
              child.childMap = {};
            }
            productGroup.childMap[val] = child;
            productGroup = child;
            productGroup.setMaxCpc(adGroup.getMaxCpc());
            //Only assign the bid to the lowest level product group
            //on that row
            if (i + 1 == productGroupsToAdd.length) {
              if (bid != 'excluded') {
                productGroup.setMaxCpc(bid);
              } else {
                productGroup.exclude();
              }
            }
          }
        } else {
          var productGroupBuilder = productGroup.newChild();
          // Verify that bidding attribute type is valid, construct
          // productGroupBuilder.
          switch (type) {
            case 'producttype':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.productTypeBuilder()
                                                       .withValue(val);
              break;
            case 'brand':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.brandBuilder()
                                                       .withName(val);
              break;
            case 'category':
              productGroupBuilder = productGroupBuilder.categoryBuilder()
                                                       .withName(val);
              break;
            case 'condition':
              val = val.toUpperCase();
              productGroupBuilder = productGroupBuilder.conditionBuilder()
                                                       .withCondition(val);
              break;
            case 'itemid':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.itemIdBuilder()
                                                       .withValue(val);
              break;
            default:
              if (type.match(/^custom((\\s)?(label|attribute))?/)) {
                val = val.toLowerCase();
                //make sure there's a number at the end that's between 0-4
                if (type.match(/[0-4]$/)) {
                  productGroupBuilder =
                      productGroupBuilder.customLabelBuilder()
                                         .withType('CUSTOM_LABEL_' +
                                            type.substring(type.length - 1))
                                         .withValue(val);
                } else {
                  resultsByRow[0] = 'ERROR';
                  resultsByRow[1] =
                      'Invalid custom attribute type: ' +
                      productGroupsToAdd[i].type;
                  return;
                }
              } else {
                resultsByRow[0] = 'ERROR';
                resultsByRow[1] =
                    'Invalid bidding attribute type: ' +
                    productGroupsToAdd[i].type;
                return;
              }
          }

          var productGroupOp = productGroupBuilder.build();

          if (!productGroupOp.isSuccessful()) {
            resultsByRow[0] = 'ERROR';
            resultsByRow[1] = 'Error creating product group ' +
                'level ' + (i + 1) + ': ' + productGroupOp.getErrors();
            return;
          }

          var result = productGroupOp.getResult();
          // Only assign the bid to the lowest level product group on that row.
          if (i + 1 == productGroupsToAdd.length) {
            if (bid == 'excluded') {
              result.exclude();
            } else if (bid) {
              result.setMaxCpc(bid);
            }
          }

          result.childMap = {};
          productGroup.childMap[val.toLowerCase()] = result;

          // Set current product group to the newly created product group.
          productGroup = result;
        }
      } else {
        // Set current product group to the last read product group.
        productGroup = productGroup.childMap[val.toLowerCase()];
      }
    }
  }
}

/**
 * Parses AdWords Editor product group format
 * (e.g. * / Condition='New' / Custom label 2='furniture' /
 *   Product type='bar carts').
 */
function parseAWEditorFormat(productGroupPath) {
  // Ignore * / case which is the root product group.
  if (productGroupPath == '* /' || !productGroupPath) {
    return [];
  }

  var regexVals = productGroupPath.match(new RegExp(/'(.*?)'/g));

  if (regexVals) {
    for (var i = 0; i < regexVals.length; i++) {
      productGroupPath = productGroupPath.replace(regexVals[i], '$' + i);
    }
  }

  var result = [];
  var productGroup = productGroupPath.split('/');

  // AW Editor format starts with '* /' so we ignore first one.
  for (var x = 1; x < productGroup.length; x++) {
    if (!productGroup[x]) {
      continue;
    }
    // AW Editor format looks like: Brand='nike'.
    var pair = productGroup[x].trim().split('=');
    if (pair.length != 2) {
      return {error: 'Product group string malformed. Should have 1 "=", ' +
            'but has ' + (pair.length - 1)};
    }
    var val = pair[1];
    if (val.charAt(0) != '$' && val.charAt(0) != '*') {
      return {error: 'Product group string malformed. Please ensure you are ' +
            'using AdWords Editor format'};
    }
    // '*' value doesn't have single quotes around it.
    if (val != '*') {
      var values = pair[1].split('$');
      // Skip 0 because it's always blank. String always starts with $.
      for (var i = 1; i < values.length; i++) {
        val = val.replace('$' + values[i], regexVals[values[i]]);
      }
      val = val.substring(1, val.length - 1).replace(/''/g, '\'');
    }

    result.push([pair[0], val]);
  }
  return result;
}

/**
 * Fetches campaign from AdWords by name.
 */
function findCampaignByName(name) {
  var campaignName = name.replace(/'/g, '\\\'');
  var shoppingCampaignSelector = AdWordsApp
        .shoppingCampaigns()
        .withCondition('Name = \'' + campaignName + '\'');

  var campaign = null;

  var shoppingCampaignIterator = shoppingCampaignSelector.get();
  if (shoppingCampaignIterator.hasNext()) {
    campaign = shoppingCampaignIterator.next();
  }

  return campaign;
}

/**
 * Fetches ad group from AdWords given ad group name and campaign ID.
 */
function findAdGroupByName(agName, campaign) {
  var adGroupName = agName.replace(/'/g, '\\\'');
  var adGroupSelector = campaign
        .adGroups()
        .withCondition('Name = \'' + adGroupName + '\'');

  var adGroup = null;

  var adGroupIterator = adGroupSelector.get();
  if (adGroupIterator.hasNext()) {
    adGroup = adGroupIterator.next();
  }

  return adGroup;
}

/**
 * Creates ad group in AdWords if it doesn't already exist, along with ad group
 * ad.
 */
function createAdGroup(name, status, defaultBid, promotion, campaign) {
  // See if ad group exists. If so, fetch it.
  var adGroup = findAdGroupByName(name, campaign);
  if (adGroup != null) {
    if (adGroup.rootProductGroup()) {
      // If root product group exists and not delete, then skip ad group.
      adGroup.skip = true;
    } else {
      adGroup.createRootProductGroup();
    }
    return adGroup;
  }

  // Build ad group.
  var adGroupOp = campaign.newAdGroupBuilder()
        .withName(name)
        .withStatus(status)
        .withMaxCpc(defaultBid)
        .build();

  // Check for errors.
  if (!adGroupOp.isSuccessful()) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Error creating ad group: ' +
        adGroupOp.getErrors();
    return null;
  }

  var adGroupResult = adGroupOp.getResult();

  adGroupResult.createRootProductGroup();

  Logger.log('Successfully created ad group [' + adGroupResult.getName() + ']');

  // Build ad group ad.
  var adGroupAdOp = adGroupResult.newAdBuilder()
                       .withPromotionLine(promotion)
                       .build();

  // Check for errors.
  if (!adGroupAdOp.isSuccessful()) {
    resultsByRow[0] = 'WARNING';
    resultsByRow[1] = 'Error creating ad group ad: ' +
        adGroupAdOp.getErrors();
  }

  return adGroupResult;
}

/**
 * DO NOT EDIT ANYTHING BELOW THIS LINE.
 * Please modify your spreadsheet URL and email addresses at the top of the file
 * only.
 */

/**
 * Validates the provided spreadsheet URL and email address
 * to make sure that they're set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @param {string} sheetname The name of the sheet within the spreadsheet that
 *     should be fetched.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL or email hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl, sheetname) {
  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.');
  }
  if (sheetname == 'YOUR_SHEET_NAME') {
    throw new Error('Please specify the name of the sheet you want to use on' +
        ' your spreadsheet.');
  }
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
  var sheet = spreadsheet.getSheetByName(sheetname);
  return sheet;
}

Send feedback about...

AdWords Scripts
AdWords Scripts
Need help? Visit our support page.