imperialjeff/gist:61944a3215ea51839bc82aa192dc0376

## gistfile1.txt
/**
 * @OnlyCurrentDoc
 * @see https://www.googleapis.com/auth/spreadsheets
 * @see https://www.googleapis.com/auth/webmasters
 * @see https://www.googleapis.com/auth/script.external_request
 * @see https://www.googleapis.com/auth/drive
 * @see https://www.googleapis.com/auth/drive.file
 * @see https://www.googleapis.com/auth/script.container.ui
 * @see https://www.googleapis.com/auth/script.scriptapp
 */

// Installation and Initialization Functions
function onInstall(e) {
  createTriggers();
  initializeSheets();
}

function createTriggers() {
  // Delete existing triggers first
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));

  // Create new trigger for initialization
  ScriptApp.newTrigger('initialize')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onOpen()
    .create();
}

function initialize() {
  SpreadsheetApp.getUi()
    .createMenu('Keyword Management')
    .addItem('Configure', 'showSidebar')
    .addSeparator()
    .addItem('Import Search Console Data', 'exportSearchTerms')
    .addItem('Process Selected Topic Filter', 'processTopicFilter')
    .addItem('Process Selected Niche Filter', 'processNicheFilter')
    .addSeparator()
    .addItem('Setup Automation', 'setupAutomation')
    .addItem('Run Auto-Process Now', 'checkAndRunAutomatedUpdate')
    .addSeparator()
    .addItem('Perform Maintenance', 'performMaintenance')
    .addToUi();
}

function initializeSheets() {
  createSettingsSheet();
  createRegistrySheet();
}

// Core Setup Functions
function createSettingsSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let settingsSheet = ss.getSheetByName('Settings');

  if (!settingsSheet) {
    settingsSheet = ss.insertSheet('Settings');
    const defaultSettings = [
      ['Setting Name', 'Value'],
      ['Update Interval (days)', '1'],
      ['Auto-Process Filters', 'true'],
      ['Last Update Time', new Date().toISOString()],
      ['Client Name', ''],
      ['Search Console Site', ''],
      ['Master Folder ID', ''],
      ['Client Folder ID', ''],
      ['Topic Filters Folder ID', ''],
      ['Audience Filters Folder ID', '']
    ];
    settingsSheet.getRange(1, 1, defaultSettings.length, 2).setValues(defaultSettings);
    settingsSheet.setFrozenRows(1);
  }
  return settingsSheet;
}

function createRegistrySheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let registrySheet = ss.getSheetByName('Filter Registry');

  if (!registrySheet) {
    registrySheet = ss.insertSheet('Filter Registry');
    const headers = [
      ['Filter Type', 'Original Phrases', 'Filter File URL', 'File ID', 'Last Processed', 'Status', 'Matched Terms Count']
    ];
    registrySheet.getRange(1, 1, 1, headers[0].length).setValues(headers);
    registrySheet.setFrozenRows(1);
  }
  return registrySheet;
}

function getSettings() {
  const settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
  const settings = {};
  const data = settingsSheet.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    settings[data[i][0]] = data[i][1];
  }
  return settings;
}

function updateSetting(settingName, value) {
  const settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
  const data = settingsSheet.getDataRange().getValues();

  for (let i = 0; i < data.length; i++) {
    if (data[i][0] === settingName) {
      settingsSheet.getRange(i + 1, 2).setValue(value);
      break;
    }
  }
}

