google sheet => csv for umap

tai-shops.php

<?php

// 詳見： 告別手動標地點！ 共筆維護試算表、地圖自動更新 - 不分尺寸版
// https://newtoypia.blogspot.com/2025/07/big-gsheet-umap.html

$fn = basename(__FILE__, '.php');
$gs_id = "15E0sastmidjk4CJlJJZoKZgbp5He5SKaUCbo4eMMlYY";
$cache_file = "$fn.csv";
$ttl = 3600; // Time-to-live: 1 hour in seconds

function print_result($content) {
    global $cache_file;
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $cache_file . '"');
    echo $content;
}

if (file_exists($cache_file) && (time() - filemtime($cache_file) < $ttl)) {
    print_result(file_get_contents($cache_file));
    exit();
}

$url = "https://docs.google.com/spreadsheets/d/$gs_id/export?format=csv";
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 10); // Avoid hanging Apache processes
$csv_data = curl_exec($ch);
curl_close($ch);

if ($csv_data !== false) {
    $lines = explode("\n", str_replace("\r", "", $csv_data));
    if (empty($lines)) exit("Empty data received.");
    $lines[0] = preg_replace('/^排序,標題,(.*),緯度,經度/', '排序,name,$1,latitude,longitude', $lines[0]);
    $header_row = str_getcsv(array_shift($lines));
    $lon_idx = array_search('longitude', $header_row);
    $lat_idx = array_search('latitude', $header_row);
    $filtered_rows = [$header_row];
    foreach ($lines as $line) {
        if (trim($line) === '') continue;
        $row = str_getcsv($line);
        if (isset($row[$lon_idx], $row[$lat_idx]) && 
            is_numeric($row[$lon_idx]) && 
            is_numeric($row[$lat_idx])) {
            $filtered_rows[] = $row;
        }
    }
    $output_stream = fopen('php://temp', 'r+');
    foreach ($filtered_rows as $f_row) {
        fputcsv($output_stream, $f_row);
    }
    rewind($output_stream);
    $csv_data = stream_get_contents($output_stream);
    fclose($output_stream);

    file_put_contents($cache_file, $csv_data, LOCK_EX);
} elseif (file_exists($cache_file)) {
    $csv_data = file_get_contents($cache_file);
} else {
    header("HTTP/1.1 503 Service Unavailable");
    exit("Data unavailable.");
}

print_result($csv_data);
?>