Single keyword ad groups are one of the best ways to structure your account to maximise your budget and conversions. However, they can be a pain to manage – but – not any more!
Creating campaigns, ad groups and keywords for each and every exact match search term is a time sink.
Thanks to our free auto SKAG script you can now – at the click of a button – let automation do the work for you. The script will look through your search query report for keywords that convert, have volume and meet or beat your target CPA or ROAS.
This script will then extract those keywords and create new ad groups (with ads) in your destination campaign.
Real nice and easy.
Auto SKAGs away!
Take a look at the video instructions below:
After watching the video, you’re going to need two files to get this running.
The script code itself and the Google Sheet that controls each account and campaign targets.
Download the files
1) Make a copy of the Google Sheet template:
https://docs.google.com/spreadsheets/d/1U1c1kQdKNXmwRLgDBvErIq1wclYxPRq8aa0Mu6R46sU/
2) Copy and paste the Google Ads script in to your MCC bulk actions area:
/***** * * AdEvolver Automated SKAG Builder * * * @version: 1.01 * AdWords script maintained on adevolver.com * ***************************************************/ var INPUT_SHEET_URL = 'https://docs.google.com/spreadsheets/d/1U1c1kQdKNXmwRLgDBvErIq1wclYxPRq8aa0Mu6R46sU/'; var INPUT_TAB_NAME = 'SQR to SKAGs'; // Do not edit anything below this line unless you know what you are doing var DEBUG = false; function main() { var SETTINGS = scanForAccounts(); var ids = Object.keys(SETTINGS); if(DEBUG){ids = ["123-456-7890"]}; if(ids.length == 0) { Logger.log('No Rules Specified'); return; }; MccApp.accounts().withIds(ids).withLimit(50).executeInParallel('runRows','callBack',JSON.stringify(SETTINGS)); } function scanForAccounts() { log("getting settings...") var map = {}; var controlSheet = SpreadsheetApp.openByUrl(INPUT_SHEET_URL).getSheetByName(INPUT_TAB_NAME) var data = SpreadsheetApp.openByUrl(INPUT_SHEET_URL).getSheetByName(INPUT_TAB_NAME).getDataRange().getValues(); data.shift(); data.shift(); data.shift(); //log(JSON.stringify(data)) var HEADER = ['ID', 'NAME', 'EMAILS', 'FLAG', 'DATE_RANGE_LITERAL', 'N','SOURCE_CAMPAIGN', 'DESTINATION_CAMPAIGN','SHARED_NEGATIVE_LIST', 'CPA_ROAS', 'TARGET', 'MIN_IMPRESSIONS', 'LABEL_NAME','KEYWORD_LEVEL_URLS','LOG_SHEET_URL','LOGS_COLUMN']; var logsColumn = 0; var col = 5 while(controlSheet.getRange(3, col).getValue()){ logsColumn = controlSheet.getRange(3, col).getValue() == "Logs" ? col : 0; if(logsColumn>0){break;} col++; } for(var k in data) { //if "run script" is not set to "yes", continue. if(data[k][0] == '' || data[k][3].toLowerCase() != 'yes') { continue; } var rowNum = parseInt(k,10) + 4; var id = data[k][0]; var rowId = id+"/"+rowNum; map[id] = map[id] || {} map[id][rowId] = { 'ROW_NUM': (parseInt(k,10) + 4) }; for(var j in HEADER) { if(HEADER[j] == "LOGS_COLUMN"){ map[id][rowId][HEADER[j]] = logsColumn; continue; } map[id][rowId][HEADER[j]] = data[k][j]; } } return map; } function callBack() { // Do something here Logger.log('Finished'); } function runRows(INPUT){ log("running rows") var SETTINGS = JSON.parse(INPUT)[AdWordsApp.currentAccount().getCustomerId().toString()] for(var rowId in SETTINGS){ runScript(SETTINGS[rowId]); } } function runScript(SETTINGS) { log('Script Started'); //**** 0: Check the label exists, create if it not checkLabel(SETTINGS); //**** 1: Process settings from the sheet parseDateRange(SETTINGS); SETTINGS.EMAILS = !SETTINGS.EMAILS || SETTINGS.EMAILS == "" ? [] : SETTINGS.EMAILS.split(",").map(Function.prototype.call, String.prototype.trim) SETTINGS.KEYWORD_LEVEL_URLS = SETTINGS.KEYWORD_LEVEL_URLS == "Yes" ? true : false; SETTINGS.PREVIEW_MODE = AdWordsApp.getExecutionInfo().isPreview(); log(JSON.stringify(SETTINGS)) var now = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMM dd, yyyy HH:mm:ss') //**** 2: Pull search queries based on sheet filters (ID: Query). Store query data. var queries = getQueries(SETTINGS); log("Returned " + Object.keys(queries).length + " queries") //log("queries: " + JSON.stringify(queries)) //**** 3: Remove existing keywords (if the query already exists in the dest. campaign, remove it from the list) removeExistingKeywords(queries, SETTINGS); //log("queries: " + JSON.stringify(queries)) log("After removals we now have " + Object.keys(queries).length + " queries") //**** 4: Create adgroup list, include adgroups which need creating (toCreate = true/false). ID: adgroup name //use the adgroup report to see which adgroups exist var adGroups = getAdGroupList(queries, SETTINGS) // return an object of names //log("adGroups: " + JSON.stringify(adGroups)) //START CREATION/BUILDING //Check there is enough time at this point if(AdWordsApp.getExecutionInfo().getRemainingTime() < 600){ log("Not enough time to process the builds for this row.") return } //**** 5: Create the necessary adgroups in the dest. Campaign. buildAdGroups(adGroups,SETTINGS) //5.1: now that the adgroups have been created, grab their IDs. We'll use them in the selecter later adGroups = getAdGroupIds(queries, SETTINGS) //log(adGroups) //**** 6: Add the keywords to the dest campaign addKeywords(adGroups,queries,SETTINGS) //**** 7: Copy ads from the source adgroups to the destination adgroups and enable //**** 7.1: Grab ad information var adInformation = grabAdInformation(queries, SETTINGS) //log(JSON.stringify(adInformation)) //**** 7.2: Create the ads createAds(adGroups, queries, adInformation, SETTINGS) //**** 8: Add the query as a negative keyword (exact) to a shared list addNegativesToSharedList(queries, SETTINGS);//SHARED_NEGATIVE_LIST //**** 9: Log & email var tab = SpreadsheetApp.openByUrl(INPUT_SHEET_URL).getSheetByName(INPUT_TAB_NAME); //grab the editors from the sheet, convert to array var editors = tab.getRange(1, SETTINGS.LOGS_COLUMN).getValue(); editors = editors.split(","); editors = editors.map(Function.prototype.call, String.prototype.trim) var logTabNames = ["Queries"]; var totalChanges = Object.keys(queries).length if(!totalChanges==0){ if(!SETTINGS.LOG_SHEET_URL) { var reportName = SETTINGS.NAME + ' - ' + INPUT_TAB_NAME + ' Results'; var logSS = SpreadsheetApp.create(reportName); SETTINGS.LOG_SHEET_URL = logSS.getUrl(); //create a tab for each set of changes for(var i in logTabNames){ if(i==0){ logSS.getSheets()[i].setName(logTabNames[i]); continue; } } for(var ed in editors){ try{ logSS.addEditor(editors[ed]); }catch(e){ log("Error: could not add email address " + editors[ed] + " as an output sheet editor. Message: " + e) } } for(var i in SETTINGS.EMAILS){ try{ logSS.addEditor(SETTINGS.EMAILS[i]); }catch(e){ log("Error: could not add email address " + editors[ed] + " as an output sheet editor. Message: " + e) } } }else{ var logSS = SpreadsheetApp.openByUrl(SETTINGS.LOG_SHEET_URL); } log("Log sheet URL: " + logSS.getUrl()) } var headersAndKeys = { "Date": "Date", "CampaignName": "Campaign", "AdGroupName": "AdGroup", "Query": "Query", "Clicks": "Clicks", "Impressions": "Impressions", "Cost":"Cost", "AllConversions":"All Conversions", "CPA": "CPA", "ROAS": "Conv. Value/Cost" } var logArray = [] for(var query in queries){ var row = [] for(var key in headersAndKeys){ //log(key) //log(queries[query][key]) if(key=="Date"){row.push(now);continue;} row.push(queries[query][key]) } logArray.push(row) } var header = Object.keys(headersAndKeys) if(totalChanges == 0) { log('No Changes'); tab.getRange(SETTINGS.ROW_NUM, SETTINGS.LOGS_COLUMN, 1, 2).setValues([['No Queries Found', now]]); log('Finished'); return; } var outputSheet = logSS.getActiveSheet() outputSheet.clear() outputSheet.getRange(1, 1, 1, header.length).setValues([header]) log(logArray.length) outputSheet.getRange(2, 1, logArray.length,header.length).setValues(logArray) var previewMessage = AdWordsApp.getExecutionInfo().isPreview() ? " (Preview mode, no changes made)" : ""; var results = [SETTINGS.LOG_SHEET_URL, totalChanges+ ' Queries Moved'+previewMessage, now ]; tab.getRange(SETTINGS.ROW_NUM, SETTINGS.LOGS_COLUMN-1, 1, results.length).setValues([results]); if(!DEBUG && !AdWordsApp.getExecutionInfo().isPreview()){ emailSheet(SETTINGS.EMAILS, SETTINGS.LOG_SHEET_URL, logTabNames, totalChanges, SETTINGS) } } //**** 2: Pull search queries based on sheet filters (ID: Query). Store query data. function getQueries(SETTINGS){ log("pulling search queries") var map = {} var whereStatement = "WHERE "; var whereStatementsArray = []; whereStatementsArray.push(' CampaignName = "' + SETTINGS.SOURCE_CAMPAIGN.replace(/"/g,'\\\"') + '" '); whereStatementsArray.push(' AND Impressions >= ' + SETTINGS.MIN_IMPRESSIONS); //if(DEBUG){whereStatementsArray.push(' AND Clicks >= ' + 100);} //log("whereStatementsArray: " + whereStatementsArray) whereStatement += whereStatementsArray.join(" "); whereStatement = whereStatement == "WHERE " ? "" : whereStatement; var during = 'during '+SETTINGS.DATE_RANGE; var OPTIONS = { includeZeroImpressions : false }; var cols = ['Query','CampaignName','AdGroupName','AdGroupId','AllConversionValue','Impressions','Clicks','Cost','AllConversions','FinalUrl']; var reportName = 'SEARCH_QUERY_PERFORMANCE_REPORT'; var query = ['select',cols.join(','),'from',reportName, whereStatement, during].join(' '); //log(query) var reportIter = AdWordsApp.report(query, OPTIONS).rows(); while(reportIter.hasNext()){ var row = reportIter.next(); //log(row.CampaignName) row.Impressions = parseInt(row.Impressions,10); row.Clicks = parseInt(row.Clicks,10); //row.AllConversions = parseFloat(row.AllConversions) < 1 && parseFloat(row.AllConversions) > 0 ? Math.ceil(parseFloat(row.AllConversions.toString().replace(/,/g,''),10)) : Math.round(parseFloat(row.AllConversions.toString().replace(/,/g,''),10)); row.CTR = row.Impressions == 0 ? 0 : round((row.Clicks / row.Impressions),4); row.Cost = parseFloat(row.Cost.toString().replace(/,/g,'')); row.CPA = row.AllConversions == 0 ? 0 : round((row.Cost / row.AllConversions),2); row.AllConversionValue = parseFloat(row.AllConversionValue.toString().replace(/,/g, '')); row.ROAS = row.Cost == 0 ? 0 : round((row.AllConversionValue / row.Cost),2); row.ConversionRate = row.Conversions > 0 ? row.Conversions/row.Clicks : 0; row.aCPC = row.Clicks == 0 ? 0 : round((row.Cost / row.Clicks),4); row.Query = row.Query.toLowerCase(); if(row.AllConversions == 0){ continue; } //CPA/ROAS check if(SETTINGS.CPA_ROAS=="ROAS"){ if(row.ROAS < SETTINGS.TARGET){ continue; } }else if(SETTINGS.CPA_ROAS=="CPA"){ if(row.CPA > SETTINGS.TARGET){ continue; } }else{ log("Error: strategy not recognised. Please select either ROAS (conv. value/cost) or CPA"); continue } map[row.Query] = row; } return map; } //**** 3: Remove existing keywords (if the query already exists in the dest. campaign, remove it from the list) function removeExistingKeywords(queries, SETTINGS){ log("removing existing queries") //pull destination campaign keywords, then compare to the queries //remove the queries which: // 1) Already exist var whereStatement = "WHERE "; var whereStatementsArray = []; whereStatementsArray.push(' CampaignName = "' + SETTINGS.DESTINATION_CAMPAIGN.replace(/"/g,'\\\"') + '" '); whereStatementsArray.push(" and AdGroupStatus = ENABLED and Status = ENABLED") whereStatement += whereStatementsArray.join(" "); whereStatement = whereStatement == "WHERE " ? "" : whereStatement; var OPTIONS = { includeZeroImpressions : true }; var cols = ['Criteria','CampaignName','AdGroupName','KeywordMatchType']; var reportName = 'KEYWORDS_PERFORMANCE_REPORT'; var query = ['select',cols.join(','),'from',reportName,whereStatement].join(' '); //log(query) var reportIter = AdWordsApp.report(query, OPTIONS).rows(); var map = {} while(reportIter.hasNext()){ var row = reportIter.next(); if(row.KeywordMatchType != "Exact"){continue;} map[row.Criteria] = row.KeywordMatchType //check the matching adgroup has a keyword/ad before deleting //if(!hasAdAndKeyword(row.Criteria, SETTINGS)){continue;} } //log("Destination keywords: " + JSON.stringify(map)) for(var kw in map){ if(typeof queries[kw] != "undefined"){ delete queries[kw] } } return queries } function hasAdAndKeyword(adGroup, SETTINGS){ //check if an adgroup has a keyword and at least 1 ad var bool = true; var whereStatement = "WHERE "; var whereStatementsArray = []; whereStatementsArray.push(' CampaignName = "' + SETTINGS.DESTINATION_CAMPAIGN.replace(/"/g,'\\\"') + '" '); whereStatementsArray.push(' and AdGroupName = "' + adGroup.replace(/"/g,'\\\"') + '" '); whereStatementsArray.push(" and AdGroupStatus IN ['ENABLED','PAUSED'] "); whereStatementsArray.push(" and Status IN ['ENABLED'] "); whereStatement += whereStatementsArray.join(" "); whereStatement = whereStatement == "WHERE " ? "" : whereStatement; var OPTIONS = { includeZeroImpressions : true }; var cols = ['AdGroupName']; var reports = ['KEYWORDS_PERFORMANCE_REPORT','AD_PERFORMANCE_REPORT'] for(var r in reports){ var reportName = reports[r]; var query = ['select',cols.join(','),'from',reportName,whereStatement].join(' '); var reportIter = AdWordsApp.report(query, OPTIONS).rows(); if(!reportIter.hasNext()){ bool=false; } } //log("checking adgroup "+adGroup+" and the result is: "+bool+"!") return bool; } //**** 4: Create adgroup list, include adgroups which need creating (toCreate = true/false). ID: adgroup name function getAdGroupList(queries, SETTINGS){ log("creating adgroup list") var adGroups = {} for(var q in queries){ adGroups[q] = {}; adGroups[q].info = getAdGroupInfo(q, SETTINGS.DESTINATION_CAMPAIGN) adGroups[q].info.bid = queries[q].aCPC } return adGroups } function getAdGroupInfo(adGroup, campaign){ //return if it exists //and if so, it's status var whereStatement = "WHERE "; var whereStatementsArray = []; whereStatementsArray.push(' CampaignName = "' + campaign.replace(/"/g,'\\\"') + '" '); whereStatementsArray.push(' and AdGroupName = "' + adGroup.replace(/"/g,'\\\"') + '" '); whereStatementsArray.push(" and AdGroupStatus IN ['ENABLED','PAUSED'] "); whereStatement += whereStatementsArray.join(" "); whereStatement = whereStatement == "WHERE " ? "" : whereStatement; var OPTIONS = { includeZeroImpressions : true }; var cols = ['AdGroupName','AdGroupStatus']; var reportName = 'ADGROUP_PERFORMANCE_REPORT'; var query = ['select',cols.join(','),'from',reportName,whereStatement].join(' '); var ret = {} var reportIter = AdWordsApp.report(query, OPTIONS).rows(); if(!reportIter.hasNext()){ ret[adGroup] = {} ret[adGroup].exists = false } while(reportIter.hasNext()){ var row = reportIter.next(); row.AdGroupName = row.AdGroupName.toLowerCase(); ret[row.AdGroupName] = {} ret[row.AdGroupName].exists = true ret[row.AdGroupName].status = row.AdGroupStatus } return ret[adGroup] } //START CREATION/BUILDING //**** 5: Create the necessary adgroups in the dest. Campaign. return ID based adGroups [adGroupId, adGroupId...] //if the adgroup exsists but is paused, enable it function buildAdGroups(adGroups,SETTINGS){ var ret = [] log("creating adgroups...") var campaigns = AdWordsApp.campaigns().withCondition("Name = '"+SETTINGS.DESTINATION_CAMPAIGN+"'").get(); var numEnts = campaigns.totalNumEntities(); //log("numEnts (dest campaigns...): " + numEnts) if(numEnts==0){ log("destination campaign not found"); return; }else if(numEnts>1){ log("multiple destination campaigns found, is the campaign name unique?") return; } var campaign = campaigns.next(); var campaignAdGroups = campaign.adGroups().withCondition("Status IN [ENABLED,PAUSED]").get(); var currentAdGroups = {} while(campaignAdGroups.hasNext()){ var campaignAdGroup = campaignAdGroups.next(); var campaignAdGroupName = campaignAdGroup.getName() var campaignAdGroupId = campaignAdGroup.getId(); if(!adGroups[campaignAdGroupName]){ //this adgroup isn't in the list, continue continue; } if(adGroups[campaignAdGroupName].info.exists && adGroups[campaignAdGroupName].info.status == "paused"){ campaignAdGroup.enable() ret.push(campaignAdGroupId) adGroups[campaignAdGroupName].info.status = "enabled" } } for(var adGroupName in adGroups){ if(adGroups[adGroupName].info.exists && adGroups[adGroupName].info.status == "enabled"){continue;} var adGroupCpc = adGroups[adGroupName].info.bid if(adGroupCpc==0){continue;}//this won't be the case in reality, but skips non-clicked queries during testing //log("creating " + adGroupName + " with bid of " + adGroupCpc); var build = campaign.newAdGroupBuilder().withName(adGroupName).withCpc(adGroupCpc).build(); var success = build.isSuccessful(); if(success && SETTINGS.LABEL_NAME!=""){ build.getResult().applyLabel(SETTINGS.LABEL_NAME); } var errors = build.getErrors(); if(!success){ log("There was a problem creating the adgroup "+adGroupName +", error log: " + errors); }else{ ret.push(build.getResult().getId()) } } // return ret; } //5.1. grab IDs... function getAdGroupIds(queries, SETTINGS){ var whereStatement = "WHERE "; var whereStatementsArray = []; whereStatementsArray.push(' CampaignName = "' + SETTINGS.DESTINATION_CAMPAIGN.replace(/"/g,'\\\"') + '" '); whereStatementsArray.push(" and AdGroupStatus IN ['ENABLED','PAUSED'] "); whereStatement += whereStatementsArray.join(" "); whereStatement = whereStatement == "WHERE " ? "" : whereStatement; var OPTIONS = { includeZeroImpressions : true }; var cols = ['AdGroupName','AdGroupId']; var reportName = 'ADGROUP_PERFORMANCE_REPORT'; var query = ['select',cols.join(','),'from',reportName,whereStatement].join(' '); var ret = [] log(query) var reportIter = AdWordsApp.report(query, OPTIONS).rows(); while(reportIter.hasNext()){ var row = reportIter.next(); //if the adgroup is in the queries list, return the ID if(queries[row.AdGroupName]){ ret.push(row.AdGroupId) } } return ret } //**** 6: Add the keywords to the dest campaign function addKeywords(adGroups,queries,SETTINGS){ log("creating keywords...") var adGroups = AdWordsApp.adGroups().withIds(adGroups).get(); while(adGroups.hasNext()){ var adGroup = adGroups.next(); var adGroupName = adGroup.getName(); var keyword = adGroup.keywords().withCondition("Status = ENABLED").get(); if(keyword.hasNext()){ var keywordText = keyword.next().getText().replace("[","").replace("]",""); log(keywordText) if(keywordText==adGroupName){ log("keyword already exists, no need to create") continue } } var text = "["+adGroupName+"]"; if(SETTINGS.KEYWORD_LEVEL_URLS){ var build = adGroup.newKeywordBuilder().withText(text).withCpc(queries[adGroupName].aCPC).withFinalUrl(queries[adGroupName].FinalUrl).build() }else{ log("building keyword "+text); var build = adGroup.newKeywordBuilder().withText(text).withCpc(queries[adGroupName].aCPC).build() } build.getResult().applyLabel(SETTINGS.LABEL_NAME) } } //**** 7: Copy ads from the source adgroups to the destination adgroups and enable //**** 7.1: Grab ad information function grabAdInformation(queries, SETTINGS){ log("grabbing ad information...") //get source adgroup ids var adGroupIds = [] for(var query in queries){ adGroupIds.push(queries[query].AdGroupId) } var adsObject = {} //use IDs to select adgroups, and populate object with ad information //split by: //source ad group name: //ad1, ad2, etc. var adGroups = AdWordsApp.adGroups().withIds(adGroupIds).get(); while(adGroups.hasNext()){ var adGroup = adGroups.next(); var adGroupName = adGroup.getName() var ads = adGroup.ads().withCondition("Status = ENABLED").withCondition("Type = EXPANDED_TEXT_AD").get() var i = 1; while(ads.hasNext()){ var ad = ads.next(); var adId = String("ad"+i) adsObject[adGroupName] = adsObject[adGroupName] || {}; adsObject[adGroupName][adId] = {} adsObject[adGroupName][adId].headline1 = ad.getHeadlinePart1(); adsObject[adGroupName][adId].headline2 = ad.getHeadlinePart2(); adsObject[adGroupName][adId].path1 = ad.getPath1()==null ? "" : ad.getPath1(); adsObject[adGroupName][adId].path2 = ad.getPath2()==null ? "" : ad.getPath2(); adsObject[adGroupName][adId].description = ad.getDescription(); i++; } } return adsObject; } //**** 7.2: Create the ads function createAds(adGroups, queries, adInformation, SETTINGS){ log("creating ads...") var adGroups = AdWordsApp.adGroups().withIds(adGroups).get() while(adGroups.hasNext()){ var adGroup = adGroups.next(); var adGroupName = adGroup.getName(); var destinationAdGroupName = queries[adGroupName].AdGroupName //log("looking at adgroup " + adGroupName) if(adGroup.ads().withCondition("Status = ENABLED").get().hasNext()){ log("the adgroup "+adGroupName+" already had ads, no need to create any") continue; } for(var ad in adInformation[destinationAdGroupName]){ //log(JSON.stringify(adInformation[destinationAdGroupName][ad])) //log(ad) var newAd = adGroup.newAd().expandedTextAdBuilder() .withHeadlinePart1(adInformation[destinationAdGroupName][ad].headline1) .withHeadlinePart2(adInformation[destinationAdGroupName][ad].headline2) .withPath1(adInformation[destinationAdGroupName][ad].path1) .withPath2(adInformation[destinationAdGroupName][ad].path2) .withDescription(adInformation[destinationAdGroupName][ad].description) .withFinalUrl(queries[adGroupName].FinalUrl)//get the URL from the query, not the ad, just incase the final URL was at keyword level .build(); //log(newAd.getResult()) } } } function addNegativesToSharedList(queries, SETTINGS){ log("adding negatives to shared list...") var listIter = AdWordsApp.negativeKeywordLists().withCondition("Name = '"+ SETTINGS.SHARED_NEGATIVE_LIST +"'").get() if(listIter.hasNext()){ var negativeList = listIter.next(); }else{ log("the shared negative list can't be found") } var negList = Object.keys(queries) var exactNegList = [] for(var n in negList){ exactNegList.push("["+negList[n]+"]") } negativeList.addNegativeKeywords(exactNegList) } function parseDateRange(SETTINGS) { var YESTERDAY = getAdWordsFormattedDate(1, 'yyyyMMdd'); SETTINGS.DATE_RANGE = '20000101,' + YESTERDAY; if(SETTINGS.DATE_RANGE_LITERAL == 'LAST_N_DAYS') { SETTINGS.DATE_RANGE = getAdWordsFormattedDate(SETTINGS.N, 'yyyyMMdd') + ',' + YESTERDAY; } if(SETTINGS.DATE_RANGE_LITERAL == 'LAST_N_MONTHS') { var now = new Date(Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), 'MMM dd, yyyy HH:mm:ss')); now.setHours(12); now.setDate(0); var TO = Utilities.formatDate(now, 'PST', 'yyyyMMdd'); now.setDate(1); var counter = 1; while(counter < SETTINGS.N) { now.setMonth(now.getMonth()-1); counter++; } var FROM = Utilities.formatDate(now, 'PST', 'yyyyMMdd'); SETTINGS.DATE_RANGE = FROM + ',' + TO; } } function emailSheet(emails, sheetUrl, logTabNames, numChanges, SETTINGS) { if(emails.length == 0 || !emails.length || emails == ""){log("No emails found");return;} var accountName = SETTINGS.NAME; var previewMessage = SETTINGS.PREVIEW_MODE ? "Preview mode, no changes made." : "" var subject = accountName + " - " + INPUT_TAB_NAME + " " + previewMessage; var message = "For more information, see the Google Spreadsheet: " + sheetUrl + "<br><br>"; for(var s in logTabNames){ var outputSheet = SpreadsheetApp.openByUrl(SETTINGS.LOG_SHEET_URL).getSheetByName(logTabNames[s]); var values = outputSheet.getDataRange().getValues(); message += '<table style="background-color:white;border-collapse:collapse;" border = 1 cellpadding = 5><tr>'; for (var row=0;row<values.length;++row){ for(var col = 0;col<values[0].length;++col){ message += isNaN(values[row][col])||values[row][col]==""||col == 0? '<td>'+values[row][col]+'</td>': '<td>'+Math.round10(values[row][col], -2)+'</td>'; } message += '</tr><tr>'; } message += '</tr></table><br><br>'; } for(var email_i in emails){ MailApp.sendEmail({ to: emails[email_i], subject: subject, htmlBody: message }); } } function decimalAdjust(type, value, exp) { // If the exp is undefined or zero... if (typeof exp === 'undefined' || +exp === 0) { return Math[type](value); } value = +value; exp = +exp; // If the value is not a number or the exp is not an integer... if (isNaN(value) || !(typeof exp === 'number' && exp % 1 === 0)) { return NaN; } // If the value is negative... if (value < 0) { return -decimalAdjust(type, -value, exp); } // Shift value = value.toString().split('e'); value = Math[type](+(value[0] + 'e' + (value[1] ? (+value[1] - exp) : -exp))); // Shift back value = value.toString().split('e'); return +(value[0] + 'e' + (value[1] ? (+value[1] + exp) : exp)); } function checkLabel(SETTINGS){ //if the label from the sheet doesn't exist, create it var labelName = SETTINGS.LABEL_NAME; if(labelName == ""){return;} var labels = AdWordsApp.labels().get(); var exists = false while(labels.hasNext()){ var label = labels.next(); if(label.getName() == labelName){ exists = true } } if(!exists){ AdWordsApp.createLabel(labelName); } } if (!Math.round10) { Math.round10 = function(value, exp) { return decimalAdjust('round', value, exp); }; } /** * Get AdWords Formatted date for n days back * @param {int} d - Numer of days to go back for start/end date * @return {String} - Formatted date yyyyMMdd **/ function getAdWordsFormattedDate(d, format){ var date = new Date(); date.setDate(date.getDate() - d); return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format); } function log(msg) { Logger.log(AdWordsApp.currentAccount().getName() + ' - ' + msg); } function round(num,n) { return +(Math.round(num + "e+"+n) + "e-"+n); }