function showSidebar() {
  const html = HtmlService.createHtmlOutput(`
    <html>
      <head>
        <base target="_top">
        <style>
          .sidebar { padding: 10px; }
          .section { margin-bottom: 20px; }
          .section-title { font-weight: bold; margin-bottom: 5px; }
          select, input { width: 100%; margin: 5px 0; padding: 5px; }
          .button-container { margin-top: 20px; }
        </style>
      </head>
      <body>
        <div class="sidebar">
          <div class="section">
            <div class="section-title">Search Console Site</div>
            <select id="siteSelect" onChange="updateSite()">
              <option value="">Loading sites...</option>
            </select>
          </div>

          <div class="section">
            <div class="section-title">Client Name</div>
            <input type="text" id="clientName" placeholder="Enter client name">
          </div>

          <div class="section">
            <div class="section-title">Drive Folder</div>
            <select id="folderSelect">
              <option value="">Loading folders...</option>
            </select>
            <div class="button-container">
              <button onclick="createNewFolder()">Create New Folder</button>
            </div>
          </div>

          <div class="section">
            <div class="section-title">Update Interval</div>
            <input type="number" id="updateInterval" min="1" value="1">
            <div style="font-size: 12px; color: #666;">Days between updates</div>
          </div>

          <div class="button-container">
            <button onclick="saveConfiguration()">Save Configuration</button>
          </div>
        </div>

        <script>
          google.script.run
            .withSuccessHandler(function(sites) {
              const select = document.getElementById('siteSelect');
              select.innerHTML = sites.map(site =>
                '<option value="' + site.siteUrl + '">' + site.siteUrl + '</option>'
              ).join('');
            })
            .getSearchConsoleSites();

          google.script.run
            .withSuccessHandler(function(folders) {
              const select = document.getElementById('folderSelect');
              select.innerHTML = folders.map(folder =>
                '<option value="' + folder.id + '">' + folder.name + '</option>'
              ).join('');
            })
            .getDriveFolders();

          function updateSite() {
            const siteUrl = document.getElementById('siteSelect').value;
            google.script.run.updateSetting('Search Console Site', siteUrl);
          }

          function createNewFolder() {
            const name = window.prompt('Enter folder name:');
            if (name) {
              google.script.run
                .withSuccessHandler(function(folders) {
                  const select = document.getElementById('folderSelect');
                  select.innerHTML = folders.map(folder =>
                    '<option value="' + folder.id + '">' + folder.name + '</option>'
                  ).join('');
                })
                .createAndUpdateFolderList(name);
            }
          }

          function saveConfiguration() {
            const config = {
              clientName: document.getElementById('clientName').value,
              folderId: document.getElementById('folderSelect').value,
              updateInterval: document.getElementById('updateInterval').value
            };

            google.script.run
              .withSuccessHandler(function() {
                google.script.host.close();
              })
              .saveConfiguration(config);
          }
        </script>
      </body>
    </html>
  `)
    .setTitle('Search Analytics Configuration')
    .setWidth(300);

  SpreadsheetApp.getUi().showSidebar(html);
}

// Supporting functions for sidebar
function getSearchConsoleSites() {
  try {
    const sites = Webmasters.Sites.list();
    return sites.siteEntry || [];
  } catch (e) {
    Logger.log('Error getting sites: ' + e.message);
    return [];
  }
}

function getDriveFolders() {
  const folders = DriveApp.getFolders();
  const folderList = [];
  while (folders.hasNext()) {
    const folder = folders.next();
    folderList.push({
      id: folder.getId(),
      name: folder.getName()
    });
  }
  return folderList;
}

function createAndUpdateFolderList(folderName) {
  const folder = DriveApp.createFolder(folderName);
  return getDriveFolders();
}

function saveConfiguration(config) {
  updateSetting('Client Name', config.clientName);
  updateSetting('Master Folder ID', config.folderId);
  updateSetting('Update Interval (days)', config.updateInterval);
  setupFolderStructure();
}
// Folder Structure Management
function setupFolderStructure() {
  const settings = getSettings();
  const masterFolderId = settings['Master Folder ID'];
  const clientName = settings['Client Name'];

  if (!masterFolderId || !clientName) {
    SpreadsheetApp.getUi().alert('Please configure client name and master folder first');
    return;
  }

  try {
    const masterFolder = DriveApp.getFolderById(masterFolderId);

    // Create or get client folder
    let clientFolder;
    const clientFolderIterator = masterFolder.getFoldersByName(clientName);
    if (clientFolderIterator.hasNext()) {
      clientFolder = clientFolderIterator.next();
    } else {
      clientFolder = masterFolder.createFolder(clientName);
    }
    updateSetting('Client Folder ID', clientFolder.getId());

    // Create Topic Filters folder
    let topicFolder;
    const topicFolderIterator = clientFolder.getFoldersByName('Topic Filters');
    if (topicFolderIterator.hasNext()) {
      topicFolder = topicFolderIterator.next();
    } else {
      topicFolder = clientFolder.createFolder('Topic Filters');
    }
    updateSetting('Topic Filters Folder ID', topicFolder.getId());

    // Create Audience Filters folder
    let audienceFolder;
    const audienceFolderIterator = clientFolder.getFoldersByName('Audience Filters');
    if (audienceFolderIterator.hasNext()) {
      audienceFolder = audienceFolderIterator.next();
    } else {
      audienceFolder = clientFolder.createFolder('Audience Filters');
    }
    updateSetting('Audience Filters Folder ID', audienceFolder.getId());

    // Create required sheets if they don't exist
    createRequiredSheets();

    SpreadsheetApp.getUi().alert('Setup completed successfully');
  } catch (error) {
    Logger.log('Error in setupFolderStructure: ' + error.message);
    SpreadsheetApp.getUi().alert('Error during setup: ' + error.message);
  }
}

function createRequiredSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Create Market Raw sheet if it doesn't exist
  if (!ss.getSheetByName('Market Raw')) {
    const marketSheet = ss.insertSheet('Market Raw');
    marketSheet.getRange('A5').setValue('Keywords');
    marketSheet.setFrozenRows(5);
  }

  // Create Audience Raw sheet if it doesn't exist
  if (!ss.getSheetByName('Audience Raw')) {
    const audienceSheet = ss.insertSheet('Audience Raw');
    audienceSheet.getRange('A5').setValue('Keywords');
    audienceSheet.setFrozenRows(5);
  }

  // Create Market Management sheet if it doesn't exist
  if (!ss.getSheetByName('Market Management')) {
    const marketManagement = ss.insertSheet('Market Management');
    const marketHeaders = [['Field', 'Subject', 'Market Topic', 'Market Types', 'Process Button',
                           'Processed Date', 'Topic Sheet Link', 'Keywords Count', 'Status']];
    marketManagement.getRange(5, 1, 1, marketHeaders[0].length).setValues(marketHeaders);
    marketManagement.setFrozenRows(5);
  }

  // Create Audience Management sheet if it doesn't exist
  if (!ss.getSheetByName('Audience Management')) {
    const audienceManagement = ss.insertSheet('Audience Management');
    const audienceHeaders = [['Audience', 'Category', 'Niche', 'Persona', 'Process Button',
                             'Processed Date', 'Niche Sheet Link', 'Keywords Count', 'Status']];
    audienceManagement.getRange(5, 1, 1, audienceHeaders[0].length).setValues(audienceHeaders);
    audienceManagement.setFrozenRows(5);
  }
}

