Shared Negative List Manager

One agency, ten clients, the same garbage search terms across all of them. Maintaining the same negative list ten times in the UI is how negatives get stale. This script makes a Google Sheet the source of truth: column A is the keyword, column B is the match type, one tab per list. AUDIT mode shows you the diff. SYNC mode applies it. APPLY mode attaches lists to campaigns by name filter. DRY_RUN is on by default so the first run never breaks anything.

Setup

1. Open Google Ads, Tools, Bulk actions, Scripts, New script.
2. Paste the code below.
3. Adjust the CONFIG block at the top: set SPREADSHEET_URL to your master control sheet (each tab name must match a shared negative list name, keywords in column A, optional BROAD or PHRASE or EXACT in column B). Start with MODE: 'AUDIT' and DRY_RUN: true to see the diff. Tune MAX_ADDITIONS_PER_RUN (default 500), MAX_REMOVALS_PER_RUN (default 100), and CONFIRM_REMOVALS (default true). Use LISTS_TO_MANAGE to scope to specific lists and CREATE_MISSING_LISTS if you want the script to create new lists.
4. Click Preview to verify access. Then schedule. Only flip DRY_RUN: false after the AUDIT output looks right.

/******************************************************************************
 * SHARED NEGATIVE KEYWORD LIST MANAGER
 *
 * Generated by PPC.io Script Engine
 * https://ppc.io
 *
 * Purpose: Manage shared negative keyword lists from a Google Sheet
 * Author:  PPC.io
 * Version: 1.0
 * Updated: 2025-01-14
 *
 * SETUP INSTRUCTIONS:
 * 1. Set SPREADSHEET_URL to your control spreadsheet
 * 2. Create sheets in the spreadsheet with names matching your negative lists
 * 3. Put keywords in column A of each sheet (one per row)
 * 4. Run in AUDIT mode first to see what would change
 * 5. Set MODE to 'SYNC' and DRY_RUN to false to apply changes
 *
 * USE CASE: "Sync my negative keyword lists from a master spreadsheet"
 *
 * OPERATION MODES:
 * - AUDIT: Compare spreadsheet to actual lists, report differences only
 * - SYNC: Update lists to match spreadsheet (respects DRY_RUN)
 * - APPLY: Attach lists to campaigns matching filters
 * - REPORT: Export current list contents to spreadsheet
 *
 * OUTPUTS:
 * - 1. Summary: Lists managed, changes made, campaign coverage
 * - 2. List Contents: Current state of each managed list
 * - 3. Sync Actions: Keywords added/removed (if SYNC mode)
 * - 4. Campaign Coverage: Which campaigns have which lists
 *
 * CHANGELOG:
 * v1.0 - Initial release with multi-mode operation
 *
 ******************************************************************************/

/******************************************************************************
 * CONFIGURATION - Adjust these values for your account
 ******************************************************************************/
var CONFIG = {
  // ═══════════════════════════════════════════════════════════════════════════
  // SPREADSHEET SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  // This spreadsheet should contain sheets named after your negative keyword lists
  // Column A = keyword text, Column B = match type (optional, defaults to BROAD)
  SPREADSHEET_URL: 'YOUR_CONTROL_SPREADSHEET_URL',

  // Sheet for summary output (will be created/updated)
  OUTPUT_SHEET_NAME: 'NKL Manager Output',

  // Email alerts
  EMAIL_RECIPIENTS: [],           // ['email@example.com']
  SLACK_WEBHOOK_URL: '',          // Slack incoming webhook URL

  // ═══════════════════════════════════════════════════════════════════════════
  // OPERATION MODE
  // ═══════════════════════════════════════════════════════════════════════════
  // AUDIT  - Compare only, no changes
  // SYNC   - Update lists to match spreadsheet
  // APPLY  - Attach lists to campaigns
  // REPORT - Export current list contents
  MODE: 'AUDIT',

  // ═══════════════════════════════════════════════════════════════════════════
  // SAFETY SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  DRY_RUN: true,                  // CRITICAL: Set false to make changes
  MAX_ADDITIONS_PER_RUN: 500,     // Safety limit on keywords added
  MAX_REMOVALS_PER_RUN: 100,      // Safety limit on keywords removed
  CONFIRM_REMOVALS: true,         // Extra confirmation for removals

  // ═══════════════════════════════════════════════════════════════════════════
  // LIST MANAGEMENT
  // ═══════════════════════════════════════════════════════════════════════════
  // Lists to manage (sheet names in your control spreadsheet)
  // Leave empty to manage ALL lists found in the spreadsheet
  LISTS_TO_MANAGE: [],            // e.g., ['Brand Terms', 'Competitor Terms']

  // Create lists if they don't exist?
  CREATE_MISSING_LISTS: false,

  // ═══════════════════════════════════════════════════════════════════════════
  // CAMPAIGN APPLICATION (for APPLY mode)
  // ═══════════════════════════════════════════════════════════════════════════
  // Apply lists to campaigns matching these filters
  APPLY_TO_CAMPAIGN_CONTAINS: '',
  APPLY_TO_CAMPAIGN_EXCLUDES: '',
  APPLY_TO_CAMPAIGN_TYPE: 'SEARCH', // SEARCH, DISPLAY, SHOPPING, or empty for all
  APPLY_ONLY_ENABLED: true,

  // Specific list-to-campaign mappings (advanced)
  // Format: { 'List Name': ['Campaign contains text'] }
  LIST_CAMPAIGN_MAPPING: {},

  // ═══════════════════════════════════════════════════════════════════════════
  // EXECUTION SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  LOG_LEVEL: 'INFO',              // DEBUG, INFO, WARN, ERROR
  TIME_LIMIT_MINUTES: 25,         // Exit gracefully before this
  BATCH_SIZE: 500                 // Rows per spreadsheet write
};

