(helpers, logger, labels, timer, sender, customizerSpreadSheet) => { const CONFIG = { SCRIPT_NAME: 'Ads - RSA Builder', LOG_CONFIG: { DEBUG_MODE: false, INFO_MODE: true, ERROR_MODE: true, FILE_MODE: false, }, TEST_MODE: false, AD_LABELS: { SALES: 'Ad Text: Sales', NORMAL: 'Ad Text: Normal', }, ADGROUP_LABEL: 'RSA_BUILD', AVAILABLE_TIME: 28, FIELD_THRESHOLD: { DESCRIPTION_1: 2, DESCRIPTION_2: 2, DESCRIPTIONS: 4, HEADLINES: 15, }, TRIGGER_CONFIG: { triggerWeeksMonth: [], triggerDaysWeek: [], triggerHours: [], }, INDICIES: { CUSTOMIZERS: { H1: 2, H2: 3, H3: 4, D1: 5, D2: 6, Capacity: 7, Start_price: 8, Sale_price: 9, Start_date: 10, End_date: 11, Discount: 12, 'Target campaign': 13, }, EXTRA_FIELDS: { HEADLINE_3: 0, DESCRIPTION_1: 1, DESCRIPTION_2: 2, }, }, EXTRA_FIELDS_SHEET_NAME: 'rsa', RSA_NAMED_RANGE: 'rsa_customizer', PINNING_MAP: { HEADLINE_1: 'headlines1', HEADLINE_2: 'headlines2', HEADLINE_3: 'headlines3', DESCRIPTION_1: 'descriptions1', DESCRIPTION_2: 'descriptions2', }, ADGROUP_LIMIT: 5000, }; const getCustomizers = (customSpreadSheet, customizerSheetId) => customSpreadSheet .getSheets() .find((sheet) => String(sheet.getSheetId()) === customizerSheetId) .getDataRange() .getValues() .slice(2) .reduce((acc, row) => { const isRowEmpty = row.slice(2).filter((value) => value.length > 0).length === 0; if (isRowEmpty) { return acc; } const campaignName = row[CONFIG.INDICIES.CUSTOMIZERS['Target campaign']]; if (campaignName.length > 0) { acc[campaignName] = {}; } else { acc.default = {}; } const campaign = acc[campaignName] || acc.default; Object.keys(CONFIG.INDICIES.CUSTOMIZERS).forEach((field) => { const index = CONFIG.INDICIES.CUSTOMIZERS[field]; campaign[field] = row[index]; }); return acc; }, {}); const isEqual = (value1, value2) => { if (value1 === value2) { return true; } const isArrays = value1 instanceof Array && value2 instanceof Array; const isObjects = value1 instanceof Object && value2 instanceof Object; if (isArrays) { return value1.every((value, index) => isEqual(value, value2[index])) && value1.length === value2.length; } if (isObjects) { return Object.keys(value1).every((key) => { if (Object.prototype.hasOwnProperty.call(value2, key)) { return isEqual(value1[key], value2[key]); } return false; }); } return false; }; const getAdRsaCopy = (ad) => { if (!ad) { return; } const adAsType = ad.asType().responsiveSearchAd(); const parse = (acc, { text, pinning }) => { const groupName = CONFIG.PINNING_MAP[pinning]; const group = acc[groupName]; if (group) { group.push(text); } else { acc[groupName] = [text]; } return acc; }; const descriptions = adAsType.getDescriptions().reduce(parse, {}); const headlines = adAsType.getHeadlines().reduce(parse, {}); const path1 = adAsType.getPath1(); const path2 = adAsType.getPath2(); const finalUrl = adAsType.urls().getFinalUrl(); const id = adAsType.getId(); const status = adAsType.isEnabled() ? 'enabled' : 'paused'; const fields = { ...headlines, ...descriptions, finalUrl, id, status, }; if (path1) { fields.path1 = path1; } if (path2) { fields.path2 = path2; } return fields; }; const getAdEtaCopy = (ads) => ads.reduce((acc, ad) => { const adAsType = ad.asType().expandedTextAd(); const headline1 = adAsType.getHeadlinePart1().trim(); const headline2 = adAsType.getHeadlinePart2().trim(); const headline3 = adAsType.getHeadlinePart3().trim(); const description1 = adAsType.getDescription1().trim(); const description2 = adAsType.getDescription2().trim(); const path1 = adAsType.getPath1(); const path2 = adAsType.getPath2(); const finalUrl = adAsType.urls().getFinalUrl(); const isPaused = adAsType.isPaused(); const status = isPaused ? 'paused' : 'enabled'; acc.headlines1.push(headline1); acc.headlines2.push(headline2); acc.headlines3.push(headline3); acc.descriptions1.push(description1); acc.descriptions2.push(description2); acc.statuses.push(status); if (!acc.path1 && !!path1) { acc.path1 = path1; } if (!acc.path2 && !!path2) { acc.path2 = path2; } if (!acc.finalUrl && !!finalUrl) { acc.finalUrl = finalUrl; } return acc; }, { headlines1: [], headlines2: [], headlines3: [], descriptions1: [], descriptions2: [], statuses: [], }); const format = (copy, campaignName, customizers, extraFields) => { const customizersData = customizers ? customizers[campaignName] || customizers.default : {}; const isExistsValue = (value) => !!value; const extraHeadlines3 = extraFields .map((row) => row[CONFIG.INDICIES.EXTRA_FIELDS.HEADLINE_3]) .filter(isExistsValue); const extraDescriptions1 = extraFields .map((row) => row[CONFIG.INDICIES.EXTRA_FIELDS.DESCRIPTION_1]) .filter(isExistsValue); const extraDescriptions2 = extraFields .map((row) => row[CONFIG.INDICIES.EXTRA_FIELDS.DESCRIPTION_2]) .filter(isExistsValue); const unique = (value, index, array) => array.indexOf(value) === index; const parse = (value, customizer) => { if (!customizer) { return value.toString().trim(); } switch (customizer.toLowerCase()) { case 'discount': return typeof customizer === 'number' && Math.abs(value) <= 1 ? `${value * 100}%` : value; case 'start_price': return typeof customizer === 'number' ? `${value}€` : value; default: return value.toString().trim(); } }; const formatDynamicFilds = (field) => { if (field.indexOf('{=') === -1) { return field; } const [customizerString] = field.match(/{.*}/g); const [customizer] = customizerString.match(/(?<=\.).*(?=:)/g); const customizerValue = customizersData[customizer]; const parsedValue = parse(customizerValue, customizer); return field.replace(/{.*}/g, parsedValue); }; const headlines1 = copy.headlines1 .filter(isExistsValue) .filter(unique) .map(formatDynamicFilds) .filter(unique); const headlines2 = copy.headlines2 .filter(isExistsValue) .filter(unique) .map(formatDynamicFilds) .filter(unique) .filter((value) => !headlines1.includes(value)); const headlines3 = copy.headlines3 .filter(isExistsValue) .filter(unique) .map(formatDynamicFilds) .filter(unique) .filter((value) => !headlines2.includes(value)); const descriptions1 = copy.descriptions1 .filter(unique) .filter(isExistsValue) .map(formatDynamicFilds) .filter(unique) .slice(0, CONFIG.FIELD_THRESHOLD.DESCRIPTION_1); const descriptions2 = copy.descriptions2 .filter(unique) .filter(isExistsValue) .map(formatDynamicFilds) .filter(unique) .slice(0, CONFIG.FIELD_THRESHOLD.DESCRIPTION_2); const statusesCount = copy.statuses.reduce((acc, status) => { acc[status] += 1; return acc; }, { enabled: 0, paused: 0, }); const status = statusesCount.paused > statusesCount.enabled ? 'paused' : 'enabled'; const totalHeadlinesCount = headlines1.length + headlines2.length + headlines3.length; const headlinesDiff = CONFIG.FIELD_THRESHOLD.HEADLINES - totalHeadlinesCount; const descriptions1Diff = CONFIG.FIELD_THRESHOLD.DESCRIPTION_1 - descriptions1.length; const descriptions2Diff = CONFIG.FIELD_THRESHOLD.DESCRIPTION_2 - descriptions2.length; if (headlinesDiff > 0) { const extraheadlines = extraHeadlines3.slice(0, headlinesDiff); headlines3.push(extraheadlines); } if (descriptions1Diff > 0) { const extraDesc1 = extraDescriptions1.slice(0, descriptions1Diff); descriptions1.push(extraDesc1); } if (descriptions2Diff > 0) { const extraDesc2 = extraDescriptions2.slice(0, descriptions2Diff); descriptions2.push(extraDesc2); } const { finalUrl, path1, path2, } = copy; const ad = { headlines1, headlines2, headlines3: headlines3.flat().filter(unique), descriptions1: descriptions1.flat().filter(unique), descriptions2: descriptions2.flat().filter(unique), status, }; if (finalUrl) { ad.finalUrl = finalUrl; } if (path1) { ad.path1 = path1; } if (path2) { ad.path2 = path2; } return ad; }; const prepareReport = (reportData, accountName, accountId) => { const headers = ['ACCOUNT', 'CAMPAIGN', 'ADGROUP', 'FIELD', 'MESSAGE'].join(','); const report = reportData.map(({ campaign, adGroup, data }, index) => { const rows = data.map(({ field, message }, rowIndex) => { const row = ['', '', '', field, message]; if (rowIndex === 0) { row[1] = campaign; row[2] = adGroup; } return row; }); if (index === 0) { rows[0][0] = `${accountName}(${accountId})`; } return rows.map((row) => row.join(',')).join('\n'); }); report.unshift(headers); return report.flat().join('\n'); }; const validate = (adCopy) => { const { headlines1, headlines2, headlines3, descriptions1, descriptions2, } = adCopy; let isValid = true; const invalids = []; const headlinesCount = headlines1.length + headlines2.length + headlines3.length; const descriptionsCount = descriptions1.length + descriptions2.length; const headlinsThreshold = CONFIG.FIELD_THRESHOLD.HEADLINES; const descriptionsThreshold = CONFIG.FIELD_THRESHOLD.DESCRIPTIONS; if (headlinesCount < headlinsThreshold) { isValid = false; invalids.push({ field: 'headlines', message: [ `Ο αριθμός των headlines στο Ad RSA πρέπει να είναι - ${headlinsThreshold}`, `ωστόσο ο συνολικός αριθμός των headlines που μαζεύτηκαν από Ads ETA συν extra headlines είναι - ${headlinesCount} και δεν επαρκεί.`, 'Συμπλήρωσε τα extra headlines στο https://drive.google.com/drive/folders/1O35BAliGjN96YHyqKCqjpBVmuzkSElU5 --> φάκελος {account name} --> SpreadSheet --> sheet "rsa"`.', ].join(' '), }); } if (descriptionsCount < descriptionsThreshold) { isValid = false; invalids.push({ field: 'descriptions', message: [ `Ο αριθμός των descriptions στο Ad RSA πρέπει να είναι - ${descriptionsThreshold}`, `ωστόσο ο συνολικός αριθμός των descriptions που μαζεύτηκαν από Ads ETA συν extra descriptions είναι - ${descriptionsCount} και δεν επαρκεί.`, 'Συμπλήρωσε τα extra descriptions στο https://drive.google.com/drive/folders/1O35BAliGjN96YHyqKCqjpBVmuzkSElU5 --> φάκελος {account name} --> SpreadSheet --> sheet "rsa".', ].join(' '), }); } if (!adCopy.finalUrl) { isValid = false; invalids.push({ field: 'finalUrl', message: 'Το FinalUrl δεν μπορεί να είναι κενό', }); } if (!adCopy.status) { invalids.push({ field: 'status', message: 'Εντοπίστηκαν ΕΤΑ διαφημίσεις με ίδιο label (Normal ή Sales) αλλά με διαφορετικές καταστάσεις (ENABLED ή PAUSED)', }); } return { isValid, invalids }; }; const createAd = (adGroup, adCopy, label) => { const { headlines1, headlines2, headlines3, descriptions1, descriptions2, finalUrl, path1, path2, status, } = adCopy; const adBuilder = adGroup .newAd() .responsiveSearchAdBuilder() .withFinalUrl(finalUrl); if (path1) { adBuilder.withPath1(path1); } if (path2) { adBuilder.withPath2(path2); } headlines1.forEach((headline) => adBuilder.addHeadline(headline, 'HEADLINE_1')); headlines2.forEach((headline) => adBuilder.addHeadline(headline, 'HEADLINE_2')); headlines3.forEach((headline) => adBuilder.addHeadline(headline, 'HEADLINE_3')); descriptions1.forEach((description) => adBuilder.addDescription(description, 'DESCRIPTION_1')); descriptions2.forEach((description) => adBuilder.addDescription(description, 'DESCRIPTION_2')); const creationResult = adBuilder.build(); if (!creationResult.isSuccessful()) { logger.info('Ad wasn\'t created.'); logger.error(JSON.stringify(creationResult.getErrors(), null, 2)); return; } const ad = creationResult.getResult(); ad.applyLabel(label); if (status === 'paused') { // eslint-disable-next-line no-undef Utilities.sleep(1000); ad.pause(); } logger.info('Ad was created successfully.'); }; const parseAds = (ads, customizers, extraFields) => { const adGroupsMap = {}; while (ads.hasNext()) { const ad = ads.next(); const adType = ad.getType(); const adGroup = ad.getAdGroup(); const adGroupId = adGroup.getId(); if (!adGroupsMap[adGroupId]) { adGroupsMap[adGroupId] = { adGroup, adGroupAds: { RESPONSIVE_SEARCH_AD: [], EXPANDED_TEXT_AD: [], }, }; } adGroupsMap[adGroupId].adGroupAds[adType].push(ad); } return Object.keys(adGroupsMap).reduce((acc, adGroupId) => { const { adGroup, adGroupAds } = adGroupsMap[adGroupId]; const { RESPONSIVE_SEARCH_AD, EXPANDED_TEXT_AD, } = adGroupAds; const [adRsa] = RESPONSIVE_SEARCH_AD; const adEtaCopy = getAdEtaCopy(EXPANDED_TEXT_AD); const formatedEta = format( adEtaCopy, adGroup.getCampaign().getName(), customizers, extraFields, ); const adRsaCopy = getAdRsaCopy(adRsa); acc[adGroupId] = { adGroup, ads: { eta: formatedEta, rsa: adRsaCopy } }; return acc; }, {}); }; const run = () => { // eslint-disable-next-line no-undef const accountName = AdsApp.currentAccount().getName(); // eslint-disable-next-line no-undef const accountId = AdsApp.currentAccount().getCustomerId(); const accountFullName = `${accountName}(${accountId})`; try { logger.setConfig(CONFIG.LOG_CONFIG); const senderData = helpers.getSenderDataByScriptName(CONFIG.SCRIPT_NAME); sender.getService(senderData.sender_type); const { ads_configs_folder_id: adsConfigFolderId, ads_customizer_sheet_id: adsCustomizerSheetId, ...accountData } = helpers.findDataByAccountId(accountId); const customizers = adsCustomizerSheetId ? getCustomizers(customizerSpreadSheet, adsCustomizerSheetId) : null; // eslint-disable-next-line no-undef const extraFieldsFile = DriveApp .getFolderById(adsConfigFolderId) .searchFiles('mimeType = "application/vnd.google-apps.spreadsheet"') .next(); // eslint-disable-next-line no-undef const extraFields = SpreadsheetApp .open(extraFieldsFile) .getSheetByName(CONFIG.EXTRA_FIELDS_SHEET_NAME) .getDataRange() .getValues() .slice(1); const errors = []; const isTime = timer.checkTimer(CONFIG.TRIGGER_CONFIG); if (!isTime) { logger.info('Stopping execution due time configuration...'); return ''; } const adsLabels = Object.keys(CONFIG.AD_LABELS).reduce((acc, label) => { acc[label.toLowerCase()] = labels.createAdsLabel(CONFIG.AD_LABELS[label]); return acc; }, {}); const adGroupLabel = labels.createAdsLabel(CONFIG.ADGROUP_LABEL); const adGroupLabelId = adGroupLabel.getId(); logger.info('----------- Start execution -----------'); // eslint-disable-next-line no-undef const salesAds = AdsApp .ads() .withCondition('ad_group.status != REMOVED') .withCondition('campaign.status = ENABLED') .withCondition('campaign.name NOT REGEXP_MATCH("Dsa")') .withCondition('campaign.advertising_channel_type = SEARCH') .withCondition('ad_group.type != SEARCH_DYNAMIC_ADS') .withCondition('ad_group_ad.status != REMOVED') .withCondition('ad_group_ad.ad.type IN (RESPONSIVE_SEARCH_AD, EXPANDED_TEXT_AD)') .withCondition(`ad_group.labels CONTAINS NONE ('customers/${accountId}/labels/${adGroupLabelId}')`) .withCondition(`ad_group_ad.labels CONTAINS ANY ('customers/${accountId}/labels/${adsLabels.sales.getId()}')`) .orderBy('campaign.name') .orderBy('ad_group.name') .withLimit(CONFIG.ADGROUP_LIMIT) .get(); // eslint-disable-next-line no-undef const normalAds = AdsApp .ads() .withCondition('ad_group.status != REMOVED') .withCondition('campaign.status = ENABLED') .withCondition('campaign.name NOT REGEXP_MATCH("Dsa")') .withCondition('campaign.advertising_channel_type = SEARCH') .withCondition('ad_group.type != SEARCH_DYNAMIC_ADS') .withCondition('ad_group_ad.status != REMOVED') .withCondition('ad_group_ad.ad.type IN (RESPONSIVE_SEARCH_AD, EXPANDED_TEXT_AD)') .withCondition(`ad_group.labels CONTAINS NONE ('customers/${accountId}/labels/${adGroupLabelId}')`) .withCondition(`ad_group_ad.labels CONTAINS ANY ('customers/${accountId}/labels/${adsLabels.normal.getId()}')`) .orderBy('campaign.name') .orderBy('ad_group.name') .withLimit(CONFIG.ADGROUP_LIMIT) .get(); const salesAdGroups = parseAds(salesAds, customizers, extraFields); const normalAdGroups = parseAds(normalAds, customizers, extraFields); const adGroupsMap = {}; Object.keys(salesAdGroups).forEach((key) => { const { adGroup, ads } = salesAdGroups[key]; const { isValid, invalids } = validate(ads.eta); errors.push({ campaigna: adGroup.getCampaign().getName(), adgroup: adGroup.getName(), data: invalids, }); if (isValid) { adGroupsMap[key] = { adGroup, ads: { sales: ads, }, }; } }); Object.keys(normalAdGroups).forEach((key) => { const { adGroup, ads } = normalAdGroups[key]; const { isValid, invalids } = validate(ads.eta); errors.push({ campaigna: adGroup.getCampaign().getName(), adgroup: adGroup.getName(), data: invalids, }); if (isValid) { let adgr = adGroupsMap[key]; if (!adgr) { adGroupsMap[key] = { adGroup, ads: {} }; adgr = adGroupsMap[key]; } adgr.ads.normal = ads; } }); const adGroups = Object.keys(adGroupsMap).map((key) => adGroupsMap[key]); const adGroupsLength = adGroups.length; logger.info(`The count of adGroups is - ${adGroupsLength}`); if (adGroupsLength === 0) { logger.info(`All AdGroups have checked. Remove label '${CONFIG.ADGROUP_LABEL}'.`); adGroupLabel.remove(); return; } adGroups.forEach(({ adGroup, ads }) => { logger.info(`Check Ad RSA on AdGroup '${adGroup.getName()}' on campaign '${adGroup.getCampaign().getName()}'`); Object.keys(CONFIG.AD_LABELS).forEach((key) => { const label = CONFIG.AD_LABELS[key]; logger.info(`Check ads with label '${label}'.`); const labelAds = ads[key.toLowerCase()]; if (!labelAds) { logger.info('No ads. Skipping...'); return; } const { eta, rsa } = labelAds; const { status: etaStatus, ...etaRest } = eta; const { id, status: rsaStatus, ...rsaRest } = rsa || {}; const isEqualAds = isEqual(etaRest, rsaRest); if (!rsa) { return createAd(adGroup, eta, label); } if (!isEqualAds) { // eslint-disable-next-line no-undef const adRsa = AdsApp.ads().withIds([[adGroup.getId(), rsa.id]]).get().next(); adRsa.remove(); return createAd(adGroup, eta, label); } if (etaStatus !== rsaStatus) { // eslint-disable-next-line no-undef const adRsa = AdsApp.ads().withIds([[adGroup.getId(), rsa.id]]).get().next(); if (etaStatus === 'enabled') { adRsa.enable(); } else { adRsa.pause(); } } }); labels.applyAdsLabel(adGroup, CONFIG.ADGROUP_LABEL); }); if (errors.length > 0) { const reportSubject = `Google Ads: Exceptions during create Ads RSA on account ${accountFullName}`; const report = prepareReport(errors, accountName, accountId); if (CONFIG.TEST_MODE) { accountData.clickup = { automations_list_id: '31853877', automations_list_email: 'add.task.31853877.u-4686461.869bb49e-26b3-4638-b97e-e3baae0853a9@tasks.clickup.com', }; accountData.manager = { email: 'ipilipchuk@omnicliq.com', clickup_id: '4686461', }; } sender.send({ recipient: accountData, subject: reportSubject, tags: ['GA', 'Automations'], content_type: senderData.content_type, attachment: report, }); } logger.info('----------- Finish execution -----------'); } catch (error) { const subject = `Script name: ${CONFIG.SCRIPT_NAME}. Exceptions during create Ads RSA on account ${accountFullName}`; const exceptionNotice = `${error.message}\nException stack: ${error.stack}`; helpers.createClickUpTask({ name: subject, text_content: exceptionNotice }); throw error; } }; run(); };