// Search Console Import Function
function exportSearchTerms() {
  const settings = getSettings();
  const propertyUrl = settings['Search Console Site'];

  if (!propertyUrl) {
    SpreadsheetApp.getUi().alert('Please configure Search Console site first');
    return;
  }

  const sheetName = 'Apps Script Import';
  const marketSheetName = 'Market Raw';
  const audienceSheetName = 'Audience Raw';
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
    sheet.appendRow(['Search Term', 'Count', 'First Imported', 'Status']);
  }

  const marketSheet = spreadsheet.getSheetByName(marketSheetName);
  const audienceSheet = spreadsheet.getSheetByName(audienceSheetName);

  // Get existing keywords from Raw sheets
  const marketKeywords = new Set(marketSheet.getRange('A6:A' + marketSheet.getLastRow()).getValues().flat().filter(String));
  const audienceKeywords = new Set(audienceSheet.getRange('A6:A' + audienceSheet.getLastRow()).getValues().flat().filter(String));

  let lastImportDate = null;
  const existingTerms = new Map();
  const today = new Date().toISOString().split('T')[0];

  // Load existing terms and their import dates
  const lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    const termsData = sheet.getRange(2, 1, lastRow - 1, 4).getValues();
    termsData.forEach(row => {
      const term = row[0];
      const count = row[1];
      const firstImported = row[2];
      const status = row[3];
      existingTerms.set(term, {
        count: parseInt(count, 10),
        firstImported: firstImported,
        status: status
      });

      if (firstImported && (!lastImportDate || new Date(firstImported) > new Date(lastImportDate))) {
        lastImportDate = firstImported;
      }
    });
  }

  // Set up date range
  const endDate = new Date();
  let startDate = new Date();

  if (lastImportDate) {
    startDate.setTime(new Date(lastImportDate).getTime() + 86400000);
    if (startDate > endDate) {
      SpreadsheetApp.getUi().alert('Already processed up to current date. Try again tomorrow.');
      return;
    }
  } else {
    startDate.setDate(endDate.getDate() - 480);
  }

  const requestTemplate = {
    startDate: startDate.toISOString().split('T')[0],
    endDate: endDate.toISOString().split('T')[0],
    dimensions: ['query'],
    rowLimit: 1000,
    startRow: 0
  };

  Logger.log(`Processing data from ${requestTemplate.startDate} to ${requestTemplate.endDate}`);

  let rowsFetched = 0;
  let updatedData = [];

  try {
    while (true) {
      const request = { ...requestTemplate, startRow: rowsFetched };

      const response = UrlFetchApp.fetch(
        `https://searchconsole.googleapis.com/webmasters/v3/sites/${encodeURIComponent(propertyUrl)}/searchAnalytics/query`,
        {
          method: 'post',
          contentType: 'application/json',
          headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
          payload: JSON.stringify(request)
        }
      );

      const data = JSON.parse(response.getContentText()).rows || [];
      if (data.length === 0) break;

      data.forEach(row => {
        const term = row.keys[0];

        if (existingTerms.has(term)) {
          const currentData = existingTerms.get(term);
          currentData.count += 1;
          existingTerms.set(term, currentData);
        } else {
          existingTerms.set(term, { count: 1, firstImported: today, status: 'NEW IMPORT' });
        }
      });

      rowsFetched += data.length;
      Logger.log(`Fetched ${data.length} rows. Total rows fetched: ${rowsFetched}`);
    }

    updatedData = Array.from(existingTerms, ([term, data]) =>
      [term, data.count, data.firstImported, data.status]);

    const newKeywords = [];
    updatedData.forEach(row => {
      const [term, count, firstImported, status] = row;
      if (status === 'NEW IMPORT') {
        newKeywords.push(term);
      }
    });

    if (newKeywords.length > 0) {
      const marketLastRow = marketSheet.getLastRow();
      const marketStartRow = Math.max(marketLastRow + 1, 6);
      marketSheet.getRange(marketStartRow, 1, newKeywords.length, 1)
        .setValues(newKeywords.map(keyword => [keyword]));

      const audienceLastRow = audienceSheet.getLastRow();
      const audienceStartRow = Math.max(audienceLastRow + 1, 6);
      audienceSheet.getRange(audienceStartRow, 1, newKeywords.length, 1)
        .setValues(newKeywords.map(keyword => [keyword]));

      updatedData = updatedData.map(row => {
        if (row[3] === 'NEW IMPORT') {
          return [row[0], row[1], row[2], 'COPIED TO RAW'];
        }
        return row;
      });
    }

    sheet.clear();
    sheet.appendRow(['Search Term', 'Count', 'First Imported', 'Status']);
    if (updatedData.length > 0) {
      sheet.getRange(2, 1, updatedData.length, 4).setValues(updatedData);
    }

    SpreadsheetApp.getUi().alert(`Processed ${newKeywords.length} new keywords`);
    Logger.log(`Process completed. ${newKeywords.length} new keywords added to raw sheets`);

  } catch (error) {
    Logger.log('Error: ' + error.message);
    SpreadsheetApp.getUi().alert('Error during import: ' + error.message);
  }
}
// Filter Processing Functions
function processTopicFilter() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const managementSheet = ss.getSheetByName('Market Management');
  const rawSheet = ss.getSheetByName('Market Raw');

  try {
    // Get the active row
    const activeRange = managementSheet.getActiveRange();
    const activeRow = activeRange.getRow();

    // Check if we're below the header row
    if (activeRow <= 5) {
      SpreadsheetApp.getUi().alert('Please select a row below the headers (row 6 or later)');
      return;
    }

    const rowValues = managementSheet.getRange(activeRow, 1, 1, 9).getValues()[0];

    // Get phrases from Subject and Topic columns
    const subjectPhrases = rowValues[1].toString().trim() ?
      rowValues[1].split(',').map(p => p.trim().toLowerCase()).filter(p => p.length > 0) : [];
    const topicPhrases = rowValues[2].toString().trim() ?
      rowValues[2].split(',').map(p => p.trim().toLowerCase()).filter(p => p.length > 0) : [];

    if (subjectPhrases.length === 0 && topicPhrases.length === 0) {
      SpreadsheetApp.getUi().alert('Please enter phrases in either Subject or Topic column');
      return;
    }

    // Get raw keywords
    const rawData = rawSheet.getRange('A6:A' + rawSheet.getLastRow()).getValues();
    const rawKeywords = rawData.map(row => row[0]).filter(String);

    if (rawKeywords.length === 0) {
      SpreadsheetApp.getUi().alert('No keywords found in Market Raw sheet');
      return;
    }

    // Match keywords
    const matches = [];
    const phraseMatches = [];

    rawKeywords.forEach(keyword => {
      const keywordLower = keyword.toLowerCase();
      let matched = false;

      if (subjectPhrases.length > 0) {
        for (let phrase of subjectPhrases) {
          if (keywordLower.includes(phrase)) {
            matches.push(keyword);
            phraseMatches.push(`Subject match: "${phrase}"`);
            matched = true;
            break;
          }
        }
      }

      if (!matched && topicPhrases.length > 0) {
        for (let phrase of topicPhrases) {
          if (keywordLower.includes(phrase)) {
            matches.push(keyword);
            phraseMatches.push(`Topic match: "${phrase}"`);
            break;
          }
        }
      }
    });

    if (matches.length === 0) {
      SpreadsheetApp.getUi().alert('No matching keywords found');
      return;
    }

    // Create filter sheet and get results
    const sourcePhrase = subjectPhrases.length > 0 ? subjectPhrases[0] : topicPhrases[0];
    const result = createTopicFilterSheet(rowValues, sourcePhrase, matches, phraseMatches);

    // Remove matched keywords from raw sheet
    const remainingKeywords = rawKeywords.filter(keyword => !matches.includes(keyword));
    rawSheet.getRange('A6:A' + rawSheet.getLastRow()).clearContent();
    if (remainingKeywords.length > 0) {
      rawSheet.getRange(6, 1, remainingKeywords.length, 1)
        .setValues(remainingKeywords.map(k => [k]));
    }

    // Update management sheet
    const today = new Date().toISOString().split('T')[0];
    managementSheet.getRange(activeRow, 6).setValue(today);
    managementSheet.getRange(activeRow, 7).setFormula(`=HYPERLINK("${result.sheetUrl}","Open Sheet")`);
    managementSheet.getRange(activeRow, 8).setValue(result.keywordCount);
    managementSheet.getRange(activeRow, 9).setValue('Processed');

    SpreadsheetApp.getUi().alert(`Processed ${matches.length} keywords`);

  } catch (error) {
    Logger.log('Error in processTopicFilter: ' + error.message);
    SpreadsheetApp.getUi().alert('Error processing filter: ' + error.message);
  }
}

