「大量購物廣告群組建立者」功能可讓您在現有的購物廣告活動中,大量建立廣告群組和產品群組。這個表格會讀取廣告群組設定 (例如預設出價和狀態) 和產品群組階層,以及試算表中的出價,然後在 Google Ads 中建立這些層級。您必須使用 Google Ads 編輯器格式為購物廣告活動指定產品群組階層。
運作方式
這個指令碼會接收輸入試算表,並逐列處理。試算表中指定的廣告活動名稱必須已存在 Google Ads 中,否則系統會擲回錯誤。
指令碼必須遵循以下規則:
- 指定的廣告群組不存在
- 建立廣告群組時,會採用指定設定 (預設出價、狀態),以及新的產品群組。
- 指定的廣告群組已存在,且沒有產品群組 (不太可能)
- 新的廣告群組設定維持不變,系統還會建立新的產品群組。
- 指定的廣告群組已存在,且有產品群組
- 廣告群組設定維持不變,現有的產品群組則維持不變。
指令碼會將結果附加到處理中資料列的結尾。
試算表規格
指令碼需要試算表包含下列欄標題:
- Campaign (廣告活動):廣告活動名稱
- 廣告群組:廣告群組名稱
- 最高單次點擊出價:預設廣告群組出價 (如果在「ad group」列中,假如產品群組出價包含產品群組,則產品群組出價)
- 產品群組:Google Ads 編輯器格式的產品群組階層規格
- 廣告群組狀態:廣告群組的狀態,可以是
enabled
或paused
。
指令碼會忽略所有其他資料欄。也就是說,只有這些設定會複製到新建的廣告群組。
廣告群組設定列必須放在同一個廣告群組的任何產品群組列之前。
如果在試算表中明確指定 everything else
產品群組,該列必須接在相同廣告群組中所有相同層級的所有產品群組之後。如果沒有明確指定,系統就會使用廣告群組預設出價建立這些出價。
設定
- 使用上述欄標題建立試算表,並填寫廣告群組和產品群組詳細資料。看起來應與這個範例類似。
- 或者,您也可以透過 Google Ads 編輯器下載現有廣告活動做為範本,並匯出為 .csv 格式,然後修改 .csv,再上傳至 Google 試算表。如果是這樣,請確認試算表中的廣告群組是新的 (不存在)。
- 檢查 Google Ads 中是否有目標廣告活動。
- 使用下方的原始碼建立新的 Google Ads 指令碼。
- 別忘了更新指令碼中的下列參數:
SPREADSHEET_URL
:要處理的試算表網址SHEET_NAME
:含有待處理資料的工作表名稱
原始碼
/// 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 2.0
*
* @changelog
* - version 2.0
* - Updated to use new Google Ads scripts features.
* - 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
*/
const SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
const SHEET_NAME = 'YOUR_SHEET_NAME';
/**
* Column header specification
* These are the actual text the script looks for in the spreadsheet header.
*/
const CAMPAIGN_COLUMN = 'Campaign';
const AD_GROUP_COLUMN = 'Ad Group';
const MAX_CPC_COLUMN = 'Max CPC';
const PRODUCT_GROUP_COLUMN = 'Product Group';
const AD_GROUP_STATUS_COLUMN = 'AdGroup Status';
const 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() {
console.log(`Using spreadsheet - ${SPREADSHEET_URL}.`);
const sheet = validateAndGetSpreadsheet(SPREADSHEET_URL, SHEET_NAME);
parseSheetAndConstructProductGroups(sheet);
console.log('Parsed spreadsheet and completed shopping campaign ' +
'construction.');
}
//Stores results of each row, along with formatting details
let resultsByRow = ['Result', 'Notes'];
let 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) {
const result = {};
const missingColumns = Object.keys(REQUIRED_COLUMN_NAMES);
// Grab the column # for each expected input column.
for (let columnIndex = 0; columnIndex < headerRow.length; columnIndex++) {
const columnName = headerRow[columnIndex];
if (columnName in REQUIRED_COLUMN_NAMES) {
result[columnName] = columnIndex;
const 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) {
const parsedRow = {};
for (const header in headers) {
const colNum = headers[header];
let val = row[colNum].toString().trim();
if (!val) {
continue;
}
// Google Ads Editor 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);
}
// Google Ads Editor escapes double quotes (") with another double quote ("").
val = val.replace(/""/g, '"');
if (header === PRODUCT_GROUP_COLUMN) {
const productGroups = [];
const parsedProductGroups =
parseEditorFormat(val);
if (parsedProductGroups.error) {
resultsByRow[0] = 'ERROR';
resultsByRow[1] = parsedProductGroups.error;
resultColors[0] = 'Red';
return null;
}
for (let 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.
const testRow = [];
for (const 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) {
const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn())
.getValues()[0];
const headers = validateHeader(headerRow);
const values = sheet.getRange(2, 1, sheet.getLastRow() - 1,
sheet.getLastColumn()).getValues();
const campaigns = {};
const outputColumn = sheet.getLastColumn() + 1;
const resultHeaderRange = sheet.getRange(1, outputColumn, 1, 2);
resultHeaderRange.setValues([resultsByRow]);
resultHeaderRange.setFontColors([resultColors]);
resultHeaderRange.setFontWeights([['Bold', 'Bold']]);
// Iterate through rows.
for (let r = 0; r < values.length; r++) {
resultsByRow = ['', ''];
resultColors = ['Black', 'Black'];
const row = values[r];
const parsedRow = parseRow(row, headers);
if (parsedRow) {
let bid = parsedRow[MAX_CPC_COLUMN];
if (bid) {
// For European locale, decimal points are commas.
bid = bid.toString().toLowerCase().replace(/,/g, '.');
if (bid != 'excluded' && (isNaN(Number(bid)) || !isFinite(bid))) {
resultsByRow[0] = 'ERROR';
resultsByRow[1] = 'Invalid bid';
}
}
const campaignName = parsedRow[CAMPAIGN_COLUMN];
campaigns[campaignName] =
fetchCampaignIfNecessary(campaigns, campaignName);
if (campaigns[campaignName]) {
const 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';
}
const 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;
}
let 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.
let 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.
let 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.
let maxLevel = -1;
let productGroupsToAdd = row[PRODUCT_GROUP_COLUMN];
let productGroup = adGroup.rootProductGroup;
for (let i = 0; i < productGroupsToAdd.length; i++) {
const type =
productGroupsToAdd[i].type.toString().toLowerCase()
.replace(/[ ]+/g, '');
let 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 {
let child = productGroup.childMap[val];
if (!child) {
const children = productGroup.children().get();
for (const element of children) {
child = element;
if (child.isOtherCase()) {
break;
}
}
child.childMap = {};
}
productGroup.childMap[val] = child;
productGroup = child;
productGroup.setMaxCpc(Number(adGroup.bidding().getCpc()));
//Only assign the bid to the lowest level product group
//on that row
if (i + 1 === productGroupsToAdd.length) {
if (bid != 'excluded') {
productGroup.setMaxCpc(Number(bid));
} else {
productGroup.exclude();
}
}
}
} else {
let 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('INDEX' +
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;
}
}
const productGroupOp = productGroupBuilder.build();
if (!productGroupOp.isSuccessful()) {
resultsByRow[0] = 'ERROR';
resultsByRow[1] = 'Error creating product group ' +
'level ' + (i + 1) + ': ' + productGroupOp.getErrors();
return;
}
const 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(Number(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 parseEditorFormat(productGroupPath) {
// Ignore * / case which is the root product group.
if (productGroupPath === '* /' || !productGroupPath) {
return [];
}
const regexVals = productGroupPath.match(new RegExp(/'(.*?)'/g));
if (regexVals) {
for (let i = 0; i < regexVals.length; i++) {
productGroupPath = productGroupPath.replace(regexVals[i], '$' + i);
}
}
const result = [];
const productGroup = productGroupPath.split('/');
// Google Ads Editor format starts with '* /' so we ignore first one.
for (let x = 1; x < productGroup.length; x++) {
if (!productGroup[x]) {
continue;
}
// Google Ads Editor format looks like: Brand='nike'.
const pair = productGroup[x].trim().split('=');
if (pair.length != 2) {
return {error: 'Product group string malformed. Should have 1 "=", ' +
'but has ' + (pair.length - 1)};
}
let 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 != '*') {
const values = pair[1].split('$');
// Skip 0 because it's always blank. String always starts with $.
for (let 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) {
const campaignName = name.replace(/'/g, '\\\'');
const shoppingCampaignSelector = AdsApp
.shoppingCampaigns()
.withCondition('Name = \'' + campaignName + '\'');
let campaign = null;
const 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) {
const adGroupName = agName.replace(/'/g, '\\\'');
const adGroupSelector = campaign
.adGroups()
.withCondition('Name = \'' + adGroupName + '\'');
let adGroup = null;
const 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.
const 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.
const 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;
}
const adGroupResult = adGroupOp.getResult();
adGroupResult.createRootProductGroup();
console.log(`Successfully created ad group [${adGroupResult.getName()}]`);
// Build ad group ad.
const 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.');
}
const spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
const sheet = spreadsheet.getSheetByName(sheetname);
return sheet;
}