Page Summary
-
The Common Negative List Script simplifies managing negative keywords and placements across multiple Google Ads campaigns by syncing them from a spreadsheet.
-
The script creates and synchronizes a shared negative criteria list with entries from a Google Spreadsheet for both keywords and placements.
-
The script can be configured to apply the negative criteria list to all enabled and paused campaigns in an account or to a subset of campaigns based on a specified label.
-
Configuration options include specifying the spreadsheet URL, limiting campaigns by label, setting names for shared negative lists, and providing an email address for status summaries.
Maintaining a list of negative keywords or negative placements is a common task in Google Ads campaign management. This list is generally used as a filter against keywords or placements that drive unwanted traffic to your website.
Google Ads supports negative criteria lists that can be shared with multiple campaigns in a single account. However, applying the list to multiple campaigns in a big account and keeping the list synced over time can present challenges. The Common Negative List Script simplifies this task by letting you manage the negative criteria through a spreadsheet.
How it works
The script reads negative criteria from a Google Spreadsheet. It creates a shared negative criteria list in the Google Ads account and syncs the list with the criteria from the spreadsheet. Separate lists are maintained for keywords and placements.
The script then ensures that the negative criteria list is applied to all the campaigns in the account. If required, you can limit the list of campaigns by specifying a label in the configuration spreadsheet to filter for inclusion when processing campaigns.
The script optionally sends out an email summarizing its actions to an email address specified in the configuration spreadsheet.
Configuration
The script uses a spreadsheet for configuration. The following configuration settings are supported:
- The script processes all the
ENABLEDandPAUSEDcampaigns in an account by default. To limit the list of campaigns processed,- Create a label in each account you want to process.
- Apply this label to the list of campaigns to be processed.
- Specify this label in cell C3 of the configuration spreadsheet.
- Specify an email address in cell C6 to receive a summary email once the script completes running.
- The script creates separate shared negative criteria lists for keywords and placements in the accounts it processes. Specify the name of the shared negative criteria lists in the configuration spreadsheet, cells C4 and C5.
- Omit any protocol prefix (
http://orhttps://) from any Placement URL in your placement list. - Ensure that none Placement URLs have trailing slashes (
/). - Ensure that all Placement URLs are in lowercase in your spreadsheet.
Scheduling
Schedule the script to run daily or hourly.
Setup
-
Click the button to create the spreadsheet-based script in your Google Ads account.
-
Click the button to make a copy of the template spreadsheet.
-
Update
spreadsheet_urlin your 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 Common Negative List Script
*
* @overview The Common Negative List script applies negative keywords and
* placements from a spreadsheet to multiple campaigns in your account using
* shared keyword and placement lists. See
* https://developers.google.com/google-ads/scripts/docs/solutions/common-negative-list
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 2.2
*
* @changelog
* - version 2.2
* - Fixed an issue where the match type of keywords in the negative list was
* ignored.
* - version 2.1
* - Split into info, config, and code.
* - version 2.0
* - Updated to use new Google Ads scripts features.
* - version 1.0.2
* - Added validation for external spreadsheet setup.
* - version 1.0.1
* - Improvements to time zone handling.
* - version 1.0
* - Released initial version.
*/
/**
* Configuration to be used for the Common Negative List script.
*/
CONFIG
=
{
/**
* The URL of the tracking spreadsheet. This should be a copy of
* https://goo.gl/PZGKVn
*/
'spreadsheet_url'
:
'INSERT_SPREADSHEET_URL_HERE'
};
const
SPREADSHEET_URL
=
CONFIG
.
spreadsheet_url
;
/**
* Keep track of the spreadsheet names for various criteria types, as well as
* the criteria type being processed.
*/
const
CriteriaType
=
{
KEYWORDS
:
'Keywords'
,
PLACEMENTS
:
'Placements'
};
/**
* Create a shared negative criteria list in the Google Ads account and
* syncs the list with the criteria from the spreadsheet.
*/
function
main
()
{
let
emailParams
=
{
// Number of placements that were synced.
PlacementCount
:
0
,
// Number of keywords that were synced.
KeywordCount
:
0
,
// Number of campaigns that were synced.
CampaignCount
:
0
,
// Status of processing this account - OK / ERROR.
Status
:
''
};
try
{
const
syncSummary
=
syncCommonLists
();
emailParams
.
PlacementCount
=
syncSummary
.
PlacementCount
;
emailParams
.
KeywordCount
=
syncSummary
.
KeywordCount
;
emailParams
.
CampaignCount
=
syncSummary
.
CampaignCount
;
emailParams
.
Status
=
'OK'
;
}
catch
(
err
)
{
emailParams
.
Status
=
'ERROR'
;
}
const
config
=
readConfig
();
const
spreadsheet
=
validateAndGetSpreadsheet
(
SPREADSHEET_URL
);
// Make sure the spreadsheet is using the account's timezone.
spreadsheet
.
setSpreadsheetTimeZone
(
AdsApp
.
currentAccount
().
getTimeZone
());
spreadsheet
.
getRangeByName
(
'LastRun'
).
setValue
(
new
Date
());
spreadsheet
.
getRangeByName
(
'CustomerId'
).
setValue
(
AdsApp
.
currentAccount
().
getCustomerId
());
sendEmail
(
config
,
emailParams
);
}
/**
* Sends a summary email about the changes that this script made.
*
* @param {Object} config The configuration object.
* @param {Object} emailParams Contains details required to create the email
* body.
*/
function
sendEmail
(
config
,
emailParams
)
{
const
html
=
[];
let
summary
=
''
;
if
(
emailParams
.
Status
==
'OK'
)
{
summary
=
`The Common Negative List script successfully processed `
+
`Customer ID:
${
AdsApp
.
currentAccount
().
getCustomerId
()
}
`
+
` and synced a total of
${
emailParams
.
KeywordCount
}
`
+
` keywords and
${
emailParams
.
PlacementCount
}
placements.`
;
}
else
{
summary
=
`The Common Negative List script failed to process `
+
`Customer ID:
${
AdsApp
.
currentAccount
().
getCustomerId
()
}
`
+
` and synced a total of
${
emailParams
.
KeywordCount
}
`
+
` keywords and
${
emailParams
.
PlacementCount
}
placements.`
;
}
html
.
push
(
'<html>'
,
'<head></head>'
,
'<body>'
,
'<table style="font-family:Arial,Helvetica; '
+
'border-collapse:collapse;font-size:10pt; '
+
'color:#444444; border: solid 1px #dddddd;" '
+
'width="600" cellpadding=20>'
,
'<tr>'
,
'<td>'
,
'<p>Hello,</p>'
,
'<p>'
+
summary
+
'</p>'
,
'<p>Cheers<br />Google Ads Scripts Team</p>'
,
'</td>'
,
'</tr>'
,
'</table>'
,
'</body>'
,
'</html>'
);
if
(
config
.
email
!=
''
)
{
MailApp
.
sendEmail
({
to
:
config
.
email
,
subject
:
'Common Negative List Script'
,
htmlBody
:
html
.
join
(
'\n'
)
});
}
}
/**
* Synchronizes the negative criteria list in an account with the common list
* in the user spreadsheet.
*
* @return {Object} A summary of the number of keywords and placements synced,
* and the number of campaigns to which these lists apply.
*/
function
syncCommonLists
()
{
const
config
=
readConfig
();
let
syncedCampaignCount
=
0
;
const
keywordListDetails
=
syncCriteriaInNegativeList
(
config
,
CriteriaType
.
KEYWORDS
);
syncedCampaignCount
=
syncCampaignList
(
config
,
keywordListDetails
.
SharedList
,
CriteriaType
.
KEYWORDS
);
const
placementListDetails
=
syncCriteriaInNegativeList
(
config
,
CriteriaType
.
PLACEMENTS
);
syncCampaignList
(
config
,
placementListDetails
.
SharedList
,
CriteriaType
.
PLACEMENTS
);
return
{
'CampaignCount'
:
syncedCampaignCount
,
'PlacementCount'
:
placementListDetails
.
CriteriaCount
,
'KeywordCount'
:
keywordListDetails
.
CriteriaCount
};
}
/**
* Synchronizes the list of campaigns covered by a negative list against the
* desired list of campaigns to be covered by the common list.
*
* @param {Object} config The configuration object.
* @param {AdsApp.NegativeKeywordList|AdsApp.ExcludedPlacementList}
* sharedList The shared negative criterion list to be synced against the
* common list.
* @param {String} criteriaType The criteria type for the shared negative list.
*
* @return {Number} The number of campaigns synced.
*/
function
syncCampaignList
(
config
,
sharedList
,
criteriaType
)
{
const
campaignIds
=
getLabelledCampaigns
(
config
.
label
);
let
totalCampaigns
=
Object
.
keys
(
campaignIds
).
length
;
const
listedCampaigns
=
sharedList
.
campaigns
().
get
();
const
campaignsToRemove
=
[];
for
(
const
listedCampaign
of
listedCampaigns
)
{
if
(
listedCampaign
.
getId
()
in
campaignIds
)
{
delete
campaignIds
[
listedCampaign
.
getId
()];
}
else
{
campaignsToRemove
.
push
(
listedCampaign
);
}
}
// Anything left over in campaignIds starts a new list.
const
campaignsToAdd
=
AdsApp
.
campaigns
().
withIds
(
Object
.
keys
(
campaignIds
)).
get
();
for
(
const
campaignToAdd
of
campaignsToAdd
)
{
if
(
criteriaType
==
CriteriaType
.
KEYWORDS
)
{
campaignToAdd
.
addNegativeKeywordList
(
sharedList
);
}
else
if
(
criteriaType
==
CriteriaType
.
PLACEMENTS
)
{
campaignToAdd
.
addExcludedPlacementList
(
sharedList
);
}
}
for
(
const
campaignToRemove
of
campaignsToRemove
)
{
if
(
criteriaType
==
CriteriaType
.
KEYWORDS
)
{
campaignToRemove
.
removeNegativeKeywordList
(
sharedList
);
}
else
if
(
criteriaType
==
CriteriaType
.
PLACEMENTS
)
{
campaignToRemove
.
removeExcludedPlacementList
(
sharedList
);
}
}
return
totalCampaigns
;
}
/**
* Gets a list of campaigns having a particular label.
*
* @param {String} labelText The label text.
*
* @return {Array.<Number>} An array of campaign IDs having the specified
* label.
*/
function
getLabelledCampaigns
(
labelText
)
{
const
campaignIds
=
{};
let
campaigns
;
if
(
labelText
!=
''
)
{
const
label
=
getLabel
(
labelText
);
campaigns
=
label
.
campaigns
().
withCondition
(
'Status in [ENABLED, PAUSED]'
).
get
();
}
else
{
campaigns
=
AdsApp
.
campaigns
().
withCondition
(
'Status in [ENABLED, PAUSED]'
).
get
();
}
for
(
const
campaign
of
campaigns
)
{
campaignIds
[
campaign
.
getId
()]
=
1
;
}
return
campaignIds
;
}
/**
* Gets a label with the specified label text.
*
* @param {String} labelText The label text.
*
* @return {AdsApp.Label} The label text.
*/
function
getLabel
(
labelText
)
{
let
labels
=
AdsApp
.
labels
().
withCondition
(
"Name='"
+
labelText
+
"'"
).
get
();
if
(
labels
.
totalNumEntities
()
==
0
)
{
const
message
=
Utilities
.
formatString
(
`Label named
${
labelText
}
is missing in ' +
'your account. Make sure the label exists in the account, and is ' +
'applied to campaigns and adgroups you wish to process.`
);
throw
(
message
);
}
return
labels
.
next
();
}
/**
* Synchronizes the criteria in a shared negative criteria list with the user
* spreadsheet.
*
* @param {Object} config The configuration object.
* @param {String} criteriaType The criteria type for the shared negative list.
*
* @return {Object} A summary of the synced negative list, and the number of
* criteria that were synced.
*/
function
syncCriteriaInNegativeList
(
config
,
criteriaType
)
{
let
criteriaFromSheet
=
loadCriteria
(
criteriaType
);
let
totalCriteriaCount
=
Object
.
keys
(
criteriaFromSheet
).
length
;
let
sharedList
=
null
;
let
listName
=
config
.
listname
[
criteriaType
];
sharedList
=
createNegativeListIfRequired
(
listName
,
criteriaType
);
let
negativeCriteria
=
null
;
try
{
if
(
criteriaType
==
CriteriaType
.
KEYWORDS
)
{
negativeCriteria
=
sharedList
.
negativeKeywords
().
get
();
}
else
if
(
criteriaType
==
CriteriaType
.
PLACEMENTS
)
{
negativeCriteria
=
sharedList
.
excludedPlacements
().
get
();
}
}
catch
(
e
)
{
console
.
error
(
`Failed to retrieve shared list. Error says
${
e
}
`
);
if
(
AdsApp
.
getExecutionInfo
().
isPreview
())
{
const
message
=
Utilities
.
formatString
(
`The script cannot create`
+
` the negative
${
criteriaType
}
list in preview mode. Either run`
+
` the script without preview, or create a negative `
+
`
${
criteriaType
}
list with name "
${
listName
}
" `
+
` manually before previewing the script.`
);
console
.
log
(
message
);
}
throw
e
;
}
const
criteriaToDelete
=
[];
for
(
const
negativeCriterion
of
negativeCriteria
)
{
let
key
=
null
;
if
(
criteriaType
==
CriteriaType
.
KEYWORDS
)
{
key
=
negativeCriterion
.
getText
();
// Since the keyword text in the spreadsheet specifies match types in the
// syntax accepted by the UI, we need to convert our keys to match it.
const
matchType
=
negativeCriterion
.
getMatchType
();
if
(
matchType
===
"PHRASE"
)
{
key
=
`"
${
key
}
"`
;
}
else
if
(
matchType
===
"EXACT"
)
{
key
=
`[
${
key
}
]`
;
}
}
else
if
(
criteriaType
==
CriteriaType
.
PLACEMENTS
)
{
key
=
negativeCriterion
.
getUrl
();
}
if
(
key
in
criteriaFromSheet
)
{
// Nothing to do with this criteria. Remove it from loaded list.
delete
criteriaFromSheet
[
key
];
}
else
{
// This criterion is not in the sync list. Mark for deletion.
criteriaToDelete
.
push
(
negativeCriterion
);
}
}
// Whatever left in the sync list are new items.
if
(
criteriaType
==
CriteriaType
.
KEYWORDS
)
{
sharedList
.
addNegativeKeywords
(
Object
.
keys
(
criteriaFromSheet
));
}
else
if
(
criteriaType
==
CriteriaType
.
PLACEMENTS
)
{
sharedList
.
addExcludedPlacements
(
Object
.
keys
(
criteriaFromSheet
));
}
for
(
let
i
=
0
;
i
<
criteriaToDelete
.
length
;
i
++
)
{
criteriaToDelete
[
i
].
remove
();
}
return
{
'SharedList'
:
sharedList
,
'CriteriaCount'
:
totalCriteriaCount
,
'Type'
:
criteriaType
};
}
/**
* Creates a shared negative criteria list if required.
*
* @param {string} listName The name of shared negative criteria list.
* @param {String} listType The criteria type for the shared negative list.
*
* @return {AdsApp.NegativeKeywordList|AdsApp.ExcludedPlacementList} An
* existing shared negative criterion list if it already exists in the
* account, or the newly created list if one didn't exist earlier.
*/
function
createNegativeListIfRequired
(
listName
,
listType
)
{
let
negativeListSelector
=
null
;
if
(
listType
==
CriteriaType
.
KEYWORDS
)
{
negativeListSelector
=
AdsApp
.
negativeKeywordLists
();
}
else
if
(
listType
==
CriteriaType
.
PLACEMENTS
)
{
negativeListSelector
=
AdsApp
.
excludedPlacementLists
();
}
let
negativeListIterator
=
negativeListSelector
.
withCondition
(
`Name = '
${
listName
}
'`
).
get
();
if
(
negativeListIterator
.
totalNumEntities
()
==
0
)
{
let
builder
=
null
;
if
(
listType
==
CriteriaType
.
KEYWORDS
)
{
builder
=
AdsApp
.
newNegativeKeywordListBuilder
();
}
else
if
(
listType
==
CriteriaType
.
PLACEMENTS
)
{
builder
=
AdsApp
.
newExcludedPlacementListBuilder
();
}
let
negativeListOperation
=
builder
.
withName
(
listName
).
build
();
return
negativeListOperation
.
getResult
();
}
else
{
return
negativeListIterator
.
next
();
}
}
/**
* Loads a list of criteria from the user spreadsheet.
*
* @param {string} sheetName The name of shared negative criteria list.
*
* @return {Object} A map of the list of criteria loaded from the spreadsheet.
*/
function
loadCriteria
(
sheetName
)
{
const
spreadsheet
=
validateAndGetSpreadsheet
(
SPREADSHEET_URL
);
const
sheet
=
spreadsheet
.
getSheetByName
(
sheetName
);
const
values
=
sheet
.
getRange
(
'B4:B'
).
getValues
();
const
retval
=
{};
for
(
const
value
of
values
)
{
let
keyword
=
value
[
0
].
toString
().
trim
();
if
(
keyword
!=
''
)
{
retval
[
keyword
]
=
1
;
}
}
return
retval
;
}
/**
* Loads a configuration object from the spreadsheet.
*
* @return {Object} A configuration object.
*/
function
readConfig
()
{
const
spreadsheet
=
validateAndGetSpreadsheet
(
SPREADSHEET_URL
);
let
values
=
spreadsheet
.
getRangeByName
(
'ConfigurationValues'
).
getValues
();
const
config
=
{
'label'
:
values
[
0
][
0
],
'listname'
:
{
},
'email'
:
values
[
3
][
0
],
};
config
.
listname
[
CriteriaType
.
KEYWORDS
]
=
values
[
1
][
0
];
config
.
listname
[
CriteriaType
.
PLACEMENTS
]
=
values
[
2
][
0
];
return
config
;
}
/**
* DO NOT EDIT ANYTHING BELOW THIS LINE.
* Please modify your spreadsheet URL 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.
* @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
)
{
if
(
spreadsheeturl
==
'INSERT_SPREADSHEET_URL_HERE'
)
{
throw
new
Error
(
'Please specify a valid Spreadsheet URL. You can find'
+
' a link to a template in the associated guide for this script.'
);
}
return
SpreadsheetApp
.
openByUrl
(
spreadsheeturl
);
}