function processNicheFilter() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const managementSheet = ss.getSheetByName('Audience Management');
  const rawSheet = ss.getSheetByName('Audience Raw');

  try {
    // Get the active row
    const activeRange = managementSheet.getActiveRange();
    const activeRow = activeRange.getRow();

    // Check if we're below the header row
    if (activeRow <= 5) {
      SpreadsheetApp.getUi().alert('Please select a row below the headers (row 6 or later)');
      return;
    }

    const rowValues = managementSheet.getRange(activeRow, 1, 1, 9).getValues()[0];

    // Get phrases from Category and Niche columns
    const categoryPhrases = rowValues[1].toString().trim() ?
      rowValues[1].split(',').map(p => p.trim().toLowerCase()).filter(p => p.length > 0) : [];
    const nichePhrases = rowValues[2].toString().trim() ?
      rowValues[2].split(',').map(p => p.trim().toLowerCase()).filter(p => p.length > 0) : [];

    if (categoryPhrases.length === 0 && nichePhrases.length === 0) {
      SpreadsheetApp.getUi().alert('Please enter phrases in either Category or Niche column');
      return;
    }

    // Get raw keywords
    const rawData = rawSheet.getRange('A6:A' + rawSheet.getLastRow()).getValues();
    const rawKeywords = rawData.map(row => row[0]).filter(String);

    if (rawKeywords.length === 0) {
      SpreadsheetApp.getUi().alert('No keywords found in Audience Raw sheet');
      return;
    }

    // Match keywords
    const matches = [];
    const phraseMatches = [];

    rawKeywords.forEach(keyword => {
      const keywordLower = keyword.toLowerCase();
      let matched = false;

      if (categoryPhrases.length > 0) {
        for (let phrase of categoryPhrases) {
          if (keywordLower.includes(phrase)) {
            matches.push(keyword);
            phraseMatches.push(`Category match: "${phrase}"`);
            matched = true;
            break;
          }
        }
      }

      if (!matched && nichePhrases.length > 0) {
        for (let phrase of nichePhrases) {
          if (keywordLower.includes(phrase)) {
            matches.push(keyword);
            phraseMatches.push(`Niche match: "${phrase}"`);
            break;
          }
        }
      }
    });

    if (matches.length === 0) {
      SpreadsheetApp.getUi().alert('No matching keywords found');
      return;
    }

    // Create filter sheet and get results
    const sourcePhrase = categoryPhrases.length > 0 ? categoryPhrases[0] : nichePhrases[0];
    const result = createNicheFilterSheet(rowValues, sourcePhrase, matches, phraseMatches);

    // Remove matched keywords from raw sheet
    const remainingKeywords = rawKeywords.filter(keyword => !matches.includes(keyword));
    rawSheet.getRange('A6:A' + rawSheet.getLastRow()).clearContent();
    if (remainingKeywords.length > 0) {
      rawSheet.getRange(6, 1, remainingKeywords.length, 1)
        .setValues(remainingKeywords.map(k => [k]));
    }

    // Update management sheet
    const today = new Date().toISOString().split('T')[0];
    managementSheet.getRange(activeRow, 6).setValue(today);
    managementSheet.getRange(activeRow, 7).setFormula(`=HYPERLINK("${result.sheetUrl}","Open Sheet")`);
    managementSheet.getRange(activeRow, 8).setValue(result.keywordCount);
    managementSheet.getRange(activeRow, 9).setValue('Processed');

    SpreadsheetApp.getUi().alert(`Processed ${matches.length} keywords`);

  } catch (error) {
    Logger.log('Error in processNicheFilter: ' + error.message);
    SpreadsheetApp.getUi().alert('Error processing filter: ' + error.message);
  }
}
function createTopicFilterSheet(row, sourcePhrase, matchedKeywords, phraseMatches) {
  const settings = getSettings();
  const clientName = settings['Client Name'];
  const topicFolderId = settings['Topic Filters Folder ID'];

  if (!topicFolderId) {
    throw new Error('Topic Filters folder not set up. Please run setup first.');
  }

  const today = new Date().toISOString().split('T')[0];

  try {
    Logger.log('Creating new topic filter spreadsheet...');
    const fileName = `Topic Filter - ${sourcePhrase} - ${clientName} - ${today}`;
    const newSpreadsheet = SpreadsheetApp.create(fileName);
    const fileId = newSpreadsheet.getId();

    // Move to correct folder
    const file = DriveApp.getFileById(fileId);
    const topicFolder = DriveApp.getFolderById(topicFolderId);
    topicFolder.addFile(file);
    DriveApp.getRootFolder().removeFile(file);

    const newSheet = newSpreadsheet.getActiveSheet();

    // Add link back to source
    const sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    newSheet.getRange('A1').setFormula('=HYPERLINK("' + sourceSpreadsheet.getUrl() +
      '", "Back to Market Management")');

    // Write keywords
    if (matchedKeywords.length > 0) {
      newSheet.getRange(3, 1, matchedKeywords.length, 1)
        .setValues(matchedKeywords.map(keyword => [keyword]));
    }

    // Add to registry
    const phrases = row[1].toString().trim() ? row[1].split(',') : row[2].split(',');
    addToRegistry(
      'Topic',
      phrases.filter(p => p.trim().length > 0),
      newSpreadsheet.getUrl(),
      fileId
    );

    return {
      sheetUrl: newSpreadsheet.getUrl(),
      keywordCount: matchedKeywords.length
    };

  } catch (error) {
    Logger.log('Error in createTopicFilterSheet: ' + error.message);
    throw error;
  }
}

