MCCBudgetsScript.js

/*
/*Will grab all our MCC accounts and throw the cost results into a google spreadsheet
*/

//grab the spreadsheet
var spreadSheet = SpreadsheetApp.openById("SHEET-ID-HERE");
var sheetName = spreadSheet.getSheetByName('Sheet1');
//var currentDate = Utilities.formatDate(new Date(), "GMT", "MMMM-yyyy");



function main() {
  //Logger.log("CurrentMonth: " + currentMonth);
  //Logger.log("Processing Clients: " );
  //getClients();
  //Logger.log("Finished Processing Clients");
  //sheetName.appendRow("test", "testing");
  itterateAllRowsCheck();
 itterateAllRows();
}

function itterateAllRowsCheck(currentDate){

  
  
  var allAdwords = SpreadsheetApp.openById("SHEET-ID-HERE");
  var adSpendSheet = allAdwords.getSheetByName("AdWordsAdSpendResults");
  var lastRow = adSpendSheet.getLastRow();
  var lastColumn = adSpendSheet.getLastColumn();
  var workingColumn = lastColumn +1;
  
  
  
  var accountIterator = MccApp.accounts().withCondition('LabelNames Contains "Client"').forDateRange("LAST_MONTH").get();
  while (accountIterator.hasNext() ) { 
    
    var account = accountIterator.next();
    var result = false;
    //grab the info field from the account object
    var currentCustomerID = account.getCustomerId();
    Logger.log(account.getName());
    for (var i = 2;i <= lastRow; i++){
      var currentRowCID = adSpendSheet.getRange(i,2).getValue(); 
      if(currentRowCID.indexOf(currentCustomerID)>-1){
        if (currentRowCID != ""){
         adSpendSheet.getRange(i,3).setValue(account.getCurrencyCode());
          result = true;
         
        }
   
        
      }
      
      
    }
    if(result == false){
      
      adSpendSheet.getRange(lastRow + 1,1).setValue(account.getName());
      adSpendSheet.getRange(lastRow + 1,2).setValue(account.getCustomerId());
       adSpendSheet.getRange(lastRow + 1,3).setValue(account.getCurrencyCode());
      lastRow = adSpendSheet.getLastRow();
       MailApp.sendEmail("EMAILSTOSENDNOTIFICATIONTO",
                      'mccBudgetSummary Notification',
                      'Added New Customer ' + account.getName() + ' to spreadsheet');
      
                 
    }
    
  }}
  
  
  
  
  
  
  
  

function itterateAllRows(currentDate){
  var date = new Date();
  date.setMonth(date.getMonth() - 1);
  var currentDate = Utilities.formatDate(date, "GMT", "MMMM-yyyy");
  
  var exchangeRate = ImportJSON("http://api.fixer.io/latest?base=USD&symbols=CAD","/rates", "noHeaders");
  var allAdwords = SpreadsheetApp.openById("1zhNd2euUxmLZtdbkNYPE6Z5btS5UbBAxQ-NLaAb_SAA");
  var adSpendSheet = allAdwords.getSheetByName("AdWordsAdSpendResults");
  var lastRow = adSpendSheet.getLastRow();
  var lastColumn = adSpendSheet.getLastColumn();
  var workingColumn = lastColumn +1;
  adSpendSheet.getRange(1,workingColumn).setValue(currentDate).setFontWeight("bold");
  for (var i = 2;i <= lastRow; i++){
     var currentRowCID = 0;
      var currentRowAdSpend = 0;
     currentRowCID = adSpendSheet.getRange(i,2).getValue(); 
     currentRowAdSpend = adSpendSheet.getRange(i,workingColumn);
    if(currentRowCID != ""){
    Logger.log("Looking for CID " + currentRowCID);

    currentRowAdSpend.setValue(getLastMonthCost(currentRowCID,exchangeRate)).setNumberFormat("$0.00");
    }
    
  }
  
}



