Google Ads Budget Pacing Script - For MCC Accounts

For people managing multiple Google Ads accounts, keeping track of monthly budget pacing is really important. If budget has started to run ahead or behind by even a few days, it can be hard to get it back on track without upsetting the algorithms with larger budget changes.

Rather than checking all the accounts manually, I created an MCC-level Script that does the heavy lifting for you (for accounts that aren't on monthly invoicing). The script is below if you want to give it a go.

‍

Schedule it to run each morning, and it will show you:

• How much each account has spent this month
• How much that is as a % of total budget
• What % you should have spent so far
• How much budget is left this month
• Remaining daily allowance

‍

You can each install this at the MCC level, just need to:

1. Create a blank Google Sheet and put the URL in the configuration section.
2. Add the account Label in the configuration section. (e.g. my accounts are all labelled "James")
3. Manually set your max monthly spend budgets in column D. (only have to do this once)

‍

This is what the output looks like:

The account will turn yellow when you hit 85%, then red when you exceed 100%.

‍

Here's the script:

/**
 * MCC Monthly Spend Monitor -> Google Sheet
 *
 * WHAT THIS SCRIPT DOES
 * - Runs at MCC level
 * - Includes only child accounts that have the specified MCC account label
 * - Writes one row per matching account into a Google Sheet tab
 * - Pulls current-month Cost for each account
 * - Preserves the user-entered "Max Monthly Spend" by Account ID
 * - Handles account renames cleanly by using Account ID as the primary key
 * - Rebuilds the report sheet on each run
 * - Stores preserved values in a hidden helper sheet
 * - Keeps helper-sheet history permanently so values survive temporary label removal
 * - Adds conditional formatting to the "% of Max Spend Used" column
 * - Adds an "Expected Percentage" row beneath the account rows showing how much
 * of the calendar month has elapsed so far
 *
 * SETUP INSTRUCTIONS
 * 1. In your MCC, apply an ACCOUNT LABEL to all child accounts you want included.
 * 2. Put that exact MCC account label name into INCLUDED_ACCOUNT_LABEL below.
 * 3. Create a Google Sheet and paste its URL into SPREADSHEET_URL below.
 * 4. Save and authorise the script.
 * 5. Run it once manually.
 * 6. In the sheet tab "Monthly Spend Monitor", fill in Column D ("Max Monthly Spend")
 * for each account.
 * The script will preserve those values on future runs by Account ID.
 */

var CONFIG = {
  INCLUDED_ACCOUNT_LABEL: 'XXXXX',
  SPREADSHEET_URL: 'XXXXX',

  REPORT_SHEET_NAME: 'Monthly Spend Monitor',
  HELPER_SHEET_NAME: '_PRESERVED_MAX_SPEND',

  HEADER_ROW: [
    'Account Name',
    'Account ID',
    'Currency',
    'Max Monthly Spend',
    'Actual Cost',
    'Percentage of Max Spend Used',
    'Budget Remaining',
    'Remaining Daily Allowance'
  ],

  YELLOW_THRESHOLD: 0.85,
  RED_THRESHOLD: 1.00
};

function main() {
  validateConfig_();

  var spreadsheet = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
  var reportSheet = getOrCreateSheet_(spreadsheet, CONFIG.REPORT_SHEET_NAME);
  var helperSheet = getOrCreateSheet_(spreadsheet, CONFIG.HELPER_SHEET_NAME);
  helperSheet.hideSheet();
  initialiseHelperSheetIfNeeded_(helperSheet);

  var preservedMap = buildPreservedMaxSpendMap_(helperSheet, reportSheet);
  var accountsData = getMatchingAccountsData_();
  var outputRows = buildOutputRows_(accountsData, preservedMap);

  rebuildReportSheet_(reportSheet, outputRows);
  syncHelperSheetPersistent_(helperSheet, outputRows);
  Logger.log('Done. Processed ' + outputRows.length + ' account(s).');
}

function validateConfig_() {
  if (!CONFIG.INCLUDED_ACCOUNT_LABEL ||
      CONFIG.INCLUDED_ACCOUNT_LABEL === 'REPLACE_WITH_MCC_ACCOUNT_LABEL') {
    throw new Error('Please set CONFIG.INCLUDED_ACCOUNT_LABEL.');
  }

  if (!CONFIG.SPREADSHEET_URL ||
      CONFIG.SPREADSHEET_URL === 'PASTE_GOOGLE_SHEET_URL_HERE') {
    throw new Error('Please set CONFIG.SPREADSHEET_URL.');
  }
}

function getMatchingAccountsData_() {
  var escapedLabel = escapeQuotes_(CONFIG.INCLUDED_ACCOUNT_LABEL);

  var accountSelector = AdsManagerApp.accounts()
    .withCondition("LabelNames CONTAINS '" + escapedLabel + "'");
  var accountIterator = accountSelector.get();
  var rows = [];

  while (accountIterator.hasNext()) {
    var managedAccount = accountIterator.next();
    var stats = managedAccount.getStatsFor('THIS_MONTH');
    var cost = stats.getCost();

    rows.push({
      accountName: managedAccount.getName(),
      accountId: managedAccount.getCustomerId(),
      currency: managedAccount.getCurrencyCode(),
      maxMonthlySpend: '',
      actualCost: cost
    });
  }

  rows.sort(function(a, b) {
    return String(a.accountName).localeCompare(String(b.accountName));
  });

  return rows;
}

function buildPreservedMaxSpendMap_(helperSheet, reportSheet) {
  var map = {};

  var helperLastRow = helperSheet.getLastRow();
  if (helperLastRow >= 2) {
    var helperValues = helperSheet.getRange(2, 1, helperLastRow - 1, 4).getValues();
    for (var i = 0; i < helperValues.length; i++) {
      var accountId = normaliseAccountId_(helperValues[i][0]);
      var maxSpend = helperValues[i][1];
      if (accountId) {
        map[accountId] = maxSpend;
      }
    }
  }

  var reportLastRow = reportSheet.getLastRow();
  if (reportLastRow >= 2) {
    var reportValues = reportSheet.getRange(2, 1, reportLastRow - 1, 4).getValues();
    for (var j = 0; j < reportValues.length; j++) {
      var reportAccountId = normaliseAccountId_(reportValues[j][1]);
      var reportMaxSpend = reportValues[j][3];
      if (reportAccountId && reportMaxSpend !== '') {
        map[reportAccountId] = reportMaxSpend;
      }
    }
  }

  return map;
}

function buildOutputRows_(accountsData, preservedMap) {
  var rows = [];
  for (var i = 0; i < accountsData.length; i++) {
    var row = accountsData[i];
    var accountId = normaliseAccountId_(row.accountId);

    if (Object.prototype.hasOwnProperty.call(preservedMap, accountId)) {
      row.maxMonthlySpend = preservedMap[accountId];
    }

    rows.push([
      row.accountName,
      row.accountId,
      row.currency,
      row.maxMonthlySpend,
      row.actualCost
    ]);
  }

  return rows;
}

function rebuildReportSheet_(sheet, outputRows) {
  var maxCols = sheet.getMaxColumns();
  var requiredCols = CONFIG.HEADER_ROW.length;
  if (maxCols < requiredCols) {
    sheet.insertColumnsAfter(maxCols, requiredCols - maxCols);
  }

  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    sheet.getRange(2, 1, lastRow - 1, requiredCols).clearContent();
  }

  // Clear old conditional formatting so it can be rebuilt cleanly
  sheet.clearConditionalFormatRules();
  // Write header row
  sheet.getRange(1, 1, 1, requiredCols).setValues([CONFIG.HEADER_ROW]);

  if (outputRows.length > 0) {
    // Write columns A:E
    sheet.getRange(2, 1, outputRows.length, 5).setValues(outputRows);
    // Write formulas for F:H
    var formulaRows = [];
    for (var r = 2; r <= outputRows.length + 1; r++) {
      formulaRows.push([
        '=IF(OR(D' + r + '="",D' + r + '=0,E' + r + '=""),"",E' + r + '/D' + r + ')',
        '=IF(OR(D' + r + '="",E' + r + '=""),"",D' + r + '-E' + r + ')',
        '=IF(OR(G' + r + '="",G' + r + '<=0),"",G' + r + '/(DAY(EOMONTH(TODAY(),0))-DAY(TODAY())+1))'
      ]);
    }
    sheet.getRange(2, 6, formulaRows.length, 3).setFormulas(formulaRows);

    // Add Expected Percentage row
    var expectedRow = outputRows.length + 2;
    sheet.getRange(expectedRow, 5).setValue('Expected Percentage');
    
    // UPDATED PACING LOGIC: Uses (Today - 1) to show percentage of completed days.
    sheet.getRange(expectedRow, 6)
      .setFormula('=(DAY(TODAY())-1)/DAY(EOMONTH(TODAY(),0))');
  }

  formatSheet_(sheet, outputRows.length);
  applyConditionalFormatting_(sheet, outputRows.length);
}

