Google Ads Budget Pacing Script - For Campaign Budgets Within a Single Account

A while back I shared an MCC-level budget pacing script that tracks spend, one row per account. That works well when each account has its own monthly budget. It doesn't help, though, when the budgets you actually need to manage sit inside a single account.

That comes up more than you'd think. Sometimes one account holds several distinct budgets that each have to spend a specific amount. A common example is where more than one brand advertises within the same account, and each brand is contributing a set monthly figure that needs to be spent in full.

So I built a campaign-level version. It groups spend by budget (campaigns sharing a budget roll into one row), pulls the month-to-date cost for each, and shows you where every budget sits against the monthly cap you've set.

Schedule it to run daily and it keeps itself current. The script is below if you want to give it a go.

‍

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

• Each active budget and the campaigns running on it
• How much that budget has spent this month
• That spend as a % of the monthly cap you've set
• How much of the budget is left this month
• The daily allowance you have left to hit the cap

‍

To install it inside an account, you just need to:

1. Create a blank Google Sheet and paste its URL into the SHEET_URL line at the top of the script.
2. Save, authorise the script, and run it once manually.
3. Fill in the "Max Monthly Spend" column for each budget. (You only do this once; the script preserves your values on future runs.)

‍

A couple of things worth knowing:

• Campaigns that share a budget are grouped into one row and labelled "(Shared)".
• Only enabled campaigns that haven't ended are counted, and experiment campaigns are left out.
• If a budget stops being used by any enabled campaign, its row is kept for reference, italicised, and labelled as an old budget, so you don't lose the cap you'd set against it.

‍

This is what the output looks like:

‍

Here's the script:

/**
 * Monthly Max Spend Monitor for Budget Groups
 *
 * Purpose:
 * This Google Ads Script writes a monthly spend monitoring report to a Google Sheet.
 * It shows one row per budget group for ENABLED campaigns that have not ended,
 * preserving the user-entered Max Monthly Spend and calculating pacing metrics
 * based on current-month spend.
 *
 * Shared budgets:
 * - If multiple enabled campaigns use the same budget, they are grouped into one row.
 * - Shared budget rows are labelled as: Budget name (Shared)
 *
 * What the user needs to do:
 * 1. Paste the Google Sheet URL into SHEET_URL below.
 * 2. Schedule this script in Google Ads to run DAILY.
 * 3. After the first run, fill in the "Max Monthly Spend" column in the sheet.
 *
 * Important:
 * - The script preserves the user's "Max Monthly Spend" values between runs.
 * - Old budgets no longer attached to enabled campaigns are kept for reference,
 *   italicised, and labelled as old budgets.
 * - "Remaining Daily Allowance" uses the number of days remaining in the month,
 *   including today.
 * - Active budget rows are highlighted once they reach 85% of Max Monthly Spend.
 *
 * GAQL tip:
 * You can build and validate GAQL queries with the Google Ads Query Builder:
 * https://developers.google.com/google-ads/api/fields/v21/overview
 *
 * Okay, let's get started:
 */

/** =========================
 * CONFIGURATION
 * =========================
 */

/**
 * Paste your Google Sheet URL here.
 */
const SHEET_URL = 'https://docs.google.com/spreadsheets/d/1lh21PR8OnU-6vtXNv8Za92qfmmLvqwTgbbZxpKsi6E4/edit?gid=0#gid=0';

/**
 * Name of the report tab inside the Google Sheet.
 */
const REPORT_SHEET_NAME = 'Monthly Spend Monitor';

/**
 * Label appended to old budgets kept for reference.
 */
const OLD_BUDGET_LABEL = ' (Old budget - no longer used by enabled campaigns)';

/**
 * Label appended to active shared budgets.
 */
const SHARED_BUDGET_LABEL = ' (Shared)';

/**
 * Threshold at which active budget rows are highlighted.
 * Example: 0.85 = 85%
 */
const WARNING_PERCENTAGE_THRESHOLD = 0.85;

/**
 * Header row values for the report.
 */
