Google Ads - The budget lifeguard (MCC script)

What the Script Does:

every Monday you get an email with budget runrates of all your clients accounts in your MCC account

How to Set Up the Script:

1. Copy this gsheet:
   • Gsheet setup

1. Configure the Script in Google Ads:
   • Log in to your Google Ads MCC account.
   • Navigate to Tools & Settings > Bulk actions > Scripts.
   • Click the "+" button to create a new script.
   • Copy and paste the script below into the script editor.
   • Replace "YOUR_EMAIL_HERE" with your email / emails in Row 2 of the script.
   • Replace "YOUR_SPREADSHEET_URL_HERE" in the script with the URL of the Google Sheet you created.
   • Preview the script for it to pull account data into the reporting sheet.
   • Go to the gsheet and fill out the target budgets of the current month in column C.
   • Save the script and run it daily. It will only send mails on mondays.
   • Preview again to receive another email. Emails will only be sent when previewing or on mondays.
   • Delete the rows after your last account to the email is perfectly formatted.
   • Have more than 25 accounts? Just copy / paste the cell content of the last row.
   • Want to track only labeled accounts? Apply the label "budget tracker" from your MCC account to the respective accounts. Then change the value in line 4 of the script to "true".

function main() {
    var yourEmailAdresses = 'YOUR_EMAIL' // use one email "example@123.de" or multiple mails like this "example@123.de, secondmail@def.de, ..."
    var spreadsheetUrl = 'YOUR_SPREADSHEETURL';
    var reportLabeledOnly = false; // Set this to true to report only on labeled accounts
	
    //no changes after here please
    var performanceSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName('Client Performance');
    var sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName('Raw Data');
    sheet.clear(); // Clear existing data
    clearColumnA(spreadsheetUrl)
  
    sheet.getRange('A1').setValue('Account');
    sheet.getRange('B1').setValue('Spend');

    var today = new Date();
    var firstDay = Utilities.formatDate(new Date(today.getFullYear(), today.getMonth(), 1), AdsApp.currentAccount().getTimeZone(), "yyyyMMdd");
    var yesterday = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 1);
    var lastDay = Utilities.formatDate(yesterday, AdsApp.currentAccount().getTimeZone(), "yyyyMMdd");
  
    getCurrentTime();

    var accounts = AdsManagerApp.accounts().get();
    var rowIndex = 2; // Start from the second row

    while (accounts.hasNext()) {
        var account = accounts.next();
        AdsManagerApp.select(account);

        // Enhanced logging to check label checking process
        //Logger.log('Checking account: ' + account.getName());

        // Check if label exists when reportLabeledOnly is true
        if (!reportLabeledOnly || (reportLabeledOnly && labelChecker(account, 'budget tracker'))) {
            var stats = account.getStatsFor(firstDay, lastDay);
            if (stats.getImpressions() > 0) {
                var accountName = account.getName();
                var adSpend = stats.getCost();

                sheet.getRange('A' + rowIndex).setValue(accountName);
                sheet.getRange('B' + rowIndex).setValue(adSpend);
                rowIndex++;
            }
        }
    }

  copyPasteColumnAB(spreadsheetUrl)
  
  var lastRow = performanceSheet.getLastRow();
  var dataRange = performanceSheet.getRange(2, 1, lastRow - 1, 7); // Assuming headers are in the first row
  var data = dataRange.getValues();

  var accountsToEmail = [];
  var accountSpend = [];
  var budgetRunRate = [];
  var accountBudget = []; // Initialize accountBudget as an array

  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    // Assuming the budget is in a specific column, correctly capture it:
    var budget = row[2]; // Example: Assuming budget is in the 3rd column (index 2)
    var percent = row[3]; // Column D for percent
    var spend = row[1]; // Column B for spend
    var accountName = row[0]; // Column A for account name

    accountsToEmail.push(accountName);
    budgetRunRate.push((percent * 100).toFixed(0)); // Assuming this calculates the run rate correctly
    accountSpend.push(spend.toFixed(0));
    accountBudget.push(budget.toFixed(0)); // Assuming budget is a number and needs formatting
  }

  cleanEmptyRows('https://docs.google.com/spreadsheets/d/1YSzISyVnh36o-HqTfWwRs5XmglBQEXM6vYu1his6VYg/edit#gid=668137273', 'Client Performance');
  
