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 Google Ads. The product group hierarchy must be specified in Google Ads 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 Google Ads 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) 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 Google Ads Editor format
- AdGroup Status - status of the ad group. Can be "enabled" or "paused"
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 Google Ads 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 Google Ads.
- Create a new Google Ads 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
- Open the template spreadsheet.
- Make a copy of the spreadsheet and save it:
- In the spreadsheet, menu, select "File -> Make a copy".
- Enter the new spreadsheet name, click "OK".
- In Google Ads, navigate to Scripts.
- Create a new script and give it a name; authorize the script.
- Copy-paste source code into the script (see below).
- Replace
var SPREADSHEET_URL = "[YOUR_URL]";
withvar SPREADSHEET_URL = "https://docs.google.com/...";
Use the URL of the spreadsheet created in step 2. - Save the script, click "Close".
- If necessary, schedule the script.
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/google-ads/scripts/docs/solutions/bulk-shopping-ad-group-creator
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 1.0.2
*
* @changelog
* - version 1.0.2
* - Removed use of ProductAdBuilder.withPromotionLine as it is deprecated.
* - 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 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[AD_GROUP_STATUS_COLUMN] = true;
/** End of column header specification */
/**
* Reads campaign and bid data from spreadsheet and writes it to Google Ads.
*/
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.
*
* @param {!Array.<string>} headerRow A list of column header names.
* @return {!Object.<number>} A mapping from column name to column index.
* @throws If required column is missing.
*/
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.
*
* @param {!Array.<!Object>} row The spreadsheet row.
* @param {!Object.<number>} headers Mapping from column name to column index.
* @return {?Object} The row in object form, or null for a parsing error.
*/
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 Google Ads.
*
* @param {!Sheet} sheet The sheet to parse.
*/
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(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 Google Ads if it hasn't already been done.
*
* @param {!Object.<ShoppingCampaign>} campaigns A cache of campaigns.
* @param {string} campaignName The name of the campaign to fetch.
* @return {?ShoppingCampaign} The campaign, or null if not found.
*/
function fetchCampaignIfNecessary(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.
*
* @param {!Array.<Object>} row A spreadsheet row.
* @param {!ShoppingCampaign} campaign
* @param {string} adGroupName The ad group to build or fetch.
* @param {number} bid
* @return {?ShoppingAdGroup} The ad group of null if there is an error.
*/
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';
// 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';
}
}
adGroup = createAdGroup(adGroupName, status, bid, campaign);
if (adGroup) {
adGroup.rootProductGroup = adGroup.rootProductGroup();
adGroup.rootProductGroup.childMap = {};
}
}
return adGroup;
}
/**
* Builds product groups from row.
*
* @param {!Array.<Object>} row A spreadsheet row.
* @param {!ShoppingAdGroup} adGroup The ad group to operate on.
* @param {number} bid The product group bid.
*/
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;
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 Google Ads Editor product group format
* (e.g. * / Condition='New' / Custom label 2='furniture' /
* Product type='bar carts').
*
* @param {string} productGroupPath The product group path.
* @return {!Array.<!Array.<string>>} A list of product group component name/
* value pairs.
*/
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 Google Ads 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 Google Ads by name.
*
* @param {string} name The campaign name.
* @return {?ShoppingCampaign} The found campaign, or null if not found.
*/
function findCampaignByName(name) {
var campaignName = name.replace(/'/g, '\\\'');
var shoppingCampaignSelector = AdsApp
.shoppingCampaigns()
.withCondition('Name = \'' + campaignName + '\'');
var campaign = null;
var shoppingCampaignIterator = shoppingCampaignSelector.get();
if (shoppingCampaignIterator.hasNext()) {
campaign = shoppingCampaignIterator.next();
}
return campaign;
}
/**
* Fetches ad group from Google Ads given ad group name and campaign.
*
* @param {string} agName The name of the ad group.
* @param {!ShoppingCampaign} campaign The campaign within which to search.
* @return {?ShoppingAdGroup} The ad group or null if not found.
*/
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 Google Ads if it doesn't already exist, along with ad
* group ad.
*
* @param {string} name The name of the ad group.
* @param {string} status The desired status of the ad group.
* @param {number} defaultBid The max CPC for the ad group.
* @param {!ShoppingCampaign} campaign The campaign to create the ad group for.
* @return {?ShoppingAdGroup} The created ad group or null if there is an error.
*/
function createAdGroup(name, status, defaultBid, 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().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;
}