// @license Copyright 2016, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
/**
* @name ETA Transition Helper.
*
* @overview An AdWords Script that exports standard text ads from an account
* to a Spreadsheet and then imports newly created expanded text ads
* back to the AdWords account.
*
* see https://developers.google.com/adwords/scripts/docs/solutions/mccapp-eta-transition-helper
* for more details.
*
* @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 1.0
*
* @changelog
* - version 1.0
* - Released initial version.
*/
//////////////////////////////////////////////////////////////////////////
//////////////////////////// CONFIGURATION ///////////////////////////////
//////////////////////////////////////////////////////////////////////////
var CONFIG = {
// Select by CIDs (For MCC only).
//selectByAccountIds: ['123-456-7890', '098-765-4321'],
// Select by sub-mcc (For MCC only).
//selectBySubMccId: '123-456-7890',
// Recipient email address for script notifications.
email: 'mcc@raskrutka.com.ua',
// Set DEBUG mode. In this mode, the script will print messages to the log
// output screen.
debug: true,
spreadsheet: {
// ID of template spreadsheet.
templateId: '1C66jjF57dZ5Me8vbUDnXTRXTNekHIVEwEQF8PnVD9Ks',
// Name of the spreadsheet where all ads are exported and later imported to
// upload ETAs.
targetName: 'ETA Transition Helper v1.0',
// Stores a reference to Sheet.
// Do not set any values for `sheet`, this is dynamically filled when
// initialized.
sheet: null,
// The name of the sheet inside the spreadsheet.
sheetName: 'main',
// The first row where the content starts.
firstContentRow: 4,
// The row index, in spreadsheet, where header is located.
headerRow: 3,
// Column to check and determine if row is not empty.
nonEmptyColumnCheck: 'customerName',
// Holds mappings between column names and their spreadsheet index.
// Do not set any values for `columnNamesToIndices`, this is dynamically
// filled when initialized.
columnNamesToIndices: {},
// Default status for each new ETA.
defaultStatus: 'paused',
// Column mapping.
columns: [
// User and Ad parent (Adgroup and Campaign) information.
'customerId',
'customerName',
'campaignId',
'campaignName',
'adGroupId',
'adGroupName',
// STA related attributes (Read Only).
'staId',
'headline',
'description1',
'description2',
'staApprovalStatus',
'displayUrl',
// STA related attributes (Editable).
'staStatus',
// STA performance metrics (Read Only).
'impressions',
'clicks',
'ctr',
// Shared attributes between both ETA and STA (Editable).
'finalUrl',
'mobileFinalUrl',
'trackingTemplate',
'customParameters',
'labels',
// ETA related attributes (Editable).
'headline1',
'charactersRemainingH1',
'headline2',
'charactersRemainingH2',
'description',
'charactersRemainingDesc',
'path1',
'path2',
'etaStatus',
// ETA related attributes (Read Only).
'etaApprovalStatus',
'etaCreated',
'etaId',
'readyToUpload',
'errorMessage'],
// Map between report fields and spreadsheet columns.
// This mapping is used when exporting report data
// to the spreadsheet. Will skip fields with `null` values.
reportFieldMap: {
customerId: 'accountId',
customerName: 'accountName',
campaignId: 'campaignId',
campaignName: 'campaignName',
adGroupId: 'adGroupId',
adGroupName: 'adGroupName',
staId: 'id',
headline: 'headline',
description1: 'description1',
description2: 'description2',
staApprovalStatus: 'creativeApprovalStatus',
displayUrl: 'displayUrl',
staStatus: 'status',
impressions: 'impressions',
clicks: 'clicks',
ctr: 'ctr',
finalUrl: 'creativeFinalUrls',
mobileFinalUrl: 'creativeFinalMobileUrls',
trackingTemplate: 'creativeTrackingUrlTemplate',
customParameters: 'creativeUrlCustomParameters',
labels: 'labels',
headline1: 'headlinePart1',
charactersRemainingH1: null,
headline2: 'headlinePart2',
charactersRemainingH2: null,
description: 'description',
charactersRemainingDesc: null,
path1: 'path1',
path2: 'path2',
etaStatus: null,
etaApprovalStatus: null,
etaCreated: null,
etaId: null,
readyToUpload: null,
errorMessage: null
},
// Columns with formulas to avoid overriding.
// Will copy these fields when appending new rows in the spreadsheet.
columnsWithFormulas: ['charactersRemainingH1',
'charactersRemainingH2',
'charactersRemainingDesc',
'etaCreated']
},
// The total number of ads we would like to handle.
numOfAds: 800,
// The total number of accounts we would like to handle (MCC only).
numOfAccounts: 50,
// The duration used to download the Ad Performance Report.
duration: 'LAST_30_DAYS',
// The API version to use when downloading the Ad Performance Report.
apiVersion: 'v201607',
// When sorting ads by performance, use the values listed here as a criteria
// when comparing different performance values. For example, if ad A has
// 100 impressions and ad B has 90 impressions, ad A should be more important
// for us - unless impressionsThreshold is equal or higher to 10, then we'll
// consider both ads with the same magnitude of impressions and therefore
// compare their CTR.
performance: {
impressionsThreshold: 10,
ctrThreshold: 0.1
},
// Fields to select from Ad Performance Report and export to selected
// spreadsheet.
reportFields: ['CampaignId', 'CampaignName', 'AdGroupId', 'AdGroupName', 'Id',
'Headline', 'Description1', 'Description2',
'CreativeApprovalStatus', 'DisplayUrl',
'Status',
'Impressions', 'Clicks', 'Ctr',
'CreativeFinalUrls', 'CreativeFinalMobileUrls',
'CreativeTrackingUrlTemplate', 'CreativeUrlCustomParameters',
'Labels',
'HeadlinePart1', 'HeadlinePart2', 'Description',
'Path1', 'Path2'
],
// The default label to apply to Ads if no value is present in the label
// column.
defaultLabelName: 'eta-upgrade',
// Cache key for storing changes done to platform.
changeCacheKey: 'platform_changes',
// Time the value will remain in the cache, in seconds.
cacheExpiration: 21600,
// The start date to filter ETA reports by.
etaReportStartDate: '20160801'
};
// Email template types.
var SPREADSHEET_CREATED = 1;
// Preview mode indicator.
var IS_PREVIEW = AdWordsApp.getExecutionInfo().isPreview();
//////////////////////////////////////////////////////////////////////////
///////////////////////////////// MAIN ///////////////////////////////////
//////////////////////////////////////////////////////////////////////////
// Declaration required for non-MCC accounts.
var MccApp = MccApp || undefined;
/**
* Main application entry.
*
* This will run first.
*/
function main() {
initConfig();
if (!validateSpreadsheet(CONFIG.spreadsheet)) {
Logger.log('Terminating execution due to malformed spreadsheet format.');
return;
}
var errorCount;
if (MccApp) {
print('Exporting STAs from MCC');
printExportResults(exportSTAMCC());
print('Processing spreadsheet');
errorCount = syncSpreadsheetMCC();
print('Processing complete');
} else {
print('Exporting STAs from account');
printExportResults(exportSTA(CONFIG.numOfAds));
print('Processing spreadsheet');
errorCount = syncSpreadsheet();
print('Processing complete');
}
if (errorCount > 0) {
throw 'Script runtime error. An error occured, please check the logs.';
}
}
/**
* Initializes dynamic properties of CONFIG.
*/
function initConfig() {
var sheet = getCachedSheet(CONFIG.spreadsheet, CONFIG.email);
var spreadsheet = sheet.getParent();
if (!canEditSpreadsheet(spreadsheet)) {
throw 'User account does not have permission to edit the spreadsheet. ' +
'Please ensure the permission is set to `Can Edit` instead of ' +
'`Can Comment` or `Can View`.';
}
if (!sheet) {
throw 'Could not find a sheet named "' + CONFIG.spreadsheet.sheetName +
'" in the spreadsheet. Please fix your spreadsheet.';
}
CONFIG.spreadsheet.sheet = sheet;
CONFIG.spreadsheet.columns.forEach(function(columnName, index) {
CONFIG.spreadsheet.columnNamesToIndices[columnName] = index;
});
// Set default email address to the spreadsheet's owner email address.
if (isEmptyString(CONFIG.email)) {
CONFIG.email = getFileOwnerEmail(spreadsheet);
}
// Notify which email address is used.
print('* This script will send notifications to: ' + CONFIG.email);
}
/**
* Checks the number of accounts to process and warns the user if the number
* is above the recommended threshold.
*
* @param {ManagedAccountIterator} accountIterator The account iterator of the
* accounts that will be used.
*/
function processAccountLimit(accountIterator) {
var accountsToProcess = accountIterator.totalNumEntities();
print('Accounts to process: ' + accountsToProcess);
if (accountsToProcess > CONFIG.numOfAccounts) {
print('WARNING: Number of accounts is above the recommended threshold: ' +
CONFIG.numOfAccounts);
print('Please use "selectByAccountIds" or "selectBySubMccId" to select');
print('which accounts to use from the CONFIG');
}
}
/**
* Get the accountIterator from the MCC using the selectors specified in the
* config.
*
* @return {ManagedAccountIterator} The iterator for the selected accounts.
* @throws {string}
*/
function getAccountIteratorFromMCC() {
var selectBySubMccId = CONFIG.selectBySubMccId || null;
var selectByAccountIds = CONFIG.selectByAccountIds || null;
// Build account iterator.
var accIterBuild = MccApp.accounts();
// Restrict to sub-mcc.
if (selectBySubMccId) {
if (!isString(selectBySubMccId)) {
throw 'selectBySubMccId: is not a string';
}
accIterBuild = accIterBuild.withCondition("ManagerCustomerId = '" +
validateCid(selectBySubMccId) + "'");
}
// Restrict to list of account IDs.
if (selectByAccountIds) {
if (!Array.isArray(selectByAccountIds)) {
throw 'selectByAccountIds: is not an Array';
}
for (var i = 0; i < selectByAccountIds.length; i++) {
validateCid(selectByAccountIds[i]);
}
accIterBuild = accIterBuild.withIds(selectByAccountIds);
}
return accIterBuild.get();
}
/**
* From the Ad Performance Report, copy all enabled and
* not disapproved Text Ads to the configured spreadsheet.
*
* This function operates only for MCC accounts.
*
* The function will iterate over each client sub-account exporting in turn.
* @return {{exportedCount: number,
* remainingRowCount: number,
* accountsProcessed: number}}
* An object containing statistics from the STA export.
* exportedCount The number of exported Ads during exportSTA.
* remainingRowCount The number of remaining rows available in the
* spreadsheet.
* accountsProcessed The number of accounts processed in the export.
*/
function exportSTAMCC() {
// Store the current MCC account.
var mccAccount = AdWordsApp.currentAccount();
// Count the number of available STAs to export.
var maxAdsToExport = CONFIG.numOfAds;
var resultObject = {
exportedCount: 0,
remainingRowCount: maxAdsToExport,
accountsProcessed: 0
};
// Get account iterator from MCC config.
var accountIterator;
try {
accountIterator = getAccountIteratorFromMCC();
}
catch (err) {
print(err);
return resultObject;
}
if (accountIterator) {
processAccountLimit(accountIterator);
while (accountIterator.hasNext()) {
var account = accountIterator.next();
// Set the account as the client account as the active account.
MccApp.select(account);
print('Account: ' + account.getName() +
' (' + account.getCustomerId() + ')');
// Execute the export.
var exportResult = exportSTA(maxAdsToExport);
resultObject.exportedCount += exportResult.exportedCount;
resultObject.remainingRowCount = exportResult.remainingRowCount;
resultObject.accountsProcessed++;
if (resultObject.remainingRowCount <= 0) {
print('Maximum number of Ads reached, skipping export');
break;
}
}
}
MccApp.select(mccAccount);
return resultObject;
}
/**
* From the Ad Performance Report, copy all enabled and
* not disapproved Text Ads to the configured spreadsheet.
*
* @param {number} maxAdsToExport The maximum number of ads to export.
*
* @return {{exportedCount: number,
* remainingRowCount: number}}
* An object containing statistics from the STA export.
* exportedCount The number of exported Ads during exportSTA.
* remainingRowCount The number of remaining rows available in the
* spreadsheet.
*/
function exportSTA(maxAdsToExport) {
if (isEmpty(maxAdsToExport) || isNaN(maxAdsToExport)) {
throw 'Failed to export STAs to the spreadsheet. Please specify a ' +
'valid integer number in CONFIG.numOfAds.';
}
// Download Ad Performance Report.
// Get the top most performing ads.
var report = AdWordsApp.report(
'SELECT ' + CONFIG.reportFields.join(',') + ' ' +
'FROM AD_PERFORMANCE_REPORT ' +
'WHERE AdType = "TEXT_AD" ' +
' AND AdGroupStatus = "ENABLED" ' +
' AND CampaignStatus = "ENABLED" ' +
' AND Status = "ENABLED" ' +
' AND CreativeApprovalStatus != "DISAPPROVED" ' +
'DURING ' + CONFIG.duration, {
apiVersion: CONFIG.apiVersion
});
var resultObject = {
exportedCount: 0,
remainingRowCount: maxAdsToExport,
accountsProcessed: 1
};
var mostPerformingAds = getMostPerformingAds(report, CONFIG);
if (mostPerformingAds !== null) {
// Open spreadsheet.
var sheet = getCachedSheet(CONFIG.spreadsheet, CONFIG.email);
var rowObject = getContentRows(sheet,
CONFIG.spreadsheet.firstContentRow,
CONFIG.spreadsheet.nonEmptyColumnCheck,
CONFIG.spreadsheet.columnNamesToIndices,
false);
var nonEmptyRows = rowObject.rows;
// If sheet is not empty, only export ads not present in sheet.
if (nonEmptyRows.length > 0) {
// Switch `mostPerformingAds` to an id=>Ad object structure.
var mostPerformingAdsObject =
createIndexableObjectFromKeys(mostPerformingAds, ['id']);
// For any ad present in the spreadsheet, set value to null.
nonEmptyRows.forEach(function(row) {
var staId = row.getNumber('staId');
if (staId in mostPerformingAdsObject) {
mostPerformingAdsObject[staId] = null;
}
// Subtract the number of empty ETAs from the total ads available
// to export. This will preserve a bounded number of ETAs in the
// spreadsheet.
var etaId = row.getString('etaId');
if (isEmptyString(etaId)) {
maxAdsToExport--;
resultObject.remainingRowCount--;
}
});
// Keep ads that are not null (not present in spreadsheet).
var reportsNotPresentInSheet = [];
Object.keys(mostPerformingAdsObject).forEach(function(adId) {
var ad = mostPerformingAdsObject[adId];
if (mostPerformingAdsObject.hasOwnProperty(adId) && !isEmpty(ad)) {
reportsNotPresentInSheet.push(ad);
}
});
mostPerformingAds = reportsNotPresentInSheet;
}
// Traverse all ads and export them to the spreadsheet.
maxAdsToExport = Math.min(Math.max(maxAdsToExport, 0),
mostPerformingAds.length);
for (var i = 0; i < maxAdsToExport; i++) {
mostPerformingAds[i].export(sheet, CONFIG.spreadsheet);
}
resultObject.exportedCount = maxAdsToExport;
// Update the number of remaining rows to export.
resultObject.remainingRowCount -= maxAdsToExport;
// Print the number of ads exported.
print(resultObject.exportedCount + ' ads exported to ' +
CONFIG.spreadsheet.sheet.getParent().getUrl());
}
return resultObject;
}
/**
* Print the result of the exportSTA function from the exportResults object.
*
* @param {{exportedCount: number,
* accountsProcessed: number}}
* exportResults An object containing statistics from the STA export.
* exportedCount The number of exported Ads during exportSTA.
* accountsProcessed The number of accounts processed in the export.
*/
function printExportResults(exportResults) {
print('Export Results:\n' +
' Total accounts processed: ' + exportResults.accountsProcessed +
'\n' +
' Total STAs exported: ' + exportResults.exportedCount + '\n');
}
/**
* Sync content from and to the exported spreadsheet.
*
* This function operates only for MCC accounts.
*
* This function will iterate over each sub-account calling syncSpreadsheet for
* the account CID
*
* @return {number} Returns the error count during the sync.
* For example: update ad status, approval reason
* and create ETAs that are flagged as ready for upload.
*/
function syncSpreadsheetMCC() {
var errorCount = 0;
// Store the current MCC account
var mccAccount = AdWordsApp.currentAccount();
// Get account iterator from MCC config.
var accountIterator;
try {
accountIterator = getAccountIteratorFromMCC();
}
catch (err) {
errorCount += 1;
}
if (accountIterator) {
processAccountLimit(accountIterator);
while (accountIterator.hasNext()) {
var account = accountIterator.next();
// Set the account as the client account as the active account
MccApp.select(account);
print('Account: ' + account.getName() +
' (' + account.getCustomerId() + ')');
errorCount += syncSpreadsheet(account.getCustomerId());
}
}
MccApp.select(mccAccount);
return errorCount;
}
/**
* Sync content from and to the exported spreadsheet.
*
* @param {string|null|undefined} customerId sync for the customer Id provided.
* If null or undefined, then sync all rows.
*
* @return {number} Returns the error count during the sync.
* For example: update ad status, approval reason
* and create ETAs that are flagged as ready for upload.
*/
function syncSpreadsheet(customerId) {
var errorCount = 0;
var sheet = getCachedSheet(CONFIG.spreadsheet, CONFIG.email);
// Combine Ad performing reports with rows in spreadsheet.
var spreadsheetRowsAndReport;
try {
spreadsheetRowsAndReport =
getReportWithSpreadsheetRows(customerId ? [customerId] : []);
} catch(err) {
Logger.log('Failed to retrieve report rows: ' + err);
spreadsheetRowsAndReport = [];
errorCount++;
}
// Keep track of changes made to AdWords platform.
var allChanges = [];
spreadsheetRowsAndReport.forEach(function(spreadsheetRowAndReport) {
// `startOfIterationErrorCount` will keep track of the error count at the
// start of this iteration.
var startOfIterationErrorCount = errorCount;
var spreadsheetRow = spreadsheetRowAndReport.row;
var sta = spreadsheetRowAndReport.sta;
var eta = spreadsheetRowAndReport.eta;
// Keep track of changes made to STA.
var staChanges = new AdChange(spreadsheetRow.getNumber('staId'),
spreadsheetRow.getNumber('adGroupId'));
// Keep track of changes made to ETA (etaId may be empty here).
var etaChanges = new AdChange(spreadsheetRow.getNumber('etaId'),
spreadsheetRow.getNumber('adGroupId'));
// Save changes for this iteration.
allChanges.push({
sta: staChanges.getChangeStruct(),
eta: [etaChanges.getChangeStruct()]
});
// If STA is null (report was not retrieved), then retrieve it.
// STA is only null when it's not returned in `getMostPerformingAds` for
// this specific row.
if (isEmpty(sta)) {
sta = getAdFromRow(spreadsheetRow, 'STA');
if (isEmpty(sta)) {
errorCount += 1;
try {
spreadsheetRow.markAsError('Error retrieving STA with Id ' +
spreadsheetRow.getNumber('staId'));
} catch (err) {
spreadsheetRow.markAsError('Error retrieving STA with missing Id, ' +
'row : ' + spreadsheetRow.getRowIndex());
}
}
}
errorCount += syncETA(eta, spreadsheetRow, etaChanges);
errorCount += syncSTA(sta, spreadsheetRow, staChanges);
});
allChanges.map(function(change) {
function _printChanges(type, id, changes) {
function _print(field) {
if (!field) {
return;
}
var prefix = type + ' (' + id + '): ';
if (isEmptyString(id)) {
prefix = type + ' ';
}
print(prefix + field.fieldName + ' changed from "' +
field.oldValue + '" to "' + field.newValue + '"');
}
if (Array.isArray(changes)) {
changes.map(_print);
} else {
_print(changes);
}
}
if (change.sta) {
_printChanges('Standard text ad', change.sta.adId, change.sta.changes);
}
if (change.eta) {
change.eta.map(function(eta) {
if (eta.created) {
print('Expanded text ad (' + eta.adId + '): created');
_printChanges('+', null, eta.changes);
} else {
_printChanges('+ Expanded text ad', eta.adId, eta.changes);
}
});
}
});
return errorCount;
}
/**
* Retrieves labels that will be used to apply on ETA or STA.
*
* @param {SpreadsheetRow} spreadsheetRow A row in spreadsheet.
* @param {string} defaultLabel
*
* @return {Array<string>} An array of label names.
*/
function getLabelsToApply(spreadsheetRow, defaultLabel) {
var labelNames = spreadsheetRow.getArray('labels');
if (isEmptyString(labelNames)) {
labelNames = [defaultLabel];
} else if (labelNames.indexOf(defaultLabel) === -1) {
labelNames.push(defaultLabel);
}
return labelNames;
}
/**
* Syncs STA in AdWords platform with STA in spreadsheet.
* Currently only sync labels and status, only if ETA
* has been created.
*
* @param {Ad} sta Ad object to apply changes to.
* @param {SpreadsheetRow} spreadsheetRow A row in spreadsheet.
* @param {AdChange} staChanges An object used for tracking changes
* made in AdWords platform.
*
* @return {number} A count of errors encountered.
*/
function syncSTA(sta, spreadsheetRow, staChanges) {
var errorCount = 0;
// Get labels to apply to STAs and newly created ETAs.
var labelNames = getLabelsToApply(spreadsheetRow, CONFIG.defaultLabelName);
// If STA object and ETA Id is present.
if (!isEmpty(sta) &&
(!isEmptyString(spreadsheetRow.getString('etaId')) &&
spreadsheetRow.getNumber('etaId') !== 0)) {
// Determine if STA status will need to be updated.
// If the setStatus operation is successful, track it.
var oldStatus = sta.getStatus();
var spreadsheetSTAStatus = spreadsheetRow.getString('staStatus');
var hasNewStatus = (oldStatus !== spreadsheetSTAStatus);
if (hasNewStatus && sta.syncStatus(spreadsheetSTAStatus)) {
staChanges.trackChange('staStatus', oldStatus, spreadsheetSTAStatus);
} else if (hasNewStatus) {
errorCount += 1;
try {
spreadsheetRow.markAsError('Error syncing status for STA with id ' +
spreadsheetRow.getNumber('staId'));
} catch (err) {
spreadsheetRow.markAsError('Error syncing status for STA with ' +
'missing Id, row : ' + spreadsheetRow.getRowIndex());
}
}
if (spreadsheetSTAStatus !== Ad.statuses.DISABLED &&
sta.getStatus() !== Ad.statuses.DISABLED) {
// Sync label(s) on STA.
var currentLabels = sta.getLabels();
// Applies labels, set in spreadsheet, on to STA.
if (sta.syncLabels(labelNames)) {
staChanges.trackChange('labels', currentLabels, labelNames);
} else {
errorCount += 1;
try {
spreadsheetRow.markAsError('Error applying labels ' + labelNames +
' on STA with Id ' + spreadsheetRow.getNumber('staId'));
} catch (err) {
spreadsheetRow.markAsError('Error applying labels on STA with ' +
'missing Id, row : ' + spreadsheetRow.getRowIndex());
}
}
}
}
return errorCount;
}
/**
* Syncs ETA in AdWords platform with STA in spreadsheet.
* Currently creates ETA if ready, sync labels, status, approval reason.
*
* @param {Ad} eta Ad object to apply changes to.
* @param {SpreadsheetRow} spreadsheetRow A row in spreadsheet.
* @param {AdChange} etaChanges An object used for tracking changes
* made in AdWords platform.
*
* @return {number} A count of errors encountered.
*/
function syncETA(eta, spreadsheetRow, etaChanges) {
var errorCount = 0;
// Get labels to apply to STAs and newly created ETAs.
var labelNames = getLabelsToApply(spreadsheetRow, CONFIG.defaultLabelName);
// If ready to upload and ETA ID is not set.
if (spreadsheetRow.getString('readyToUpload').trim().toLowerCase() ===
'yes' && (isEmptyString(spreadsheetRow.getNumber('etaId')) ||
spreadsheetRow.getNumber('etaId') === 0)) {
// Create a new ETA.
var result = createETA(spreadsheetRow);
eta = result.ad;
// Only apply ETA changes, if ETA object is present.
if (isEmpty(eta)) {
errorCount += 1;
if (result.errors.length > 0) {
spreadsheetRow.markAsError(result.errors);
} else {
spreadsheetRow.markAsError('Error creating new ETA for STA with Id ' +
spreadsheetRow.getNumber('staId'));
}
} else {
etaChanges.trackCreate(eta.getId(),
spreadsheetRow.getNumber('adGroupId'));
if (!IS_PREVIEW) {
// Update spreadsheet with ETA values.
spreadsheetRow.set('etaId', eta.getId());
// If ETA status is not defined set it to paused.
if (isEmptyString(spreadsheetRow.getString('etaStatus'))) {
spreadsheetRow.set('etaStatus', 'paused');
}
}
// Applies labels, set in spreadsheet, on to ETA.
if (eta.syncLabels(labelNames)) {
etaChanges.trackChange('labels', '', labelNames);
} else {
errorCount += 1;
spreadsheetRow.markAsError('Error applying labels ' + labelNames +
' on ETA with Id ' + eta.getId());
}
var spreadsheetETAStatus = spreadsheetRow.getString('etaStatus');
if (eta.syncStatus(spreadsheetETAStatus)) {
etaChanges.trackChange('status', '', spreadsheetETAStatus);
} else {
errorCount += 1;
spreadsheetRow.markAsError('Error syncing status for ETA with Id ' +
eta.getId());
}
}
} else if (!isEmptyString(spreadsheetRow.getNumber('etaId')) &&
spreadsheetRow.getNumber('etaId') !== 0) {
// If ETA is null, retrieve it.
// ETA is only null when it's not returned in `getETAReports` for this
// specific row.
if (isEmpty(eta)) {
eta = getAdFromRow(spreadsheetRow, 'ETA');
}
if (isEmpty(eta)) {
errorCount += 1;
try {
spreadsheetRow.markAsError('Error retrieving ETA with Id ' +
spreadsheetRow.getNumber('etaId'));
}
catch (err) {
spreadsheetRow.markAsError('Error retrieving ETA with missing Id, ' +
'row : ' + spreadsheetRow.getRowIndex());
}
// Only apply ETA changes, if ETA object is present.
} else {
// Set latest approval status of ETA in spreadsheet.
var etaApprovalStatus = eta.getApprovalStatus();
if (!isEmptyString(etaApprovalStatus)) {
spreadsheetRow.set('etaApprovalStatus', etaApprovalStatus);
}
var currentStatus = eta.getStatus();
var spreadsheetETAStatus = spreadsheetRow.getString('etaStatus');
if (eta.syncStatus(spreadsheetETAStatus)) {
etaChanges.trackChange('status', currentStatus, spreadsheetETAStatus);
}
// If ETA has not been disabled.
if (spreadsheetETAStatus !== Ad.statuses.DISABLED) {
// Sync label(s) on ETA.
var currentLabels = eta.getLabels();
// Applies labels, set in spreadsheet, on to ETA.
if (eta.syncLabels(labelNames)) {
etaChanges.trackChange('labels', currentLabels, labelNames);
} else {
errorCount += 1;
spreadsheetRow.markAsError('Error applying labels ' + labelNames +
' on ETA with Id ' + eta.getId());
}
}
}
}
return errorCount;
}
//////////////////////////////////////////////////////////////////////////
////////////////////////////////// AD ////////////////////////////////////
//////////////////////////////////////////////////////////////////////////
// Depends on the following global functions:
// - createLabel
// - isEmpty
// - isEmptyString
// - print
/**
* Represents an Ad.
*
* @param {Object} row A report row from which to parse an Ad. This object
* is expected to have properties included in `rowFields`.
* @param {Array<string>} rowFields An array of fields to select from `row`.
* @param {AdWordsApp.Ad} adWordsAppAd An Ad object.
* @param {AdWordsApp.Account} account Current AW account.
*
* @constructor
*/
function Ad(row, rowFields, adWordsAppAd, account) {
this.row = {
accountId: account.getCustomerId(),
accountName: account.getName()
};
// Stores AdWordsApp.Ad.
this.ad = null;
if (!isEmpty(adWordsAppAd)) {
this.ad = adWordsAppAd;
}
if (!isEmpty(row) && !isEmpty(rowFields) && Array.isArray(rowFields)) {
// Copy all selected fields.
var self = this;
rowFields.forEach(function(field) {
// Lowercase the first character.
var normalizedFieldName = field.charAt(0).toLowerCase() +
field.substring(1, field.length);
if (normalizedFieldName === 'status' ||
normalizedFieldName === 'creativeApprovalStatus') {
self.row[normalizedFieldName] = row[field].trim().toLowerCase();
} else {
self.row[normalizedFieldName] = row[field];
}
// Assign `id` to self, for efficient access.
if (normalizedFieldName === 'id') {
self.id = row[field];
}
});
}
}
/**
* Supported statuses.
* @enum {string}
*/
Ad.statuses = {
ENABLED: 'enabled',
PAUSED: 'paused',
DISABLED: 'disabled'
};
/**
* Returns true if this instance has an AdWordsApp.Ad object.
*
* @return {boolean}
*/
Ad.prototype.hasAdWordsAppAd = function() {
return (!isEmpty(this.ad));
};
/**
* Returns an Ad's id.
*
* @return {number}
* @throws {string}
*/
Ad.prototype.getId = function() {
// If AdWordsApp.Ad object is present.
if (this.hasAdWordsAppAd()) {
return this.ad.getId();
// Otherwise, dealing with Ad Report object.
} else if (!isEmptyString(this.row.id)) {
return this.row.id;
} else {
throw 'Invalid ad object.';
}
};
/**
* Returns an Ad's labels.
*
* @return {Array<string>} An array of label names.
* @throws {string}
*/
Ad.prototype.getLabels = function() {
var labelNames = [];
// If AdWordsApp.Ad object is present.
if (this.hasAdWordsAppAd()) {
if (this.ad.getId() > 0) {
var labelIterator = this.ad.labels().get();
while (labelIterator.hasNext()) {
var label = labelIterator.next();
labelNames.push(label.getName());
}
}
// Dealing with an Ad report.
} else if (this.row['labels'] !== undefined &&
typeof this.row.labels === 'string') {
// If no labels are present.
if (isEmpty(this.row.labels) || this.row.labels === '--') {
return [];
} else {
try {
labelNames = JSON.parse(this.row.labels);
} catch (err) {
throw 'Invalid JSON string stored in Ad.labels';
}
}
// Dealing with an unknown object.
} else {
throw 'Invalid ad object';
}
return labelNames;
};
/**
* Determines which labels to remove and which labels to add.
*
* @param {Array<string>} newLabelNames An array of new label names to apply.
*
* @return {{add: Array<string>,
* remove: Array<string>,
* hasDiff: boolean}} An object with new labels to apply under an
* `add` attribute, labels to remove under a
* `remove` attribute, and a `hasDiff` attribute
* used to indicate if any differences were found.
*
* @private
*/
Ad.prototype.diffLabels_ = function(newLabelNames) {
// `newLabelNames` must be an array.
if (!Array.isArray(newLabelNames)) {
throw 'Incorrect `newLabelNames` parameter.';
}
var diff = {
add: [],
remove: [],
hasDiff: false
};
var currentLabelNames = this.getLabels();
newLabelNames.forEach(function(newLabelName) {
newLabelName = newLabelName.trim();
// If new label is not present in current labels, append to `add`.
if (currentLabelNames.indexOf(newLabelName) === -1) {
diff.add.push(newLabelName);
diff.hasDiff = true;
}
return;
});
currentLabelNames.forEach(function(currentLabelName) {
currentLabelName = currentLabelName.trim();
// If current label is not present in new labels, append to `remove`.
if (newLabelNames.indexOf(currentLabelName) === -1) {
diff.remove.push(currentLabelName);
diff.hasDiff = true;
}
return;
});
return diff;
};
/**
* Adds labels on Ad based on `labelNamesDiff` values.
*
* @param {Array<string>} newLabels Contains an array of label names to add.
*
* @return {boolean} Representing whether all labels were successfully applied.
*/
Ad.prototype.syncLabels = function(newLabels) {
// If newLabels is empty, nothing to add.
if (isEmpty(newLabels) || newLabels.length <= 0) {
return true;
}
var allApplied = true;
var labelNamesDiff = this.diffLabels_(newLabels);
// If differences are present, make sure we have AdWordsApp.Ad object.
if (labelNamesDiff.hasDiff && !this.hasAdWordsAppAd()) {
var success = this.getAd();
if (!success) {
return false;
}
}
var self = this;
labelNamesDiff.add.forEach(function(labelName) {
// Create label, if it doesn't exist.
var success = createLabel(labelName);
if (success) {
try {
if (self.ad.getId() > 0) {
self.ad.applyLabel(labelName);
}
} catch (err) {
print('Failed to apply ' + labelName + ' to Ad Id: ' + self.getId(),
[err]);
allApplied = false;
}
} else {
print('Failed to create ' + labelName + ' and apply to Ad Id: ' +
self.getId());
allApplied = false;
}
});
return allApplied;
};
/**
* Returns status of Ad.
*
* @return {string}
*/
Ad.prototype.getStatus = function() {
var adStatus = '';
// If we have AdWordsApp.Ad object.
if (this.hasAdWordsAppAd()) {
if (this.ad.getId() > 0) {
if (this.ad.isEnabled()) {
adStatus = Ad.statuses.ENABLED;
} else if (this.ad.isPaused()) {
adStatus = Ad.statuses.PAUSED;
}
}
// Otherwise, dealing with Ad Report object.
} else if (!isEmptyString(this.row.status)) {
adStatus = this.row.status;
} else {
throw 'Invalid ad object.';
}
return adStatus;
};
/**
* Returns approval status of Ad.
*
* @return {?string}
*/
Ad.prototype.getApprovalStatus = function() {
var approvalStatus = null;
// If dealing with an AdWordsApp.Ad object.
if (this.hasAdWordsAppAd()) {
if (this.ad.getId() > 0) {
approvalStatus = this.ad.getApprovalStatus();
}
// Otherwise, dealing with Ad Report object.
} else {
approvalStatus = this.row.creativeApprovalStatus;
}
if (isEmptyString(approvalStatus)) {
return null;
}
return approvalStatus.trim().toLowerCase();
};
/**
* Syncs the status of an Ad in spreadsheet with AdWordsApp.Ad.
*
* @param {Ad.statuses} status Possible choices are `enabled`, `paused`, and
* `disabled`.
*
* @return {boolean} A boolean indicating if operation was successfull.
*/
Ad.prototype.syncStatus = function(status) {
if (isEmptyString(status)) {
throw 'Incorrect status parameter.';
}
var normalizedStatus = status.trim().toLowerCase();
// If status does not differ, no changes need to be made.
if (this.getStatus() === normalizedStatus) {
return true;
// Otherwise, change will have to be made, therefore
// make sure AdWordsApp.Ad is present.
} else if (!this.hasAdWordsAppAd()) {
var success = this.getAd();
if (!success) {
return false;
}
}
try {
if (this.ad.getId() < 0) {
// Negative ad ID indicate running in preview mode.
return true;
}
switch (normalizedStatus) {
case Ad.statuses.ENABLED:
this.ad.enable();
return true;
case Ad.statuses.PAUSED:
this.ad.pause();
return true;
// For no value, set to pause.
case '':
this.ad.pause();
return true;
default:
return false;
}
} catch (err) {
return false;
}
};
/**
* Retrieves and sets an Ad object to this instance.
*
* @return {boolean} Whether the ad was found or not.
*/
Ad.prototype.getAd = function() {
if (isEmptyString(this.row.adGroupId) || isEmptyString(this.row.id)) {
throw 'Invalid Ad instance.';
}
var adIdAndAdgroupId = [[this.row.adGroupId, this.row.id]];
var adSelector = AdWordsApp.ads()
.withIds(adIdAndAdgroupId);
var adIterator = adSelector.get();
// There is at most 1 Ad.
if (adIterator.hasNext()) {
this.ad = adIterator.next();
return true;
} else {
return false;
}
};
/**
* Export ad to selected Google sheet.
*
* @param {Sheet} sheet Output sheet.
* @param {{columns: Array<string>,
* reportFieldMap: Object,
* columnNamesToIndices: Object,
* nonEmptyColumnCheck: string
* }} sheetConfig Configuration for selected sheet.
*/
Ad.prototype.export = function(sheet, sheetConfig) {
if (isEmpty(sheetConfig) ||
isEmpty(sheetConfig.columns) ||
isEmpty(sheetConfig.reportFieldMap) ||
isEmpty(sheetConfig.columnNamesToIndices) ||
isEmpty(sheetConfig.nonEmptyColumnCheck)) {
throw 'Failed exporting ad ' + this.id + '. Must provide valid sheet ' +
'configuration when exporting an Ad to a spreadsheet';
}
var fields = [];
var self = this;
// Check if exporting to the first content row in the spreadsheet.
var lastRow = sheet.getLastRow();
var colIndex =
sheetConfig.columnNamesToIndices[sheetConfig.nonEmptyColumnCheck] + 1;
var isFirstRow = (lastRow === sheetConfig.firstContentRow) &&
isEmptyString(sheet.getRange(lastRow, colIndex).getValue());
// Parse all columns.
sheetConfig.columns.forEach(function(key) {
var field;
// Detect whether this field is a formula and copy the formula from
// previous cell if necessary.
if (!isFirstRow && !isEmpty(sheetConfig.columnsWithFormulas) &&
sheetConfig.columnsWithFormulas.indexOf(key) !== -1) {
// Copy formula.
var formulaIndex = sheetConfig.columns.indexOf(key);
if (formulaIndex !== -1) {
// Add 1 to index because spreadsheet begins at index 1.
var formulaPreviousCell = sheet.getRange(lastRow, formulaIndex + 1);
// Return the formula from one cell above this row.
field = formulaPreviousCell.getFormulaR1C1();
}
}
if (isEmptyString(field)) {
// Parse field by column name.
field = self.parseField_(key, sheetConfig);
}
// Stage field.
fields.push(field);
});
if (isFirstRow) {
// Because we're "freezing" rows, we can't delete all other non-frozen
// rows and so we're left with one empty row. Instead of adding a new
// row, replace the values in the cells.
var prevSectionIndex = 1;
var values = [];
// Copy fields in batches.
fields.forEach(function(field) {
if (!isEmptyString(field)) {
// Batch fields together to avoid making many I/O operations.
values.push(field);
} else if (values.length > 0) {
// Flush fields.
var row = sheet.getRange(lastRow, prevSectionIndex, 1, values.length);
row.setValues([values]);
// Update section start index.
prevSectionIndex += values.length + 1;
// Reset batch.
values = [];
} else {
// Skip empty field.
prevSectionIndex++;
}
});
} else {
// Add a new row and copy all fields.
sheet.insertRowAfter(lastRow);
lastRow++;
// Flush fields.
var row = sheet.getRange(lastRow, 1, 1, fields.length);
row.setValues([fields]);
}
};
/**
* Parse a field based on a column key.
*
* @param {string} key Column key.
* @param {{reportFieldMap: Array<dict>,
* defaultStatus: string
* }} sheetConfig A configuration object that contains a mapping from
* spreadsheet column names to report columns names,
* and a default value for `etaStatus`.
*
* @return {string} A field based on the column key.
*
* @private
*/
Ad.prototype.parseField_ = function(key, sheetConfig) {
var proxyKey = sheetConfig.reportFieldMap[key];
if (proxyKey === undefined) {
throw 'Failed exporting ad ' + this.id +
'. Missing report field mapping for: ' + key + '.';
}
// Get the field value using the key from the spreadhseet-column name.
var field = this.row[proxyKey];
// Handle specific columns differently.
switch (key) {
case 'description':
// Concatenate description 1 and description 2
// in case description field is blank.
if (isEmptyString(field)) {
field = this.row.description1 +
(isEmptyString(this.row.description2) ? '' :
'\n' + this.row.description2);
}
break;
case 'headline1':
// If headline part 1 is blank, use headline instead.
if (isEmptyString(field)) {
field = this.row.headline;
}
break;
case 'etaStatus':
if (isEmptyString(field)) {
field = sheetConfig.defaultStatus;
}
break;
case 'finalUrl':
case 'mobileFinalUrl':
// Support only single value for Final URL and
// Mobile Final URL.
try {
field = JSON.parse(field);
} catch (ignore) {
// Value is most likely already a string, use it
// directly. For example "--".
}
field = getFirstElementInArray(field);
break;
}
if (isEmptyString(field) || field === '--') {
// Replace all undefined fields because Range.setValues won't play well
// with them.
// Replace all '--' with empty strings to avoid exporting empty values
// formatted like that.
field = '';
}
return field;
};
//////////////////////////////////////////////////////////////////////////
//////////////////////////////// UTILS ///////////////////////////////////
//////////////////////////////////////////////////////////////////////////
/**
* Get a unique file tag that be used to locate the file.
*
* @param {string} seed A seed string used to generate a unique tag.
*
* @return {string} A unique string associated with the current account and
* the seed used.
*/
function getUniqueFileTag(seed) {
// Use the account ID as an additional identifier.
var currentAccount = AdWordsApp.currentAccount();
return seed + '-' + currentAccount.getCustomerId();
}
/**
* Compare two arrays. Nested objects within the array will be compared
* by reference. Therefore, an identical objects but of two different
* instances will be considered not equal.
*
* For example:
* isArrayShallowEquals([1, {a: 1}], [1, {a: 1}]) // false.
* var obj = {a: 1};
* isArrayShallowEquals([1, obj], [1, obj]) // true.
*
* @param {Array} a Array A.
* @param {Array} b Array B.
*
* @return {boolean} Whether both arrays are shallowly equal or not.
*/
function isArrayShallowEquals(a, b) {
// Condition 1: one of the arrays is empty.
if ((!a && b) || (!b && a)) {
return false;
}
// Condition 2: both arrays are null or undefined.
if (a == null && a == b) {
return true;
}
// Condition 3: either one of the arguments is not an array.
if (!Array.isArray(a) || !Array.isArray(b)) {
return false;
}
// Condition 4: arrays of different length.
if (a.length !== b.length) {
return false;
}
// Condition 5: compare each element in the arrays.
for (var i = 0; i < a.length; i++) {
// Shallow compare.
if (a[i] !== b[i]) {
return false;
}
}
return true;
}
/**
* Get the first element in an array.
*
* @param {Array} arr The array from which to get the first element.
*
* @return {Object} The first element in the array or `null` if empty. If
* `arr` is not an array, return it as is.
*/
function getFirstElementInArray(arr) {
if (!arr || !Array.isArray(arr)) {
return arr;
}
if (arr.length === 0) {
return null;
}
return arr[0];
}
/**
* Check if given string is empty.
*
* @param {string} str Empty string candidate.
*
* @return {boolean} True if string is empty, o/w false.
*/
function isEmptyString(str) {
if (str == null) {
return true;
}
if (isString(str)) {
return str.trim() === '';
}
return false;
}
/**
* Check if `obj` is null or undefined.
*
* @param {Object} obj
*
* @return {boolean}
*/
function isEmpty(obj) {
return obj === null || obj === undefined;
}
/**
* Check if `obj` is an object.
*
* @param {Object} obj
*
* @return {boolean}
*/
function isObject(obj) {
// Check if obj exists and has Object string value
if (!obj ||
toString.call(obj) !== '[object Object]') {
return false;
}
// Check is constructor is owned
if (obj.constructor &&
!hasOwnProperty.call(obj, 'constructor') &&
!hasOwnProperty.call(obj.constructor.prototype, 'isPrototypeOf')) {
return false;
}
// Empty if no properties OR
// All properties are owned is last enum property is owned
var key;
for (key in obj) {}
return key === undefined || hasOwnProperty.call(obj, key);
}
/**
* Check if `obj` is a valid params object.
*
* @param {Object} obj
* @param {Array} valueTypes A list of valid value types allowed as parameters
* @param {boolean} emptyIsInvalid A boolean defining if an empty object is
* invalid
*
* @return {Object} An object with { success: <boolean>, message: <string> },
* where message is the reason for failure if success is
* false.
*/
function isValidParamsObject(obj, valueTypes, emptyIsInvalid) {
var result = {
success: false,
message: ''
};
if (!emptyIsInvalid && (isEmpty(obj) || isEmptyString(obj))) {
result.success = true;
return result;
}
if (obj === null) {
result.message = 'params is null';
return result;
}
if (obj === undefined) {
result.message = 'params is undefined';
return result;
}
if (!isObject(obj)) {
result.message = 'params is not an object';
return result;
}
if (!valueTypes || !Array.isArray(valueTypes)) {
result.message = 'valueTypes is not a valid array';
return result;
}
var keyCount = 0;
for (var key in obj) {
if (obj.hasOwnProperty(key)) {
var value = obj[key];
if (valueTypes.indexOf(typeof value) === -1) {
result.message = 'key `' + key + '` has an invalid type `' +
typeof value + '`';
return result;
}
}
keyCount++;
}
if (emptyIsInvalid && keyCount === 0) {
result.message = 'params is empty';
return result;
}
result.success = true;
return result;
}
/**
* Print to logger if in DEBUG mode.
*
* @param {string} msg The message to print with Logger.
* @param {?Array} arr An array of strings to print.
*/
function print(msg, arr) {
if (CONFIG.debug) {
if (arr) {
Logger.log(msg + ' ' + arr.join(', '));
} else {
Logger.log(msg);
}
}
}
/**
* Check if `str` is a string.
*
* @param {string} str Questionable string.
*
* @return {boolean} Whether `str` is indeed a string.
*/
function isString(str) {
return typeof str === 'string' || str instanceof String;
}
/**
* Compares the performance between two ads.
*
* @param {Ad} a Ad a.
* @param {Ad} b Ad b.
*
* @return {number} +1 if Ad a has better performance, -1 if Ad b has better
* performance, and 0 if equal.
*/
function comparePerformance(a, b) {
var impressionsDiff = a.row.impressions - b.row.impressions;
var ctrDiff = parseFloat(a.row.ctr) - parseFloat(b.row.ctr);
var absImpressionsDiff = Math.abs(impressionsDiff);
var absCtrDiff = Math.abs(ctrDiff);
/**
* Helper function to compare impressions and CTR.
*
* @param {number} impressionDiff The difference between both impressions.
* @param {number} ctrDiff The difference between both CTRs.
*
* @return {number} +1 if impressionDiff is > 0, or if impressionDiff === 0
* and ctrDiff > 0. -1 if impressionDiff is < 0, or if
* impressionDiff === 0 and ctrDiff < 0. Otherwise, returns
* 0.
*/
function _compare(impressionDiff, ctrDiff) {
// Choose the ad with the most impressions.
if (impressionsDiff > 0) {
return 1;
} else if (impressionsDiff < 0) {
return -1;
} else {
// Both ads have equal impressions, choose highest CTR.
if (ctrDiff > 0) {
return 1;
} else if (ctrDiff < 0) {
return -1;
} else {
return 0;
}
}
}
if (absImpressionsDiff < CONFIG.performance.impressionsThreshold) {
// The difference in impressions is insignificant, gauge at CTR.
if (absCtrDiff < CONFIG.performance.ctrThreshold) {
// The difference in CTR is insignificant, choose the highest impresssion,
// or the highest CTR in case impressions are equal.
return _compare(impressionsDiff, ctrDiff);
} else if (ctrDiff > 0) {
// The difference in CTR is significant, therefore if it's positive it
// should be in favor of *this* ad.
return 1;
} else {
// The difference in CTR is significant, therefore if it's negative it
// should be in favor of *compared* ad.
return -1;
}
} else if (impressionsDiff > 0) {
// The difference in impressions is significant, therefore if it's positive
// it should be in favor of *this* ad.
return 1;
} else {
// The difference in impressions is significant, therefore if it's negative
// it should be in favor of *compared* ad.
return -1;
}
}
/**
* Composite to reverse ad compare in order to have the most perfoming ad at
* index 0.
*
* @param {function (number, number) : boolean} compareFunc The original compare
* function.
*
* @return {function (number, number) : boolean} A reverse version of
* compareFunc.
*/
function reverseCompare(compareFunc) {
return function(a, b) {
return -compareFunc(a, b);
};
}
/**
* Traverse the report and keep the top most NUM_OF_ADS that
* are considered most performing based on the configuration given.
*
* @param {AdWordsApp.Report} report The report from which we want to
* extrapolate the most performing ads.
* @param {{reportFields: Array<string>,
* numOfAds: number}} config The configuration used to define which
* ads to consider most performing.
*
* @return {Array<Ad>} A sorted array of Ads by performance
*/
function getMostPerformingAds(report, config) {
if (report === null) {
throw 'Failed to find most performing ads: Report used for retrieving ' +
'top performing ads is empty';
}
// Traverse all rows.
var currentAccount = AdWordsApp.currentAccount();
var result = [];
var rows = report.rows();
while (rows.hasNext()) {
var ad = new Ad(rows.next(), config.reportFields, null, currentAccount);
result.push(ad);
}
// Sort by performance.
result.sort(reverseCompare(comparePerformance));
return result;
}
/**
* Retrieve ETA reports.
*
* @param {{etaReportStartDate: string,
* reportFields: Array<string>,
* apiVersion: string}} config The configuration used to select and
* parse ETA reports. `etaReportStartDate`
* should be formatted as YYYYMMDD.
*
* @return {Array<Ad>} A sorted array of ETAs.
*/
function getETAReports(config) {
var report = AdWordsApp.report(
'SELECT ' + config.reportFields.join(',') + ' ' +
'FROM AD_PERFORMANCE_REPORT ' +
'WHERE AdType = "EXPANDED_TEXT_AD" ' +
'AND Date >= "' + config.etaReportStartDate + '"', {
apiVersion: config.apiVersion
});
if (report === null) {
return null;
}
// Traverse all rows.
var currentAccount = AdWordsApp.currentAccount();
var result = [];
var rows = report.rows();
while (rows.hasNext()) {
var ad = new Ad(rows.next(), config.reportFields, null, currentAccount);
result.push(ad);
}
return result;
}
/**
* Attempt to open an existing spreadsheet with description containing
* `config.templateId`. If not found, copy template spreadsheet with id
* `config.templateId` and return its handle.
*
* @param {{templateId: string,
* targetName: string}} config A configuration object with meta-data
* about the target spreadsheet.
* @param {?string} emailToNotify An email address to send out notification
* when creating a new spreadsheet. If none is
* given, will use the email address of the
* owner of the spreadsheet.
*
* @return {Spreadsheet} Selected spreadsheet.
*/
function getSpreadsheet(config, emailToNotify) {
if (isEmpty(config)) {
throw 'Config is missing. Cannot get spreadsheet';
}
if (isEmptyString(config.templateId) || isEmptyString(config.targetName)) {
throw 'Config must include both `templateId` and `targetName` fields.';
}
var spreadsheetFile;
// Attempt to locate the spreadsheet.
// Throws an exception when spreadsheet not found.
var files = DriveApp.searchFiles('fullText contains "' +
getUniqueFileTag(config.templateId) + '"');
if (files.hasNext()) {
spreadsheetFile = files.next();
if (files.hasNext()) {
print('WARNING: more than one file with \'' + config.templateId +
'\' detected.');
}
} else {
// Create a copy of the template spreadsheet.
spreadsheetFile = copyFile(config.templateId, config.targetName);
if (spreadsheetFile) {
// Notify via email.
if (isEmptyString(emailToNotify)) {
// Set the default email address to owner's email address.
emailToNotify = getFileOwnerEmail(spreadsheetFile);
}
notify(emailToNotify, SPREADSHEET_CREATED, spreadsheetFile.getUrl());
}
}
// Get spreadsheet by ID.
var spreadsheet = null;
try {
spreadsheet = !!spreadsheetFile &&
SpreadsheetApp.openById(spreadsheetFile.getId());
} catch (err) {
print(err);
}
if (!spreadsheet) {
throw 'Spreadsheet is missing or unavailable. Please ensure the URL is ' +
'correct and that you have permission to edit';
} else {
return spreadsheet;
}
}
/**
* Open spreadsheet with name `config.targetName` and select its sheet with
* name `config.sheetName`.
*
* @param {{sheetName: string,
* templateId: string,
* targetName: string}} config Containing sheet meta-data.
* @param {?string} emailToNotify An email address to send out notification
* when creating a new spreadsheet. If none is
* given, will use the email address of the
* owner of the spreadsheet.
*
* @return {Sheet} Selected sheet.
*/
function getSheet(config, emailToNotify) {
if (isEmptyString(config.sheetName)) {
throw 'Config must include `sheetName` field. Please set to the name of ' +
'sheet within the spreadsheet e.g. main';
}
var spreadsheet = getSpreadsheet(config, emailToNotify);
var sheet = spreadsheet.getSheetByName(config.sheetName);
if (!sheet) {
throw 'Spreadsheet is missing a sheet named: ' + config.sheetName +
'. Please ensure the sheet exists in the spreadsheet';
}
return sheet;
}
/**
* Retrieves cached sheet.
*
* @param {{sheet: Sheet,
* sheetName: string,
* templateId: string,
* targetName: string}} config An object to retrieve cached sheet from.
* @param {?string} emailToNotify An email address to send out notification
* when creating a new spreadsheet. If none is
* given, will use the email address of the
* owner of the spreadsheet.
*
* @return {?Sheet} Selected sheet.
*/
function getCachedSheet(config, emailToNotify) {
// If sheet already present.
if (!isEmpty(config.sheet)) {
return config.sheet;
// Else, cache and return.
} else {
var sheet = getSheet(config, emailToNotify);
config.sheet = sheet;
return sheet;
}
}
/**
* Returns a boolean if the current user is able to edit the specified
* spreadsheet.
*
* @param {Spreadsheet} spreadsheet The spreadsheet to detect edit permissions
* on.
*
* @return {boolean} Returns true if the current user can edit the spreadsheet
* provided.
*/
function canEditSpreadsheet(spreadsheet) {
// Validate the spreadsheet argument exists.
if (isEmpty(spreadsheet)) {
throw 'Argument provided to canEditSpreadsheet is not a valid spreadsheet';
}
// Return RANGE level protections.
var protections = spreadsheet.getProtections(
SpreadsheetApp.ProtectionType.RANGE);
if (protections.length === 0) {
// No protections at a spreadsheet level.
return true;
}
for (var i = 0; i < protections.length; i++) {
if (protections[i].canEdit()) {
return true;
}
}
// If no protections allow editing, not editable.
return false;
}
/**
* Wrap `func` so that it may receive default input.
* ```
* For example:
* function add(a, b) {
* return a + b;
* }
*
* var add1 = compose(add, 1);
* add1(2); // 3
*
* @param {Function} func The function to wrap.
*
* @return {Function} The composed function. (for lack of better name.)
*/
function compose(func) {
var prevArgs = Array.prototype.slice.call(arguments, 1);
return function() {
var newArgs = Array.prototype.slice.call(arguments);
var args = prevArgs.concat(newArgs);
return func.apply(this, args);
};
}
/**
* Validate the structure of the spreadsheet.
*
* @param {{templateId: string,
* targetName: string,
* columns: Array<string>,
* firstContentRow: number,
* columnMappingSpecialCases: Object
* }} config Spreadsheet configuration. `templateId` and `targetName`
* are used to find the spreadsheet. `columns` are the
* expected columns in the spreadsheet. `firstContentRow` is
* the first row index after the header row.
* `columnsMappingSpecialCases` is a dictionary (string:
* string) for headers that require special handling.
*
* @return {boolean} Whether the spreadsheet is valid.
*/
function validateSpreadsheet(config) {
/**
* Remove whitespaces, any non-numeric value, and lower case given header.
*
* @param {Object} specialCases A dictionary (string: string) for
* headers that require special handling.
* @param {string} header The header to parse.
*
* @return {string} A normalized header.
*/
function _parseHeader(specialCases, header) {
// Remove whitespaces, non-alphanumeric characters and lowercase
// all letters.
var parsed = header.replace(/ |\?|\W/g, '').toLowerCase();
// Handle special cases.
if (!isEmpty(specialCases)) {
var specialCase = specialCases[parsed];
if (!isEmptyString(specialCase)) {
parsed = specialCase;
}
}
return parsed;
}
/**
* Verify that the existing headers are equal to expected headers.
*
* @param {Array<string>} headers Existing headers.
* @param {Array<string>} expectedHeaders The expected headers.
*
* @return {{pass: boolean, mapping: Object}} The verification result.
* `pass` indicates whether the validation passed. `mapping`
* (string: string) contains meta-data on failed mapping.
*/
function _checkHeaders(headers, expectedHeaders) {
var pass = true;
var failureMapping = {};
// Create a copy of expectedHeaders so we could manipulate it.
expectedHeaders = expectedHeaders.slice();
// Go over every header.
headers.forEach(function(header) {
var expectedHeader = expectedHeaders.shift();
if (header !== expectedHeader) {
pass = false;
failureMapping[expectedHeader] = header;
}
});
return {
pass: pass,
mapping: failureMapping
};
}
/**
* Validate a spreadsheet's column headers.
*
* @param {{templateId: string,
* targetName: string,
* columns: Array<string>,
* firstContentRow: number,
* columnMappingSpecialCases: {'string': string}
* }} config Spreadsheet configuration.
*
* @return {{pass: boolean, mapping: Object}} The verification result.
* `pass` indicates whether the validation passed. `mapping`
* (string: string) contains meta-data on failed mapping.
*/
function _verifyColumns(config) {
var sheet = getCachedSheet(config);
var header = sheet.getRange(config.firstContentRow - 1, 1, 1,
config.columns.length);
var headerValues = header.getValues()[0];
var passObj = _checkHeaders(config.columns.map(compose(_parseHeader,
config.columnMappingSpecialCases)),
headerValues.map(compose(_parseHeader,
config.columnMappingSpecialCases)));
return passObj;
}
// Verify columns.
var passMain = _verifyColumns(config);
if (!passMain.pass) {
Logger.log('\nColumns mapping (sheet:script):\n' +
JSON.stringify(passMain.mapping, null, 2));
}
return passMain.pass;
}
/**
* Retrieves an Ad object from a SpreadsheetRow object.
*
* @param {SpreadsheetRow} row
* @param {string} type The type of ad. Options are either 'STA' or 'ETA'.
*
* @return {?Ad} Returns an Ad if found, or null if no ad is found.
*/
function getAdFromRow(row, type) {
var ad = null;
if (type === 'ETA') {
try {
ad = getAd([row.get('adGroupId'), row.get('etaId')]);
} catch (err) {
Logger.log('Failed to get ETA due to: ' + err);
}
}
else if (type === 'STA') {
try {
ad = getAd([row.get('adGroupId'), row.get('staId')]);
} catch (err) {
Logger.log('Failed to get STA due to: ' + err);
}
}
else {
throw 'Incorrect Ad type.';
}
return ad;
}
/**
* Retrieves an Ad object.
*
* @param {Array<number>} adIdAndAdgroupId An array consisting of an
* AdGroup Id and Ad Id. AdGroup Id and Ad Id
* is the unique key for identifying an Ad.
*
* @return {?Ad} Returns an Ad if found, or null if no ad is found.
*/
function getAd(adIdAndAdgroupId) {
if (!adIdAndAdgroupId || adIdAndAdgroupId.length < 2 ||
isEmptyString(adIdAndAdgroupId[0]) ||
isEmptyString(adIdAndAdgroupId[1])) {
throw '`adIdAndAdgroupId` must include two values: adGroup ID, and Ad ID';
}
var adSelector = AdWordsApp.ads()
.withIds([adIdAndAdgroupId]);
var adIterator = adSelector.get();
// There is at most 1 Ad.
if (adIterator.hasNext()) {
var currentAccount = AdWordsApp.currentAccount();
return new Ad(null, null, adIterator.next(), currentAccount);
} else {
return null;
}
}
/**
* An object representation of a row in a spreadsheet.
*
* @constructor
*
* @param {Array<Object>} values The values in this row.
* @param {Range} allRange A range where this row is part of.
* @param {number} rowOffset The row offset for this row within `allRange`.
* @param {Object} columnNamesToIndices A mapping between a columnName
* and an index.
*/
function SpreadsheetRow(values, allRange, rowOffset, columnNamesToIndices) {
if (isEmpty(values) || isEmpty(allRange) || isNaN(rowOffset) ||
isEmpty(columnNamesToIndices)) {
throw 'Unable to retrieve row in spreadsheet because of incorrect ' +
'parameters passed to the "SpreadsheetRow" function.';
}
// Because of the hard dependency in 'errorMessage', validate whether it
// exists.
if (!columnNamesToIndices.hasOwnProperty('errorMessage') ||
isEmpty(columnNamesToIndices.errorMessage)) {
throw '`errorMessage` is a required attribute for `columnNamesToIndices` ' +
'in order to be able to properly identify where to write error ' +
'messages in a spreadsheet row';
}
// The Range of the row in sheet.
this.range_ = allRange.offset(rowOffset, 0, 1, allRange.getLastColumn());
// The index of the row in sheet.
this.rowIndex_ = this.range_.getRowIndex();
// Storing a reference to this object and it will be used as read-only.
this.columnNamesToIndices_ = columnNamesToIndices;
this.hasErrors_ = false;
// The values of the row in sheet.
this.values_ = values;
// Start fresh by removing any existing errors in this particular row.
this.markAsResolved();
// Check for proper values.
this.validateValues_();
}
/**
* Validates values in this row, if any improper values are detected
* then display appropriate error message in error column.
*
* @private
*/
SpreadsheetRow.prototype.validateValues_ = function() {
var staStatusValue = this.get('staStatus');
if (!isSupportedStatus(staStatusValue)) {
this.markAsError('Unsuported STA status with value of \'' +
staStatusValue + '\'.');
}
var etaStatusValue = this.get('etaStatus');
if (!isSupportedStatus(etaStatusValue)) {
this.markAsError('Unsuported ETA status with value of \'' +
etaStatusValue + '\'.');
}
};
/**
* Retrieve the column index of a column name.
*
* @param {string} columnName The name of the column in the spreadsheet we want
* to retrieve index for.
*
* @return {number} The index of columnName.
* @private
*/
SpreadsheetRow.prototype.getColumnIndex_ = function(columnName) {
if (!(columnName in this.columnNamesToIndices_)) {
throw 'Column "' + columnName + '" does not exist.';
} else {
return this.columnNamesToIndices_[columnName];
}
};
/**
* Sets the value of a given column for this row.
*
* @param {string} columnName
* @param {string} value
*
*/
SpreadsheetRow.prototype.set = function(columnName, value) {
var columnIndex = this.getColumnIndex_(columnName);
// Add 1, because SpreadSheetApp's Range is relative to 1.
this.range_.getCell(1, columnIndex + 1).setValue(value);
this.values_[columnIndex] = value;
};
/**
* Gets the value at a given column for this row.
*
* @param {string} columnName
*
* @return {number|boolean|Date|string} The value stored at given column.
*/
SpreadsheetRow.prototype.get = function(columnName) {
var columnIndex = this.getColumnIndex_(columnName);
return this.values_[columnIndex];
};
/**
* Parse value as a JSON object. Value must be either string or an array.
* Also handles the case where a value may be a string with out
* quotations.
*
* @param {string} columnName
*
* @return {Array}
*/
SpreadsheetRow.prototype.getArray = function(columnName) {
var columnIndex = this.getColumnIndex_(columnName);
var values = [];
if (isEmptyString(this.values_[columnIndex])) {
return values;
}
try {
values = JSON.parse(this.values_[columnIndex]);
} catch (err) {
// Maybe a valid string with no quotations is present.
var valueWithQuotes = '"' + this.values_[columnIndex] + '"';
try {
values = JSON.parse(valueWithQuotes);
} catch (err) {
throw 'Incorrect JSON value stored in `' + columnName + '` column.';
}
}
// Must be an array or string.
if (!Array.isArray(values) && (typeof values !== 'string')) {
throw 'Incorrect array value stored in `' + columnName + '` column.';
}
// At this point it must be an array or string. If it's a string,
// keep return type consistent by always returning an array.
if (!Array.isArray(values)) {
values = [values];
}
return values;
};
/**
* Gets the number value at a given column for this row.
*
* @param {string} columnName
*
* @return {number} The number value stored at given column.
* @throws {string}
*/
SpreadsheetRow.prototype.getNumber = function(columnName) {
var value = this.get(columnName);
if (isNaN(value)) {
throw 'Value stored in "' + columnName + '" is not a valid number.';
}
return Number(value);
};
/**
* Gets the string value at a given column for this row.
*
* @param {string} columnName
*
* @return {string} The string value stored at given column.
*/
SpreadsheetRow.prototype.getString = function(columnName) {
var value = this.get(columnName);
return value.toString();
};
/**
* Retrieve the cell at a given column for this row.
*
* @param {string} columnName
*
* @return {Range}
*/
SpreadsheetRow.prototype.getCell = function(columnName) {
var columnIndex = this.getColumnIndex_(columnName);
return this.range_.getCell(1, columnIndex + 1);
};
/**
* Returns true if row has been marked with an error.
*
* @return {boolean}
*/
SpreadsheetRow.prototype.hasErrors = function() {
return this.hasErrors_;
};
/**
* Highlights row signalling that an error has occured.
*
* @param {string|Array<string>} messages Messages to print and add to error
* column. Can be a single string or an
* array of strings.
*/
SpreadsheetRow.prototype.markAsError = function(messages) {
if (isEmpty(messages) || isEmptyString(messages)) {
throw 'Empty `messages` parameter provided. A non-empty message must be ' +
'provided.';
}
this.range_.setBackground('red');
if (Array.isArray(messages)) {
messages = messages.join('\n- ');
}
messages = '- ' + messages + '\n';
// Append to any content already present in `errorMessage` column.
this.set('errorMessage', this.get('errorMessage') + messages);
print(messages);
this.hasErrors_ = true;
};
/**
* Removes any previous highlights set on this row.
*/
SpreadsheetRow.prototype.markAsResolved = function() {
this.range_.setBackground(null);
this.set('errorMessage', '');
};
/**
* Retrieve the index of this row in spreadsheet.
*
* @return {number}
*/
SpreadsheetRow.prototype.getRowIndex = function() {
return this.rowIndex_;
};
/**
* Determines whether the `status` parameter is equivalant to one of the
* supported Ad statuses.
*
* @param {string} status
*
* @return {boolean}
*/
function isSupportedStatus(status) {
return (status === Ad.statuses.ENABLED ||
status === Ad.statuses.PAUSED);
}
/**
* Makes an array of objects indexable by a certain attribute in it's objects.
*
* @param {Array<Object>} objects An array of objects from which index
* keys will be retrieved from.
* @param {Array<string>} keys The keys to set index as. If more than one
* element, then keys will get concatenated.
*
* @return {Object}
*/
function createIndexableObjectFromKeys(objects, keys) {
if (isEmpty(objects)) {
return {};
}
var indexableObject = {};
objects.forEach(function(object) {
var newIndexArray = [];
keys.forEach(function(key) {
var indexValue = object[key];
if (!isEmptyString(indexValue)) {
newIndexArray.push(indexValue);
}
});
if (newIndexArray.length > 0) {
var newIndexString = newIndexArray.join('|');
if (!isEmptyString(newIndexString)) {
indexableObject[newIndexString] = object;
}
}
});
return indexableObject;
}
/**
* Retrieves the last row checked when syncing spreadsheet and increments
* value by 1.
*
* @param {Sheet} sheet
* @param {string} key
*
* @return {Object} An object with a reference to cell and integer representing
* the index of last row checked.
*/
function getLastRowCheck(sheet, key) {
var cell = sheet.getRange(key);
var lastRowChecked = cell.getValue();
if (isNaN(lastRowChecked)) {
return {
cell: null,
value: 0
};
}
cell.setValue(lastRowChecked + 1);
return {
cell: cell,
value: lastRowChecked
};
}
/**
* Retrieves all non-empty rows, starting from config.startRowIndex.
*
* @param {Sheet} sheet A sheet in the spreadsheet.
* @param {number} firstContentRow The index which content is expected to start.
* @param {number} nonEmptyColumnCheck The column index to check and determine
* if row is considered empty.
* @param {Object} columnNamesToIndices A mapping between column indices and
* column names.
* @param {boolean} isValidOnly If true, invalid rows will be filtered out.
* @param {Array<SpreadsheetRow>} rowCache The row cache to use for the rows
* output. Only valid if it matches the
* target output size.
*
* @return {{rows: Array<SpreadsheetRow>,
* maxRows: number,
* newRowCache: Array<SpreadsheetRow> }} Object containing:
* rows Containing the main content of the sheet.
* maxRows The maximum number of row values possible in
* the range.
* newRowCache A sparse array of all rows added + cached
* rows during this execution of getContentRows.
*/
function getContentRows(sheet, firstContentRow, nonEmptyColumnCheck,
columnNamesToIndices, isValidOnly, rowCache) {
// Make sure parameters are valid.
if (isNaN(firstContentRow) ||
firstContentRow <= 0 ||
isEmptyString(nonEmptyColumnCheck) ||
isEmptyString(columnNamesToIndices) ||
(!isEmpty(rowCache) && !Array.isArray(rowCache))) {
throw 'Unable to retrieve spreadsheet\'s content because of incorrect ' +
'parameters passed to the "getContentRows" function.';
}
// Retrieve all the range at once to avoid checking each row separately.
var endRowIndex = sheet.getLastRow();
var lastColumnIndex = sheet.getLastColumn();
if (firstContentRow > endRowIndex) {
// Spreadsheet is empty.
return {
rows: []
};
}
var range = sheet.getRange(firstContentRow, 1,
endRowIndex - firstContentRow + 1,
lastColumnIndex);
var values = range.getValues();
var maxRows = range.getNumRows();
if (rowCache &&
rowCache.length !== maxRows) {
throw 'Parameter rowCache passed to "getContentRows" function ' +
'is incorrect size: ' +
'rowCache.length: ' + rowCache.length + '!== ' +
'maxRows: ' + maxRows;
}
var spreadsheetRows = [];
var nonEmptyIndexCheck = columnNamesToIndices[nonEmptyColumnCheck];
if (isEmpty(nonEmptyIndexCheck)) {
throw 'Please make sure to supply a value for `nonEmptyColumnCheck` that ' +
'defines whether a row is considered to be empty or not.';
}
var newRowCache = [];
newRowCache.length = maxRows;
for (var rowOffset = 0; rowOffset < maxRows; rowOffset++) {
// Check against the nonEmptyColumnIndex, to make sure
// this row contains values.
if (!isEmptyString(values[rowOffset][nonEmptyIndexCheck])) {
var row = null;
if (rowCache) {
row = rowCache[rowOffset];
}
if (!row) {
row = new SpreadsheetRow(values[rowOffset], range, rowOffset,
columnNamesToIndices);
}
if (!row) {
throw 'Spreadsheet row was not correctly created for rowOffset: ' +
rowOffset;
}
// Cache row, even if it contains errors.
newRowCache[rowOffset] = row;
if (isValidOnly && row.hasErrors()) {
continue;
}
spreadsheetRows.push(row);
}
}
return {
rows: spreadsheetRows,
newRowCache: newRowCache,
maxRows: maxRows
};
}
/**
* Send an email notification.
*
* @param {string} to The email address to send the notification to.
* @param {number} event The event type.
* @param {Object} payload The payload used in the email template.
*
* @return {boolean} Success/failure.
*/
function notify(to, event, payload) {
var template = getEmailTemplate(event, payload);
if (template) {
// Strip down HTML elements for devices that don't render HTML.
var body = template.body.replace(/<b>|<\/b>|<i>|<\/i>/g, '')
.replace(/<br\/>/g, '\n');
try {
MailApp.sendEmail(to, template.subject, body, {
htmlBody: template.body
});
// Successfully sent email notification.
return true;
} catch (err) {
print('\n******* ERROR: Failed to send email to: ' + to + '. ' +
err + '*******\n');
}
}
// Failed to send email notification.
return false;
}
/**
* Get email template by event type.
*
* @param {number} event The event type.
* @param {Object} payload The payload used in the template.
*
* @return {Object} An object with `subject` and `body` attributes,
* or `null` if not found.
*/
function getEmailTemplate(event, payload) {
var template = null;
switch (event) {
case SPREADSHEET_CREATED:
template = {
subject: '[ETA Transition Helper] installed successfully',
body: 'Hello,<br/><br/>' +
'The ETA Transition Helper was installed successfully on your ' +
'account \'' + AdWordsApp.currentAccount().getName() +
'\'.<br/><br/>' +
'To view the exported standard text ads and begin creating ' +
'expanded text ads open <a href=\'' + payload + '\'>this ' +
'spreadsheet</a>.<br/><br/>' +
'Yours,<br/>' +
'ETA Transition Helper'
};
break;
}
return template;
}
/**
* Copies an existing file.
*
* @param {string} sourceId The ID of the existing file.
* @param {string} outputName The output file name.
*
* @return {?File} A handler on the new file, or null if failed.
*/
function copyFile(sourceId, outputName) {
var output = null;
try {
var source = DriveApp.getFileById(sourceId);
var destination = DriveApp.getRootFolder();
// Create the copy in the root folder.
output = source.makeCopy(outputName, destination);
output.setDescription('[' + getUniqueFileTag(sourceId) + '] ' + outputName);
print('- Created a new file: ' + output.getUrl());
} catch (e) {
print('\n******* ERROR: Failed to copy file: ' + e + '*******');
}
return output;
}
/**
* Get the email address of the user who owns this file.
*
* @param {File|Spreadsheet} file Selected file.
*
* @return {?string} The email address or `null` if no owner found.
*/
function getFileOwnerEmail(file) {
var owner = file && file.getOwner();
return owner && owner.getEmail();
}
/**
* Validate the CID for an account in the format 123-456-7890.
*
* @param {string} cid The CID string to validate.
*
* @return {number} Return the CID in numerical value i.e. 1234567890
* @throws {string}
*/
function validateCid(cid) {
if (!cid) {
throw 'Invalid CID: Empty or null : ' + cid;
}
if (!isString(cid)) {
throw 'Invalid CID: Is not a string : ' + cid;
}
var cidArray = cid.split('-');
if (cidArray.length !== 3) {
throw 'Invalid CID: Incorrect separators : ' + cid;
}
if (cidArray[0].length !== 3) {
throw 'Invalid CID: Incorrect part 1 : ' + cid;
}
if (cidArray[1].length !== 3) {
throw 'Invalid CID: Incorrect part 2 : ' + cid;
}
if (cidArray[2].length !== 4) {
throw 'Invalid CID: Incorrect part 3 : ' + cid;
}
var cidInt = parseInt(cidArray.join(''), 10);
if (isNaN(cidInt)) {
throw 'Invalid CID: isNaN : ' + cid;
}
return cidInt;
}
//////////////////////////////////////////////////////////////////////////
/////////////////////// SYNC SPREADSHEET HELPERS /////////////////////////
//////////////////////////////////////////////////////////////////////////
/**
* Combines an array of Ads with their respective rows in the spreadsheet.
*
* @param {Array<string>} customerIds An array of customerIds to filter
* resulting Ads. Use empty array to not
* filter any Ads.
*
* @return {Array<Object>} Returns an array of objects, in which each object
* has a reference to a given row in the spreadsheet,
* and its respective report.
*/
function getReportWithSpreadsheetRows(customerIds) {
if (!Array.isArray(customerIds)) {
throw "customerIds is not a valid array";
}
// Open spreadsheet.
var sheet = getCachedSheet(CONFIG.spreadsheet, CONFIG.email);
var rowObject = getContentRows(sheet,
CONFIG.spreadsheet.firstContentRow,
CONFIG.spreadsheet.nonEmptyColumnCheck,
CONFIG.spreadsheet.columnNamesToIndices,
true,
CONFIG.spreadsheet.rowCache);
var nonEmptyValidRows = rowObject.rows;
CONFIG.spreadsheet.rowCache = rowObject.newRowCache;
// Get rows containing content.
var report = AdWordsApp.report(
'SELECT ' + CONFIG.reportFields.join(',') + ' ' +
'FROM AD_PERFORMANCE_REPORT ' +
'WHERE AdType = "TEXT_AD" ' +
' AND Status = "ENABLED" ' +
' AND CreativeApprovalStatus != "DISAPPROVED" ' +
'DURING ' + CONFIG.duration, {
apiVersion: CONFIG.apiVersion
});
var mostPerformingAds = getMostPerformingAds(report, CONFIG);
var performingETA = getETAReports(CONFIG);
// Swith Ads to a [id]=>Ad object structure.
mostPerformingAds = createIndexableObjectFromKeys(mostPerformingAds, ['id']);
performingETA = createIndexableObjectFromKeys(performingETA, ['id']);
var rowsAndAds = [];
nonEmptyValidRows.forEach(function(row) {
// Skip if this row does not belong to any of the customerIds passed.
if (customerIds.length > 0 &&
customerIds.indexOf(row.get('customerId')) === -1) {
return;
}
var sta = mostPerformingAds[row.get('staId')];
var eta = performingETA[row.get('etaId')];
if (!sta) {
sta = null;
}
if (!eta) {
eta = null;
}
rowsAndAds.push({
row: row,
sta: sta,
eta: eta
});
});
return rowsAndAds;
}
/**
* Splices `spreadsheetRowsAndReport` according to `lastRowCheckedIndex`
*
* @param {Array<Object>} spreadsheetRowsAndReport
* @param {number} lastRowCheckedIndex The index of last row processed in
* previous run.
* @param {number} headerIndex The index of the row containing header.
*/
function spliceFromLastRowChecked(spreadsheetRowsAndReport, lastRowCheckedIndex,
headerIndex) {
if (isNaN(lastRowCheckedIndex)) {
lastRowCheckedIndex = 0;
}
// Get Index stored in lastRowCheckedCell relative to header.
lastRowCheckedIndex = lastRowCheckedIndex - headerIndex;
// If lastRowCheckedIndex is between 0 and number of rows in spreadsheet
// (not inclusive) then we can ignore all rows up to lastRowCheckedIndex.
if (lastRowCheckedIndex > 0 &&
lastRowCheckedIndex < spreadsheetRowsAndReport.length) {
spreadsheetRowsAndReport.splice(0, lastRowCheckedIndex);
}
}
/**
* Determines whether 'etaObj' has the necessary fields and values
* set for ETA creation.
*
* @param {Object} etaObj An ETA object with relevant ETA attributes for ETA
* creation.
*
* @return {Object} An Ad object with 'ad' and 'errors' attributes. If no errors
* were encountered then 'ad' will contain an Ad object of
* newly created Ad, and 'errors' will be an empty array.
* Otherwise, if an error was encountered then 'error' will be
* an array of strings and 'ad' will be null.
*/
function isValidForETACreation(etaObj) {
var returnObject = {
ad: null,
errors: []
};
/**
* REQUIRED:
* - finalURL
* - headline1
* - headline2
* - description
*/
if (!etaObj.finalURLs || etaObj.finalURLs.length < 1) {
returnObject.errors.push('Failed to create ETA: finalUrl is missing.' +
' [Required]');
}
if (etaObj.finalURLs && etaObj.finalURLs.length > 1) {
returnObject.errors.push('Failed to create ETA: finalUrl supports only a' +
' single URL');
}
if (!etaObj.headline1) {
returnObject.errors.push('Failed to create ETA: headline1 is missing.' +
' [Required]');
}
if (!etaObj.headline2) {
returnObject.errors.push('Failed to create ETA: headline2 is missing.' +
' [Required]');
}
if (!etaObj.description) {
returnObject.errors.push('Failed to create ETA: description is missing.' +
' [Required]');
}
/**
* OPTIONAL:
* - path1
* - path2 (Only if path1)
* - mobileFinalURL
* - trackingTemplate
* - customParameters
*/
if (etaObj.path2 && !etaObj.path1) {
returnObject.errors.push('Failed to create ETA: path1 is missing. Setting' +
' path2 requires path1 to be set');
}
if (etaObj.mobileFinalURLs && etaObj.mobileFinalURLs.length > 1) {
returnObject.errors.push('Failed to create ETA: mobileFinalURL supports' +
' only a single URL');
}
if (!isValidParamsObject(etaObj.customParameters,
['string'],
false).success) {
returnObject.errors.push('Failed to create ETA: customParameters is not' +
' a valid parameters object');
}
return returnObject;
}
/**
* Parses a `spreadsheetRow` for appropriate ETA attributes.
*
* @param {SpreadSheetRow} spreadsheetRow A row in the spreadsheet.
*
* @return {Object} Returns an object containing the necessary fields
* for creating an ETA.
* @throws {string}
*/
function parseETA(spreadsheetRow) {
var customParametersStr = spreadsheetRow.getString('customParameters');
var customParameters = null;
if (customParametersStr) {
try {
customParameters = JSON.parse(customParametersStr);
}
catch (err) {
throw 'Invalid customParemeters value in spreadsheet.';
}
}
return {
campaignId: spreadsheetRow.getNumber('campaignId'),
adGroupId: spreadsheetRow.getNumber('adGroupId'),
finalURLs: spreadsheetRow.getArray('finalUrl'),
headline1: spreadsheetRow.getString('headline1'),
headline2: spreadsheetRow.getString('headline2'),
description: spreadsheetRow.getString('description'),
path1: spreadsheetRow.getString('path1'),
path2: spreadsheetRow.getString('path2'),
mobileFinalURLs: spreadsheetRow.getArray('mobileFinalUrl'),
trackingTemplate: spreadsheetRow.getString('trackingTemplate'),
customParameters: customParameters
};
}
/**
* Creates a new ETA.
*
* @param {SpreadSheetRow} spreadsheetRow A row in the spreadsheet.
*
* @return {Object} An Ad object with 'ad' and 'errors' attributes. If no errors
* were encountered then 'ad' will contain an Ad object of
* newly created Ad, and 'errors' will be an empty array.
* Otherwise, if an error was encountered then 'error' will be
* an array of strings and 'ad' will be null.
*/
function createETA(spreadsheetRow) {
var etaObj;
// Retrieve ETA fields from spreadsheet row.
try {
etaObj = parseETA(spreadsheetRow);
}
catch (err) {
return {
ad: null,
errors: ['Failed to create ETA: ' + err.message]
};
}
var returnObject = isValidForETACreation(etaObj);
// Retrieve parent Campaign to check if campaign exists.
var campaignIterator = AdWordsApp.campaigns()
.withIds([etaObj.campaignId])
.get();
if (!campaignIterator.hasNext()) {
returnObject.errors.push('Unable to create ETA because Campaign parent' +
' with id ' + etaObj.campaignId + ' no longer' +
' exists.');
}
// Retrieve parent AdGroup to add new Ad and check if AdGroup exists.
var adGroupIterator = AdWordsApp.adGroups()
.withIds([etaObj.adGroupId])
.get();
if (!adGroupIterator.hasNext()) {
returnObject.errors.push('Unable to create ETA because AdGroup parent' +
' with id ' + etaObj.adGroupId + ' no longer' +
' exists.');
}
var adGroup = adGroupIterator.next();
if (returnObject.errors.length === 0) {
try {
var adBuilder = adGroup.newAd().expandedTextAdBuilder()
.withHeadlinePart1(etaObj.headline1)
.withHeadlinePart2(etaObj.headline2)
.withDescription(etaObj.description)
.withFinalUrl(etaObj.finalURLs[0]);
if (etaObj.path1) {
adBuilder.withPath1(etaObj.path1);
if (etaObj.path2) {
adBuilder.withPath2(etaObj.path2);
}
}
if (etaObj.mobileFinalURLs && etaObj.mobileFinalURLs[0]) {
adBuilder.withMobileFinalUrl(etaObj.mobileFinalURLs[0]);
}
if (etaObj.trackingTemplate) {
adBuilder.withTrackingTemplate(etaObj.trackingTemplate);
}
if (etaObj.customParameters) {
adBuilder.withCustomParameters(etaObj.customParameters);
}
var result = adBuilder.build();
if (!result.isSuccessful()) {
// No ad was created.
var errors = result.getErrors();
var message = 'Failed to create ETA with errors:';
for (var i = 0; i < errors.length; i++) {
message += '\n' + errors[i];
}
returnObject.errors.push(message);
} else {
var currentAccount = AdWordsApp.currentAccount();
returnObject.ad = new Ad(null, null, result.getResult(),
currentAccount);
}
}
catch (err) {
var message = 'Failed to create ETA: ' + err.message;
returnObject.errors.push(message);
}
}
return returnObject;
}
/**
* Creates a label if it doesn't exist.
*
* @param {string} labelName The name of the label to create.
*
* @return {boolean} True if label was successfully created, or already exist.
*/
function createLabel(labelName) {
if (isEmptyString(labelName)) {
return false;
}
var trimmedLabelName = labelName.trim();
// There can only be one label with `labelName`.
var labelIterator = AdWordsApp.labels()
.withCondition('Name = "' + trimmedLabelName + '"')
.get();
// If labelIterator has no value, then create label.
if (!labelIterator.hasNext()) {
try {
AdWordsApp.createLabel(trimmedLabelName);
return true;
} catch (err) {
print('Failed to create ' + trimmedLabelName);
return false;
}
}
// Label exist, no need to create it.
return true;
}
/**
* Used for tracking changes to an Ad.
*
* @param {?number} adId An id of an Ad.
* @param {?number} adGroupId An id of an AdGroup.
*
* @constructor
*/
function AdChange(adId, adGroupId) {
this.structure = {
adId: null,
adGroupId: null
};
this.structure.adId = null;
this.structure.adGroupId = null;
if (!isEmpty(adId) && !isEmpty(adGroupId)) {
this.structure.adId = adId;
this.structure.adGroupId = adGroupId;
}
this.structure.changes = [];
}
/**
* Append an object representing change, if changeFunction returns a truthful
* value. This serves as a wrapper to all Ad change events such as changing
* status, labels, etc.
*
* @param {string} fieldName The name field being changed.
* @param {string} oldValue
* @param {string} newValue
*/
AdChange.prototype.trackChange = function(fieldName, oldValue, newValue) {
if (oldValue === newValue || isArrayShallowEquals(oldValue, newValue)) {
return;
}
this.structure.changes.push({
fieldName: fieldName,
oldValue: oldValue,
newValue: newValue
});
};
/**
* This serves as a wrapper for Ad create changes.
*
* @param {number} adId Id of newly created Ad.
* @param {number} adGroupId Id of AdGroup parent of newly created Ad.
*/
AdChange.prototype.trackCreate = function(adId, adGroupId) {
this.structure.created = true;
this.structure.adId = adId;
this.structure.adGroupId = adGroupId;
};
/**
* Retrieves a reference to changes. Changes are represented by the internal
* `structure` property.
*
* @return {Object}
*/
AdChange.prototype.getChangeStruct = function() {
return this.structure;
};
/**
* Reset all changes that were tracked.
*/
AdChange.prototype.resetChanges = function() {
this.structure.changes = [];
this.structure.created = false;
};
