adwords_get_list_of_accounts

/**
 * Writes a list of AdWords accounts in an MCC to a Google Sheet
 * Useful in conjunction with scripts that run on batches of accounts
 * 
 * The label-batching portion of this script was adapted from:
 * http://magentosupport.help/knowledgebase/using-labels-to-executeinparallel-exceeding-the-50-account-limit/
 */

var TIMEZONE = 'MST';
var TODAY_STR = Utilities.formatDate(new Date(), TIMEZONE, 'yyyy-MM');
var LABEL_PREFIX = 'LOA - Completed - ';
var FINISHED_LABEL_NAME = LABEL_PREFIX + TODAY_STR;
var DEST_SHEET_ID = "1AHog-Uau8bDUP90SEJlT64e8SA1QrI_jip2uY4w7CvE";
var SHEET_NAME = 'AW Accounts';
 
function main() {
  // Warning: if running in preview mode, this function will fail
  // and the selector that follows will also fail with "cannot read from AdWords"
  createLabelIfNeeded();
  removeLastMonthsLabel(); // This might not exist, but try to remove it
  var accountIter = MccApp.accounts()
    .withCondition("LabelNames DOES_NOT_CONTAIN '"+FINISHED_LABEL_NAME+"'")
    .withCondition("Impressions > 0")
    .forDateRange("LAST_MONTH")
    .withLimit(50)
    .get();
  // Add them to a list for the executeInParallel later
  var accountList = [];
  var completedAccounts = [];
  
  // Find all the accounts that have not been processed
  while(accountIter.hasNext()) {
    var account = accountIter.next();
    var accountName = account.getName() ? account.getName() : '--';
    var accountID = account.getCustomerId();
    accountList.push([accountID,accountName]);
    completedAccounts.push(accountID);
  }
  // If there are unprocessed accounts, process them
  if(accountList.length > 0) {
    updateSheet(accountList,SHEET_NAME,DEST_SHEET_ID)
  }
  applyLabelsToCompletedAccounts(completedAccounts);
}
 
// This function creates the required label to apply
// to completed accounts. 
function createLabelIfNeeded() {
  try {
    var labelIter = MccApp.accountLabels()
      .withCondition("LabelNames CONTAINS '"+FINISHED_LABEL_NAME+"'")
      .get();
  } catch(e) {
    MccApp.createAccountLabel(FINISHED_LABEL_NAME);
  }
}
 
// This function applies FINISHED_LABEL_NAME to each completed account
function applyLabelsToCompletedAccounts(completedAccounts) {
  var finishedAccountsIter = MccApp.accounts().withIds(completedAccounts).get();
  while(finishedAccountsIter.hasNext()) {
    var account = finishedAccountsIter.next();
    account.applyLabel(FINISHED_LABEL_NAME);
  }
}
 
// This function attempts to remove last month's label if it exists.
// If it doesn't exist, it does nothing.
function removeLastMonthsLabel() {
  var lastMonthStr = getLastMonth();
  var lastMonthLabel = LABEL_PREFIX + lastMonthStr;
  try {
    var labelIter = MccApp.accountLabels().withCondition("Name CONTAINS '"+lastMonthLabel+"'").get();
    while(labelIter.hasNext()) {
      labelIter.next().remove();
    }
  } catch(e) { 
    // do nothing
  }
}
 
function getLastMonth(){
  var d = new Date();
  var dString;
  d.setDate(1);
  d.setMonth(d.getMonth()-1);
  dString = Utilities.formatDate(d, TIMEZONE, 'yyyy-MM');
  return dString;
}

// takes an array with a header row and updates
// a sheet with a header row, replacing existing rows
// when the columns specified in the columns argument
// match
function updateSheet(ar,sheetName,id){
  var ss =  (id) ? SpreadsheetApp.openById(id) : SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  if (sheet == null) { 
    sheet = ss.insertSheet();
    sheet.setName(sheetName);
    writeArrayToSheet(ar,sheet,false);
  } else {
    var arSheet = arrayFromSheet(sheet) || ['ID','Account'];
    if (arSheet[0].length === ar[0].length){ //proceed if arrays match
      arSheet = arSheet.concat(ar);
      arSheet = deduplicateArray(arSheet);
      arSheet.sort(sortFunction2D);
      writeArrayToSheet(arSheet,sheet,true);
    } else {Logger.log('updateSheet() failed, arrays dont match')}
  }
}

//helper function to sort 2d arrays
function sortFunction2D(a, b) {
    if (a[0] === b[0]) {
        return 0;
    }
    else {
        return (a[0] > b[0]) ? -1 : 1;
    }
}

function arrayFromSheet(sheet,propertyNames,newPropertyNames){
  if (typeof sheet === 'string'){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheet);
  } 
  var arSheet = sheet.getDataRange().getValues();
  var headerData = arSheet[0];
  propertyNames = propertyNames || headerData;
  newPropertyNames = newPropertyNames || propertyNames;
  var ar = [];
  ar.push(newPropertyNames);
  var namesAndColumns = {};
  for (var i = 0, x = propertyNames.length; i<x;i++){
    namesAndColumns[propertyNames[i]] = headerData.indexOf(propertyNames[i]);
  }
  for (var j = 1, x = arSheet.length; j<x;j++){
    var row = [];
    for (var k in namesAndColumns){
      row.push(arSheet[j][namesAndColumns[k]]);
    }
    ar.push(row);
  }
  return ar;
}

function writeArrayToSheet(ar,sheet,replaceFirstRow){
  if (typeof sheet === 'string'){
    var sheetName = sheet;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheet);
  } 
  if (sheet == null) { 
    sheet = ss.insertSheet();
    sheet.setName(sheetName);
  };
  var range = sheet.getDataRange();
  range = replaceFirstRow ? range : sheet.getRange(2,1,range.getHeight(),range.getWidth());
  range.clearContent();
  var startRow = replaceFirstRow ? 1 : 2;
  var newRange = sheet.getRange(startRow,1,ar.length,ar[0].length);
  newRange.setValues(ar);
}


function deduplicateArray(ar) {
    var seen = {};
    var out = [];
    var len = ar.length;
    var j = 0;
    for(var i = 0; i < len; i++) {
         var item = ar[i];
         if(seen[item] !== 1) {
               seen[item] = 1;
               out[j++] = item;
         }
    }
    return out;
}