How to Monitor Google Ads Goal Status Across MCC Accounts Using Google Sheets

This tutorial documents a script that audits conversion goal activity across all accounts under your MCC. It writes the results to a Google Sheet, tags the health of each goal, color-codes the status, and emails a summary only when there's an issue.

There are no unnecessary tools or third-party dashboards, just raw control and complete visibility.

What This Script Does

This script audits every Google Ads account under your MCC and evaluates the status of each conversion goal over a defined lookback period. Instead of guessing which goals are active or broken, this system gives you a real-time snapshot in a Google Sheet and only alerts you when something needs your attention.

• Connects to your MCC and loops through each linked account
• Skips accounts you've blacklisted (excluded)
• Pulls campaign-level conversion performance over a set lookback period
• Evaluates the health of each conversion goal
• Log results in a Google Sheet, applying background colors to the status
• Emails a summary of only problematic accounts or goals

Email Notifications

There will be two email notifications: one for accounts that need attention and the other for accounts that are all good.

Example:

Messages:

Got it—your script needs two distinct email outputs:

1. ✅ “All Good” Email (if no issues are found)

2. ⚠️ “Needs Attention” Email (if any goals are inactive, missing, or low-performing)

Here’s precisely how both versions should look, using your sample data and current logic:

Message #1

All monitored conversion goals are reporting activity over the last 60 days.

No issues found in any accounts.

View the full report:
https://docs.google.com/spreadsheets/d/yoursheetid

Message #2

The following conversion goals may need review (e.g., no recent conversions or inactive):

Bright Widgets Inc (123-456-789) - Contact Form Submission (Needs Attention)
Acme Corp (987-654-321) - Schedule Call (Inactive)
Acme Corp (987-654-321) - Free Trial Signup (Needs Attention)
Rocket Leads (456-789-123) - (configured, no activity) (No Recent Conversions)
Beta Test Group (321-654-987) - (not set) (Inactive)
Zebra Analytics (999-111-222) - Whitepaper Download (Needs Attention)

View the full report:
https://docs.google.com/spreadsheets/d/yoursheetid

Requirements

Before deploying the script, make sure your environment is configured correctly. This script is designed to run within a Google Ads MCC context to write data and send alerts.

• Google Ads MCC account
• Google Spreadsheet (must be created manually)

The Original Script

This is the script in its complete, unedited form. All comments, formatting, and logic are exactly as originally written. Do not modify this if you plan to follow the breakdown later in this tutorial.

// ========== CONFIGURATION ==========

const SHEET_ID = '1PvpW3eUl5fqRwabBg0P7D8LKzW83MOTVX1KDBMB3ipA';

const LOOKBACK_DAYS = 60;

const EXCLUDED_ACCOUNT_IDS = [

'000-000-000', '000-000-000', '000-000-000'

];

const RECIPIENT_EMAILS = '[email protected]';

// ===================================

function main() {

const sheet = SpreadsheetApp.openById(SHEET_ID).getSheets()[0];

sheet.clear();

sheet.appendRow(['Account Name', 'Account ID', 'Goal Name', 'Conversions', 'Status', 'Date Range']);

const startDate = getDateXDaysAgo(LOOKBACK_DAYS);

const endDate = getTodayDate();

const dateRangeLabel = `Last ${LOOKBACK_DAYS} days (${startDate} to ${endDate})`;

const accounts = MccApp.accounts().get();

const flagged = [];

let rowIndex = 2;

while (accounts.hasNext()) {

const account = accounts.next();

const accountId = account.getCustomerId();

const accountName = account.getName();

Logger.log(`⏳ Checking account: ${accountName} (${accountId})`);

if (EXCLUDED_ACCOUNT_IDS.includes(accountId)) {

Logger.log(`🚫 Skipping excluded account: ${accountName} (${accountId})`);

continue;

}

try {

MccApp.select(account);

const query = `

SELECT ConversionTypeName, Conversions

FROM CAMPAIGN_PERFORMANCE_REPORT

DURING ${startDate},${endDate}

`;

const report = AdsApp.report(query);

const rows = report.rows();

const goalMap = {};

while (rows.hasNext()) {

const row = rows.next();

const goalName = row['ConversionTypeName'] || '(not set)';

const conversions = parseFloat(row['Conversions']) || 0;

if (!goalMap[goalName]) {

goalMap[goalName] = 0;

}

goalMap[goalName] += conversions;

}

if (Object.keys(goalMap).length === 0) {

Logger.log(`⚠️ No conversion data returned for account: ${accountName} (${accountId})`);

const status = 'No Recent Conversions';

sheet.appendRow([

accountName,

accountId,

'(configured, no activity)',

0,

status,

dateRangeLabel

]);

setStatusColumnColor(sheet, rowIndex, status);

flagged.push(`${accountName} (${accountId}) - no conversion goals triggered`);

rowIndex++;

continue;

}

for (const [goalName, totalConversions] of Object.entries(goalMap)) {

let status;

if (totalConversions > 0) {

status = 'Active';

} else if (goalName.toLowerCase().includes('test') || goalName === '(not set)') {

status = 'Inactive';

} else {

status = 'Needs Attention';

}

sheet.appendRow([

accountName,

accountId,

goalName,

totalConversions,

status,

dateRangeLabel

]);

setStatusColumnColor(sheet, rowIndex, status);

if (status !== 'Active') {

flagged.push(`${accountName} (${accountId}) - ${goalName} (${status})`);

}

rowIndex++;

}

} catch (e) {

Logger.log(`❌ Error processing account: ${accountName} (${accountId}) - ${e.message}`);

}

}

if (flagged.length > 0) {

const subject = '⚠️ Conversion Goals Needing Attention';

const body = `The following conversion goals may need review (e.g., no recent conversions or inactive):\n\n`

+ flagged.join('\n')

+ `\n\nView the full report:\nhttps://docs.google.com/spreadsheets/d/${SHEET_ID}`;

MailApp.sendEmail(RECIPIENT_EMAILS, subject, body);

} else {

Logger.log('✅ All goals are reporting conversions.');

}

}

