// Code.gs
function showCustomForm() {
  const html = HtmlService.createHtmlOutputFromFile('FormDialog')
    .setWidth(400)
    .setHeight(500)
    .setTitle('Data Entry Form');
  SpreadsheetApp.getUi().showModalDialog(html, 'Submit Request');
}

function submitFormData(formData) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  
  // Validate data
  if (!validateFormData(formData)) {
    return { success: false, error: 'Validation failed' };
  }
  
  // Append to sheet
  sheet.appendRow([
    new Date(),
    formData.name,
    formData.email,
    formData.category,
    formData.description,
    'Pending'
  ]);
  
  // Send confirmation email
  sendConfirmationEmail(formData.email, formData);
  
  return { success: true };
}

function validateFormData(data) {
  if (!data.name || data.name.trim() === '') return false;
  if (!data.email || !isValidEmail(data.email)) return false;
  if (!data.category) return false;
  return true;
}

function isValidEmail(email) {
  const regex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  return regex.test(email);
}

// Code.gs
function sendConfirmationEmail(recipientEmail, data) {
  const subject = `Confirmation: ${data.category} Request`;
  
  const htmlBody = `
    <div style="font-family: Arial, sans-serif; max-width: 600px;">
      <h2 style="color: #1a73e8;">Request Confirmation</h2>
      <p>Hello ${data.name},</p>
      <p>We've received your request with the following details:</p>
      <div style="background-color: #f5f5f5; padding: 15px; border-radius: 5px; margin: 20px 0;">
        <p><strong>Category:</strong> ${data.category}</p>
        <p><strong>Description:</strong> ${data.description}</p>
        <p><strong>Status:</strong> Pending Review</p>
        <p><strong>Submitted:</strong> ${new Date().toLocaleString()}</p>
      </div>
      <p>We'll review your request and get back to you soon.</p>
      <p style="color: #666; font-size: 12px; margin-top: 30px;">
        This is an automated message. Please do not reply to this email.
      </p>
    </div>
  `;
  
  GmailApp.sendEmail(recipientEmail, subject, '', {
    htmlBody: htmlBody,
    name: 'Automated System'
  });
}

function sendBulkNotifications() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  
  // Find columns
  const emailCol = headers.indexOf('Email');
  const statusCol = headers.indexOf('Status');
  const notifiedCol = headers.indexOf('Notified');
  
  // Process each row
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    
    // Skip if already notified or not relevant status
    if (row[notifiedCol] === 'Yes' || row[statusCol] !== 'Completed') {
      continue;
    }
    
    // Send notification
    try {
      const email = row[emailCol];
      sendStatusUpdateEmail(email, row, headers);
      
      // Mark as notified
      sheet.getRange(i + 1, notifiedCol + 1).setValue('Yes');
      
      // Add delay to avoid rate limiting
      Utilities.sleep(1000);
    } catch (error) {
      Logger.log(`Error sending to row ${i}: ${error}`);
    }
  }
}

// Code.gs
function createDashboard() {
  const html = HtmlService.createHtmlOutputFromFile('Dashboard')
    .setWidth(800)
    .setHeight(600)
    .setTitle('Analytics Dashboard');
  SpreadsheetApp.getUi().showModalDialog(html, 'Dashboard');
}

function getDashboardData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  
  // Calculate metrics
  const metrics = {
    total: data.length - 1,
    byCategory: {},
    byStatus: {},
    recentItems: []
  };
  
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const category = row[headers.indexOf('Category')];
    const status = row[headers.indexOf('Status')];
    
    // Count by category
    metrics.byCategory[category] = (metrics.byCategory[category] || 0) + 1;
    
    // Count by status
    metrics.byStatus[status] = (metrics.byStatus[status] || 0) + 1;
    
    // Recent items (last 10)
    if (i <= 10) {
      metrics.recentItems.push({
        date: row[0],
        name: row[1],
        category: category,
        status: status
      });
    }
  }
  
  return metrics;
}

// Code.gs
function setupTriggers() {
  // Remove existing triggers
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));
  
  // Daily report at 9 AM
  ScriptApp.newTrigger('generateDailyReport')
    .timeBased()
    .atHour(9)
    .everyDays(1)
    .create();
  
  // Weekly summary on Monday at 10 AM
  ScriptApp.newTrigger('generateWeeklyReport')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(10)
    .create();
  
  // OnEdit trigger
  ScriptApp.newTrigger('onEditHandler')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create();
}

function onEditHandler(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  
  // Example: Auto-timestamp on status change
  if (sheet.getName() === 'Data' && range.getColumn() === 6) { // Status column
    const timestampCol = 7; // Adjacent column
    sheet.getRange(range.getRow(), timestampCol)
      .setValue(new Date())
      .setNumberFormat('yyyy-mm-dd hh:mm:ss');
    
    // Send notification if status is "Completed"
    if (range.getValue() === 'Completed') {
      const row = sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];
      const email = row[2]; // Email column
      sendStatusUpdateEmail(email, 'Your request has been completed!');
    }
  }
}

// Code.gs
function fetchExternalData() {
  const apiUrl = 'https://api.example.com/data';
  const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
  
  const options = {
    method: 'get',
    headers: {
      'Authorization': `Bearer ${apiKey}`,
      'Content-Type': 'application/json'
    },
    muteHttpExceptions: true
  };
  
  try {
    const response = UrlFetchApp.fetch(apiUrl, options);
    const responseCode = response.getResponseCode();
    
    if (responseCode === 200) {
      const data = JSON.parse(response.getContentText());
      processExternalData(data);
      return { success: true, data: data };
    } else {
      throw new Error(`API returned status: ${responseCode}`);
    }
  } catch (error) {
    Logger.log(`Error fetching data: ${error}`);
    return { success: false, error: error.message };
  }
}

function processExternalData(data) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('API Data');
  sheet.clear();
  
  // Write headers
  const headers = Object.keys(data[0]);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  
  // Write data
  const rows = data.map(item => headers.map(header => item[header]));
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

// Use Script Properties for sensitive data
function setApiKey() {
  PropertiesService.getScriptProperties().setProperty('API_KEY', 'your_key_here');
}

function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty('API_KEY');
}

// Check user permissions
function isAuthorizedUser() {
  const authorizedEmails = ['[email protected]', '[email protected]'];
  const userEmail = Session.getActiveUser().getEmail();
  return authorizedEmails.includes(userEmail);
}