function formatSheet_(sheet, dataRowCount) {
  var expectedRow = dataRowCount > 0 ? dataRowCount + 2 : 2;
  var lastRow = Math.max(2, expectedRow);

  sheet.setFrozenRows(1);

  // Bold and colour header row
  var headerRange = sheet.getRange(1, 1, 1, CONFIG.HEADER_ROW.length);
  headerRange.setFontWeight('bold');
  headerRange.setBackground('#d9eaf7');

  if (dataRowCount > 0) {
    // Number formats
    sheet.getRange(2, 4, dataRowCount, 1).setNumberFormat('#,##0.00'); // D Max Monthly Spend
    sheet.getRange(2, 5, dataRowCount, 1).setNumberFormat('#,##0.00'); // E Actual Cost
    sheet.getRange(2, 6, dataRowCount, 1).setNumberFormat('0.00%');   // F Percentage of Max Spend Used
    sheet.getRange(2, 7, dataRowCount, 1).setNumberFormat('#,##0.00'); // G Budget Remaining
    sheet.getRange(2, 8, dataRowCount, 1).setNumberFormat('#,##0.00'); // H Remaining Daily Allowance

    // Bold Expected Percentage label and value
    sheet.getRange(expectedRow, 5).setFontWeight('bold');
    sheet.getRange(expectedRow, 6).setFontWeight('bold').setNumberFormat('0.00%');
  }

  sheet.getRange(1, 1, lastRow, CONFIG.HEADER_ROW.length)
    .setBorder(true, true, true, true, true, true);

  sheet.setColumnWidth(1, 240);
  sheet.setColumnWidth(2, 120);
  sheet.setColumnWidth(3, 80);
  sheet.setColumnWidth(4, 140);
  sheet.setColumnWidth(5, 120);
  sheet.setColumnWidth(6, 170);
  sheet.setColumnWidth(7, 140);
  sheet.setColumnWidth(8, 170);
}

