Automating Negative Keyword Analysis in Google Ads with ChatGPT and AI

Why You Need This

Manually digging through search terms in Google Ads to find negative keywords can be tedious, especially when thousands of search terms come in daily.  Yet, we all know negative keywording is one of the most essential things your Google Ads account needs. This guide will show you how to automate the process to get the insights you need without boring yourself to death.

By the end of this, your system will:

✅ Pull search term data from Google Ads into Google Sheets automatically.

✅ Send a Slack message with the top recommendations and a link to download the full analysis.
✅ Use ChatGPT to analyze and suggest which terms should be negative keywords.

Even if you’ve never touched Google Ads Scripts or Google Sheets Apps Script before, I've got you. Let’s go step by step.

Step 1: Set Up the Google Ads Script (The Data Collector)

This script pulls search term data from your Google Ads account and dumps it into a Google Sheet.

1. Open Google Ads

• Log in to your Google Ads account.
• Click on Tools (the little wrench icon 🔧) at the top.
• Under “Bulk Actions,” click Scripts.

2. Create a New Script

• Click the + button to create a new script.
• Name it something like “Search Term Exporter”
• Delete any existing code in the editor.

• Copy and paste the script below into the editor.

function main() {
  var reportQuery = `
    SELECT Query, CampaignName, AdGroupName, Clicks, Impressions, Cost, Conversions
    FROM SEARCH_QUERY_PERFORMANCE_REPORT
    WHERE Impressions > 1
    DURING LAST_30_DAYS
  `;
  
  var report = AdsApp.report(reportQuery);
  var spreadsheet = SpreadsheetApp.openById("YOUR_SHEET_ID_HERE");
  var sheet = spreadsheet.getActiveSheet();
  sheet.clear();
  
  var rows = report.rows();
  var headers = ["Search Term", "Campaign", "Ad Group", "Clicks", "Impressions", "Cost", "Conversions"];
  sheet.appendRow(headers);
  
  while (rows.hasNext()) {
    var row = rows.next();
    sheet.appendRow([
      row["Query"],
      row["CampaignName"],
      row["AdGroupName"],
      row["Clicks"],
      row["Impressions"],
      row["Cost"],
      row["Conversions"]
    ]);
  }
  
  Logger.log("Search Term Report exported successfully to Google Drive");
}

3. Connect to Google Sheets