// 🎨 Apply color to only the "Status" column (Column E)

function setStatusColumnColor(sheet, row, status) {

const range = sheet.getRange(row, 5); // Column E

switch (status) {

case 'Active':

range.setBackground('#d9ead3'); // Light green

break;

case 'Inactive':

range.setBackground('#f4cccc'); // Light red

break;

case 'Needs Attention':

range.setBackground('#fff2cc'); // Light yellow

break;

case 'No Recent Conversions':

range.setBackground('#e6e6fa'); // Light purple

break;

default:

range.setBackground(null);

}

}

// 🕒 Helpers

function getTodayDate() {

const date = new Date();

return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

}

function getDateXDaysAgo(days) {

const date = new Date();

date.setDate(date.getDate() - days);

return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

}

Script Breakdown by Section

CONFIGURATION

• SHEET_ID: Google Sheets destination for all output
• LOOKBACK_DAYS: Reporting window in days
• EXCLUDED_ACCOUNT_IDS: Client accounts to skip
• RECIPIENT_EMAILS: Who gets emailed when there's a problem

main() Function

• Opens the sheet, clears it, and sets up headers
• Calculates the date range
• Loops through every MCC sub-account
• Skips any that are blacklisted
• Uses CAMPAIGN_PERFORMANCE_REPORT to pull all goal data
• For each goal:
  • Aggregates total conversions
  • Assigns a status:
    • Active: conversions > 0
    • Inactive: test goal or not set
    • Needs Attention: zero conversions, not a test
    • No Recent Conversions: no goal data returned
• Appends data to the sheet
• Calls setStatusColumnColor() to mark each row
• Builds a flagged list and emails if any issue is found

setStatusColumnColor(sheet, row, status)

• Applies a background color to Column E depending on the goal status:
  • Green for active
  • Red for inactive
  • Yellow for needs attention
  • Purple for no activity

Date Helpers

• getTodayDate() returns today's date in the correct timezone
• getDateXDaysAgo() returns the date X days ago

This walkthrough shows exactly how to set up the conversion goal health checker for your Google Ads MCC account. Follow each step to get the system running, writing to Google Sheets, and sending alerts when something breaks.

Step 1: Create the Google Sheet

Start by setting up the output destination.

1. Go to Google Sheets
2. Click "Blank" to create a new spreadsheet
3. Give it a name like Goal Monitor
4. The part in the middle is your Sheet ID
5. Paste the ID into your script where it says const SHEET_ID = '...'

Step 2: Open the Google Ads Scripts Panel

You will deploy this script inside your MCC account.

1. Log in to your Google Ads MCC account
2. Click "Tools and Settings" in the top navigation
3. Under "Bulk Actions," select "Scripts"
4. Click the plus (+) button to add a new script
5. Paste the entire script exactly as written into the code editor

Step 3: Authorize the Script

The first time you use the script, you need to authorize it.

1. Click "Authorize" in the upper right
2. Select your Google account
3. Approve all requested permissions

You must complete this step or the script will fail to run.

Step 4: Set Your Script Configuration

Inside the script, review and modify the following:

• SHEET_ID: Paste in your copied Sheet ID
• LOOKBACK_DAYS: Change if needed (default is 60)
• EXCLUDED_ACCOUNT_IDS: Add any accounts you do not want scanned
• RECIPIENT_EMAILS: Add the email or emails to receive alert reports

You can leave the rest of the script exactly as it is.

Step 5: Run a Manual Test

Run it once manually to make sure everything works.

1. Click "Run" at the top of the navigation.
2. Wait until execution completes.
3. Open the connected Google Sheet.
4. Check that:
   • Headers were created
   • Data was written for each active account.
   • Statuses and colors are applied correctly in Column E.

Step 6: Schedule the Script (Optional)

To make this automatic, set it to run on a recurring schedule.

1. From the script editor, click "Create Schedule."
2. Choose a frequency (daily is recommended)
3. Set the run time (early morning is ideal)
4. Save and close

The script will run automatically and only alert you when something is off.

Step 7: Review Alerts

When the script runs, it checks all conversion goals. If any are inactive or not firing, you receive an email.

Summary

This script is not just a data dump. It's a watchdog for every conversion goal across your entire Google Ads MCC. You get alerted when something breaks, stalls, or quietly stops tracking. When everything is fine, you get silence.

It checks every account you manage, flags inactive or underperforming goals, and delivers a filtered, no-noise report straight to your inbox. The Google Sheet gives you a live view of goal status by account, goal name, and conversion volume, with visual cues baked in.

You’re not digging through interfaces. You’re not waiting until the end of the month to realize you’ve been blind. You’re in control before it becomes a problem.

Contact Us

Tired of guessing whether your tracking works? Want a team that builds systems instead of just reports?

Bright Vessel doesn’t just manage Google Ads, we engineer performance visibility at scale. Whether you need help deploying this script, integrating it into a larger automation stack, or building a custom analytics layer that tells you something, we’re ready.

Talk to the team that builds what other agencies fake.

Contact Bright Vessel