/******************************************************************************
 * MAIN EXECUTION
 ******************************************************************************/
function main() {
  var startTime = new Date();
  log('INFO', 'Shared Negative List Manager started: ' + startTime.toISOString());
  log('INFO', 'Mode: ' + CONFIG.MODE + (CONFIG.DRY_RUN ? ' [DRY RUN]' : ' [LIVE]'));

  try {
    validateConfig();
    var ss = openSpreadsheet();
    var results = manageNegativeLists(ss, startTime);
    writeResults(ss, results);
    sendNotifications(results, ss.getUrl(), startTime);
    logSummary(results, startTime);

  } catch (error) {
    handleFatalError(error, startTime);
  }
}

function validateConfig() {
  if (!CONFIG.SPREADSHEET_URL || CONFIG.SPREADSHEET_URL === 'YOUR_CONTROL_SPREADSHEET_URL') {
    throw new Error('SPREADSHEET_URL not configured. Please set your control spreadsheet URL.');
  }

  var validModes = ['AUDIT', 'SYNC', 'APPLY', 'REPORT'];
  if (validModes.indexOf(CONFIG.MODE) === -1) {
    throw new Error('Invalid MODE: ' + CONFIG.MODE + '. Must be one of: ' + validModes.join(', '));
  }
}

/******************************************************************************
 * MAIN LOGIC
 ******************************************************************************/
function manageNegativeLists(ss, startTime) {
  var results = {
    existingLists: [],
    desiredLists: [],
    syncActions: [],
    campaignCoverage: [],
    listContents: [],
    summary: {
      mode: CONFIG.MODE,
      dryRun: CONFIG.DRY_RUN,
      listsManaged: 0,
      keywordsAdded: 0,
      keywordsRemoved: 0,
      campaignsUpdated: 0,
      errors: 0
    }
  };

  // Get existing shared negative keyword lists
  log('INFO', 'Fetching existing negative keyword lists...');
  results.existingLists = getExistingLists();
  log('INFO', 'Found ' + results.existingLists.length + ' existing lists');

  // Get desired state from spreadsheet
  log('INFO', 'Reading desired state from spreadsheet...');
  results.desiredLists = getDesiredLists(ss);
  log('INFO', 'Found ' + results.desiredLists.length + ' lists in spreadsheet');

  // Execute based on mode
  switch (CONFIG.MODE) {
    case 'AUDIT':
      performAudit(results, startTime);
      break;

    case 'SYNC':
      performSync(results, startTime);
      break;

    case 'APPLY':
      performApply(results, startTime);
      break;

    case 'REPORT':
      performReport(results, startTime);
      break;
  }

  // Always collect campaign coverage
  getCampaignCoverage(results);

  return results;
}

/******************************************************************************
 * LIST DATA COLLECTION
 ******************************************************************************/
function getExistingLists() {
  var lists = [];

  try {
    var listIterator = AdsApp.negativeKeywordLists().get();

    while (listIterator.hasNext()) {
      var list = listIterator.next();
      var listName = list.getName();

      // Filter to managed lists if specified
      if (CONFIG.LISTS_TO_MANAGE.length > 0 &&
          CONFIG.LISTS_TO_MANAGE.indexOf(listName) === -1) {
        continue;
      }

      var keywords = [];
      var keywordIterator = list.negativeKeywords().get();

      while (keywordIterator.hasNext()) {
        var kw = keywordIterator.next();
        keywords.push({
          text: kw.getText(),
          matchType: kw.getMatchType(),
          normalizedKey: normalizeKeyword(kw.getText(), kw.getMatchType())
        });
      }

      // Get campaigns using this list
      var campaignsUsing = [];
      var campaignIterator = list.campaigns().get();
      while (campaignIterator.hasNext()) {
        var campaign = campaignIterator.next();
        campaignsUsing.push({
          name: campaign.getName(),
          id: campaign.getId()
        });
      }

      lists.push({
        name: listName,
        id: list.getId(),
        listObject: list,
        keywords: keywords,
        keywordMap: buildKeywordMap(keywords),
        campaigns: campaignsUsing,
        campaignCount: campaignsUsing.length
      });
    }
  } catch (e) {
    log('ERROR', 'Failed to fetch existing lists: ' + e.message);
  }

  return lists;
}

function getDesiredLists(ss) {
  var lists = [];
  var sheets = ss.getSheets();

  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();

    // Skip output/summary sheets
    if (sheetName.indexOf('Summary') !== -1 ||
        sheetName.indexOf('Output') !== -1 ||
        sheetName.indexOf('Actions') !== -1 ||
        sheetName.indexOf('Coverage') !== -1 ||
        sheetName === CONFIG.OUTPUT_SHEET_NAME) {
      continue;
    }

    // Filter to managed lists if specified
    if (CONFIG.LISTS_TO_MANAGE.length > 0 &&
        CONFIG.LISTS_TO_MANAGE.indexOf(sheetName) === -1) {
      continue;
    }

    // Read keywords from sheet
    var keywords = [];
    var lastRow = sheet.getLastRow();

    if (lastRow > 0) {
      var data = sheet.getRange(1, 1, lastRow, 2).getValues();

      for (var j = 0; j < data.length; j++) {
        var text = (data[j][0] || '').toString().trim();
        if (!text) continue;

        var matchType = (data[j][1] || 'BROAD').toString().toUpperCase().trim();
        if (['BROAD', 'PHRASE', 'EXACT'].indexOf(matchType) === -1) {
          matchType = 'BROAD';
        }

        keywords.push({
          text: text,
          matchType: matchType,
          normalizedKey: normalizeKeyword(text, matchType)
        });
      }
    }

    lists.push({
      name: sheetName,
      keywords: keywords,
      keywordMap: buildKeywordMap(keywords)
    });
  }

  return lists;
}

function buildKeywordMap(keywords) {
  var map = {};
  for (var i = 0; i < keywords.length; i++) {
    map[keywords[i].normalizedKey] = keywords[i];
  }
  return map;
}

function normalizeKeyword(text, matchType) {
  return text.toLowerCase().trim() + '|' + matchType;
}

/******************************************************************************
 * AUDIT MODE
 ******************************************************************************/
function performAudit(results, startTime) {
  log('INFO', 'Performing audit...');

  for (var i = 0; i < results.desiredLists.length; i++) {
    var desired = results.desiredLists[i];

    // Find matching existing list
    var existing = findListByName(results.existingLists, desired.name);

    if (!existing) {
      // List doesn't exist
      results.syncActions.push({
        action: 'CREATE_LIST',
        listName: desired.name,
        details: 'List does not exist in account',
        keywordsToAdd: desired.keywords.length,
        status: CONFIG.CREATE_MISSING_LISTS ? 'WOULD_CREATE' : 'SKIPPED'
      });
      continue;
    }

    // Compare keywords
    var toAdd = [];
    var toRemove = [];

    // Find keywords to add
    for (var key in desired.keywordMap) {
      if (!existing.keywordMap[key]) {
        toAdd.push(desired.keywordMap[key]);
      }
    }

    // Find keywords to remove
    for (var key in existing.keywordMap) {
      if (!desired.keywordMap[key]) {
        toRemove.push(existing.keywordMap[key]);
      }
    }

    if (toAdd.length > 0 || toRemove.length > 0) {
      results.syncActions.push({
        action: 'UPDATE_LIST',
        listName: desired.name,
        listId: existing.id,
        keywordsInSheet: desired.keywords.length,
        keywordsInList: existing.keywords.length,
        keywordsToAdd: toAdd.length,
        keywordsToRemove: toRemove.length,
        addDetails: toAdd.slice(0, 10).map(function(k) { return k.text; }).join(', ') +
                    (toAdd.length > 10 ? '...' : ''),
        removeDetails: toRemove.slice(0, 10).map(function(k) { return k.text; }).join(', ') +
                       (toRemove.length > 10 ? '...' : ''),
        status: 'AUDIT_ONLY'
      });
    } else {
      results.syncActions.push({
        action: 'NO_CHANGE',
        listName: desired.name,
        listId: existing.id,
        keywordsInSheet: desired.keywords.length,
        keywordsInList: existing.keywords.length,
        status: 'IN_SYNC'
      });
    }

    results.summary.listsManaged++;
    checkTimeLimit(startTime);
  }

  log('INFO', 'Audit complete. ' + results.syncActions.length + ' lists analyzed');
}