function createNicheFilterSheet(row, sourcePhrase, matchedKeywords, phraseMatches) {
  const settings = getSettings();
  const clientName = settings['Client Name'];
  const audienceFolderId = settings['Audience Filters Folder ID'];

  if (!audienceFolderId) {
    throw new Error('Audience Filters folder not set up. Please run setup first.');
  }

  const today = new Date().toISOString().split('T')[0];

  try {
    Logger.log('Creating new niche filter spreadsheet...');
    const fileName = `Audience Filter - ${sourcePhrase} - ${clientName} - ${today}`;
    const newSpreadsheet = SpreadsheetApp.create(fileName);
    const fileId = newSpreadsheet.getId();

    // Move to correct folder
    const file = DriveApp.getFileById(fileId);
    const audienceFolder = DriveApp.getFolderById(audienceFolderId);
    audienceFolder.addFile(file);
    DriveApp.getRootFolder().removeFile(file);

    const newSheet = newSpreadsheet.getActiveSheet();

    // Add link back to source
    const sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    newSheet.getRange('A1').setFormula('=HYPERLINK("' + sourceSpreadsheet.getUrl() +
      '", "Back to Audience Management")');

    // Write keywords
    if (matchedKeywords.length > 0) {
      newSheet.getRange(3, 1, matchedKeywords.length, 1)
        .setValues(matchedKeywords.map(keyword => [keyword]));
    }

    // Add to registry
    const phrases = row[1].toString().trim() ? row[1].split(',') : row[2].split(',');
    addToRegistry(
      'Niche',
      phrases.filter(p => p.trim().length > 0),
      newSpreadsheet.getUrl(),
      fileId
    );

    return {
      sheetUrl: newSpreadsheet.getUrl(),
      keywordCount: matchedKeywords.length
    };

  } catch (error) {
    Logger.log('Error in createNicheFilterSheet: ' + error.message);
    throw error;
  }
}

