What the Script Does:
every Monday you get an email with budget runrates of all your clients accounts in your MCC account
![](https://cdn.prod.website-files.com/5e134e3cc017e663760ad0fb/6617b9fbb6159688bb4c39c3_Every%20monday%20you%20get%20this%20email.png)
How to Set Up the Script:
- Copy this gsheet:
- 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));
if (budget !== undefined && budget !== null && budget !== '') {
let numericBudget = parseFloat(budget);
if (!isNaN(numericBudget)) {
accountBudget.push(numericBudget.toFixed(0));
} else {
accountBudget.push('0');
}
} else {
accountBudget.push('0');
}; // Assuming budget is a number and needs formatting
}
cleanEmptyRows(spreadsheetUrl, '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();
}
}
}