AdWords Budget Checker

budget_checker.js

var resultsUrl = "ENTER URL HERE" //Should be a configured COPY of this template (check 'Data' sheet): https://docs.google.com/spreadsheets/d/1c5vQWC8VPF8G0mDqvlWG-CB6xo3Vt4eSDg4l7Vbokmo/edit
var notify = false; //Leave as false

function main () {
    var ss = SpreadsheetApp.openByUrl(resultsUrl);  
    var results = [];
    checkBudgets(ss,results);
    Logger.log("Done checking");
    if (notify) {
        notifyAm(ss,results);
        Logger.log("Notification sent");
    }
    Logger.log("Execution complete");
}

function notifyAm(spreadsheet,results) {
    var emails = spreadsheet.getRangeByName("emails").getValue();
    results = results.join(", ");
    MailApp.sendEmail(emails,"Budget Outage Notification","The following campaigns have run out of budget this hour: "+results+"\nYou can check the spreadsheet here:"+resultsUrl);    
}

function getCampaignsToCheck(sheet) {
    var data = sheet.getDataRange().getValues();
    var array = []

    for (var i = 1; i < data.length; i++) {
        array.push(data[i][0]);
    }
    return array;
}

function checkBudgets(spreadsheet,results) {
	var sheet = spreadsheet.getSheetByName("Budgets Ran Out");
	var d = new Date();
	var day = d.getDay();
	var lastrun = spreadsheet.getRangeByName("lastRunBudget").getValue();
	var i = sheet.getLastRow()+1;
	if (lastrun != day) {
		i = 2;
		clearSheet(sheet,2,5);
		SpreadsheetApp.flush()
		Logger.log("It's %s, not %s -- clearing sheet for new day",day,lastrun);
	}
	spreadsheet.getRangeByName("lastRunBudget").setValue(day);
	var budgetThreshold = spreadsheet.getRangeByName("budgetThreshold").getValue();
	Logger.log("Budget threshold is %s",budgetThreshold);

    var spentBudget = [];
    var toCheck = getCampaignsToCheck(spreadsheet.getSheetByName("Data"));
    Logger.log("Campaigns to check: %s",toCheck);

    var budgetIterator = AdWordsApp.budgets()
    .withCondition("BudgetReferenceCount > 0")
    .withCondition("Cost > 0")
    .forDateRange("TODAY")
    .get()

    while (budgetIterator.hasNext()) {
        var budget = budgetIterator.next();
        var budgetStats = budget.getStatsFor("TODAY");
        var budgetAmount = budget.getAmount();
        var budgetSpend = budgetStats.getCost();
        var budgetName = budget.getName();

        if (budgetSpend/budgetAmount > budgetThreshold && !checkBudgetList(sheet,budgetName)) {
            sheet.getRange(i,1).setValue(budgetName);
            sheet.getRange(i,2).setValue(budgetAmount);
            sheet.getRange(i,3).setValue(budgetSpend);
            sheet.getRange(i,4).setValue(d.getHours());
            i++;
            Logger.log("%s has exceeded the warning threshold and has spent %s of %s",budgetName,budgetSpend,budgetAmount);

            if (getContains(toCheck,budgetName)) {
                notify = true;
                results.push(budgetName);
                Logger.log("%s is listed as one of the budgets to send a notification about and is down.",budgetName)
            }
        }	
    }

}

function checkBudgetList(sheet,test1) {
	var column1 = 0; //Column where test1 should be found
	var sheetData = sheet.getDataRange().getValues();
	for (var x=1;x<sheetData.length;x++) {
		if (sheetData[x][column1].indexOf(test1) > -1) {
			Logger.log("%s is down but already in list, skipping",test1);
			return true;
		}
	}
	Logger.log("%s is down and is not in list, adding",test1);
	return false;

}

function getContains(str, test) {
    if (!str)
        return false;
	return str.indexOf(test) >= 0;
}

function clearSheet(sheet, i, y){
	sheet.getRange(i,1,100,y).clearContent();	
}