/* | |
/*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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment