Google Ads negative keywords may also block searches for related search terms. This script helps identify scenarios where a negative keyword typo may interfere with a positive keyword.

Identify positive keywords that conflict with negative typos

function main() {
  // Set up the output sheet
  var outputSheet = setUpOutputSheet();
  
  // Replace with the URL of your input Google Sheet
  const SPREADSHEET_URL = ''; // Add the URL of the same Google Sheet as in the first script 
  const sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();

  // Fetch all keywords from active campaigns and store in a data structure
  const keywordsMap = fetchAllKeywords();

  // Read the spreadsheet to get the list of typos
  const typoRows = sheet.getDataRange().getValues();
  const header = typoRows[0];
  const typoIndex = header.indexOf('typos hardcoded');
  const levelIndex = header.indexOf('Level'); // Add a new column to indicate the level (Account, Campaign, Ad Group)
  const campaignIndex = header.indexOf('Campaign Name'); // Campaign name column
  const adGroupIndex = header.indexOf('Ad Group Name'); // Ad group name column

  if (typoIndex == -1 || levelIndex == -1 || campaignIndex == -1 || adGroupIndex == -1) {
    Logger.log("Required columns not found in the spreadsheet");
    return;
  }

  // Loop through each row in the spreadsheet
  for (var i = 1; i < typoRows.length; i++) {
    const row = typoRows[i];
    const typos = row[typoIndex].split(',');
    const level = row[levelIndex];
    const campaignName = row[campaignIndex];
    const adGroupName = row[adGroupIndex];

    // Check each typo against all keywords
    typos.forEach(function(typo) {
      typo = typo.trim();
      if (typo) {
        checkKeywordsForTypo(keywordsMap, typo, row, outputSheet, level, campaignName, adGroupName);
      }
    });
  }
}

function fetchAllKeywords() {
  const keywordsMap = {};

  const campaignIterator = AdsApp.campaigns()
    .withCondition("Status = ENABLED")
    .get();

  while (campaignIterator.hasNext()) {
    const campaign = campaignIterator.next();
    const campaignName = campaign.getName();
    const adGroupIterator = campaign.adGroups()
      .withCondition("Status = ENABLED")
      .get();

    while (adGroupIterator.hasNext()) {
      const adGroup = adGroupIterator.next();
      const adGroupName = adGroup.getName();
      const keywordIterator = adGroup.keywords()
        .withCondition("Status = ENABLED")
        .get();

      while (keywordIterator.hasNext()) {
        const keyword = keywordIterator.next().getText();
        keywordsMap[keyword] = {
          campaign: campaignName,
          adGroup: adGroupName
        };
      }
    }
  }

  return keywordsMap;
}

function checkKeywordsForTypo(keywordsMap, typo, row, outputSheet, level, campaignName, adGroupName) {
  for (const keyword in keywordsMap) {
    const data = keywordsMap[keyword];

    if (level === 'Account' || 
        (level === 'Campaign' && data.campaign === campaignName) || 
        (level === 'Ad Group' && data.campaign === campaignName && data.adGroup === adGroupName)) {
      if (keyword.includes(typo)) {
        Logger.log(`Typo found: "${typo}" in keyword "${keyword}" - Campaign: "${data.campaign}", Ad Group: "${data.adGroup}"`);
        logResult(typo, keyword, data.campaign, data.adGroup, row, outputSheet);
      }
    }
  }
}

function setUpOutputSheet() {
  // Replace with the URL of your output Google Sheet
  const SPREADSHEET_URL = ''; // Add the URL of the Google Sheet where you'd like to get a summary of all the negative keyword conflicts
  const outputSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
  outputSheet.clear();

  // Append the result to the spreadsheet
  outputSheet.appendRow(['Possible Typo That Conflicts With Keyword', 'Keyword', 'Campaign', 'Ad Group', 'Typo Details']);
  
  return outputSheet;
}

function logResult(typo, keyword, campaign, adGroup, row, outputSheet) {
  // Append the result to the spreadsheet
  outputSheet.appendRow([typo, keyword, campaign, adGroup, row.join(', ')]);
}

List All Negative Keywords for Google Ads

function main() {
  // Replace with the URL of your Google Sheet
  const SPREADSHEET_URL = ''; // Add the URL of the Google Sheet where all negative keywords will be listed. You will need this same URL in the next script

  const sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
  sheet.clear();  // Clear the sheet before adding new data

  // Set up headers in the Google Sheet
  sheet.appendRow(['Negative Keyword', 'Level', 'Campaign Name', 'Ad Group Name']);

  // Fetch campaign-level negative keywords
  fetchCampaignLevelNegativeKeywords(sheet);

  // Fetch ad group-level negative keywords
  fetchAdGroupLevelNegativeKeywords(sheet);
  
  // Since fetching account-level negative keywords is not directly supported,
  // we need to manually add them if you have a known list or fetch through another method.
  // You can add your account-level negative keywords manually here.
  fetchManualAccountLevelNegativeKeywords(sheet);
}

function fetchManualAccountLevelNegativeKeywords(sheet) {
  // Manually add your account-level negative keywords here
  // Example: Add a list of known account-level negative keywords
  const accountLevelNegatives = [
    'example negative keyword 1',
    'example negative keyword 2'
  ];
  
  for (var i = 0; i < accountLevelNegatives.length; i++) {
    sheet.appendRow([accountLevelNegatives[i], 'Account', '', '']);
  }
}

function fetchCampaignLevelNegativeKeywords(sheet) {
  const campaignIterator = AdsApp.campaigns().get();

  while (campaignIterator.hasNext()) {
    const campaign = campaignIterator.next();
    const negativeKeywordIterator = campaign.negativeKeywords().get();

    while (negativeKeywordIterator.hasNext()) {
      const negativeKeyword = negativeKeywordIterator.next();
      sheet.appendRow([negativeKeyword.getText(), 'Campaign', campaign.getName(), '']);
    }
  }
}

function fetchAdGroupLevelNegativeKeywords(sheet) {
  const adGroupIterator = AdsApp.adGroups().get();

  while (adGroupIterator.hasNext()) {
    const adGroup = adGroupIterator.next();
    const negativeKeywordIterator = adGroup.negativeKeywords().get();

    while (negativeKeywordIterator.hasNext()) {
      const negativeKeyword = negativeKeywordIterator.next();
      sheet.appendRow([negativeKeyword.getText(), 'Ad Group', adGroup.getCampaign().getName(), adGroup.getName()]);
    }
  }
}