const REPORT_HEADERS = [
  'Budget name',
  'Campaign count',
  'Campaign names',
  'Max Monthly Spend',
  'Actual Cost',
  'Percentage of Max spend used',
  'Budget Remaining',
  'Remaining Daily Allowance'
];

/**
 * Main entry point.
 */
function main() {
  runMonthlySpendMonitor();
}

/**
 * Runs the monthly spend monitor flow end-to-end.
 */
function runMonthlySpendMonitor() {
  validateConfiguration();

  const account = AdsApp.currentAccount();
  const accountTimeZone = account.getTimeZone();
  const dateRange = getCurrentMonthDateRange(accountTimeZone);
  const todayDate = getTodayDateString(accountTimeZone);
  const daysRemainingInMonth = getDaysRemainingInMonthIncludingToday(accountTimeZone);

  console.log(`Account time zone: ${accountTimeZone}`);
  console.log(`Date range start: ${dateRange.startDate}`);
  console.log(`Date range end: ${dateRange.endDate}`);
  console.log(`Today date for end-date filter: ${todayDate}`);
  console.log(`Days remaining in month including today: ${daysRemainingInMonth}`);

  const spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL);
  const sheet = getOrCreateReportSheet(spreadsheet, REPORT_SHEET_NAME);

  const storedBudgetDataById = readStoredBudgetDataById(sheet);
  console.log(`Stored budget rows found: ${Object.keys(storedBudgetDataById).length}`);

  const enabledCampaignBudgetRows = getEnabledCampaignBudgetRows(
    dateRange.startDate,
    dateRange.endDate,
    todayDate
  );
  console.log(`Enabled non-ended campaign-budget rows found: ${enabledCampaignBudgetRows.length}`);
  logSampleItems('Enabled non-ended campaign-budget sample', enabledCampaignBudgetRows, function(item) {
    return `${item.budgetId} | ${item.budgetName} | ${item.campaignName}`;
  });

  const reportData = buildBudgetGroupReportData(enabledCampaignBudgetRows, storedBudgetDataById);
  writeReportToSheet(sheet, reportData, daysRemainingInMonth);

  console.log('Monthly spend monitor completed successfully.');
}

/**
 * Validates the required configuration values.
 */
function validateConfiguration() {
  if (!SHEET_URL || SHEET_URL === 'PASTE_YOUR_GOOGLE_SHEET_URL_HERE') {
    throw new Error(
      'Please paste your Google Sheet URL into SHEET_URL at the top of the script.'
    );
  }

  if (WARNING_PERCENTAGE_THRESHOLD <= 0) {
    throw new Error('WARNING_PERCENTAGE_THRESHOLD must be greater than 0.');
  }
}

/**
 * Returns the first and last date for the current month-to-date window.
 *
 * @param {string} timeZone The Google Ads account time zone.
 * @return {{startDate: string, endDate: string}}
 */
function getCurrentMonthDateRange(timeZone) {
  const now = new Date();
  const year = Utilities.formatDate(now, timeZone, 'yyyy');
  const month = Utilities.formatDate(now, timeZone, 'MM');
  const day = Utilities.formatDate(now, timeZone, 'dd');

  return {
    startDate: `${year}-${month}-01`,
    endDate: `${year}-${month}-${day}`
  };
}

/**
 * Returns today's date string in the account time zone.
 *
 * @param {string} timeZone The Google Ads account time zone.
 * @return {string}
 */
function getTodayDateString(timeZone) {
  return Utilities.formatDate(new Date(), timeZone, 'yyyy-MM-dd');
}

/**
 * Returns the number of days remaining in the current month, including today.
 *
 * @param {string} timeZone The Google Ads account time zone.
 * @return {number}
 */
function getDaysRemainingInMonthIncludingToday(timeZone) {
  const now = new Date();
  const year = Number(Utilities.formatDate(now, timeZone, 'yyyy'));
  const monthIndex = Number(Utilities.formatDate(now, timeZone, 'MM')) - 1;
  const dayOfMonth = Number(Utilities.formatDate(now, timeZone, 'dd'));

  const lastDayOfMonth = new Date(year, monthIndex + 1, 0).getDate();
  return lastDayOfMonth - dayOfMonth + 1;
}