// Email construction corrected here:
if ((today.getDay() === 1 || AdsApp.getExecutionInfo().isPreview()) && accountsToEmail.length > 0) {
    var emailBody = '<html><body>';
    emailBody += '<p>Please see the budget run rates for your accounts this month. <br><br> Orange rows are > 120% runrate <br> Yellow rows are < 80% runrate <br> Green rows are < 120% and > 80% <br><br> runrate = spend till yesterday / budget till yesterday <br><br>Here is your <a href="' + spreadsheetUrl + '">Gsheet</a>.</p>';
    // Define table width and style, adjust column widths
    emailBody += '<table border="1" style="border-collapse: collapse; width: 100%;">';
    // Adjust column widths: column 1 remains the same, columns 2, 3, and 4 are now half the size
    emailBody += '<tr>' + 
                 '<th style="width: 40%; max-width: 200px;">Account</th>' + 
                 '<th style="width: 20%; max-width: 100px;">Account Spend</th>' + 
                 '<th style="width: 20%; max-width: 100px;">Monthly Budget</th>' + 
                 '<th style="width: 20%; max-width: 100px;">Budget Run Rate (%)</th>' +
                 '</tr>';
  
for (var i = 0; i < accountsToEmail.length; i++) {
    // Highlight logic...
    var highlight;
    if (budgetRunRate[i] > 120) {
        highlight = ' style="background-color: rgb(242, 204, 162);"'; // Orange-like color for >120%
    } else if (budgetRunRate[i] < 80) {
        highlight = ' style="background-color: rgb(253, 243, 208);"'; // Light yellow for <80%
    } else {
        highlight = ' style="background-color: rgb(190, 224, 205);"'; // Light green for within range
    }
    emailBody += '<tr' + highlight + '><td>' + accountsToEmail[i] + '</td><td>' + accountSpend[i] + '</td><td>' + accountBudget[i] + '</td><td>' + budgetRunRate[i] + '%</td></tr>';
}

emailBody += '</table>';
emailBody += '<p>Regards,<br>Your budget lifeguard</p>';
// Send the email with HTML format
MailApp.sendEmail({
  to: yourEmailAdresses,
  subject: 'Your MCC Account Performance (Script by Marius Blau)',
  htmlBody: emailBody
});
}
}
    // Further operations such as email sending can be implemented here


function labelChecker(account, targetLabel) {
    // Select the account to operate within its context
    AdsManagerApp.select(account);

    // Retrieve all labels for the current account using account object
    var labelsIterator = account.labels().get();

    // Logging to check label retrieval process
    //Logger.log('Retrieving labels for account: ' + account.getName());

    while (labelsIterator.hasNext()) {
        var label = labelsIterator.next();
        if (label.getName().toLowerCase() === targetLabel.toLowerCase()) {
            //Logger.log('Label found: ' + label.getName().toLowerCase() + ' | ' + targetLabel.toLowerCase() + ' | ' + 'true');
            return true;
        }
    }

    //Logger.log('Label not found for account: ' + account.getName() + ' looking for: ' + targetLabel);
    return false; // Return false if the target label is not found
}

function copyPasteColumnAB(spreadsheetUrl) {
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  
  // Access the 'Raw Data' sheet and get values from column A
  var rawDataSheet = spreadsheet.getSheetByName('Raw Data');
  var lastRowRawData = rawDataSheet.getLastRow(); // Get the last row with data in 'Raw Data'
  var rawDataValues = rawDataSheet.getRange('A2:B' + lastRowRawData).getValues(); // Assuming the first row is headers
  
  // Access the 'Client Performance' sheet
  var clientPerformanceSheet = spreadsheet.getSheetByName('Client Performance');
  
  // Check if 'Client Performance' has enough rows, if not, append them
  var requiredRows = rawDataValues.length;
  var lastRowClientPerformance = clientPerformanceSheet.getLastRow();
  if (lastRowClientPerformance < requiredRows + 1) { // +1 because starting from row 2
    clientPerformanceSheet.insertRowsAfter(lastRowClientPerformance, requiredRows + 1 - lastRowClientPerformance);
  }
  
  // Paste values into the 'Client Performance' sheet starting from row 2, column 1 (A2)
  clientPerformanceSheet.getRange('A2:B' + (requiredRows + 1)).setValues(rawDataValues); // +1 because we start from row 2
}

function clearColumnA(spreadsheetUrl) {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    var performanceSheet = spreadsheet.getSheetByName('Client Performance');
    
    // Calculate the number of rows to clear
    var lastRow = performanceSheet.getLastRow();
    
    if (lastRow > 1) { // Check if there's more than just the header row
        // Clear from row 2 to the last row in column A
        performanceSheet.getRange('A2:A' + lastRow).clearContent();
    }
}

function getCurrentTime() {
    // Retrieve the time zone from the current Ads account
    var timeZone = AdsApp.currentAccount().getTimeZone();
    
    // Create a new date object for the current time
    var now = new Date();
    
    // Format the current date and time to the account's time zone
    var formattedTime = Utilities.formatDate(now, timeZone, "yyyy-MM-dd'T'HH:mm:ss'Z'");
    
    // Logging the formatted time for debugging
    Logger.log('Current time in ' + timeZone + ' is: ' + formattedTime);
    
    return formattedTime;
}

function cleanEmptyRows(spreadsheetUrl, sheetName) {
  // Open the spreadsheet by URL
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheet = spreadsheet.getSheetByName(sheetName);

  // Get all the data in the sheet
  var data = sheet.getDataRange().getValues();

  // Loop through the data starting from the second row (index 1)
  // assuming the first row is a header row
  for (var i = data.length - 1; i >= 0; i--) {
    if (!data[i][0]) {
      // Clear the row if column A is empty
      sheet.getRange(i + 1, 1, 1, sheet.getLastColumn()).clearContent();
    }
  }
}