• Open Google Sheets in a new tab.
• Create a blank sheet and copy the spreadsheet ID (the string of numbers and letters after https://docs.google.com/spreadsheets/d/)  See the screenshot below.

• In the script, replace YOUR_SHEET_ID_HERE with the one you just copied.

4. Authorize & Test

• Click Authorize to give the script permission to access your Ads and Sheets.

• Click Preview to check that it runs without errors (bottom right-hand corner).
  • Depending on the amount of search term data in your account, this script could take anywhere from 2 minutes to complete to 30 minutes.
  • You should get a success message in the logs that looks like this:

• If you receive the message above, you can hit Save.
  • Note: if you did the preview the code step above, Google will automatically save the script. That is why you will see “Save” grayed out.  That means the script has been saved and you can hit Close.
• To automate this, you will want to Schedule it the script to run weekly.
  • On the Scripts page, click the little pencil icon under Frequency.  

• • On the dropdown, select Weekly and select the desired day and time you’d like the script to run.

Tips:

1. The script pulls in the last 30 days data. If you want it to pull in search term data for the last 7 days. just change DURING LAST_30_DAYS to be DURING LAST_7_DAYS. You can change this to be 14 days, 90 days, or whatever you would like.
2. The script filters for only search terms with more than 1 impression. You can update this line  WHERE Impressions > 1 to be whatever metric you would like to filter by. For example, WHERE Clicks > 0 would give you search terms with 1 or more clicks.

Boom! Your search term data is now flowing into Google Sheets like magic.

Step 2: Create a Slack App & Webhook URL

Let’s get your team notified about what matters.

1. Create a Slack App

• Open Slack and go to Slack API (api.slack.com).
• Click on Create an App > From Scratch.
• Name your app something like “Google Ads Reporting”
• Choose the Slack workspace and channel where you want it to send messages.
• Click Create App.

2. Enable Incoming Webhooks

• In your Slack App settings, go to Features > Incoming Webhooks.
• Turn on Activate Incoming Webhooks.
• Scroll down and click Add New Webhook to Workspace.
• Choose the Slack channel where the messages should be sent and click Allow.
• Copy the generated Webhook URL.

3. Store in a Safe Place for the Next Step

• Paste your SLACK_WEBHOOK_URL in a safe place, as we will need it in this guide's next step.

Step 3: Set Up the Google Sheets Script (The Brain)

Now, we need to analyze those search terms. This script will send them to ChatGPT for analysis AND send a message in your Slack with the downloadable CSV.

Resources you will need:

• OpenAI API key. Don’t have an OpenAI API key? Here’s a guide on how to get one.
• Your API key should look something like this:  sk-proj-kg4id_bKyGDjPpoHoZ6VgCugOqObgMNkLjyM7TibHbx4e_PdLzgkrsFokQ-nHyBfkLUz2Su8lpT3BlbkFJJXvRnYYRVm5SSYi9YqEZFc-2WnJtklXiOWKmvmOuykYAN2BImT4fDdQQatqRxZvPYxqv4sEwDsA

1. Open Google Sheets

• Go to the Google Sheet you linked in Step 1.
• Click Extensions > Apps Script.
  • If you’re logged into multiple Google accounts, you may have an issue accessing the script page. That’s ok you can get there by navigating to https://script.google.com/.

2. Paste the Analysis Script

• Delete any existing code and replace it with the Apps Script code below:

function exportAndAnalyzeSheet() {
  // 1. Specify the spreadsheet by ID
  var spreadsheetId = 'YOUR_SPREADSHEET_ID_HERE'; 
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  
  // 2. Get the first sheet (or specify by name)
  var sheet = spreadsheet.getSheets()[0]; 
  
  // CUSTOMIZABLE PARAMETERS
  var industry = 'YOUR_INDUSTRY_HERE';
  var column1 = 'COLUMN_1_HEADER';
  var column2 = 'COLUMN_2_HEADER';
  var column3 = 'COLUMN_3_HEADER';
  var column4 = 'COLUMN_4_HEADER';
  
  // Slack webhook URL
  var slackWebhookUrl = 'YOUR_SLACK_WEBHOOK_URL';
  
  // 3. Get data from the sheet
  var dataRange = sheet.getDataRange();
  var allData = dataRange.getValues();
  
  // Extract header row
  var headerRow = allData[0];
  var dataRows = allData.slice(1);
  
  // Set batch size - adjust based on your data size
  var BATCH_SIZE = 100;
  
  // Create a new header row with the additional columns
  var newHeaderRow = headerRow.slice(); // Clone the original header
  newHeaderRow.push(column1, column2, column3, column4);
  
  // Variable to store final processed data with the new headers
  var processedData = [newHeaderRow];
  
  // Process in batches
  for (var i = 0; i < dataRows.length; i += BATCH_SIZE) {
    // Get a batch of rows
    var batchRows = dataRows.slice(i, Math.min(i + BATCH_SIZE, dataRows.length));
    
    // Convert batch to CSV
    var batchCsv = [headerRow].concat(batchRows).map(function(row) {
      return row.map(function(cell) {
        // Properly handle cell values, ensuring numbers are preserved
        var cellValue = cell === null || cell === undefined ? "" : cell.toString();
        return '"' + cellValue.replace(/"/g, '""') + '"';
      }).join(',');
    }).join('\n');
    
    // Send batch to ChatGPT for analysis
    Logger.log("Processing batch " + (Math.floor(i/BATCH_SIZE) + 1) + " of " + Math.ceil(dataRows.length/BATCH_SIZE));
    var batchResult = sendToChatGPT(batchCsv, industry, column1, column2, column3, column4);
    
    if (!batchResult.success) {
      Logger.log("Error processing batch: " + batchResult.error);
      continue; // Skip this batch if there's an error
    }
    
    // Parse the returned CSV
    var csvRows = batchResult.content.split('\n');
    
    // Find the first non-header row (skip CSV header)
    var headerIndex = -1;
    for (var j = 0; j < csvRows.length; j++) {
      if (csvRows[j].indexOf(column1) >= 0 && csvRows[j].indexOf(column2) >= 0) {
        headerIndex = j;
        break;
      }
    }
    
    // Skip header row(s) from the response
    var startIndex = headerIndex >= 0 ? headerIndex + 1 : 1;
    
    // Process each data row
    for (var j = startIndex; j < csvRows.length; j++) {
      // Skip empty rows
      if (csvRows[j].trim() === "") continue;
      
      // Parse CSV row
      var rowValues = parseCSVRow(csvRows[j]);
      
      // Add to processed data
      if (rowValues.length >= headerRow.length) {
        processedData.push(rowValues);
      }
    }
  }
  
  // Convert the completed data back to CSV
  var finalCsv = processedData.map(function(row) {
    return row.map(function(cell) {
      // Ensure proper handling of cell values
      var cellValue = cell === null || cell === undefined ? "" : cell;
      // Handle numbers correctly - don't quote them
      if (typeof cellValue === 'number') {
        return cellValue;
      } else {
        return '"' + cellValue.toString().replace(/"/g, '""') + '"';
      }
    }).join(',');
  }).join('\n');
  
  try {
    // Save the analyzed data as a CSV file in Google Drive
    var fileName = spreadsheet.getName() + "_Analyzed.csv";
    var file = DriveApp.createFile(fileName, finalCsv, MimeType.CSV);
    var fileUrl = file.getUrl();
    
    // Send the file to Slack
    var slackResult = sendToSlack(slackWebhookUrl, fileName, fileUrl, file.getId());
    
    Logger.log("Analysis complete! CSV file created and sent to Slack.");
    return {
      success: true,
      message: "Analysis complete! CSV file created and sent to Slack.",
      fileUrl: fileUrl,
      slackResult: slackResult
    };
  } catch (e) {
    Logger.log("Error saving or sending file: " + e);
    return {
      success: false,
      error: "Error saving or sending file: " + e.toString()
    };
  }
}

// Helper function to parse a CSV row properly
function parseCSVRow(csvRow) {
  var row = [];
  var inQuotes = false;
  var currentValue = "";
  
  for (var i = 0; i < csvRow.length; i++) {
    var char = csvRow[i];
    
    if (char === '"') {
      // Handle double quotes
      if (i + 1 < csvRow.length && csvRow[i + 1] === '"') {
        currentValue += '"';
        i++; // Skip the next quote
      } else {
        inQuotes = !inQuotes;
      }
    } else if (char === ',' && !inQuotes) {
      // Try to convert to number if appropriate
      var numValue = Number(currentValue);
      row.push(isNaN(numValue) ? currentValue : numValue);
      currentValue = "";
    } else {
      currentValue += char;
    }
  }
  
  // Don't forget the last value - convert to number if appropriate
  var numValue = Number(currentValue);
  row.push(isNaN(numValue) ? currentValue : numValue);
  
  return row;
}

function sendToChatGPT(csvData, industry, column1, column2, column3, column4) {
  // Your OpenAI API key
  var OPENAI_API_KEY = "your-openai-api-key";
  
  // Create a more specific prompt that emphasizes the format we need
  var prompt = `Act as a Google Ads expert and review the search terms for this ads account in the ${industry} industry.

I need you to add exactly 4 new columns to the end of the CSV data with these headers:
1. ${column1}
2. ${column2}
3. ${column3}
4. ${column4}

For EACH search term in the original data, evaluate it against these 4 criteria, and add either "TRUE" or "FALSE" in each column.

Do NOT provide explanations or analysis text - I ONLY want the complete CSV data returned with the 4 new columns added, containing "TRUE" or "FALSE" values for each row.

The output should be in CSV format, with the EXACT same data as the original, plus these 4 new columns at the end.`;
  
  var payload = {
    "model": "gpt-4-turbo",
    "messages": [
      {"role": "system", "content": "You are a Google Ads expert that only outputs data in CSV format as requested, with no explanations or additional text."},
      {"role": "user", "content": prompt + "\n\nHere's the CSV data:\n\n" + csvData}
    ]
  };
  
  // Set up the API request
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Bearer ' + OPENAI_API_KEY
    },
    'payload': JSON.stringify(payload),
    'muteHttpExceptions': true
  };
  
  try {
    // Make the API request
    var response = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', options);
    var responseCode = response.getResponseCode();
    var responseText = response.getContentText();
    
    if (responseCode !== 200) {
      return {
        success: false,
        error: "API Error: " + responseCode + " - " + responseText
      };
    }
    
    var responseData = JSON.parse(responseText);
    
    // Extract the analysis from the response
    if (responseData && responseData.choices && responseData.choices.length > 0) {
      return {
        success: true,
        content: responseData.choices[0].message.content
      };
    } else {
      return {
        success: false,
        error: "Unexpected API response format: " + JSON.stringify(responseData)
      };
    }
  } catch (error) {
    Logger.log("Error calling ChatGPT API: " + error);
    return {
      success: false,
      error: "Exception: " + error.toString()
    };
  }
}

function sendToSlack(webhookUrl, fileName, fileUrl, fileId) {
  try {
    // Create a direct download link
    var downloadUrl = "https://drive.google.com/uc?export=download&id=" + fileId;
    
    // Prepare the message for Slack
    var message = {
      "blocks": [
        {
          "type": "header",
          "text": {
            "type": "plain_text",
            "text": "Google Ads Search Term Analysis"
          }
        },
        {
          "type": "section",
          "text": {
            "type": "mrkdwn",
            "text": `The analysis of search terms is complete! A CSV file has been created: *${fileName}*`
          }
        },
        {
          "type": "section",
          "text": {
            "type": "mrkdwn",
            "text": `<${fileUrl}|View in Google Drive> or <${downloadUrl}|Download CSV directly>`
          }
        },
        {
          "type": "context",
          "elements": [
            {
              "type": "mrkdwn",
              "text": "Analyzed on: " + new Date().toLocaleString()
            }
          ]
        }
      ]
    };
    
    // Send the message to Slack
    var options = {
      'method': 'post',
      'contentType': 'application/json',
      'payload': JSON.stringify(message),
      'muteHttpExceptions': true
    };
    
    var response = UrlFetchApp.fetch(webhookUrl, options);
    return {
      success: response.getResponseCode() === 200,
      response: response.getContentText()
    };
  } catch (error) {
    Logger.log("Error sending to Slack: " + error);
    return {
      success: false,
      error: error.toString()
    };
  }
}

3. Replace the Placeholder Text

• Replace 'YOUR_SLACK_WEBHOOK_URL' with the webhook URL you copied

• Replace 'YOUR_SPREADSHEET_ID_HERE' with your spreadsheet ID

• Replace 'your-openai-api-key' with your OpenAI API key

4. Customize the Script to Your Industry

• Towards the top of the script your will see a code block that looks like this:

// CUSTOMIZABLE PARAMETERS - Change these variables as needed
 var industry = 'YOUR_INDUSTRY_HERE'; // e.g., 'Google Ads Agency', 'Healthcare', 'Real Estate', etc.
 var column1 = 'COLUMN_1_HEADER'; // e.g., 'High cost low return','Low intent', etc.
 var column2 = 'COLUMN_2_HEADER'; // e.g., 'Competitor','Informational','Free/cheap',etc.
 var column3 = 'COLUMN_3_HEADER'; // e.g., 'Ad group mismatch', 'Location mismatch', etc.
 var column4 = 'COLUMN_4_HEADER'; // e.g., 'Wrong Service','Industry Tool', etc.

• This is the code section you want to customize to your specific industry.
• Replace 'YOUR_INDUSTRY_HERE' with your industry.
  • Examples: Google Ads Agency, HVAC Repair Company, Women’s Classic Rock Shirts, Florida Real Estate Agent.
  • The industry you put in here will be the industry that ChatGPT references to analyze your search terms.
• Next, you will be replacing COLUMN_1_HEADER, COLUMN_2_HEADER, COLUMN_3_HEADER, COLUMN_4_HEADER. With your own parameters.
  • ChatGPT uses these columns as parameters to analyze the search terms. These can be anything you currently use to determine if a search term should become a negative keyword. Below are some examples:
    • A Miami-based HVAC company might use the following Column Headers
      • ‘Wrong Service’
      • ‘Outside our service area of Miami Dade County’
      • ‘High cost but low converting’
      • ‘Competitor Brand Names’
  • You can test different columns/parameters to get better results.
    • If you can’t think of columns/parameters to use I’ve added some to the end of this blog. Hint: You can also ask ChatGPT
    • This script requires 4 columns to run. So you will need to come up with 4 parameters.
  • Note: Try to avoid using commas in your column or industry fields.
• Once you have replaced your columns, click Save project to Drive. Don’t forget to name the project “Negative Keyword -> ChatGPT” or whatever you like. Naming the project does not affect the script running. So you can name the project “I like Cats” and the script will still run.

5. Authorize & Test

• Click the Run ▶️ button to test the script.
  • You may need to authorize the script and log into your google account for the script to run.
  • Depending on the amount of search terms you have, this script could take up to 30 mins to complete.
• If everything works, it will send a link to a CSV in the slack channel you selected with:
  • The search term data from Google ads.
  • 4 new columns with the headers/parameters you chose.
    • Each Cell in the column will be labeled as TRUE or FALSE.
      • TRUE means the Search Term fits your parameter (column header) based on ChatGPT’s understanding.
      • FALSE means the Search Term does not fit your parameter.
    • If ChatGPT mislabelled your search term, try new column headers or parameters.  ChatGPT is imperfect, so you’ll need to play around with it a bit to get it to think like you.
  • Now you can filter each column for TRUE, copy the ones you want to negative and paste them into Google ads.
• If you see a message like the one below in your Slack channel, you're golden! 🌟

Step 4: Schedule Your Automation

You’re almost there! Just make sure everything runs weekly. Let’s set a Trigger so the script runs automatically weekly AFTER your Google Ads script from Step 1.

1. Create a Trigger

• With your Apps Script still open click on Triggers (left-hand side of the screen)

• Click Add Trigger at the bottom right-hand corner of the screen.
• Customize the trigger parameters to your preference.  I prefer to schedule mine weekly for an hour after the Google Ads script from Step 1 is scheduled to run. Below is an example:

Click Save.

Limitations

The biggest limitation to this set up, is to volume of data it can take.  Google Ads and Google AppScripts have time execution limits.  If you find yourself running into these limits, you may want to further filter your search term data.  ChatGPT can help you with that.

Occasionally, the CSV export will be poorly formatted. You can manually format it yourself, or rerun the script.  I’ve found that rerunning the script helps.

ChatGPT isn’t perfect, so if your parameters are broad or unclear, it may categorize search terms incorrectly.  To fix this, make your parameters more specific or enhance the ChatGPT prompt found in the code here:

 // Create a more specific prompt that emphasizes the format we need
  var prompt = `Act as a Google Ads expert and review the search terms for this ads account in the ${industry} industry.

I need you to add exactly 4 new columns to the end of the CSV data with these headers:
1. ${column1}
2. ${column2}
3. ${column3}
4. ${column4}

For EACH search term in the original data, evaluate it against these 4 criteria, and add either "TRUE" or "FALSE" in each column.

Do NOT provide explanations or analysis text - I ONLY want the complete CSV data returned with the 4 new columns added, containing "TRUE" or "FALSE" values for each row.

The output should be in CSV format, with the EXACT same data as the original, plus these 4 new columns at the end.`;

Optional: Notification via Email

If you don’t use slack, you can have the script send you an email when it completes instead. Below is the script needed to send a notification via email. Set up the script by following the above guide except you’ll want to swap 'YOUR_EMAIL_HERE' with your email address.

function exportAndAnalyzeSheet() {
  // 1. Specify the spreadsheet by ID
  var spreadsheetId = 'YOUR_SPREADSHEET_ID_HERE'; 
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  
  // 2. Get the first sheet (or specify by name)
  var sheet = spreadsheet.getSheets()[0]; 
  
  // CUSTOMIZABLE PARAMETERS
  var industry = 'YOUR_INDUSTRY_HERE';
  var column1 = 'COLUMN_1_HEADER';
  var column2 = 'COLUMN_2_HEADER';
  var column3 = 'COLUMN_3_HEADER';
  var column4 = 'COLUMN_4_HEADER';
  
  // Email parameters
  var emailTo = 'YOUR_EMAIL_HERE';
  var emailSubject = 'Google Ads Search Term Analysis Complete';
  
  // 3. Get data from the sheet
  var dataRange = sheet.getDataRange();
  var allData = dataRange.getValues();
  
  // Extract header row
  var headerRow = allData[0];
  var dataRows = allData.slice(1);
  
  // Set batch size - adjust based on your data size
  var BATCH_SIZE = 100;
  
  // Create a new header row with the additional columns
  var newHeaderRow = headerRow.slice(); // Clone the original header
  newHeaderRow.push(column1, column2, column3, column4);
  
  // Variable to store final processed data with the new headers
  var processedData = [newHeaderRow];
  
  // Process in batches
  for (var i = 0; i < dataRows.length; i += BATCH_SIZE) {
    // Get a batch of rows
    var batchRows = dataRows.slice(i, Math.min(i + BATCH_SIZE, dataRows.length));
    
    // Convert batch to CSV
    var batchCsv = [headerRow].concat(batchRows).map(function(row) {
      return row.map(function(cell) {
        // Properly handle cell values, ensuring numbers are preserved
        var cellValue = cell === null || cell === undefined ? "" : cell.toString();
        return '"' + cellValue.replace(/"/g, '""') + '"';
      }).join(',');
    }).join('\n');
    
    // Send batch to ChatGPT for analysis
    Logger.log("Processing batch " + (Math.floor(i/BATCH_SIZE) + 1) + " of " + Math.ceil(dataRows.length/BATCH_SIZE));
    var batchResult = sendToChatGPT(batchCsv, industry, column1, column2, column3, column4);
    
    if (!batchResult.success) {
      Logger.log("Error processing batch: " + batchResult.error);
      continue; // Skip this batch if there's an error
    }
    
    // Parse the returned CSV
    var csvRows = batchResult.content.split('\n');
    
    // Find the first non-header row (skip CSV header)
    var headerIndex = -1;
    for (var j = 0; j < csvRows.length; j++) {
      if (csvRows[j].indexOf(column1) >= 0 && csvRows[j].indexOf(column2) >= 0) {
        headerIndex = j;
        break;
      }
    }
    
    // Skip header row(s) from the response
    var startIndex = headerIndex >= 0 ? headerIndex + 1 : 1;
    
    // Process each data row
    for (var j = startIndex; j < csvRows.length; j++) {
      // Skip empty rows
      if (csvRows[j].trim() === "") continue;
      
      // Parse CSV row
      var rowValues = parseCSVRow(csvRows[j]);
      
      // Add to processed data
      if (rowValues.length >= headerRow.length) {
        processedData.push(rowValues);
      }
    }
  }
  
  // Convert the completed data back to CSV
  var finalCsv = processedData.map(function(row) {
    return row.map(function(cell) {
      // Ensure proper handling of cell values
      var cellValue = cell === null || cell === undefined ? "" : cell;
      // Handle numbers correctly - don't quote them
      if (typeof cellValue === 'number') {
        return cellValue;
      } else {
        return '"' + cellValue.toString().replace(/"/g, '""') + '"';
      }
    }).join(',');
  }).join('\n');
  
  try {
    // Save the analyzed data as a CSV file in Google Drive
    var fileName = spreadsheet.getName() + "_Analyzed.csv";
    var file = DriveApp.createFile(fileName, finalCsv, MimeType.CSV);
    var fileUrl = file.getUrl();
    var fileId = file.getId();
    
    // Send the file info via email
    var emailResult = sendByEmail(emailTo, emailSubject, fileName, fileUrl, fileId);
    
    Logger.log("Analysis complete! CSV file created and sent via email.");
    return {
      success: true,
      message: "Analysis complete! CSV file created and sent via email.",
      fileUrl: fileUrl,
      emailResult: emailResult
    };
  } catch (e) {
    Logger.log("Error saving or sending file: " + e);
    return {
      success: false,
      error: "Error saving or sending file: " + e.toString()
    };
  }
}

// Helper function to parse a CSV row properly
function parseCSVRow(csvRow) {
  var row = [];
  var inQuotes = false;
  var currentValue = "";
  
  for (var i = 0; i < csvRow.length; i++) {
    var char = csvRow[i];
    
    if (char === '"') {
      // Handle double quotes
      if (i + 1 < csvRow.length && csvRow[i + 1] === '"') {
        currentValue += '"';
        i++; // Skip the next quote
      } else {
        inQuotes = !inQuotes;
      }
    } else if (char === ',' && !inQuotes) {
      // Try to convert to number if appropriate
      var numValue = Number(currentValue);
      row.push(isNaN(numValue) ? currentValue : numValue);
      currentValue = "";
    } else {
      currentValue += char;
    }
  }
  
  // Don't forget the last value - convert to number if appropriate
  var numValue = Number(currentValue);
  row.push(isNaN(numValue) ? currentValue : numValue);
  
  return row;
}

function sendToChatGPT(csvData, industry, column1, column2, column3, column4) {
  // Your OpenAI API key
  var OPENAI_API_KEY = "your-openai-api-key";
  
  // Create a more specific prompt that emphasizes the format we need
  var prompt = `Act as a Google Ads expert and review the search terms for this ads account in the ${industry} industry.

I need you to add exactly 4 new columns to the end of the CSV data with these headers:
1. ${column1}
2. ${column2}
3. ${column3}
4. ${column4}

For EACH search term in the original data, evaluate it against these 4 criteria, and add either "TRUE" or "FALSE" in each column.

Do NOT provide explanations or analysis text - I ONLY want the complete CSV data returned with the 4 new columns added, containing "TRUE" or "FALSE" values for each row.

The output should be in CSV format, with the EXACT same data as the original, plus these 4 new columns at the end.`;
  
  var payload = {
    "model": "gpt-4-turbo",
    "messages": [
      {"role": "system", "content": "You are a Google Ads expert that only outputs data in CSV format as requested, with no explanations or additional text."},
      {"role": "user", "content": prompt + "\n\nHere's the CSV data:\n\n" + csvData}
    ]
  };
  
  // Set up the API request
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Bearer ' + OPENAI_API_KEY
    },
    'payload': JSON.stringify(payload),
    'muteHttpExceptions': true
  };
  
  try {
    // Make the API request
    var response = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', options);
    var responseCode = response.getResponseCode();
    var responseText = response.getContentText();
    
    if (responseCode !== 200) {
      return {
        success: false,
        error: "API Error: " + responseCode + " - " + responseText
      };
    }
    
    var responseData = JSON.parse(responseText);
    
    // Extract the analysis from the response
    if (responseData && responseData.choices && responseData.choices.length > 0) {
      return {
        success: true,
        content: responseData.choices[0].message.content
      };
    } else {
      return {
        success: false,
        error: "Unexpected API response format: " + JSON.stringify(responseData)
      };
    }
  } catch (error) {
    Logger.log("Error calling ChatGPT API: " + error);
    return {
      success: false,
      error: "Exception: " + error.toString()
    };
  }
}

function sendByEmail(emailTo, emailSubject, fileName, fileUrl, fileId) {
  try {
    // Create a direct download link
    var downloadUrl = "https://drive.google.com/uc?export=download&id=" + fileId;
    
    // Get the file as a blob to attach to the email
    var fileBlob = DriveApp.getFileById(fileId).getBlob();
    
    // Prepare the email body
    var emailBody = `
    <h2>Google Ads Search Term Analysis</h2>
    <p>The analysis of search terms is complete! A CSV file has been created: <strong>${fileName}</strong></p>
    <p>
      <a href="${fileUrl}">View in Google Drive</a> or 
      <a href="${downloadUrl}">Download CSV directly</a>
    </p>
    <p><em>Analyzed on: ${new Date().toLocaleString()}</em></p>
    `;
    
    // Send the email with the file attached
    GmailApp.sendEmail(
      emailTo,
      emailSubject,
      // Plain text alternative for email clients that don't support HTML
      "The analysis of search terms is complete! A CSV file has been created and attached to this email.",
      {
        htmlBody: emailBody,
        attachments: [fileBlob]
      }
    );
    
    return {
      success: true,
      message: "Email sent successfully to " + emailTo
    };
  } catch (error) {
    Logger.log("Error sending email: " + error);
    return {
      success: false,
      error: error.toString()
    };
  }
}

Final Thoughts

You’ve just set up a fully automated system that:

📤 Pulls search term data from Google Ads
🤖 Uses AI to analyze and categorize search terms
📩 Notifies your team with actionable insights

No more sifting through data manually. You’re now optimizing like a pro—without the extra effort. 🚀

If you would like help implementing this script please email “Script Help” to admin@maius.com.

Cheers to smarter Google Ads management! 🥂