/**
 * Creates the GAQL query for enabled campaigns with their budgets and current-month cost.
 *
 * Excludes:
 * - experiment/treatment campaigns by only including BASE campaigns
 * - campaigns that have already ended
 *
 * @param {string} startDate YYYY-MM-DD
 * @param {string} endDate YYYY-MM-DD
 * @param {string} todayDate YYYY-MM-DD
 * @return {string}
 */
function buildEnabledCampaignBudgetQuery(startDate, endDate, todayDate) {
  return [
    'SELECT',
    '  campaign.id,',
    '  campaign.name,',
    '  campaign.end_date_time,',
    '  campaign_budget.id,',
    '  campaign_budget.name,',
    '  campaign_budget.explicitly_shared,',
    '  metrics.cost_micros',
    'FROM campaign',
    'WHERE campaign.status = ENABLED',
    '  AND campaign.experiment_type = BASE',
    `  AND campaign.end_date_time >= '${todayDate}'`,
    `  AND segments.date BETWEEN '${startDate}' AND '${endDate}'`,
    'ORDER BY campaign_budget.name, campaign.name'
  ].join('\n');
}

/**
 * Pulls enabled campaigns with their budgets and current-month cost.
 * Returns one row per campaign-budget combination.
 *
 * Excludes campaigns that are already ended.
 *
 * @param {string} startDate YYYY-MM-DD
 * @param {string} endDate YYYY-MM-DD
 * @param {string} todayDate YYYY-MM-DD
 * @return {Array.<{
 *   campaignId: string,
 *   campaignName: string,
 *   budgetId: string,
 *   budgetName: string,
 *   explicitlyShared: boolean,
 *   actualCost: number
 * }>}
 */
function getEnabledCampaignBudgetRows(startDate, endDate, todayDate) {
  const query = buildEnabledCampaignBudgetQuery(startDate, endDate, todayDate);
  console.log(`Enabled non-ended campaign-budget query:\n${query}`);

  const report = AdsApp.report(query);
  const rows = report.rows();
  const results = [];

  while (rows.hasNext()) {
    const row = rows.next();

    results.push({
      campaignId: String(row['campaign.id']),
      campaignName: String(row['campaign.name']),
      budgetId: String(row['campaign_budget.id']),
      budgetName: String(row['campaign_budget.name']),
      explicitlyShared: String(row['campaign_budget.explicitly_shared']) === 'true',
      actualCost: roundToTwoDecimals(microsToCurrency(Number(row['metrics.cost_micros']) || 0))
    });
  }

  return results;
}

/**
 * Converts micros to standard currency units.
 *
 * @param {number} micros
 * @return {number}
 */
function microsToCurrency(micros) {
  return micros / 1000000;
}

/**
 * Gets the report sheet, creating it if needed.
 *
 * @param {Spreadsheet} spreadsheet
 * @param {string} sheetName
 * @return {Sheet}
 */
function getOrCreateReportSheet(spreadsheet, sheetName) {
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }

  return sheet;
}

/**
 * Reads the existing visible report so the script can preserve the user's
 * Max Monthly Spend values across rebuilds.
 *
 * Budget IDs are stored as notes in column A.
 *
 * @param {Sheet} sheet
 * @return {Object.<string, {budgetName: string, maxMonthlySpend: string}>}
 */
function readStoredBudgetDataById(sheet) {
  const lastRow = sheet.getLastRow();
  const storedBudgetDataById = {};

  if (lastRow < 2) {
    return storedBudgetDataById;
  }

  const values = sheet.getRange(2, 1, lastRow - 1, 4).getValues();
  const notes = sheet.getRange(2, 1, lastRow - 1, 1).getNotes();

  for (let i = 0; i < values.length; i++) {
    const budgetNameCell = values[i][0];
    const maxMonthlySpendCell = values[i][3];
    const budgetIdNote = notes[i][0];

    if (!budgetIdNote) {
      continue;
    }

    storedBudgetDataById[String(budgetIdNote)] = {
      budgetName: stripBudgetLabels(String(budgetNameCell || '')),
      maxMonthlySpend: maxMonthlySpendCell
    };
  }

  return storedBudgetDataById;
}

