GAS Library: Automated Blog Post NG Word Checking with AI (Gemini/OpenAI)

Purpose of the Library

The purpose of this library is to automate the quality management and compliance checking of published content at high speed and with high accuracy using AI, a task that would be incredibly time-consuming to do manually.

The system consists of two main functions:

1. AI-Powered NG Word List ExpansionBy simply registering a small number of "base NG words" (e.g., "Competitor A") in a sheet, the AI automatically generates related and similar words to create a comprehensive "Final NG Word List."
2. AI-Powered Blog Post ScanningWhen you register a list of URLs for articles you want to check, the script automatically crawls each URL. It extracts the article's body text, passes the "Final NG Word List" created above to the AI, and has the AI determine if the article contains any NG words, recording the result back in the sheet.

Primary Use Cases

This library is extremely effective in the following scenarios:

• Compliance & Legal ChecksRegister expressions that could violate advertising laws (like the Act against Unjustifiable Premiums and Misleading Representations or pharmaceutical regulations), such as definitive claims ("absolutely," "will definitely heal"), and have the AI scan articles to detect risky content before publication.
• Brand Damage & Crisis PreventionCreate a list of discriminatory terms, offensive language, or overly negative words to prevent the publication of content that could damage the corporate brand image.
• Competitor & Confidential Info ManagementRegister "competitor names" or "specific product names" as NG words to check that your own media content does not inadvertently contain confidential information or references to competitors.

Setup Procedure

The setup procedure is as follows.

Step 1: Add the GAS Library

1. Open your Google Sheet and go to Extensions > Apps Script to open the GAS editor.
2. Click the + icon next to "Libraries" in the left-hand menu.
3. In the "Script ID" field, paste the following ID:1KQUhXpCDcO86RYiOrBl6COCIvHnL7A9IoIYsMisY-OKm7aM7qbghjOkm
4. Click "Look up" and select the latest version.
5. Change the "Identifier" to AINGWordChecker (recommended) and click "Add."

Step 2: Paste the Sample Code

Copy and paste the "Code.gs Sample Code" from the end of this article into your Code.gs file in the GAS editor. This code will create the custom menu in your spreadsheet.

Step 3: Prepare the "settings" Sheet

This library reads its configuration from a sheet named "settings" via the getLibraryConfig function (in the sample code).

1. Go back to your Google Sheet and create a sheet named exactly "settings".
2. Cell B1: Enter your Gemini API key.
3. Cell B2: Enter your OpenAI API key. (Only one of the two is required for it to work).
4. Cell B3: Enter the AI model name you wish to use (e.g., gemini-1.5-flash or gpt-4o).
5. Cell B4: Enter the name for your URL check sheet (e.g., URLcheck).
6. Cell B5: Enter the name for your base NG word settings sheet (e.g., NGwordsetting).
7. Cell B6: Enter the name for your final NG word list output sheet (e.g., Final_NG_Word_List).

Step 4: Run "Update Final NG Word List"

1. Create a sheet with the name you specified in cell B5 (e.g., NGwordsetting).
2. In Column A of this sheet, enter the "base NG words" you want the AI to expand upon.
3. Reload the spreadsheet page. A new custom menu named "AI Checker" will appear.
4. Select AI Checker > Update Final NG Word List.
5. The AI will generate related words (in column C) and then create a complete, final list in the sheet you specified in cell B6 (e.g., Final_NG_Word_List).

Step 5: Run "Run NG Word Check"

1. Create a sheet with the name you specified in cell B4 (e.g., URLcheck).
2. In Column A of this sheet, enter the article URLs you want the AI to check.
3. From the custom menu, select AI Checker > Run NG Word Check.
4. The script will crawl each URL, and the AI will check its content. The results (e.g., "Detected: free") will be written to Column B, and the status (done) will be written to Column C.

Code.gs Sample Code

(This is the code to paste into your Code.gs file in Step 2)

/**
* Adds a custom menu when the spreadsheet is opened.
*/
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AI Checker')
    .addItem('Run NG Word Check', 'runNGWordCheck')
    .addItem('Update Final NG Word List', 'runRelatedWordGeneration')
    .addToUi();
}

/**
* Helper function to read settings from the 'Settings' sheet.
*/
function getLibraryConfig() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('settings');
  if (!sheet) {
    throw new Error('Cannot find the Settings sheet.');
  }
  // Read values from B1 to B6
  const values = sheet.getRange('B1:B6').getValues();
  return {
    geminiApiKey: values[0][0],
    openaiApiKey: values[1][0],
    model: values[2][0],
    urlSheetName: values[3][0],
    ngSheetName: values[4][0],
    finalNgWordSheetName: values[5][0]
  };
}

/**
* Runs the NG word check function from the library.
*/
function runNGWordCheck() {
  try {
    const config = getLibraryConfig();
    
    // Calls the library function using the Identifier (AINGWordChecker)
    AINGWordChecker.checkArticlesForNGWords(config);
    
    SpreadsheetApp.getUi().alert('NG Word Check complete.');
    
  } catch (e) {
    Logger.log(`Error: ${e.message}`);
    SpreadsheetApp.getUi().alert(`Error: ${e.message}`);
  }
}

/**
* Runs the related word generation and list update function from the library.
*/
function runRelatedWordGeneration() {
  try {
    const config = getLibraryConfig();
    
    // Calls the library function using the Identifier (AINGWordChecker)
    AINGWordChecker.generateRelatedNGWords(config);
    
    SpreadsheetApp.getUi().alert('Final NG Word List update complete.');
    
  } catch (e) {
    Logger.log(`Error: ${e.message}`);
    SpreadsheetApp.getUi().alert(`Error: ${e.message}`);
  }
}