function main() { //This script excludes Tier 3 locations from campaigns, making it useful for businesses that struggle to operate in areas with higher Covid restrictions. //It uses a spreadsheet as its data source, which features Google locations ids for each of the 40+ counties/cities in Tier 3. //This data is updated manually, using the information provided on the .gov site (https://www.gov.uk/guidance/full-list-of-local-restriction-tiers-by-area). //This data will be monitored and updated on a regular basis. //As the data is subject to change, the script first removes all excluded locations. //If the account does not run ads in certain regions/cities, please make a copy of this spreadsheet: https://docs.google.com/spreadsheets/d/108KJskfOoBVZd5MWDxGFmLckc4PQIsehn3_VIs56WPc/edit#gid=0. //You will find a list of Google Ad region/city codes attached to the sheet. //Add the codes of the regions you wish to stay excluded and replace the url in the exclusionDataURL variable. //If the account does not have any data in it, leave the url in the exclusionDataURL variable as it is. var exclusionDataURL = "https://docs.google.com/spreadsheets/d/108KJskfOoBVZd5MWDxGFmLckc4PQIsehn3_VIs56WPc/edit#gid=0"; //If you have a list of excluded locations, paste it here - otherwise leave this url as is var tierDataURL = "https://docs.google.com/spreadsheets/d/1e8ji10sO3EP_4LJt2HmRx1m6qUNLKixGlACmM27xA70/edit?usp=sharing"; //Pass in a nested array and return a flattened array function flatten(array) { var flat = []; for (var i = 0; i < arguments.length; i += 1) { if (arguments[i] instanceof Array) { flat.push.apply(flat, flatten.apply(this, arguments[i])); } else { flat.push(arguments[i]); } } return flat; } //Get data from each sheet function pullSheetData(url, rowStart) { var results = []; var spreadsheet = SpreadsheetApp.openByUrl(url); var ss = spreadsheet.getActiveSheet(); var values = ss.getRange(rowStart, 2, ss.getLastRow()).getValues(); if (values) { results.push(values); var mergeResults = flatten(results).slice(0, -1); return mergeResults; } } //Check for values in an array function notInArray(array, value) { return array.indexOf(value) === -1; } //Call pullSheetData for each sheet var tierData = pullSheetData(tierDataURL, 1); var exclusionData = pullSheetData(exclusionDataURL, 2); //Remove excluded locations from the campaign in case they have moved tiers function resetCampaigns() { var campaignIterator = AdsApp.campaigns() .withCondition("Status = ENABLED") .get(); while (campaignIterator.hasNext()) { var campaign = campaignIterator.next(); var currentExclusions = campaign.targeting().excludedLocations().get(); while (currentExclusions.hasNext()) { var resetLocation = currentExclusions.next(); var resetLocationId = resetLocation.getId(); if (notInArray(exclusionData, resetLocationId)) { resetLocation.remove(); } } } Logger.log("Campaigns re-enabled"); } //Add Tier locations to all campaigns function removeLocations() { var campaignIterator = AdsApp.campaigns() .withCondition("Status = ENABLED") .get(); while (campaignIterator.hasNext()) { var campaign = campaignIterator.next(); for (var i = 0; i < tierData.length; i += 1) { campaign.excludeLocation(tierData[i]); } } Logger.log("Campaigns paused in areas under Tier 3 restrictions"); } //Run campaigns if (!tierData) { resetCampaigns(); Logger.log("No more Tier 3 locations - hooray"); } else { resetCampaigns(); removeLocations(); } } /* Created by Analysts at Adapt - https://www.adaptworldwide.com/ Written by Tom Meyrick Change log: - version 1.0 (initial version) */