/**
 * Removes formatting labels from a budget name when restoring data.
 *
 * @param {string} budgetName
 * @return {string}
 */
function stripBudgetLabels(budgetName) {
  let cleanedBudgetName = budgetName || '';

  if (cleanedBudgetName.indexOf(OLD_BUDGET_LABEL) !== -1) {
    cleanedBudgetName = cleanedBudgetName.replace(OLD_BUDGET_LABEL, '');
  }

  if (cleanedBudgetName.indexOf(SHARED_BUDGET_LABEL) !== -1) {
    cleanedBudgetName = cleanedBudgetName.replace(SHARED_BUDGET_LABEL, '');
  }

  return cleanedBudgetName;
}

/**
 * Builds report data grouped by budget ID.
 *
 * @param {Array.<Object>} enabledCampaignBudgetRows
 * @param {Object.<string, {budgetName: string, maxMonthlySpend: string}>} storedBudgetDataById
 * @return {{
 *   activeRows: Array.<Array.<string|number>>,
 *   activeNotes: Array.<string>,
 *   oldRows: Array.<Array.<string|number>>,
 *   oldNotes: Array.<string>
 * }}
 */
function buildBudgetGroupReportData(enabledCampaignBudgetRows, storedBudgetDataById) {
  const budgetGroupsById = {};
  const activeRows = [];
  const activeNotes = [];
  const oldRows = [];
  const oldNotes = [];
  const activeBudgetIdMap = {};

  for (let i = 0; i < enabledCampaignBudgetRows.length; i++) {
    const row = enabledCampaignBudgetRows[i];
    const budgetId = row.budgetId;

    if (!budgetGroupsById[budgetId]) {
      budgetGroupsById[budgetId] = {
        budgetId: budgetId,
        budgetName: row.budgetName,
        explicitlyShared: row.explicitlyShared,
        actualCost: 0,
        campaignNames: [],
        campaignCount: 0
      };
    }

    budgetGroupsById[budgetId].actualCost += row.actualCost;
    budgetGroupsById[budgetId].campaignNames.push(row.campaignName);
    budgetGroupsById[budgetId].campaignCount += 1;
  }

  const activeBudgetIds = Object.keys(budgetGroupsById);

  activeBudgetIds.sort(function(a, b) {
    return budgetGroupsById[a].budgetName.localeCompare(budgetGroupsById[b].budgetName);
  });

  for (let j = 0; j < activeBudgetIds.length; j++) {
    const budgetId = activeBudgetIds[j];
    const group = budgetGroupsById[budgetId];
    const isShared = group.campaignCount > 1 || group.explicitlyShared;
    const displayBudgetName = isShared
      ? `${group.budgetName}${SHARED_BUDGET_LABEL}`
      : group.budgetName;

    let storedMaxMonthlySpend = '';
    if (storedBudgetDataById[budgetId]) {
      storedMaxMonthlySpend = storedBudgetDataById[budgetId].maxMonthlySpend;

      if (storedBudgetDataById[budgetId].budgetName !== group.budgetName) {
        console.log(
          `Budget rename detected for ID ${budgetId}: ` +
          `"${storedBudgetDataById[budgetId].budgetName}" -> "${group.budgetName}"`
        );
      }
    }

    activeBudgetIdMap[budgetId] = true;

    group.campaignNames.sort(function(a, b) {
      return a.localeCompare(b);
    });

    activeRows.push([
      displayBudgetName,
      group.campaignCount,
      group.campaignNames.join(', '),
      storedMaxMonthlySpend,
      roundToTwoDecimals(group.actualCost),
      '',
      '',
      ''
    ]);

    activeNotes.push(budgetId);
  }

  const storedBudgetIds = Object.keys(storedBudgetDataById);

  for (let k = 0; k < storedBudgetIds.length; k++) {
    const storedBudgetId = storedBudgetIds[k];

    if (activeBudgetIdMap[storedBudgetId]) {
      continue;
    }

    const storedBudget = storedBudgetDataById[storedBudgetId];

    oldRows.push([
      `${storedBudget.budgetName}${OLD_BUDGET_LABEL}`,
      '',
      '',
      storedBudget.maxMonthlySpend,
      '',
      '',
      '',
      ''
    ]);

    oldNotes.push(storedBudgetId);
  }

  oldRows.sort(function(a, b) {
    return String(a[0]).localeCompare(String(b[0]));
  });

  return {
    activeRows: activeRows,
    activeNotes: activeNotes,
    oldRows: oldRows,
    oldNotes: oldNotes
  };
}

