共筆維護試算表、地圖自動更新，大尺寸 (較大試算表) 版本

flow.dot

// dot -Tsvg flow.dot > flow.svg
digraph "gs2umap 資料流向 (上下游關係)" {

    rankdir = TB;
    overlap = scale;

    key [ label="gsheet-key.txt" ];
    gs [ label="google sheet" ];
    php [ label="gs2umap.php" ];
    python [ label="gs2umap.py" ];
    csv [ label="state-organs.csv" ];

    { key gs } -> php -> python -> csv -> umap;

}

flow.svg

gs2umap.php

<?php

header('Content-type: text/plain');

$mydir = dirname(__FILE__);

$proj_table = [
    'so' => '1x26waGdKyGDNJMcmTP4XX6-39B2lvqlL5YcSoau16KE:main:config:state-organs.csv',
];
$gskey=file_get_contents('/home/ckhung/secret/gsheet-key.txt');

parse_str($_SERVER['QUERY_STRING'], $ARGS);
if (! array_key_exists('p', $ARGS))
    exit("QUERY_STRING 內找不到必要參數 'p'");
$projs = explode(',', $ARGS['p']);
foreach ($projs as $proj_name) {
    if (! array_key_exists($proj_name, $proj_table)) {
	echo("未定義的專案名稱 '$proj_name'\n");
	continue;
    }
    $tmp = explode(':', $proj_table[$proj_name]);
    [$gs_id, $data_sheet, $config_sheet, $out_file] = $tmp;
    assert(preg_match('/^[\w-]+$/', $gs_id));
    assert(preg_match('/^[\w-]+$/', $data_sheet));
    assert(preg_match('/^[\w-]+$/', $config_sheet));
    assert(preg_match('/^[\w\.-]+$/', $out_file));
    $cfg_rows = shell_exec("curl -G 'https://sheets.googleapis.com/v4/spreadsheets/$gs_id/values/$config_sheet' --data-urlencode key='$gskey' | jq -r '.values as \$r | (\$r[0]), (\$r[1:][]) | @csv'");
    $cfg_rows = preg_split("#[\r\n]+#", $cfg_rows);
    $opt_str = '';
    foreach ($cfg_rows as $line) {
	$line = str_replace('"', '', $line);
	$line = explode(',', $line);
	if (count($line) != 2) continue;
	// input sanitization
	if (! preg_match('/^\w+/', $line[0], $matches)) continue;
	$key = $matches[0];
	if (! preg_match('/^#?\w+/', $line[1], $matches)) continue;
	$value = $matches[0];
	if (strlen($key)>0)
	    $opt_str .= " --$key='$value'";
    }
    print("$proj_name: $opt_str\n");
    $ret = shell_exec("curl -G 'https://sheets.googleapis.com/v4/spreadsheets/$gs_id/values/$data_sheet' --data-urlencode key='$gskey' | jq -r '.values as \$r | (\$r[0]), (\$r[1:][]) | @csv' | $mydir/gs2umap.py $opt_str > $mydir/$out_file");
}

$date = date('Y/m/d H:i:s');
echo($date);

?>

gs2umap.py

#!/usr/bin/python3

import argparse, csv, sys
from datetime import datetime
from itertools import zip_longest

parser = argparse.ArgumentParser(
    description='google sheet to umap csv',
    formatter_class=argparse.ArgumentDefaultsHelpFormatter)
parser.add_argument('-c', '--color', type=str, default='#00f',
    help='color of a marker on map')
parser.add_argument('-f', '--future_color', type=str, default='#f00',
    help='color of a marker on map')
args = parser.parse_args()

today = datetime.today()
uniq_table = {}
reader = csv.reader(sys.stdin)
header = next(reader)
for row in reader:
    item = dict(zip_longest(header, row))
    place = item['縣市'] + ' ' + item['地點']
    item['地點'] = place
    del item['縣市']
    del item['放映時間']
    item['日期'] = datetime.strptime(item['日期'], '%y/%m/%d')
    if not place in uniq_table:
        uniq_table[place] = item
        uniq_table[place]['播放場數'] = 0
    uniq_table[place]['播放場數'] += 1
    if 'latitude' in item:
        uniq_table[place]['latitude'] = item['latitude']
        uniq_table[place]['longitude'] = item['longitude']
        uniq_table[place]['地址'] = item['地址']
    if item['日期'] >= today:
        if uniq_table[place]['日期'] > item['日期']:
            # 新的一筆記錄是更近的未來
            uniq_table[place]['日期'] = item['日期'] 

print('color,日期,地點,播放場數,latitude,longitude,地址')
for place, item in uniq_table.items():
    if item['日期'] >= today:
        item['日期'] = item['日期'].strftime('%Y/%m/%d')
        color = args.future_color
    else:
        item['日期'] = ''
        color = args.color
    print('{},{},{},{},{:.6f},{:.6f},{}'.format(color, item['日期'], place, item['播放場數'], float(item['latitude']), float(item['longitude']), item['地址']))

'''
日期,放映時間,縣市,地點,latitude,longitude,地址
2025/12/31,12:15,彰化,彰基
2025/12/30,12:15,彰化,彰基
2025/12/13,12:15,彰化,彰基
2025/12/13,7:15,彰化,彰基
2025/11/03,14:00,高雄,總圖店喜樂時代影城
2025/07/05,14:00,雲林,鵝媽媽鵝童樂園(雲林縣勞工育樂中心)
2025/06/15,,台南,勞工育樂中心
2025/06/08,14:30,台南,麻豆戲院
2025/06/07,19:00,新竹,新豐鄉公益場-新豐中正堂,24.8619561,120.9900924,新竹縣新豐鄉商學館
2025/06/07,14:00,雲林,鵝媽媽鵝童樂園(雲林縣勞工育樂中心),23.6808545,120.5523373,雲林縣鬥六市嘉新路222號
2025/06/07,13:30,新竹,新埔公益場-新埔下寮里活動中心
2025/06/07,10:00,新竹,新埔公益場-新埔下寮里活動中心,24.8488016,121.0363768,新竹縣新埔鎮下寮裡
'''