function addToRegistry(filterType, phrases, fileUrl, fileId) {
  const registrySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Filter Registry');
  const today = new Date().toISOString().split('T')[0];

  registrySheet.appendRow([
    filterType,
    phrases.join(', '),
    fileUrl,
    fileId,
    today,
    'Active',
    0
  ]);
}

function getFilterRegistry() {
  const registrySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Filter Registry');
  const data = registrySheet.getDataRange().getValues();
  const registry = [];

  for (let i = 1; i < data.length; i++) {
    if (data[i][5] === 'Active') {  // Only include active filters
      registry.push({
        filterType: data[i][0],
        phrases: data[i][1].split(',').map(p => p.trim().toLowerCase()).filter(p => p.length > 0),
        fileUrl: data[i][2],
        fileId: data[i][3],
        lastProcessed: data[i][4],
        status: data[i][5],
        matchCount: data[i][6]
      });
    }
  }
  return registry;
}
// Automation Setup and Trigger Management
function setupAutomation() {
  try {
    // Clear any existing triggers
    const existingTriggers = ScriptApp.getProjectTriggers();
    existingTriggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));

    const settings = getSettings();
    const interval = parseInt(settings['Update Interval (days)']);

    if (isNaN(interval) || interval < 1) {
      throw new Error('Invalid update interval. Please set a valid number of days in Settings.');
    }

    // Create new daily trigger to check for updates
    ScriptApp.newTrigger('checkAndRunAutomatedUpdate')
      .timeBased()
      .everyDays(1)
      .atHour(2)  // Run at 2 AM
      .create();

    Logger.log(`Automation setup complete. Will check for updates every ${interval} days`);
    SpreadsheetApp.getUi().alert('Automation setup completed successfully');

  } catch (error) {
    Logger.log('Error in setupAutomation: ' + error.message);
    SpreadsheetApp.getUi().alert('Error setting up automation: ' + error.message);
  }
}

function checkAndRunAutomatedUpdate() {
  try {
    const settings = getSettings();
    const lastUpdate = new Date(settings['Last Update Time']);
    const interval = parseInt(settings['Update Interval (days)']);
    const now = new Date();

    // Check if it's time to run update
    if ((now - lastUpdate) / (1000 * 60 * 60 * 24) >= interval) {
      Logger.log('Running automated update...');

      // Run import
      exportSearchTerms();

      // Process existing filters if auto-process is enabled
      if (settings['Auto-Process Filters'] === 'true') {
        processExistingFilters();
      }

      // Update last update time
      updateSetting('Last Update Time', now.toISOString());
      Logger.log('Automated update completed successfully');
    }
  } catch (error) {
    Logger.log('Error in checkAndRunAutomatedUpdate: ' + error.message);
    // Send email notification of error if configured
    sendErrorNotification(error);
  }
}