/**
 * Writes the full report to the sheet and reapplies formulas + formatting.
 *
 * @param {Sheet} sheet
 * @param {{
 *   activeRows: Array.<Array.<string|number>>,
 *   activeNotes: Array.<string>,
 *   oldRows: Array.<Array.<string|number>>,
 *   oldNotes: Array.<string>
 * }} reportData
 * @param {number} daysRemainingInMonth
 */
function writeReportToSheet(sheet, reportData, daysRemainingInMonth) {
  sheet.clear();
  sheet.clearFormats();
  sheet.clearNotes();

  sheet.getRange(1, 1, 1, REPORT_HEADERS.length).setValues([REPORT_HEADERS]);
  sheet.getRange(1, 1, 1, REPORT_HEADERS.length).setFontWeight('bold');
  sheet.setFrozenRows(1);

  let currentRow = 2;
  let activeStartRow = 0;
  let activeRowCount = 0;

  if (reportData.activeRows.length > 0) {
    activeStartRow = currentRow;
    activeRowCount = reportData.activeRows.length;

    sheet.getRange(currentRow, 1, reportData.activeRows.length, 8).setValues(reportData.activeRows);
    sheet.getRange(currentRow, 1, reportData.activeRows.length, 1)
      .setNotes(convertSingleColumnArrayToMatrix(reportData.activeNotes));

    applyFormulasToRows(sheet, currentRow, reportData.activeRows.length, daysRemainingInMonth);
    currentRow += reportData.activeRows.length;
  }

  if (reportData.oldRows.length > 0) {
    currentRow += 1;
    sheet.getRange(currentRow, 1).setValue('Old budgets kept for reference');
    sheet.getRange(currentRow, 1).setFontWeight('bold');

    currentRow += 1;

    sheet.getRange(currentRow, 1, reportData.oldRows.length, 8).setValues(reportData.oldRows);
    sheet.getRange(currentRow, 1, reportData.oldRows.length, 1)
      .setNotes(convertSingleColumnArrayToMatrix(reportData.oldNotes));

    applyFormulasToRows(sheet, currentRow, reportData.oldRows.length, daysRemainingInMonth);
    sheet.getRange(currentRow, 1, reportData.oldRows.length, 8).setFontStyle('italic');
  }

  applySheetFormatting(sheet);

  if (activeRowCount > 0) {
    applyConditionalFormatting(sheet, activeStartRow, activeRowCount);
  }

  autoResizeReportColumns(sheet);

  console.log(
    `Sheet updated. Active budget rows: ${reportData.activeRows.length}, old budget rows: ${reportData.oldRows.length}`
  );
}

/**
 * Applies formulas to a block of rows.
 *
 * @param {Sheet} sheet
 * @param {number} startRow
 * @param {number} rowCount
 * @param {number} daysRemainingInMonth
 */
function applyFormulasToRows(sheet, startRow, rowCount, daysRemainingInMonth) {
  const formulas = [];

  for (let rowNumber = startRow; rowNumber < startRow + rowCount; rowNumber++) {
    formulas.push([
      getPercentageUsedFormula(rowNumber),
      getBudgetRemainingFormula(rowNumber),
      getRemainingDailyAllowanceFormula(rowNumber, daysRemainingInMonth)
    ]);
  }

  sheet.getRange(startRow, 6, rowCount, 3).setFormulas(formulas);
}

