AdWords script - provides framework for scheduling reports in an MCC. Writes reports to a Google Sheet. Handles MCC limit of 50 accounts per script

adwords_report_scheduler.js

/* 
* written by Nico Brooks - www.twooctobers.com 
* use at your own risk
* 
* This script provides a generic framework for scheduling
* monthly AdWords reports and writing them to a Google sheet. 
* To use, copy the script, set the global variables at the top of the file 
* and customize the function getData. See 
* https://developers.google.com/adwords/api/docs/guides/awql
* for reporting options
* 
* The script is designed to run in an MCC, and overcomes the 50 
* account script limit by writing a datestamp to REPORT_COLUMN.
* When the script runs, it 
* The sheet specified by SHEET_ID needs to have a column for
* account IDs with the header 'ID'
*/

var SHEET_ID = '1e2XU-k5hO5nEIIaaPk4kp6MWxE-iZYo85-O8_7mqAJI'; // destination Google sheet
var ACCOUNTS_SHEET_NAME = 'Accounts'; // tab from which to get/put accounts and report date stamps
var RESULTS_SHEET_NAME = 'SQR ' + getMonthString(-1); // tab to which report data is written
var REPORT_COLUMN = 'SQR'; // the column on the ACCOUNTS_SHEET_NAME tab
var REPORT_HEADER = ['Account','Query','Clicks','Impressions']; // array specifying the column headers for the report tab
var TIMEZONE = 'MST'; // the timezone in which the report runs - this is so datestamps are accurate

// get list of accounts from sheet - check column for last update
function main() {
  var ss = SpreadsheetApp.openById(SHEET_ID);
  var sheet = ss.getSheetByName(ACCOUNTS_SHEET_NAME);
  var accountsList = objectifySheet(sheet);
  var toCheck = parseListOfAccounts(accountsList);
  var idList = [];
  if (toCheck.length){
    for(var i in toCheck) {
      idList.push(toCheck[i]['ID']);
    }
    Logger.log('Checking the following accounts: '+JSON.stringify(idList));
    MccApp.accounts().withIds(idList).executeInParallel('getData', 'finished');
    updateListOfAccounts(idList);
  }
}

// make changes to this function to generate different reports
function getData(){
  var account = AdWordsApp.currentAccount().getName();
  Logger.log('Running getData for ' + account);
    try {
      var ar = [];
      var report = AdWordsApp.report(
        'SELECT AccountDescriptiveName, Query, Clicks, Impressions ' +
        'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
        'WHERE Clicks > 0 ' +
        'DURING LAST_MONTH');
      var rows = report.rows();
      while (rows.hasNext()) {
        var row = rows.next();
        var account = row['AccountDescriptiveName'];
        var query = row['Query'];
		var clicks = parseInt(row['Clicks']);
        var imps = parseInt(row['Impressions']);
		ar.push([account,query,clicks,imps])
      }
      if (ar !== []){
        ar.sort(sortFunction);
        ar.splice(10,ar.length-10);
        ar = JSON.stringify(ar);
        return ar
      } else {
        return null
      }
    } catch(err){
      Logger.log('getData error: ' + err)
    }
}

// helper function for getData
// for sorting a 2D array on column 3 descending
function sortFunction(a, b) {
    if (a[2] === b[2]) {
        return 0;
    }
    else {
        return (a[2] > b[2]) ? -1 : 1;
    }
}


/*
* Everything after this shouldn't need to be changed
*/


function finished(results){
  var returnAr = [REPORT_HEADER];
  for(var i in results) {
    var result = results[i];
    // If the account function returns success
    if(result.getStatus() === 'OK') {
      var returnedValue = JSON.parse(result.getReturnValue());
      for (j = 0;j < returnedValue.length;j++){
        returnAr.push(returnedValue[j]);
      }
    } else {
      Logger.log({customerId:result.getCustomerId(), error: result.getError()});
    }
  }
  appendArrayToSheet(returnAr,SHEET_ID,RESULTS_SHEET_NAME);
  Logger.log('wrote results');
}


function updateListOfAccounts(list) {
  var lastMonth = getMonthString(-1);
  var ss = SpreadsheetApp.openById(SHEET_ID);
  var sheet = ss.getSheetByName(ACCOUNTS_SHEET_NAME);
  var ar = sheet.getDataRange().getValues();
  var headerRow = ar[0];
  var col_index = headerRow.indexOf(REPORT_COLUMN);
  for (var i = 1;i<ar.length;i++){
    var id = ar[i][0];
    if (list.indexOf(id) > -1){
      ar[i][col_index] = lastMonth;
    }
  }
  writeArrayToSheet(ar,sheet,true);
  Logger.log('updated ' + ACCOUNTS_SHEET_NAME);
}

function parseListOfAccounts(list) {
  var newList = [];
  var lastMonth = getMonthString(-1);
  for (var i in list){
    if (list[i][REPORT_COLUMN] !== lastMonth){
      newList.push(list[i]);
    }
  }
  if (newList.length > 30){
    newList.splice(30,newList.length-30);
  }
  return newList;
}

/**
 * Function returns yyyy-MM as string, useful for sheet naming and logging
 * @param {number} offset - the month to return. 0 equals this month, -1 equals last month
 * @return {string}
 */
function getMonthString(offset){
  var d = new Date();
  var timeZone = 'MST';
  var dString;
  d.setDate(1);
  d.setMonth(d.getMonth()+offset);
  dString = Utilities.formatDate(d, timeZone, 'yyyy-MM');
  return dString.toString();
}


function appendArrayToSheet(ar,id,sheetName){
  var ss = SpreadsheetApp.openById(id);
  var sheet = ss.getSheetByName(sheetName);
  if (sheet === null) { 
    sheet = ss.insertSheet();
    sheet.setName(sheetName);
  };
  var range = sheet.getDataRange();
  var row = 1;
  if (range.getLastRow()>1){
    ar.shift();
    row = range.getLastRow() + 1;
  }
  var newRange = sheet.getRange(row,1,ar.length,ar[0].length);
  newRange.setValues(ar);
}

/**
 * Takes a sheet with a header row and converts it into an array of objects
 * with property names matching column headers
 * @param {string} sheet - the sheet to objectify
 * @param {array} propertyNames - optional column headings to turn into properties. If not supplied,
 * properties will be set to header text.
 * @param {array} newPropertyNames - optional parameter to overide column names as property names
 * @return {array}
 */
function objectifySheet(sheet,propertyNames,newPropertyNames){
  if (typeof sheet === 'string'){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheet);
  }
  var arSheet = sheet.getDataRange().getValues();
  var objArray = [];
  var namesAndColumns = {};
  var headerData = arSheet[0];
  propertyNames = propertyNames || headerData;
  for (var i = 0, x = propertyNames.length; i<x;i++){
    var propertyName = newPropertyNames ? newPropertyNames[i] : propertyNames[i];
    namesAndColumns[propertyName] = headerData.indexOf(propertyNames[i]);
  }
  for (var j = 1, x = arSheet.length; j<x;j++){
    var rowObj = {};
    for (var k in namesAndColumns){
      rowObj[k] = arSheet[j][namesAndColumns[k]];
    }
    objArray.push(rowObj);
  }
  return objArray;
}


/**
 * Write an array to a sheet. If the sheet doesn't exist, it is created.
 * @param {array} ar - the array to write
 * @param {obj / string} sheet - the object reference or name of the sheet to which to write
 * @param {boolean} replaceFirstRow - if true, the first row of the sheet is overwritten
 */
function writeArrayToSheet(ar,sheet,replaceFirstRow){
  if (typeof sheet === 'string'){
    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);
}