gasでgoogleフォームを使わずにスプレッドシートから引っ張ってきた選択肢で動的プルダウンを作りたい。

前提・実現したいこと

素人質問で失礼します。

現在私はgasを利用してgoogleフォームを使わない形で営業マン用の社内システムを作成することになりました。

私はプログラミングに関して全くの素人であり逐一ググりながらコーディングを進めているのですが、かなり初歩的な段階で詰まってしまっているのでお知恵を貸していただければ幸いです。

実現したいことといたしましては、１つ目のプルダウンで各社員を選択すると、２つ目のプルダウンでその社員が担当しているクライアントだけが表示されるという２段界構成のプルダウンです。

社員と担当クライアントを選択したら別のスプレッドシートに出力するという

①1つめのプルダウンで各社員を選択する
②選択された社員が担当しているクライアントをスプレッドシートに探しに行く
③スプレッドシートに記載されている数社が2つ目のプルダウンに格納される
④選択された社員と担当クライアントをスプレッドシートに出力する

という流れになると思うのですが、②と③について全く出来ていません。

該当のソースコード

gs
1function doGet() {
2  return HtmlService.createTemplateFromFile('index').evaluate();
3}
4
5function doPost(e) {
6  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
7  var date = new Date();
8  //parameterはobject型なのでstring型に変換
9  var staffname = e.parameter.staffname.toString();
10  sheet.appendRow([date,staffname]);
11  return HtmlService.createTemplateFromFile('index').evaluate();
12}

html
1<!DOCTYPE html>
2<html>
3  <head>
4    <base target="_top">
5  </head>
6  <body>
7    <form action="https://script.google.com/macros/s/AKfycbzOlmWPUQv2hUjbk3XAM6u3_YxCf9JKzRRzxbyjeSEq1mfbq34/exec" method="post">
8      <select name="staffname" onchange="getCompany()">
9       <option value="山田">山田</option>
10       <option value="佐藤">佐藤</option>
11      </select>
12      <input type="submit" value="確定">
13    </form>
14  </body>
15</html>

行動規範の内容に同意します

回答1件

javascript
1//code.gs
2const doGet = e => {
3    const template = HtmlService.createTemplateFromFile("246345");
4    template.assign = JSON.stringify(buildJson());
5    return template.evaluate();
6}
7const buildJson = () => SpreadsheetApp.openById("id").getSheetByName("シート1").getDataRange().getValues().reduce((a,c)=> a[c[0]] ? {...a,[c[0]]:a[c[0]].concat(c[1])}:{...a,[c[0]]:[c[1]]},{});

html
1//246345.html\
2<!DOCTYPE html>
3<html>
4<head>
5    <base target="_top">
6</head>
7<body>
8<form action="https://script.google.com/macros/s/AKfycbzOlmWPUQv2hUjbk3XAM6u3_YxCf9JKzRRzxbyjeSEq1mfbq34/exec" method="post">
9    <select name="staffname" id="staffname" onchange="selectStaff(this)"></select>
10    <select name="company" id="company"></select>
11    <input type="submit" value="確定">
12</form>
13</body>
14<script>
15    var dat = JSON.parse(<?= assign ?>);
16    window.onload = () => {
17        Object.keys(dat).forEach(n =>{
18            const o = document.createElement("option")
19            o.value = n;
20            o.text = n;
21            document.getElementById("staffname").appendChild(o);
22            selectStaff(document.getElementById("staffname"));
23        });
24    }
25    function selectStaff(sel) {
26        const companies = dat[sel.value];
27        if(!companies) return;
28        const target = document.getElementById("company");
29        target.options.length = 0;
30        companies.forEach(c => {
31            const o =  document.createElement("option")
32            o.value = c;
33            o.text = c;
34            target.appendChild(o);
35        });
36    }
37</script>
38</html>