With the ETA Transition Helper, advertisers can now customize the bulk creation of expanded text ads (ETAs) from their existing standard text ads (STAs).
The tool has two main components:
- An AdWords script that copies your STAs to a Google Sheet and then helps you write ETAs to add to your AdWords account.
- A Google Sheet that displays STAs and allows you to configure the associated ETAs.
This guide will walk you through the setup and ad creation process.
Installation and setup
Before beginning the setup process, select the AdWords account that contains the STAs you'd like to use as the basis for your ETAs. You can install the script on either a manager account or a client account.
Adding the script
- Sign in to the AdWords account you selected above.
- Select Bulk operations in the left navigation
bar and click Scripts.
- Click the red + SCRIPT button.
- Remove everything in the text area. Then copy the source code at the bottom of this page and paste it into the text area.
- If you've decided to install the script on a manager account, you can whitelist specific
sub-manager or client accounts by entering their CIDs in the script. By default the fields
selectByAccountIdsandselectBySubMccIdare commented out. Depending on your use case, uncomment one or both fields. If you would like to target a list of client account CIDs, enter them in theselectByAccountIdsfield as shown below. If you wish to process all client accounts within one sub-manager account, you can specify it in theselectBySubMccIdfield. (Note that you can only enter one value in theselectBySubMccIdfield.) If you have a list of client account CIDs (for example 123-456-7890, 098-765-4321), but only want to target the client accounts under a specific sub-manager account (for example, where 123-456-7890 belongs to 765-432-1098), you can use both fields as shown below (only account 123-456-7890 will be processed).To ensure processing doesn't exceed limits, we recommend running the script on a maximum of 50 accounts at a time.
- Name your script by entering a name in the Script field located above the text area. We recommend including the version number in the script name, for example, "ETA Transition Helper VX.Y".
- During the first run the script will generate a Google Sheets spreadsheet and send it to the email address associated with the AdWords account. If you wish to send the spreadsheet to a different address, enter the address in the email field at the top of the script, as shown in the image above.
- Save the script by clicking the Save button in the top left corner of the text area.
- Click Authorize now to allow the script to act on your behalf. A pop-up window appears asking you to allow this script to manage your AdWords campaigns on your behalf. Click Allow.
- Perform the first run of the script by clicking the Run script
now button below the text area. This will open a text box. Select
either PREVIEW or Run without previewing.
On the script's first run both options will populate the generated spreadsheet with the highest performing enabled STAs in your account. For an account with approximately 2,000 active STAs and
numOfAds= 10, we expect the script to finish exporting the ads to the spreadsheet in under two minutes. The fieldnumOfAdsrepresents the maximum number of ETAs that are available for creation in a given run. It is set to 800 by default, and can be optimized to your use case. We recommend against settingnumOfAdsto a higher number than the default value of 800.You can read a summary of all changes made during the first run of the script by clicking Details or Logs from the Scripts overview page as shown below.
By default, performance is determined as a function of CTR and Impressions. The performance measure and the number of ads that gets pulled into the spreadsheet can be configured in the script.
- Keep the script open. We'll return to it in the steps below. Note that
future script runs can be made directly from the Bulk operations > Scripts
home screen.
Preparing the spreadsheet
The first run of the script creates the template spreadsheet, and sends its link to the previously entered email address. Open the spreadsheet.
Do the following if you need to give other Google accounts access to the sheet.
- Click the Share button in the top right corner. This opens a pop-up where you can configure share permissions.
- Enter the email address associated with the AdWords account of the user.
- Make sure the Google account associated with the AdWords account has edit
access to the spreadsheet by selecting Can edit from the
dropdown. Click Send.
Creating expanded text ads
Hooray! You're now ready to create expanded text ads!
First, let's take a minute to examine your spreadsheet. Read-only fields have grey column headers and editable fields have blue and orange ones. There are three main groups of columns:
- The first group provides information on the existing STAs that were imported. You'll see that most of the fields are read-only (grey). The only editable field is the STA Status (blue). Changing the STA Status updates the STA during the next script run. Any mismatches between display and final URLs will be highlighted since these fields are required to be identical for ETAs.
- The second group of columns are designated for the ETAs you'll soon be creating. You'll see that Headline 1 and Description are already pre-populated based on the corresponding STA. Again, this group is broken out into read-only (grey) and input (blue) fields.
- The third section is for the Ready To Upload? flag which indicates an ETA is ready to be created the next time the script is run.
If you'd like to change the status of your existing STAs, you can do so by simply changing the value in the STA Status column:
Now let's create some ETAs!
- The minimum requirement to create an ETA is to add a second headline, but you can also set any other fields such as ETA Status, Mobile Final URL, or Path 1. Feel free to change any of the pre-populated fields as well. There are validation rules in place to ensure the fields comply with most character restrictions, as well as columns showing how many characters you can add. Also, be sure to check out our Help Center article for tips and tricks for writing effective text ads!
- While editing the ETA fields, you can request a preview of an ad in the active cell by selecting the Click to Preview button in the first row of the spreadsheet. Note that the display URL is taken from the standard text ad and not directly computed from the final URL and therefore may be different during serving time.
- Once you're happy with how your ETAs look, make sure to switch the Ready To Upload? column to Yes. This ensures that the ETA will be created and the STA status will be updated when the script is run.
- The second time you run the script, ETAs will be created. We recommend always previewing a script before running. You can do so by selecting Edit on the Scripts overview page and clicking the red PREVIEW button. This allows you to review the changes that would have been made if the script had been executed. Once you feel comfortable committing to the changes, click Run script now.
- After the script runs, you'll see that both the STA and the associated ETA have the label eta-upgrade. To revert unwanted changes, follow the steps in our Help Center article.
Source code
// @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: '',
// 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;
};
Need help? Visit our