function processExistingFilters() {
  const registry = getFilterRegistry();
  Logger.log(`Processing ${registry.length} existing filters`);

  registry.forEach(filter => {
    try {
      // Get the appropriate raw sheet and function based on filter type
      const rawSheetName = filter.filterType === 'Topic' ? 'Market Raw' : 'Audience Raw';
      const rawSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(rawSheetName);

      // Get raw keywords
      const rawData = rawSheet.getRange('A6:A' + rawSheet.getLastRow()).getValues();
      const rawKeywords = rawData.map(row => row[0]).filter(String);

      // Match keywords against filter phrases
      const matches = [];
      rawKeywords.forEach(keyword => {
        const keywordLower = keyword.toLowerCase();
        for (const phrase of filter.phrases) {
          if (keywordLower.includes(phrase)) {
            matches.push(keyword);
            break;
          }
        }
      });

      if (matches.length > 0) {
        // Append matches to existing filter file
        const filterSpreadsheet = SpreadsheetApp.openById(filter.fileId);
        const filterSheet = filterSpreadsheet.getActiveSheet();
        const lastRow = Math.max(filterSheet.getLastRow(), 2);
        filterSheet.getRange(lastRow + 1, 1, matches.length, 1)
          .setValues(matches.map(keyword => [keyword]));

        // Remove matched keywords from raw sheet
        const remainingKeywords = rawKeywords.filter(keyword => !matches.includes(keyword));
        rawSheet.getRange('A6:A' + rawSheet.getLastRow()).clearContent();
        if (remainingKeywords.length > 0) {
          rawSheet.getRange(6, 1, remainingKeywords.length, 1)
            .setValues(remainingKeywords.map(k => [k]));
        }

        // Update registry
        updateRegistryCount(filter.fileId, matches.length);
      }
    } catch (error) {
      Logger.log(`Error processing filter ${filter.fileId}: ${error.message}`);
    }
  });
}

// Maintenance Functions
function performMaintenance() {
  try {
    // Backup registry
    backupRegistry();

    // Verify all registered files still exist
    cleanupRegistry();

    // Clean up old backup sheets
    removeOldBackups();

    // Verify folder structure
    validateFolderStructure();

    SpreadsheetApp.getUi().alert('Maintenance completed successfully');
  } catch (error) {
    Logger.log('Error in maintenance: ' + error.message);
    SpreadsheetApp.getUi().alert('Error during maintenance: ' + error.message);
  }
}

function backupRegistry() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const registrySheet = ss.getSheetByName('Filter Registry');
  const today = new Date().toISOString().split('T')[0];

  // Create backup in same spreadsheet
  const backupName = `Registry Backup ${today}`;
  const backup = ss.insertSheet(backupName);

  // Copy data
  const data = registrySheet.getDataRange().getValues();
  backup.getRange(1, 1, data.length, data[0].length).setValues(data);

  // Hide backup sheet
  backup.hideSheet();
}

function cleanupRegistry() {
  const registry = getFilterRegistry();
  const registrySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Filter Registry');

  const data = registrySheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    try {
      // Try to access the file
      DriveApp.getFileById(data[i][3]);
    } catch (error) {
      // If file doesn't exist, mark as inactive
      registrySheet.getRange(i + 1, 6).setValue('Inactive');
      Logger.log(`Marked inactive: ${data[i][3]}`);
    }
  }
}

function removeOldBackups() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

  sheets.forEach(sheet => {
    const name = sheet.getName();
    if (name.startsWith('Registry Backup ')) {
      const dateStr = name.replace('Registry Backup ', '');
      const backupDate = new Date(dateStr);

      if (backupDate < thirtyDaysAgo) {
        ss.deleteSheet(sheet);
      }
    }
  });
}

function validateFolderStructure() {
  const settings = getSettings();
  const requiredFolders = [
    'Master Folder ID',
    'Client Folder ID',
    'Topic Filters Folder ID',
    'Audience Filters Folder ID'
  ];

  requiredFolders.forEach(folderId => {
    const id = settings[folderId];
    if (id) {
      try {
        DriveApp.getFolderById(id);
      } catch (error) {
        throw new Error(`Invalid or missing folder: ${folderId}`);
      }
    }
  });
}