/******************************************************************************
 * SYNC MODE
 ******************************************************************************/
function performSync(results, startTime) {
  log('INFO', 'Performing sync...');

  var totalAdded = 0;
  var totalRemoved = 0;

  for (var i = 0; i < results.desiredLists.length; i++) {
    var desired = results.desiredLists[i];
    var existing = findListByName(results.existingLists, desired.name);

    // Create list if needed
    if (!existing) {
      if (CONFIG.CREATE_MISSING_LISTS) {
        existing = createNewList(desired.name, results);
        if (!existing) continue;
      } else {
        results.syncActions.push({
          action: 'SKIP_CREATE',
          listName: desired.name,
          details: 'List does not exist and CREATE_MISSING_LISTS is false',
          status: 'SKIPPED'
        });
        continue;
      }
    }

    // Calculate diff
    var toAdd = [];
    var toRemove = [];

    for (var key in desired.keywordMap) {
      if (!existing.keywordMap[key]) {
        toAdd.push(desired.keywordMap[key]);
      }
    }

    for (var key in existing.keywordMap) {
      if (!desired.keywordMap[key]) {
        toRemove.push(existing.keywordMap[key]);
      }
    }

    // Apply additions
    var addedCount = 0;
    if (toAdd.length > 0 && totalAdded < CONFIG.MAX_ADDITIONS_PER_RUN) {
      for (var j = 0; j < toAdd.length && totalAdded < CONFIG.MAX_ADDITIONS_PER_RUN; j++) {
        var kw = toAdd[j];

        if (CONFIG.DRY_RUN) {
          log('DEBUG', '[DRY RUN] Would add: ' + kw.text + ' [' + kw.matchType + '] to ' + desired.name);
        } else {
          try {
            var formatted = formatKeywordForAdd(kw.text, kw.matchType);
            existing.listObject.addNegativeKeyword(formatted);
            addedCount++;
            totalAdded++;
          } catch (e) {
            log('WARN', 'Failed to add keyword: ' + kw.text + ' - ' + e.message);
            results.summary.errors++;
          }
        }
      }
    }

    // Apply removals (with extra caution)
    var removedCount = 0;
    if (toRemove.length > 0 && totalRemoved < CONFIG.MAX_REMOVALS_PER_RUN && !CONFIG.CONFIRM_REMOVALS) {
      for (var k = 0; k < toRemove.length && totalRemoved < CONFIG.MAX_REMOVALS_PER_RUN; k++) {
        var kw = toRemove[k];

        if (CONFIG.DRY_RUN) {
          log('DEBUG', '[DRY RUN] Would remove: ' + kw.text + ' [' + kw.matchType + '] from ' + desired.name);
        } else {
          try {
            // Find and remove the keyword
            var keywordIterator = existing.listObject.negativeKeywords()
              .withCondition('KeywordText = "' + kw.text.replace(/"/g, '\\"') + '"')
              .get();

            while (keywordIterator.hasNext()) {
              var negKw = keywordIterator.next();
              if (negKw.getMatchType() === kw.matchType) {
                negKw.remove();
                removedCount++;
                totalRemoved++;
                break;
              }
            }
          } catch (e) {
            log('WARN', 'Failed to remove keyword: ' + kw.text + ' - ' + e.message);
            results.summary.errors++;
          }
        }
      }
    }

    // Record action
    results.syncActions.push({
      action: (addedCount > 0 || removedCount > 0 || CONFIG.DRY_RUN) ? 'SYNCED' : 'NO_CHANGE',
      listName: desired.name,
      listId: existing.id,
      keywordsToAdd: toAdd.length,
      keywordsAdded: CONFIG.DRY_RUN ? 0 : addedCount,
      keywordsToRemove: toRemove.length,
      keywordsRemoved: CONFIG.DRY_RUN ? 0 : removedCount,
      status: CONFIG.DRY_RUN ? 'DRY_RUN' : 'APPLIED'
    });

    results.summary.keywordsAdded += CONFIG.DRY_RUN ? toAdd.length : addedCount;
    results.summary.keywordsRemoved += CONFIG.DRY_RUN ? toRemove.length : removedCount;
    results.summary.listsManaged++;

    checkTimeLimit(startTime);
  }

  log('INFO', 'Sync complete. Added: ' + totalAdded + ', Removed: ' + totalRemoved);
}

function createNewList(name, results) {
  if (CONFIG.DRY_RUN) {
    log('INFO', '[DRY RUN] Would create list: ' + name);
    results.syncActions.push({
      action: 'CREATE_LIST',
      listName: name,
      status: 'DRY_RUN'
    });
    return null;
  }

  try {
    var builder = AdsApp.newNegativeKeywordListBuilder()
      .withName(name)
      .build();

    var list = builder.getResult();
    log('INFO', 'Created new list: ' + name);

    results.syncActions.push({
      action: 'CREATE_LIST',
      listName: name,
      listId: list.getId(),
      status: 'CREATED'
    });

    return {
      name: name,
      id: list.getId(),
      listObject: list,
      keywords: [],
      keywordMap: {},
      campaigns: [],
      campaignCount: 0
    };
  } catch (e) {
    log('ERROR', 'Failed to create list: ' + name + ' - ' + e.message);
    results.summary.errors++;
    return null;
  }
}

function formatKeywordForAdd(text, matchType) {
  switch (matchType) {
    case 'EXACT':
      return '[' + text + ']';
    case 'PHRASE':
      return '"' + text + '"';
    default:
      return text;
  }
}

/******************************************************************************
 * APPLY MODE
 ******************************************************************************/
function performApply(results, startTime) {
  log('INFO', 'Applying lists to campaigns...');

  var campaigns = getTargetCampaigns();
  log('INFO', 'Found ' + campaigns.length + ' target campaigns');

  for (var i = 0; i < results.existingLists.length; i++) {
    var list = results.existingLists[i];
    var appliedCount = 0;

    for (var j = 0; j < campaigns.length; j++) {
      var campaign = campaigns[j];

      // Check if already applied
      var alreadyApplied = list.campaigns.some(function(c) {
        return c.id == campaign.id;
      });

      if (alreadyApplied) continue;

      // Check custom mapping
      if (Object.keys(CONFIG.LIST_CAMPAIGN_MAPPING).length > 0) {
        var mapping = CONFIG.LIST_CAMPAIGN_MAPPING[list.name];
        if (mapping) {
          var matches = mapping.some(function(filter) {
            return campaign.name.toLowerCase().indexOf(filter.toLowerCase()) !== -1;
          });
          if (!matches) continue;
        }
      }

      // Apply list to campaign
      if (CONFIG.DRY_RUN) {
        log('DEBUG', '[DRY RUN] Would apply "' + list.name + '" to ' + campaign.name);
        appliedCount++;
      } else {
        try {
          campaign.campaignObject.addNegativeKeywordList(list.listObject);
          appliedCount++;
          log('INFO', 'Applied "' + list.name + '" to ' + campaign.name);
        } catch (e) {
          log('WARN', 'Failed to apply list to campaign: ' + e.message);
          results.summary.errors++;
        }
      }
    }

    if (appliedCount > 0) {
      results.syncActions.push({
        action: 'APPLY_LIST',
        listName: list.name,
        campaignsApplied: appliedCount,
        status: CONFIG.DRY_RUN ? 'DRY_RUN' : 'APPLIED'
      });
      results.summary.campaignsUpdated += appliedCount;
    }

    checkTimeLimit(startTime);
  }

  log('INFO', 'Apply complete. ' + results.summary.campaignsUpdated + ' campaign-list associations');
}

function getTargetCampaigns() {
  var campaigns = [];

  var selector = AdsApp.campaigns();

  if (CONFIG.APPLY_TO_CAMPAIGN_TYPE) {
    selector = selector.withCondition('AdvertisingChannelType = ' + CONFIG.APPLY_TO_CAMPAIGN_TYPE);
  }

  if (CONFIG.APPLY_ONLY_ENABLED) {
    selector = selector.withCondition('Status = ENABLED');
  }

  var iterator = selector.get();

  while (iterator.hasNext()) {
    var campaign = iterator.next();
    var name = campaign.getName();

    // Apply filters
    if (CONFIG.APPLY_TO_CAMPAIGN_CONTAINS &&
        name.toLowerCase().indexOf(CONFIG.APPLY_TO_CAMPAIGN_CONTAINS.toLowerCase()) === -1) {
      continue;
    }
    if (CONFIG.APPLY_TO_CAMPAIGN_EXCLUDES &&
        name.toLowerCase().indexOf(CONFIG.APPLY_TO_CAMPAIGN_EXCLUDES.toLowerCase()) !== -1) {
      continue;
    }

    campaigns.push({
      id: campaign.getId(),
      name: name,
      campaignObject: campaign
    });
  }

  return campaigns;
}

/******************************************************************************
 * REPORT MODE
 ******************************************************************************/
function performReport(results, startTime) {
  log('INFO', 'Generating report of current list contents...');

  for (var i = 0; i < results.existingLists.length; i++) {
    var list = results.existingLists[i];

    results.listContents.push({
      listName: list.name,
      listId: list.id,
      keywordCount: list.keywords.length,
      campaignCount: list.campaignCount,
      keywords: list.keywords
    });

    results.summary.listsManaged++;
    checkTimeLimit(startTime);
  }

  log('INFO', 'Report complete. ' + results.listContents.length + ' lists documented');
}

/******************************************************************************
 * CAMPAIGN COVERAGE
 ******************************************************************************/
function getCampaignCoverage(results) {
  var coverageMap = {};

  // Build map of which lists are applied to which campaigns
  for (var i = 0; i < results.existingLists.length; i++) {
    var list = results.existingLists[i];

    for (var j = 0; j < list.campaigns.length; j++) {
      var campaign = list.campaigns[j];

      if (!coverageMap[campaign.id]) {
        coverageMap[campaign.id] = {
          campaignName: campaign.name,
          campaignId: campaign.id,
          lists: []
        };
      }

      coverageMap[campaign.id].lists.push(list.name);
    }
  }

  // Convert to array
  for (var id in coverageMap) {
    var coverage = coverageMap[id];
    results.campaignCoverage.push({
      'Campaign': coverage.campaignName,
      'Campaign ID': coverage.campaignId,
      'Lists Applied': coverage.lists.length,
      'List Names': coverage.lists.join(', ')
    });
  }

  // Sort by list count descending
  results.campaignCoverage.sort(function(a, b) {
    return b['Lists Applied'] - a['Lists Applied'];
  });
}

/******************************************************************************
 * OUTPUT FUNCTIONS
 ******************************************************************************/
function openSpreadsheet() {
  return SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
}

function writeResults(ss, results) {
  // Write to output sheet
  var outputSheet = ss.getSheetByName(CONFIG.OUTPUT_SHEET_NAME);
  if (!outputSheet) {
    outputSheet = ss.insertSheet(CONFIG.OUTPUT_SHEET_NAME);
  }
  outputSheet.clear();

  writeSummaryToSheet(outputSheet, results);

  // Write sync actions
  if (results.syncActions.length > 0) {
    var actionsSheet = ss.getSheetByName('Sync Actions');
    if (!actionsSheet) {
      actionsSheet = ss.insertSheet('Sync Actions');
    }
    actionsSheet.clear();
    writeActionsSheet(actionsSheet, results);
  }

  // Write campaign coverage
  if (results.campaignCoverage.length > 0) {
    var coverageSheet = ss.getSheetByName('Campaign Coverage');
    if (!coverageSheet) {
      coverageSheet = ss.insertSheet('Campaign Coverage');
    }
    coverageSheet.clear();
    writeCoverageSheet(coverageSheet, results);
  }

  // Write list contents (for REPORT mode)
  if (results.listContents.length > 0) {
    writeListContentsSheets(ss, results);
  }
}

function writeSummaryToSheet(sheet, results) {
  var data = [
    ['SHARED NEGATIVE KEYWORD LIST MANAGER', ''],
    ['Generated by PPC.io Script Engine', ''],
    ['https://ppc.io', ''],
    ['', ''],
    ['Account: ' + AdsApp.currentAccount().getName(), ''],
    ['Run Date: ' + new Date().toISOString(), ''],
    ['Mode: ' + CONFIG.MODE + (CONFIG.DRY_RUN ? ' [DRY RUN]' : ' [LIVE]'), ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['SUMMARY', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Lists in Account', results.existingLists.length],
    ['Lists in Spreadsheet', results.desiredLists.length],
    ['Lists Managed', results.summary.listsManaged],
    ['', ''],
    ['Keywords Added', results.summary.keywordsAdded],
    ['Keywords Removed', results.summary.keywordsRemoved],
    ['Campaigns Updated', results.summary.campaignsUpdated],
    ['Errors', results.summary.errors],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['EXISTING LISTS', ''],
    ['═══════════════════════════════════════════════════════════════', '']
  ];

  for (var i = 0; i < results.existingLists.length; i++) {
    var list = results.existingLists[i];
    data.push([list.name, list.keywords.length + ' keywords, ' + list.campaignCount + ' campaigns']);
  }

  data = data.concat([
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['AI ANALYSIS PROMPTS', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Prompt 1', '"Are my negative keyword lists in sync with my master sheet?"'],
    ['Prompt 2', '"Which campaigns are missing negative keyword lists?"'],
    ['Prompt 3', '"What negatives should I add based on search term performance?"'],
    ['Prompt 4', '"Review my negative keyword strategy for gaps"']
  ]);

  sheet.getRange(1, 1, data.length, 2).setValues(data);
  sheet.getRange(1, 1).setFontWeight('bold').setFontSize(14);
  sheet.setColumnWidth(1, 350);
  sheet.setColumnWidth(2, 400);
}

function writeActionsSheet(sheet, results) {
  var headers = ['Action', 'List Name', 'Keywords to Add', 'Keywords Added',
                 'Keywords to Remove', 'Keywords Removed', 'Status', 'Details'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
  sheet.setFrozenRows(1);

  var rows = results.syncActions.map(function(action) {
    return [
      action.action,
      action.listName,
      action.keywordsToAdd || 0,
      action.keywordsAdded || 0,
      action.keywordsToRemove || 0,
      action.keywordsRemoved || 0,
      action.status,
      action.addDetails || action.details || ''
    ];
  });

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

    // Color code status
    var statusCol = headers.indexOf('Status') + 1;
    var statusColors = {
      'APPLIED': '#d4edda',
      'DRY_RUN': '#cce5ff',
      'IN_SYNC': '#d4edda',
      'SKIPPED': '#e9ecef',
      'AUDIT_ONLY': '#fff3cd'
    };

    for (var i = 0; i < rows.length; i++) {
      var status = rows[i][6];
      if (statusColors[status]) {
        sheet.getRange(i + 2, statusCol).setBackground(statusColors[status]);
      }
    }
  }

  for (var col = 1; col <= headers.length; col++) {
    sheet.autoResizeColumn(col);
  }
}

function writeCoverageSheet(sheet, results) {
  var headers = ['Campaign', 'Lists Applied', 'List Names'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
  sheet.setFrozenRows(1);

  var rows = results.campaignCoverage.map(function(c) {
    return [c['Campaign'], c['Lists Applied'], c['List Names']];
  });

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  }

  for (var col = 1; col <= headers.length; col++) {
    sheet.autoResizeColumn(col);
  }
}

function writeListContentsSheets(ss, results) {
  for (var i = 0; i < results.listContents.length; i++) {
    var list = results.listContents[i];
    var sheetName = 'List: ' + list.listName.substring(0, 20);

    var sheet = ss.getSheetByName(sheetName);
    if (!sheet) {
      sheet = ss.insertSheet(sheetName);
    }
    sheet.clear();

    var headers = ['Keyword', 'Match Type'];
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');

    var rows = list.keywords.map(function(kw) {
      return [kw.text, kw.matchType];
    });

    if (rows.length > 0) {
      sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
    }
  }
}

/******************************************************************************
 * NOTIFICATION FUNCTIONS
 ******************************************************************************/
function sendNotifications(results, spreadsheetUrl, startTime) {
  var duration = ((new Date() - startTime) / 1000).toFixed(1);

  var message = [
    'Shared Negative List Manager Complete',
    '',
    'Account: ' + AdsApp.currentAccount().getName(),
    'Mode: ' + CONFIG.MODE + (CONFIG.DRY_RUN ? ' [DRY RUN]' : ' [LIVE]'),
    'Duration: ' + duration + 's',
    '',
    'Summary:',
    '- Lists Managed: ' + results.summary.listsManaged,
    '- Keywords Added: ' + results.summary.keywordsAdded,
    '- Keywords Removed: ' + results.summary.keywordsRemoved,
    '- Campaigns Updated: ' + results.summary.campaignsUpdated,
    '- Errors: ' + results.summary.errors,
    '',
    'Report: ' + spreadsheetUrl,
    '',
    '--',
    'Generated by PPC.io Script Engine'
  ].join('\n');

  if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
    try {
      MailApp.sendEmail({
        to: CONFIG.EMAIL_RECIPIENTS.join(','),
        subject: '[PPC.io] NKL Manager - ' + CONFIG.MODE + ' - ' +
                 results.summary.keywordsAdded + ' added, ' +
                 results.summary.keywordsRemoved + ' removed',
        body: message
      });
      log('INFO', 'Email sent');
    } catch (e) {
      log('ERROR', 'Failed to send email: ' + e.message);
    }
  }

  if (CONFIG.SLACK_WEBHOOK_URL) {
    try {
      UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify({
          text: ':no_entry_sign: *PPC.io Negative List Manager*\n```' + message + '```'
        })
      });
      log('INFO', 'Slack sent');
    } catch (e) {
      log('ERROR', 'Failed to send Slack: ' + e.message);
    }
  }
}

/******************************************************************************
 * UTILITY FUNCTIONS
 ******************************************************************************/
function findListByName(lists, name) {
  for (var i = 0; i < lists.length; i++) {
    if (lists[i].name === name) {
      return lists[i];
    }
  }
  return null;
}

function checkTimeLimit(startTime) {
  var elapsed = (new Date() - startTime) / 1000 / 60;
  if (elapsed > CONFIG.TIME_LIMIT_MINUTES) {
    throw new Error('TIME_LIMIT: Processing stopped after ' + elapsed.toFixed(1) + ' minutes.');
  }
}

function log(level, message) {
  var levels = { 'DEBUG': 0, 'INFO': 1, 'WARN': 2, 'ERROR': 3 };
  if (levels[level] >= levels[CONFIG.LOG_LEVEL]) {
    Logger.log('[' + level + '] ' + message);
  }
}

function logSummary(results, startTime) {
  var duration = ((new Date() - startTime) / 1000).toFixed(1);
  log('INFO', '════════════════════════════════════════');
  log('INFO', 'NEGATIVE LIST MANAGER COMPLETE');
  log('INFO', 'Mode: ' + CONFIG.MODE);
  log('INFO', 'Duration: ' + duration + ' seconds');
  log('INFO', 'Lists Managed: ' + results.summary.listsManaged);
  log('INFO', 'Keywords Added: ' + results.summary.keywordsAdded);
  log('INFO', 'Keywords Removed: ' + results.summary.keywordsRemoved);
  log('INFO', 'Errors: ' + results.summary.errors);
  log('INFO', '════════════════════════════════════════');
}

function handleFatalError(error, startTime) {
  log('ERROR', '════════════════════════════════════════');
  log('ERROR', 'FATAL ERROR: ' + error.message);
  log('ERROR', 'Stack: ' + error.stack);
  log('ERROR', '════════════════════════════════════════');

  if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
    try {
      MailApp.sendEmail({
        to: CONFIG.EMAIL_RECIPIENTS.join(','),
        subject: '[PPC.io ERROR] NKL Manager Failed - ' + AdsApp.currentAccount().getName(),
        body: 'Script failed after ' + ((new Date() - startTime) / 1000).toFixed(1) +
              ' seconds.\n\nError: ' + error.message + '\n\nStack:\n' + error.stack
      });
    } catch (e) {
      log('ERROR', 'Could not send error email: ' + e.message);
    }
  }
}

How to read the output

The script writes back to your master sheet in three places. The NKL Manager Output tab is the run summary: lists in account, lists in sheet, keywords added, keywords removed, errors. The Sync Actions tab is the per-list diff with color-coded status (green APPLIED, blue DRY_RUN, yellow AUDIT_ONLY, gray SKIPPED). On a first AUDIT run, scan the Sync Actions tab for any list showing surprising removals, that is the sheet drift you want to fix before you ever flip DRY_RUN off. The Campaign Coverage tab tells you which campaigns have which lists attached, so you can spot a new campaign someone shipped without applying the brand-defense list.