This is the link to copy the spreadsheet template:
https://docs.google.com/spreadsheets/d/1N-MF2YI3zUwCTiNdx30LcxAFO8-myLtj-ypNbi5nExY/copy
And here you can get the script code:
By downloading the following script you agree to the fair use agreement.
// Uncle Sem's Asset Group Status Handler V1.1
// Copyright by Felix Kohmaier 2023 (c)
//
// In the SEA business since 2007 already, managing 7 digit Google Ads spendings every month.
// Google Partner since 2015 and having the Google Premium Partner Status (beeing withing the top 3% of agencies worldwide) since 2022.
// Multiple Awards in the field of SEA.
//
// For any requests you can contact me via email: felix@kohmaier.com
// Or reach out to me on LinkedIn: https://www.linkedin.com/in/felix-kohmaier-b7667291/
//
// IMPORTANT:
// This script is only designed for feed based Performance Max Campaigns! It does not support PMax Campaigns running without feeds or product data.
// Before running it, I would recommend testing the script in preview mode. This will allow it to complete a run, showing you what WOULD happen, without actually changing the status of any asset group.
// You can review the results of this test in the "Logs" and "Changes Protocol." Then verify that only the correct asset groups are being activated and paused.
// Due to the various ways you can set up and structure your Account, there may be scenarios I haven't accounted for when creating this script.
// However, by using this script you acknowledge that you are full responsible for its output yourself, as I can´t guarantee a 100% correct behavior for every account and every scenario.
//
//
// HOW TO USE IT:
//
// Open this URL in your browser and copy the template: https://docs.google.com/spreadsheets/d/1N-MF2YI3zUwCTiNdx30LcxAFO8-myLtj-ypNbi5nExY/copy
//
// Then copy the new URL or your Spreadsheet and paste it directly below ↓ where it says "ENTER YOUR SPREADSHEET URL HERE"
const spreadsheetUrl = "ENTER YOUR SPREADSHEET URL HERE"; // <-- Here you enter the spreadsheet URL (don´t copy the part "edit#gid=*****)
// Here the script starts -> DON´T edit anything below here!
function main() {
const products = extractProductData(1);
if (products.length > 0) {
pushToSpreadsheet(products);
} else {
console.log("No product information found for the specified period.");
}
const assetGroups = fetchDataFromGoogleAds();
if (assetGroups.length > 0) {
pushAssetGroupsToSpreadsheet(assetGroups);
} else {
console.log("No Asset Groups found.");
}
const filters = fetchFiltersFromGoogleAds();
if (filters.length > 0) {
pushFiltersToSpreadsheet(filters);
} else {
console.log("No Product Groups found.");
}
Utilities.sleep(84000); // 84000 Milliseconds are 3.5 Minutes
// Open the Google Sheets document
const spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
const sheet = spreadsheet.getSheetByName('asset groups status export');
// Get the data range from the sheet
const data = sheet.getDataRange().getValues();
// Iterate through each row of data
for (const row of data) {
const campaignName = row[0]; // Assumes campaign name is in the first column
const assetGroupName = row[1]; // Assumes asset group name is in the second column
const status = row[2].toLowerCase(); // Assumes status is in the third column
if (status === "paused") {
pauseAssetGroup(campaignName, assetGroupName);
} else if (status === "active") {
activateAssetGroup(campaignName, assetGroupName);
}
}
}
function extractProductData(daysAgo) {
const today = new Date();
const cutoffDate = new Date(today.getTime() - ((daysAgo - 0.25) * 240 * 60 * 60 * 1000));
const dateString = Utilities.formatDate(cutoffDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
const query = `
SELECT ProductTitle, ProductTypeL1, ProductTypeL2, ProductTypeL3, ProductTypeL4, ProductTypeL5,
CustomAttribute0, CustomAttribute1, CustomAttribute2, CustomAttribute3, CustomAttribute4,
OfferId, Brand, ProductCondition, Channel, CategoryL1, CategoryL2, CategoryL3, CategoryL4, CategoryL5
FROM SHOPPING_PERFORMANCE_REPORT
WHERE Date = '${dateString}'
`;
const products = [];
const report = AdsApp.report(query);
const rows = report.rows();
products.push(["Product Title", "Product Type 1", "Product Type 2", "Product Type 3", "Product Type 4", "Product Type 5",
"Custom Label 0", "Custom Label 1", "Custom Label 2", "Custom Label 3", "Custom Label 4",
"Item ID", "Brand", "CategoryL1", "CategoryL2", "CategoryL3", "CategoryL4", "CategoryL5", "Channel", "ProductCondition"]);
while (rows.hasNext()) {
const row = rows.next();
const productTitle = row['ProductTitle'];
const productTypeL1 = row['ProductTypeL1'];
const productTypeL2 = row['ProductTypeL2'];
const productTypeL3 = row['ProductTypeL3'];
const productTypeL4 = row['ProductTypeL4'];
const productTypeL5 = row['ProductTypeL5'];
const customAttribute0 = row['CustomAttribute0'];
const customAttribute1 = row['CustomAttribute1'];
const customAttribute2 = row['CustomAttribute2'];
const customAttribute3 = row['CustomAttribute3'];
const customAttribute4 = row['CustomAttribute4'];
const offerId = '"' + row['OfferId'] + '"';
const brand = row['Brand'];
const productCondition = row['ProductCondition'];
const channel = row['Channel'];
const categoryL1 = '=if("' + row['CategoryL1'] + '"="--";"--";SUBSTITUTE(REPLACE("' + row['CategoryL1'] + '";1;FIND("LEVEL1";"' + row['CategoryL1'] + '")-1;"");"LEVEL1~";""))';
const categoryL2 = '=if("' + row['CategoryL2'] + '"="--";"--";SUBSTITUTE(REPLACE("' + row['CategoryL2'] + '";1;FIND("LEVEL2";"' + row['CategoryL2'] + '")-1;"");"LEVEL2~";""))';
const categoryL3 = '=if("' + row['CategoryL3'] + '"="--";"--";SUBSTITUTE(REPLACE("' + row['CategoryL3'] + '";1;FIND("LEVEL3";"' + row['CategoryL3'] + '")-1;"");"LEVEL3~";""))';
const categoryL4 = '=if("' + row['CategoryL4'] + '"="--";"--";SUBSTITUTE(REPLACE("' + row['CategoryL4'] + '";1;FIND("LEVEL4";"' + row['CategoryL4'] + '")-1;"");"LEVEL4~";""))';
const categoryL5 = '=if("' + row['CategoryL5'] + '"="--";"--";SUBSTITUTE(REPLACE("' + row['CategoryL5'] + '";1;FIND("LEVEL5";"' + row['CategoryL5'] + '")-1;"");"LEVEL5~";""))';
products.push([productTitle, productTypeL1, productTypeL2, productTypeL3, productTypeL4, productTypeL5,
customAttribute0, customAttribute1, customAttribute2, customAttribute3, customAttribute4, offerId, brand, categoryL1, categoryL2, categoryL3, categoryL4, categoryL5, channel, productCondition]);
}
return products;
}
function pushToSpreadsheet(data) {
const sheetName = "products with categories";
const spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
const sheet = spreadsheet.getSheetByName(sheetName);
sheet.clear();
if (data.length > 0) {
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
}
function fetchDataFromGoogleAds() {
const query = `
SELECT
campaign.name,
asset_group.name,
asset_group.status
FROM
asset_group
WHERE
campaign.status = 'ENABLED'
`;
const data = [];
const report = AdsApp.report(query);
const rows = report.rows();
while (rows.hasNext()) {
const row = rows.next();
const campaignName = row['campaign.name'];
const assetGroupName = row['asset_group.name'];
const assetGroupStatus = '=if("' + row['asset_group.status'] + '" = "ENABLED"; "active"; if("' + row['asset_group.status'] + '" = "PAUSED"; "paused"; "FALSE"))';
data.push([campaignName, assetGroupName, assetGroupStatus]);
}
return data;
}
function pushAssetGroupsToSpreadsheet(data) {
const sheetName = "pause activate asset groups";
const spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
const sheet = spreadsheet.getSheetByName(sheetName);
sheet.getRange('A:C').clearContent();
const headers = ["Campaign Name", "Asset Group Name", "Asset Group Status"];
data.unshift(headers);
if (data.length > 0) {
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
}
function fetchFiltersFromGoogleAds() {
const query = `
SELECT
campaign.name,
asset_group.name,
asset_group_listing_group_filter.type,
asset_group_listing_group_filter.case_value.product_item_id.value,
asset_group_listing_group_filter.case_value.product_brand.value,
asset_group_listing_group_filter.case_value.product_bidding_category.id,
asset_group_listing_group_filter.case_value.product_bidding_category.level,
asset_group_listing_group_filter.case_value.product_channel.channel,
asset_group_listing_group_filter.case_value.product_condition.condition,
asset_group_listing_group_filter.case_value.product_type.value,
asset_group_listing_group_filter.case_value.product_type.level,
asset_group_listing_group_filter.case_value.product_custom_attribute.index,
asset_group_listing_group_filter.case_value.product_custom_attribute.value,
asset_group_listing_group_filter.id,
asset_group_listing_group_filter.parent_listing_group_filter
FROM
asset_group_listing_group_filter
WHERE
campaign.status = 'ENABLED'
`;
const data = [];
const report = AdsApp.report(query);
const rows = report.rows();
while (rows.hasNext()) {
const row = rows.next();
const campaignName = row['campaign.name'];
const assetGroupName = row['asset_group.name'];
const type = row['asset_group_listing_group_filter.type'];
const brand = '=if("' + row['asset_group_listing_group_filter.case_value.product_brand.value'] + '"="undefined";"*";"' + row['asset_group_listing_group_filter.case_value.product_brand.value'] + '")';
const productId = '=if("' + row['asset_group_listing_group_filter.case_value.product_item_id.value'] + '"="undefined";"*";"""' + row['asset_group_listing_group_filter.case_value.product_item_id.value'] + '""")';
const channel = '=if("' + row['asset_group_listing_group_filter.case_value.product_channel.channel'] + '"="undefined";"*";"' + row['asset_group_listing_group_filter.case_value.product_channel.channel'] + '")';
const condition = '=if("' + row['asset_group_listing_group_filter.case_value.product_condition.condition'] + '"="undefined";"*";"' + row['asset_group_listing_group_filter.case_value.product_condition.condition'] + '")';
const filterID = row['asset_group_listing_group_filter.id'];
const parentFilterId = '=value(REGEXREPLACE("' + row['asset_group_listing_group_filter.parent_listing_group_filter'] + '"; ".*~"; ""))';
const productTypeL1 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL1"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL1"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '")';
const productTypeL2 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL2"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL2"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '")';
const productTypeL3 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL3"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL3"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '")';
const productTypeL4 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL4"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL4"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '")';
const productTypeL5 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL5"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_type.level'] + '" = "LEVEL5"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_type.value'] + '")';
const categoryL1 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL1"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL1"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '")';
const categoryL2 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL2"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL2"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '")';
const categoryL3 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL3"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL3"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '")';
const categoryL4 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL4"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL4"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '")';
const categoryL5 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL5"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_bidding_category.level'] + '" = "LEVEL5"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_bidding_category.id'] + '")';
const customLabel0 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX0"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX0"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '")';
const customLabel1 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX1"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX1"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '")';
const customLabel2 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX2"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX2"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '")';
const customLabel3 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX3"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX3"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '")';
const customLabel4 = '=if(or(if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX4"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="undefined";if("' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.index'] + '" = "INDEX4"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '"; "*")="*"); "*"; "' + row['asset_group_listing_group_filter.case_value.product_custom_attribute.value'] + '")';
data.push([campaignName, assetGroupName, filterID, parentFilterId, type, productId, brand, channel, condition, productTypeL1, productTypeL2, productTypeL3, productTypeL4, productTypeL5, categoryL1, categoryL2, categoryL3, categoryL4, categoryL5, customLabel0, customLabel1, customLabel2, customLabel3, customLabel4]);
}
return data;
}
function pushFiltersToSpreadsheet(data) {
const sheetName = "product groups import";
const spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
const sheet = spreadsheet.getSheetByName(sheetName);
const headers = ["Campaign Name", "Asset Group Name", "Filter ID", "Parent Filter ID", "Product group Type", "Item ID", "Brand", "Channel", "Condition", "product Type L1", "Product Type L2", "Product Type L3", "Product Type L4", "Product Type L5", "Category L1", "Category L2", "Category L3", "Category L4", "Category L5", "Custom Label 0", "Custom Label 1", "Custom Label 2", "Custom Label 3", "Custom Label 4"];
data.unshift(headers);
sheet.clear();
if (data.length > 0) {
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
}
function findAssetGroupByCampaignAndName(campaignName, assetGroupName) {
const performanceMaxCampaigns = AdsApp.performanceMaxCampaigns().get();
while (performanceMaxCampaigns.hasNext()) {
const campaign = performanceMaxCampaigns.next();
const assetGroups = campaign.assetGroups().get();
while (assetGroups.hasNext()) {
const assetGroup = assetGroups.next();
if (campaign.getName() === campaignName && assetGroup.getName() === assetGroupName) {
return assetGroup;
}
}
}
return null;
}
function pauseAssetGroup(campaignName, assetGroupName) {
const assetGroup = findAssetGroupByCampaignAndName(campaignName, assetGroupName);
if (assetGroup) {
if (!assetGroup.isPaused()) {
assetGroup.pause();
console.log(`AssetGroup with name: ${assetGroup.getName()} has been paused.`);
} else {
console.log(`AssetGroup with name: ${assetGroup.getName()} is already paused.`);
}
} else {
console.log(`AssetGroup with name "${assetGroupName}" in campaign "${campaignName}" not found.`);
}
}
function activateAssetGroup(campaignName, assetGroupName) {
const assetGroup = findAssetGroupByCampaignAndName(campaignName, assetGroupName);
if (assetGroup) {
if (assetGroup.isPaused()) {
assetGroup.enable();
console.log(`AssetGroup with name: ${assetGroup.getName()} has been activated.`);
} else {
console.log(`AssetGroup with name: ${assetGroup.getName()} is already active.`);
}
} else {
console.log(`AssetGroup with name "${assetGroupName}" in campaign "${campaignName}" not found.`);
}
}