Skip to content

Instantly share code, notes, and snippets.

@edmeehan

edmeehan/Cake_Final_URL.gs

Last active August 17, 2021 09:44
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save edmeehan/de0b26c54a1d98f640228071bcb97b7d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save edmeehan/de0b26c54a1d98f640228071bcb97b7d to your computer and use it in GitHub Desktop.
Download ZIP
Google Ad Script - add campaign & adgroup custom param keys & values
Raw
Cake_Final_URL.gs
/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "ADD_URL_HERE";
var CakeCreativeGoogleSheetURL = "https://docs.google.com/spreadsheets/d/1PDq3JJXrd-os-GzD7UiW8vF3DCHOmlgpWVFqM4GQAZQ/edit#gid=1492232636";
/* ------------------------------------------------------------------------------ */
var cakeCreativeValues, actionListValues;
function main() {
var googleSheet = connectToGoogleSheet(GoogleSheetURL);
cakeCreativeValues = getCakeCreativeValues();
actionListValues = getActionListValues(googleSheet);
createAdsList(googleSheet);
}
function getCakeCreativeValues() {
var sheet = getSheet(connectToGoogleSheet(CakeCreativeGoogleSheetURL),"creatives");
return sheet.getRange(2,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
}
function connectToGoogleSheet(urlString) {
try {
var sheet = SpreadsheetApp.openByUrl(urlString);
Logger.log("Google Sheets Success: %s",sheet.getName());
return sheet;
} catch (error) {
Logger.log("Google Sheets Fail: %s", error);
}
}
function getSheet(sheet, tabName) {
return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName);
}
function getActionListValues(googleSheet) {
var sheet = getSheet(googleSheet,"Action List");
var values = sheet.getRange(1,1,sheet.getLastRow()).getValues() || [];
return [].concat.apply([], values);
}
function createAdsList(sheet) {
var accountIterator = AdsManagerApp.accounts().withIds(actionListValues).get();
var sheetData = [];
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
var ads = AdsApp.ads()
.withCondition("Status = ENABLED")
.withCondition("CampaignStatus = ENABLED")
.withCondition("AdGroupStatus = ENABLED")
.withCondition("CreativeFinalUrls CONTAINS 'tracking.'")
.get();
while (ads.hasNext()) {
var ad = ads.next();
sheetData.push([
account.getCustomerId(),
ad.getId(),
ad.urls().getFinalUrl(),
//Object.keys(ad.urls().getCustomParameters() || {}).join(','),
//ad.urls().getFinalUrlSuffix(),
//ad.urls().getTrackingTemplate() ? 'TRUE' : 'FALSE',
translateFinalUrl(ad.urls().getFinalUrl())
]);
}
}
if (sheetData.length > 0) {
var newSheet = sheet.insertSheet("Cake FinalURL " + Date.now());
newSheet.appendRow([
"Account ID",
"Ad ID",
"Final URL Before",
"Final URL After"
]);
newSheet.getRange(2,1,sheetData.length,4).setValues(sheetData);
Logger.log("%s ads added to sheet.", sheetData.length)
} else {
Logger.log("No active ads in accounts - nothing to do here.");
}
};
function translateFinalUrl(finalUrl) {
var aParam = finalUrl.match(/[&?]a=([^&]*)/)[1];
var cParam = finalUrl.match(/[&?]c=([^&]*)/)[1];
var destDomain = "https://www." + finalUrl.match(/.*tracking.([^?]*)/)[1];
var destUrl;
for (var x = 0; cakeCreativeValues.length > x ; x++) {
if (cakeCreativeValues[x][0] == cParam) {
destUrl = cakeCreativeValues[x][2];
break;
}
}
return destUrl
? ("https://" + destUrl.match(/\/\/([^?]*)/)[1] + "?a=" + aParam)
: (destDomain + "?a=" + aParam);
}
Raw
Cake_Transition_Support.gs
/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "ADD_URL_HERE";
/* ------------------------------------------------------------------------------ */
function main() {
var sheets = connectToGoogleSheet(GoogleSheetURL);
createAccountsList(sheets);
makeActionsTab(sheets);
}
function createAccountsList(sheet) {
var accountsSheet = getSheet(sheet, 'All Accounts List');
var accountIterator = AdsManagerApp.accounts().get();
var sheetData = [];
try {
// Set Title Row
accountsSheet.clear().appendRow([
"Account ID",
"Account Name",
"Enabled Campaigns Count"
]);
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
var campaigns = AdsApp.campaigns()
.withCondition("Status = ENABLED")
.get();
// Set Data Rows
sheetData.push([
account.getCustomerId(),
account.getName(),
campaigns.totalNumEntities()
]);
}
// Update Google Sheet
accountsSheet.getRange(2,1,sheetData.length,3).setValues(sheetData);
Logger.log("Account tab created with %s accounts.", accountIterator.totalNumEntities());
} catch (error) {
Logger.log(error);
}
};
function connectToGoogleSheet(urlString) {
try {
var sheet = SpreadsheetApp.openByUrl(urlString);
Logger.log("Google Sheets Success: %s",sheet.getName());
return sheet;
} catch (error) {
Logger.log("Google Sheets Fail: %s", error);
}
}
function getSheet(sheet, tabName) {
return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName);
}
function makeActionsTab(sheet) {
getSheet(sheet,"Action List").clear();
}
Raw
Update_AdGroup_Campaign_Custom_Parameters.gs
/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "ADD_URL_HERE";
/* ------------------------------------------------------------------------------ */
var data = [["Type","ID","Custom Parameters"]];
function main() {
var googleSheet = connectToGoogleSheet(GoogleSheetURL);
var actionListValues = getActionListValues(googleSheet);
var accountIterator = AdsManagerApp.accounts().withIds(actionListValues).get();
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
updateCampaigns();
updateAdGroups();
}
var newSheet = googleSheet.insertSheet("Campaign & AdGroup " + Date.now());
newSheet.getRange(1,1,data.length,3).setValues(data);
}
function updateCampaigns() {
var campaigns = AdsApp.campaigns()
.withCondition("Status = ENABLED")
.get();
while (campaigns.hasNext()) {
var campaign = campaigns.next();
var name = updateCustomParams(campaign,'campaign');
data.push([
"campaign",
campaign.getId(),
campaign.urls().getCustomParameters(),
])
}
}
function updateAdGroups() {
var adGroups = AdsApp.adGroups()
.withCondition("Status = ENABLED")
.withCondition("CampaignStatus = ENABLED")
.get();
while (adGroups.hasNext()) {
var adGroup = adGroups.next();
var name = updateCustomParams(adGroup,'adgroup');
data.push([
"adgroup",
adGroup.getId(),
adGroup.urls().getCustomParameters(),
])
}
}
function connectToGoogleSheet(urlString) {
try {
var sheet = SpreadsheetApp.openByUrl(urlString);
Logger.log("Google Sheets Success: %s",sheet.getName());
return sheet;
} catch (error) {
Logger.log("Google Sheets Fail: %s", error);
}
}
function getSheet(sheet, tabName) {
return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName);
}
function getActionListValues(googleSheet) {
var sheet = getSheet(googleSheet,"Action List");
var values = sheet.getRange(1,1,sheet.getLastRow()).getValues() || [];
return [].concat.apply([], values);
}
function updateCustomParams(model,paramName) {
var name = encodeURIComponent(formatNameString(model.getName()));
try {
// -------------- GET PARAMETERS ---------------
var customParams = model.urls().getCustomParameters() || {};
customParams[paramName] = name;
model.urls().setCustomParameters(customParams);
return name;
} catch (error) {
// if a custom parameter is defined without a value it breaks the getCustomParameters method
Logger.log("%s error getting parameters: %s", name, error);
}
}
function formatNameString(string) {
return string.replace(/\s/g,""); // remove all spaces
}
Raw
Update_Campaign_Tracking_Template_Final_URL_Suffix.gs
/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "ADD_URL_HERE";
/* ---------- UPDATE TRACKING TEMPLATE & FINAL URL SUFFIX------------------------ */
var TrackingTemplate = "{lpurl}?gclid={gclid}&traffic[source]=google&traffic[medium]=SETMEDIUM&traffic[campaign]={_campaign}&traffic[term]={keyword}&traffic[content]={_adgroup}:{creative}&s1=CM-{_campaign}&s2=AG-{_adgroup}&s3=CRE-{creative}"
var FinalURLSuffix = "subtheme={_subtheme}&tone={_tone}"
/* ------------------------------------------------------------------------------ */
var data = [[
"Account",
"Campaign",
"Tracking Template Before",
"Tracking Template After",
"Final URL Suffix Before",
"Final URL Suffix After"
]];
function main() {
var googleSheet = connectToGoogleSheet(GoogleSheetURL);
var actionListValues = getActionListValues(googleSheet);
var accountIterator = AdsManagerApp.accounts().withIds(actionListValues).get();
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
updateCampaigns(account.getCustomerId());
}
var newSheet = googleSheet.insertSheet("Tracking Template & FinalURL Suffix " + Date.now());
newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
}
function updateCampaigns(accountId) {
var campaigns = AdsApp.campaigns()
.withCondition("Status = ENABLED")
.get();
while (campaigns.hasNext()) {
var campaign = campaigns.next();
var urls = campaign.urls();
var ttBefore = urls.getTrackingTemplate();
var urlBefore = urls.getFinalUrlSuffix();
urls.setTrackingTemplate(TrackingTemplate);
urls.setFinalUrlSuffix(FinalURLSuffix);
data.push([
accountId,
campaign.getId(),
ttBefore,
campaign.urls().getTrackingTemplate(),
urlBefore,
campaign.urls().getFinalUrlSuffix(),
])
}
}
function connectToGoogleSheet(urlString) {
try {
var sheet = SpreadsheetApp.openByUrl(urlString);
Logger.log("Google Sheets Success: %s",sheet.getName());
return sheet;
} catch (error) {
Logger.log("Google Sheets Fail: %s", error);
}
}
function getSheet(sheet, tabName) {
return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName);
}
function getActionListValues(googleSheet) {
var sheet = getSheet(googleSheet,"Action List");
var values = sheet.getRange(1,1,sheet.getLastRow()).getValues() || [];
return [].concat.apply([], values);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment