Hatyai Flood Rescue (POC) Apps Script

ExportToKML.gs

/**
 * Generate a KML file (rescue_export.kml) from the sheet data.
 * Add this inside Google Apps Script.
 */

function generateKML() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  const data = sheet.getDataRange().getValues();

  let kml = [];
  kml.push('<?xml version="1.0" encoding="UTF-8"?>');
  kml.push('<kml xmlns="http://www.opengis.net/kml/2.2">');
  kml.push('<Document>');
  kml.push('<name>Rescue Points (Auto-generated)</name>');

  // Optional icon style
  kml.push(`
    <Style id="rescueStyle">
      <IconStyle>
        <Icon><href>http://maps.google.com/mapfiles/kml/paddle/red-circle.png</href></Icon>
        <scale>1.2</scale>
      </IconStyle>
    </Style>
  `);

  for (let i = 1; i < data.length; i++) {
    const row = data[i];

    const timestamp = row[0];
    const rawLocation = clean(row[1]);
    const victims = clean(row[2]);
    const animals = clean(row[3]);
    const contact = clean(row[4]);
    const special = clean(row[5]);
    const inMap = clean(row[6]);
    const status = clean(row[7]);

    const formattedAddress = clean(row[8]);
    const lat = clean(row[9]);
    const lng = clean(row[10]);

    // const coords = extractCoordinates(rawLocation);
    const coords = (lat && lng) ? `${lng},${lat}` : null;

    kml.push(`<Placemark>
      <name>Case #${i}</name>
      <styleUrl>#rescueStyle</styleUrl>
      <description><![CDATA[
        <b>Timestamp:</b> ${timestamp}<br/>
        <b>ที่อยู่/ลิงก์พิกัด:</b> ${rawLocation}<br/>
        <b>ที่อยู่เพิ่มเติม:</b> ${formattedAddress}<br/>
        <b>จำนวนผู้ประสบภัย:</b> ${victims}<br/>
        <b>สัตว์:</b> ${animals}<br/>
        <b>Contact:</b> ${contact}<br/>
        <b>Special Needs:</b> ${special}<br/>
        <b>Map Logged:</b> ${inMap}<br/>
        <b>Status:</b> ${status}<br/>
        <b>ลองจิจูด/ละติจูด</b> ${lng},${lat}<br/>
      ]]></description>`);

    if (coords) {
      kml.push(`<Point><coordinates>${coords}</coordinates></Point>`);
    } else {
      Logger.log(`⚠️ Missing coordinates for row ${i + 1}: ${rawLocation}`);
    }

    kml.push(`</Placemark>`);
  }

  kml.push('</Document></kml>');

  // Overwrite existing file if exists
  const folder = DriveApp.getRootFolder();
  const existing = folder.getFilesByName("rescue_export.kml");
  if (existing.hasNext()) existing.next().setTrashed(true);

  folder.createFile("rescue_export.kml", kml.join("\n"));
  Logger.log("✅ KML generated successfully: rescue_export.kml");
}

/**
 * Cleans empty values to avoid "undefined" in KML.
 */
function clean(val) {
  if (val === null || val === undefined) return "";
  return String(val).trim();
}