function getLastMonthCost(targetCustomerID,exchangeRate){
  var convertAdSpend = 0;
  var lastMonthCost = 0;
  var accountIterator = MccApp.accounts().withCondition('LabelNames Contains "Client"').forDateRange("LAST_MONTH").get();
  while (accountIterator.hasNext() ) { 
    
    var account = accountIterator.next();
    
    //grab the info field from the account object
    var currentCustomerID = account.getCustomerId();
    
    if(currentCustomerID.indexOf(targetCustomerID)>-1){
      lastMonthCost = account.getStatsFor("LAST_MONTH").getCost();
      if (account.getCurrencyCode() == "USD" && convertAdSpend == "1"){
        lastMonthCost = lastMonthCost * exchangeRate;
      }
      return lastMonthCost;
      
    }
    
    
  }
  
  return "No AdSpend Found";
}



function ImportJSON(url, query, options) {
  return ImportJSONAdvanced(url, query, options, includeXPath_, defaultTransform_);
}

/**
* An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a 
* spreadsheet.
*
* Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create 
* a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in 
* the JSON feed. The remaining rows contain the data. 
*
* Use the include and transformation functions to determine what to include in the import and how to transform the data after it is
* imported. 
*
* For example:
*
*   =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", 
*               "/feed/entry",
*                function (query, path) { return path.indexOf(query) == 0; },
*                function (data, row, column) { data[row][column] = data[row][column].toString().substr(0, 100); } )
*
* In this example, the import function checks to see if the path to the data being imported starts with the query. The transform 
* function takes the data and truncates it. For more robust versions of these functions, see the internal code of this library.
*
* @param {url}           the URL to a public JSON feed
* @param {query}         the query passed to the include function
* @param {options}       a comma-separated list of options that may alter processing of the data
* @param {includeFunc}   a function with the signature func(query, path, options) that returns true if the data element at the given path
*                        should be included or false otherwise. 
* @param {transformFunc} a function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data 
*                        and row & column are the current row and column being processed. Any return value is ignored. Note that row 0 
*                        contains the headers for the data, so test for row==0 to process headers only.
*
* @return a two-dimensional array containing the data, with the first row containing headers
**/
function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
  var jsondata = UrlFetchApp.fetch(url);
  var object   = JSON.parse(jsondata.getContentText());
  
  return parseJSONObject_(object, query, options, includeFunc, transformFunc);
}

/** 
* Encodes the given value to use within a URL.
*
* @param {value} the value to be encoded
* 
* @return the value encoded using URL percent-encoding
*/
function URLEncode(value) {
  return encodeURIComponent(value.toString());  
}

/** 
* Parses a JSON object and returns a two-dimensional array containing the data of that object.
*/
function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
  var headers = new Array();
  var data    = new Array();
  
  if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
    query = query.toString().split(",");
  }
  
  if (options) {
    options = options.toString().split(",");
  }
  
  parseData_(headers, data, "", 1, object, query, options, includeFunc);
  parseHeaders_(headers, data);
  transformData_(data, options, transformFunc);
  
  return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
}

/** 
* Parses the data contained within the given value and inserts it into the data two-dimensional array starting at the rowIndex. 
* If the data is to be inserted into a new column, a new header is added to the headers array. The value can be an object, 
* array or scalar value.
*
* If the value is an object, it's properties are iterated through and passed back into this function with the name of each 
* property extending the path. For instance, if the object contains the property "entry" and the path passed in was "/feed",
* this function is called with the value of the entry property and the path "/feed/entry".
*
* If the value is an array containing other arrays or objects, each element in the array is passed into this function with 
* the rowIndex incremeneted for each element.
*
* If the value is an array containing only scalar values, those values are joined together and inserted into the data array as 
* a single value.
*
* If the value is a scalar, the value is inserted directly into the data array.
*/
function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
  var dataInserted = false;
  
  if (isObject_(value)) {
    for (key in value) {
      if (parseData_(headers, data, path + "/" + key, rowIndex, value[key], query, options, includeFunc)) {
        dataInserted = true; 
      }
    }
  } else if (Array.isArray(value) && isObjectArray_(value)) {
    for (var i = 0; i < value.length; i++) {
      if (parseData_(headers, data, path, rowIndex, value[i], query, options, includeFunc)) {
        dataInserted = true;
        rowIndex++;
      }
    }
  } else if (!includeFunc || includeFunc(query, path, options)) {
    // Handle arrays containing only scalar values
    if (Array.isArray(value)) {
      value = value.join(); 
    }
    
    // Insert new row if one doesn't already exist
    if (!data[rowIndex]) {
      data[rowIndex] = new Array();
    }
    
    // Add a new header if one doesn't exist
    if (!headers[path] && headers[path] != 0) {
      headers[path] = Object.keys(headers).length;
    }
    
    // Insert the data
    data[rowIndex][headers[path]] = value;
    dataInserted = true;
  }
  
  return dataInserted;
}

