google_apps_script_example.js

/**
 * Google Apps Script example for integrating with Kamlan KML Analyzer API
 * 
 * Instructions:
 * 1. Replace 'YOUR_API_BASE_URL' with your actual deployed app URL
 * 2. Use this script in Google Sheets to analyze KML files automatically
 * 3. The functions can be called from spreadsheet cells or triggered by events
 */

const API_BASE_URL = 'YOUR_API_BASE_URL'; // Replace with your deployed app URL

/**
 * Analyzes a KML file and returns geometry metrics
 * @param {string} kmlUrl - URL of the KML file to analyze
 * @return {Array} Array containing [centroid_lat, centroid_lon, area_ha, perimeter_m]
 */
function analyzeKML(kmlUrl) {
  try {
    const apiUrl = `${API_BASE_URL}/api/analyze_kml?file_url=${encodeURIComponent(kmlUrl)}`;
    
    const response = UrlFetchApp.fetch(apiUrl, {
      method: 'GET',
      headers: {
        'Content-Type': 'application/json'
      },
      muteHttpExceptions: true
    });
    
    const responseText = response.getContentText();
    const data = JSON.parse(responseText);
    
    if (data.success) {
      return [
        data.data.centroid_lat,
        data.data.centroid_lon,
        data.data.area_ha,
        data.data.perimeter_m
      ];
    } else {
      throw new Error(`API Error: ${data.error}`);
    }
    
  } catch (error) {
    console.error('Error analyzing KML:', error);
    return [`Error: ${error.toString()}`, '', '', ''];
  }
}

/**
 * Get only the centroid coordinates from a KML file
 * @param {string} kmlUrl - URL of the KML file to analyze
 * @return {Array} Array containing [latitude, longitude]
 */
function getCentroid(kmlUrl) {
  const result = analyzeKML(kmlUrl);
  if (typeof result[0] === 'string' && result[0].startsWith('Error:')) {
    return result.slice(0, 2);
  }
  return [result[0], result[1]];
}

/**
 * Get area in hectares from a KML file
 * @param {string} kmlUrl - URL of the KML file to analyze
 * @return {number} Area in hectares
 */
function getArea(kmlUrl) {
  const result = analyzeKML(kmlUrl);
  if (typeof result[0] === 'string' && result[0].startsWith('Error:')) {
    return result[0];
  }
  return result[2];
}

/**
 * Get perimeter in meters from a KML file
 * @param {string} kmlUrl - URL of the KML file to analyze
 * @return {number} Perimeter in meters
 */
function getPerimeter(kmlUrl) {
  const result = analyzeKML(kmlUrl);
  if (typeof result[0] === 'string' && result[0].startsWith('Error:')) {
    return result[0];
  }
  return result[3];
}

/**
 * Batch analyze multiple KML files
 * @param {Array} kmlUrls - Array of KML URLs
 * @return {Array} Array of analysis results
 */
function batchAnalyzeKMLs(kmlUrls) {
  const results = [];
  
  for (let i = 0; i < kmlUrls.length; i++) {
    const url = kmlUrls[i];
    if (url && url.trim() !== '') {
      const result = analyzeKML(url);
      results.push([url, ...result]);
      
      // Add small delay to avoid rate limiting
      Utilities.sleep(500);
    }
  }
  
  return results;
}

/**
 * Check if the API is healthy
 * @return {string} API health status
 */
function checkAPIHealth() {
  try {
    const response = UrlFetchApp.fetch(`${API_BASE_URL}/api/health`);
    const data = JSON.parse(response.getContentText());
    return data.status;
  } catch (error) {
    return `Error: ${error.toString()}`;
  }
}

/**
 * Example function to populate a Google Sheet with KML analysis data
 * This function assumes:
 * - Column A contains KML URLs
 * - Results will be written starting from Column B
 */
function analyzeKMLsInSheet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  
  // Get KML URLs from column A (starting from row 2, assuming row 1 has headers)
  const urlRange = sheet.getRange(2, 1, lastRow - 1, 1);
  const urls = urlRange.getValues().flat();
  
  // Set up headers if they don't exist
  const headers = ['KML URL', 'Latitude', 'Longitude', 'Area (ha)', 'Perimeter (m)', 'Status'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  
  // Analyze each KML and populate results
  for (let i = 0; i < urls.length; i++) {
    const url = urls[i];
    const rowNum = i + 2; // +2 because we start from row 2
    
    if (url && url.trim() !== '') {
      sheet.getRange(rowNum, 6).setValue('Processing...');
      
      const result = analyzeKML(url);
      
      // Write results to columns B, C, D, E
      sheet.getRange(rowNum, 2, 1, 4).setValues([result]);
      
      // Update status
      const status = typeof result[0] === 'string' && result[0].startsWith('Error:') ? 'Error' : 'Complete';
      sheet.getRange(rowNum, 6).setValue(status);
      
      // Add delay to avoid overwhelming the API
      Utilities.sleep(1000);
    }
  }
}

/**
 * Custom menu function to add KML Analysis menu to Google Sheets
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('KML Analysis')
    .addItem('Analyze KMLs in Column A', 'analyzeKMLsInSheet')
    .addItem('Check API Health', 'showAPIHealth')
    .addToUi();
}

/**
 * Show API health status in a dialog
 */
function showAPIHealth() {
  const status = checkAPIHealth();
  const ui = SpreadsheetApp.getUi();
  ui.alert('API Health Status', `Status: ${status}`, ui.ButtonSet.OK);
}

// Usage examples in spreadsheet formulas:
// =analyzeKML("https://example.com/your-file.kml") - Returns all metrics
// =getCentroid("https://example.com/your-file.kml") - Returns only lat, lon
// =getArea("https://example.com/your-file.kml") - Returns only area in hectares
// =getPerimeter("https://example.com/your-file.kml") - Returns only perimeter in meters