Remove keywords that don't contain and create negative keyword list

Here’s a Google Sheets script that filters the keywords in column A and deletes rows if they do not contain any of the specified keywords or phrases. Then it create a new sheet called, "Negative keyword lists" and adds all the removed keywords to the list. Follow these steps to use the script:

Steps:

1. Open your Google Sheets document.

2. Click on Extensions > Apps Script.

3. Paste the following code into the script editor:

function createNegativeKeywordList() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1).getValues(); // Get values starting from A2
  const negativeKeywords = []; // Array to store negative keywords
  
  // Loop through each row to find rows that do not meet the condition
  data.forEach((row, index) => {
    const cellValue = row[0].toLowerCase(); // Make the string lowercase for case-insensitive matching
    if (!(cellValue.includes("INSERT-KEYWORD-OR-PHRASE") && cellValue.includes("INSERT-KEYWORD-OR-PHRASE"))) {
      negativeKeywords.push(cellValue); // Add the non-matching term to the array
    }
  });

  // Create a new sheet to store negative keywords
  const negativeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Negative Keywords") || 
                        SpreadsheetApp.getActiveSpreadsheet().insertSheet("Negative Keywords");
  negativeSheet.clear(); // Clear existing data if the sheet already exists
  negativeSheet.getRange(1, 1).setValue("Negative Keywords"); // Set header

  // Write the negative keywords to the new sheet
  negativeSheet.getRange(2, 1, negativeKeywords.length).setValues(negativeKeywords.map(keyword => [keyword]));
}

1. Run authorize the script .

2. Close the Apps Script editor and return to your sheet.

3. Run the script by clicking Extensions > Apps Script > Run function