function applyConditionalFormatting_(sheet, dataRowCount) {
  if (dataRowCount <= 0) return;

  var percentageRange = sheet.getRange(2, 6, dataRowCount, 1);

  var redRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(CONFIG.RED_THRESHOLD)
    .setBackground('#f4cccc')
    .setRanges([percentageRange])
    .build();
  var yellowRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(
      '=AND(F2>' + CONFIG.YELLOW_THRESHOLD + ',F2<=' + CONFIG.RED_THRESHOLD + ')'
    )
    .setBackground('#fff2cc')
    .setRanges([percentageRange])
    .build();
  sheet.setConditionalFormatRules([redRule, yellowRule]);
}

function initialiseHelperSheetIfNeeded_(helperSheet) {
  if (helperSheet.getLastRow() === 0) {
    helperSheet.getRange(1, 1, 1, 4).setValues([[
      'Account ID',
      'Max Monthly Spend',
      'Last Seen Account Name',
      'Currency'
    ]]);
    helperSheet.getRange(1, 1, 1, 4).setFontWeight('bold');
  }
}

function syncHelperSheetPersistent_(helperSheet, outputRows) {
  var existingMap = {};
  var helperLastRow = helperSheet.getLastRow();
  if (helperLastRow >= 2) {
    var existingValues = helperSheet.getRange(2, 1, helperLastRow - 1, 4).getValues();
    for (var i = 0; i < existingValues.length; i++) {
      var existingAccountId = normaliseAccountId_(existingValues[i][0]);
      if (!existingAccountId) continue;

      existingMap[existingAccountId] = {
        accountId: existingValues[i][0],
        maxMonthlySpend: existingValues[i][1],
        accountName: existingValues[i][2],
        currency: existingValues[i][3]
      };
    }
  }

  for (var j = 0; j < outputRows.length; j++) {
    var row = outputRows[j];
    var accountId = normaliseAccountId_(row[1]);

    existingMap[accountId] = {
      accountId: row[1],
      maxMonthlySpend: row[3],
      accountName: row[0],
      currency: row[2]
    };
  }

  var mergedRows = [];
  for (var key in existingMap) {
    if (Object.prototype.hasOwnProperty.call(existingMap, key)) {
      mergedRows.push([
        existingMap[key].accountId,
        existingMap[key].maxMonthlySpend,
        existingMap[key].accountName,
        existingMap[key].currency
      ]);
    }
  }

  mergedRows.sort(function(a, b) {
    var nameCompare = String(a[2]).localeCompare(String(b[2]));
    if (nameCompare !== 0) return nameCompare;
    return String(a[0]).localeCompare(String(b[0]));
  });
  helperSheet.clearContents();
  initialiseHelperSheetIfNeeded_(helperSheet);

  if (mergedRows.length > 0) {
    helperSheet.getRange(2, 1, mergedRows.length, 4).setValues(mergedRows);
  }
}

function getOrCreateSheet_(spreadsheet, sheetName) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  return sheet;
}

function normaliseAccountId_(accountId) {
  if (accountId === null || accountId === undefined) return '';
  return String(accountId).replace(/\s+/g, '');
}

function escapeQuotes_(text) {
  return String(text).replace(/'/g, "\\'");
}

‍