/** 
* Parses the headers array and inserts it into the first row of the data array.
*/
function parseHeaders_(headers, data) {
  data[0] = new Array();
  
  for (key in headers) {
    data[0][headers[key]] = key;
  }
}

/** 
* Applies the transform function for each element in the data array, going through each column of each row.
*/
function transformData_(data, options, transformFunc) {
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      transformFunc(data, i, j, options);
    }
  }
}

/** 
* Returns true if the given test value is an object; false otherwise.
*/
function isObject_(test) {
  return Object.prototype.toString.call(test) === '[object Object]';
}

/** 
* Returns true if the given test value is an array containing at least one object; false otherwise.
*/
function isObjectArray_(test) {
  for (var i = 0; i < test.length; i++) {
    if (isObject_(test[i])) {
      return true; 
    }
  }  
  
  return false;
}

/** 
* Returns true if the given query applies to the given path. 
*/
function includeXPath_(query, path, options) {
  if (!query) {
    return true; 
  } else if (Array.isArray(query)) {
    for (var i = 0; i < query.length; i++) {
      if (applyXPathRule_(query[i], path, options)) {
        return true; 
      }
    }  
  } else {
    return applyXPathRule_(query, path, options);
  }
  
  return false; 
};

/** 
* Returns true if the rule applies to the given path. 
*/
function applyXPathRule_(rule, path, options) {
  return path.indexOf(rule) == 0; 
}

/** 
* By default, this function transforms the value at the given row & column so it looks more like a normal data import. Specifically:
*
*   - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values 
*     of the rows representing their parent elements.
*   - Values longer than 256 characters get truncated.
*   - Values in row 0 (headers) have slashes converted to spaces, common prefixes removed and the resulting text converted to title 
*      case. 
*
* To change this behavior, pass in one of these values in the options parameter:
*
*    noInherit:     Don't inherit values from parent elements
*    noTruncate:    Don't truncate values
*    rawHeaders:    Don't prettify headers
*    debugLocation: Prepend each value with the row & column it belongs in
*/
function defaultTransform_(data, row, column, options) {
  if (!data[row][column]) {
    if (row < 2 || hasOption_(options, "noInherit")) {
      data[row][column] = "";
    } else {
      data[row][column] = data[row-1][column];
    }
  } 
  
  if (!hasOption_(options, "rawHeaders") && row == 0) {
    if (column == 0 && data[row].length > 1) {
      removeCommonPrefixes_(data, row);  
    }
    
    data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
  }
  
  if (!hasOption_(options, "noTruncate") && data[row][column]) {
    data[row][column] = data[row][column].toString().substr(0, 256);
  }
  
  if (hasOption_(options, "debugLocation")) {
    data[row][column] = "[" + row + "," + column + "]" + data[row][column];
  }
}

/** 
* If all the values in the given row share the same prefix, remove that prefix.
*/
function removeCommonPrefixes_(data, row) {
  var matchIndex = data[row][0].length;
  
  for (var i = 1; i < data[row].length; i++) {
    matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
    
    if (matchIndex == 0) {
      return;
    }
  }
  
  for (var i = 0; i < data[row].length; i++) {
    data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
  }
}

/** 
* Locates the index where the two strings values stop being equal, stopping automatically at the stopAt index.
*/
function findEqualityEndpoint_(string1, string2, stopAt) {
  if (!string1 || !string2) {
    return -1; 
  }
  
  var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
  
  for (var i = 0; i < maxEndpoint; i++) {
    if (string1.charAt(i) != string2.charAt(i)) {
      return i;
    }
  }
  
  return maxEndpoint;
}


/** 
* Converts the text to title case.
*/
function toTitleCase_(text) {
  if (text == null) {
    return null;
  }
  
  return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
}

/** 
* Returns true if the given set of options contains the given option.
*/
function hasOption_(options, option) {
  return options && options.indexOf(option) >= 0;
}