メモ

スプレッドシートからマイマップ用kml作成

const spreadSheetId = "1rrfUDVSvgHe7mauLmgIHB1HGWet9ZFsQbs2XT7VRyvU";

let data;
let row;

function doGet() {

  // スプレッドシート取得
  sheet = SpreadsheetApp.openById(spreadSheetId).getSheetByName("data");

  // 最終行取得
  row = sheet.getLastRow();

  // 範囲指定
  let range = sheet.getRange(2, 1, row, 5);

  data = range.getValues();

  let output = HtmlService.createTemplateFromFile("doc");
  let result = output.evaluate();

  let kml = '<?xml version="1.0" encoding="UTF-8"?>' + "\n" + result.getContent();

  // コンテントタイプ指定
  return ContentService.createTextOutput(kml).setMimeType(ContentService.MimeType.XML);


}

// 行データ取得
function getRowData(rowNum) {
  return data[rowNum];
}

// 行データ取得
function getLastRow() {
  return row;
}

doc.html

<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<name>楽天モバイル</name>
<description/>

<Style id="icon-ci-1-normal">
<IconStyle><scale>1.1</scale><Icon><href>images/icon-1.png</href></Icon><hotSpot x="16" xunits="pixels" y="16" yunits="insetPixels"/></IconStyle>
<LabelStyle><scale>0</scale></LabelStyle>
</Style>

<Style id="icon-ci-1-highlight">
<IconStyle><scale>1.1</scale><Icon><href>images/icon-1.png</href></Icon><hotSpot x="16" xunits="pixels" y="16" yunits="insetPixels"/></IconStyle>
<LabelStyle><scale>1.1</scale></LabelStyle>
</Style>

<StyleMap id="icon-ci-1">
<Pair><key>normal</key><styleUrl>#icon-ci-1-normal</styleUrl></Pair>
<Pair><key>highlight</key><styleUrl>#icon-ci-1-highlight</styleUrl></Pair>
</StyleMap>

<Folder>
<name>ehime</name>

 <?
 let n = getLastRow();
 for(var i=0;i<n;i++){
 if(!getRowData(i)[0]) continue;
 ?>

<Placemark>
<name><?= getRowData(i)[1] ?></name>
<description><![CDATA[場所: <?= getRowData(i)[1] ?><br>eNB-LCID: <?= getRowData(i)[4] ?>]]></description>
<styleUrl>#icon-ci-1</styleUrl>
<ExtendedData>
<Data name="eNB-LCID"><value><?= getRowData(i)[4] ?></value></Data>
</ExtendedData>
<Point><coordinates><?= getRowData(i)[3] ?>,<?= getRowData(i)[2] ?>,0</coordinates></Point>
</Placemark>

<? } ?>

</Folder>
</Document>
</kml>

結果

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
    <Document>
        <name>楽天モバイル</name>
        <description/>

        <Style id="icon-ci-1-normal">
            <IconStyle>
                <scale>1.1</scale>
                <Icon>
                    <href>images/icon-1.png</href>
                </Icon>
                <hotSpot x="16" xunits="pixels" y="16" yunits="insetPixels"/>
            </IconStyle>
            <LabelStyle>
                <scale>0</scale>
            </LabelStyle>
        </Style>

        <Style id="icon-ci-1-highlight">
            <IconStyle>
                <scale>1.1</scale>
                <Icon>
                    <href>images/icon-1.png</href>
                </Icon>
                <hotSpot x="16" xunits="pixels" y="16" yunits="insetPixels"/>
            </IconStyle>
            <LabelStyle>
                <scale>1.1</scale>
            </LabelStyle>
        </Style>

        <StyleMap id="icon-ci-1">
            <Pair>
                <key>normal</key>
                <styleUrl>#icon-ci-1-normal</styleUrl>
            </Pair>
            <Pair>
                <key>highlight</key>
                <styleUrl>#icon-ci-1-highlight</styleUrl>
            </Pair>
        </StyleMap>

        <Folder>
            <name>ehime</name>


            <Placemark>
                <name>今治市立花2丁目</name>
                <description>場所: 今治市立花2丁目&lt;br&gt;eNB-LCID: 737335-1,2,3</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737335-1,2,3</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>132.999271,34.052319,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市中寺①</name>
                <description>場所: 今治市中寺①&lt;br&gt;eNB-LCID: 737331-1,2,3</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737331-1,2,3</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>132.991341,34.037485,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市桜井4丁目</name>
                <description>場所: 今治市桜井4丁目&lt;br&gt;eNB-LCID: 737342-7,8,9</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737342-7,8,9</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>133.041459,34.017402,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市阿方①</name>
                <description>場所: 今治市阿方①&lt;br&gt;eNB-LCID: 737354-4,5,6</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737354-4,5,6</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>132.9708406,34.0611983,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市波止浜</name>
                <description>場所: 今治市波止浜&lt;br&gt;eNB-LCID: 737346-7,8,9</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737346-7,8,9</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>132.971486,34.098534,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市登畑</name>
                <description>場所: 今治市登畑&lt;br&gt;eNB-LCID: 737352-10,11,12</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737352-10,11,12</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>133.016295,34.019522,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市北日吉町1丁目</name>
                <description>場所: 今治市北日吉町1丁目&lt;br&gt;eNB-LCID: 737359-1,2,3</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737359-1,2,3</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>132.9892611,34.06289884,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市菊間町種</name>
                <description>場所: 今治市菊間町種&lt;br&gt;eNB-LCID: 737360-7,8</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737360-7,8</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>132.872497,34.05439287,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市喜田村1丁目</name>
                <description>場所: 今治市喜田村1丁目&lt;br&gt;eNB-LCID: 737360-1,2,3</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737360-1,2,3</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>133.0195108,34.0455434,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市拝志</name>
                <description>場所: 今治市拝志&lt;br&gt;eNB-LCID: 737360-4,5,6</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737360-4,5,6</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>133.0272994,34.04096706,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市古国分1丁目</name>
                <description>場所: 今治市古国分1丁目&lt;br&gt;eNB-LCID: 737344-10,11</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737344-10,11</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>133.0362018,34.03209681,0</coordinates>
                </Point>
            </Placemark>


            <Placemark>
                <name>今治市中寺②</name>
                <description>場所: 今治市中寺②&lt;br&gt;eNB-LCID: 737331-4,5,6</description>
                <styleUrl>#icon-ci-1</styleUrl>
                <ExtendedData>
                    <Data name="eNB-LCID">
                        <value>737331-4,5,6</value>
                    </Data>
                </ExtendedData>
                <Point>
                    <coordinates>132.9987931,34.031775,0</coordinates>
                </Point>
            </Placemark>


        </Folder>
    </Document>
</kml>