/**
 * Formula for Percentage of Max spend used.
 *
 * @param {number} rowNumber
 * @return {string}
 */
function getPercentageUsedFormula(rowNumber) {
  return `=IF(OR(D${rowNumber}="",E${rowNumber}="",D${rowNumber}=0),"",E${rowNumber}/D${rowNumber})`;
}

/**
 * Formula for Budget Remaining.
 *
 * @param {number} rowNumber
 * @return {string}
 */
function getBudgetRemainingFormula(rowNumber) {
  return `=IF(OR(D${rowNumber}="",E${rowNumber}=""),"",D${rowNumber}-E${rowNumber})`;
}

/**
 * Formula for Remaining Daily Allowance.
 *
 * @param {number} rowNumber
 * @param {number} daysRemainingInMonth
 * @return {string}
 */
function getRemainingDailyAllowanceFormula(rowNumber, daysRemainingInMonth) {
  return `=IF(OR(G${rowNumber}="",${daysRemainingInMonth}=0),"",G${rowNumber}/${daysRemainingInMonth})`;
}

/**
 * Applies number formatting to the sheet.
 *
 * @param {Sheet} sheet
 */
function applySheetFormatting(sheet) {
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    return;
  }

  sheet.getRange(2, 2, lastRow - 1, 1).setNumberFormat('0');
  sheet.getRange(2, 4, lastRow - 1, 1).setNumberFormat('0.00');
  sheet.getRange(2, 5, lastRow - 1, 1).setNumberFormat('0.00');
  sheet.getRange(2, 6, lastRow - 1, 1).setNumberFormat('0.00%');
  sheet.getRange(2, 7, lastRow - 1, 1).setNumberFormat('0.00');
  sheet.getRange(2, 8, lastRow - 1, 1).setNumberFormat('0.00');
}

/**
 * Applies conditional formatting to active budget rows.
 *
 * @param {Sheet} sheet
 * @param {number} startRow
 * @param {number} rowCount
 */
function applyConditionalFormatting(sheet, startRow, rowCount) {
  const rules = [];
  const activeRange = sheet.getRange(startRow, 1, rowCount, 8);

  const warningRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=AND($F${startRow}<1,$F${startRow}>=${WARNING_PERCENTAGE_THRESHOLD})`)
    .setBackground('#fff2cc')
    .setRanges([activeRange])
    .build();

  const overLimitRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$F${startRow}>=1`)
    .setBackground('#f4cccc')
    .setRanges([activeRange])
    .build();

  rules.push(warningRule);
  rules.push(overLimitRule);

  sheet.setConditionalFormatRules(rules);
}

/**
 * Auto-resizes the main report columns.
 *
 * @param {Sheet} sheet
 */
function autoResizeReportColumns(sheet) {
  for (let column = 1; column <= 8; column++) {
    sheet.autoResizeColumn(column);
  }
}

/**
 * Converts a single-column array into a notes matrix.
 *
 * @param {Array.<string>} singleColumnArray
 * @return {Array.<Array.<string>>}
 */
function convertSingleColumnArrayToMatrix(singleColumnArray) {
  const matrix = [];

  for (let i = 0; i < singleColumnArray.length; i++) {
    matrix.push([singleColumnArray[i]]);
  }

  return matrix;
}

/**
 * Rounds a number to two decimals.
 *
 * @param {number} value
 * @return {number}
 */
function roundToTwoDecimals(value) {
  return Math.round(value * 100) / 100;
}

/**
 * Logs only the first 5 items from a list.
 *
 * @param {string} label
 * @param {Array} items
 * @param {function(*): string} formatter
 */
function logSampleItems(label, items, formatter) {
  const limit = Math.min(items.length, 5);

  for (let i = 0; i < limit; i++) {
    console.log(`${label} ${i + 1}: ${formatter(items[i])}`);
  }
}