スプレッドシートのデータをGASでウェブアプリとして出力、抽出、ソートしたい

前提

プログラムど素人です。
GASでウェブアプリを作っています。
右も左も分からない状態から、ほぼコピペでなんとか手をつけてみましたが、
どうにもつまずいてしまい、投稿させていただきます。

HTMLのテーブルのデータを検索（フィルタリング）して、
該当のデータを抽出して、「検索ワードに紐づいているデータ」をソートして、
重複するデータに色付けするウェブアプリを作りたいと考えています。

具体的には、
複数人対戦のゲームの1試合ごとのメンバー4人の名前(ID)のデータベースをGoogleスプレッドシートにまとめて、
それをHTMLに出力して、上記の処理をかけるという内容となります。

スプレッドシートのデータを出力、検索抽出まではなんとかできましたが、
それ以降の処理の方法が分からない状態です。

以下、おかしな点や不明な点など多々あるかもしれませんが、
お力添えいただきたく存じます。

実現したいこと

データベースのテーブルからの検索結果をソートして別のテーブルや結果表示欄に出力（検索ワードを除いて出力）
ソート、出力したデータの重複するデータ（テキスト又は背景）に色をつける
可能であれば検索前のデータベースは非表示にしたい。（検索結果のみを出力できれば望ましい）
テキストボックスのサイズやデザインをカスタマイズしたい。（見つけたcssのテンプレートをうまく適用させることができず）

制作中のソースコード①

gs
1
2function valsget() {
3  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
4　let vals = ss.getDataRange().getValues();
5  Logger.log(vals); 
6  return vals;
7}
8
9
10function doGet(){
11  let hotpt = HtmlService.createTemplateFromFile('index').evaluate();
12  hotpt.setTitle(Sample');
13  return hotpt;
14}
15
16
17function valsget() {
18  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
19  let vals = ss.getDataRange().getValues();
20  vals.shift();
21  return vals;
22}
23

制作中のソースコード②

html
1<!DOCTYPE html>
2<html>
3  <head>
4    <base target="_top">
5    <?!= HtmlService.createHtmlOutputFromFile('css').getContent(); ?>
6  </head>
7  <body>
8  <section class="container">
9  <div class="cp_iptxt">
10  <input type="search" size="25" class="light-table-filter" data-table="order-table" placeholder="ここにプレイヤー名を入力"  />  
11  <i class="fa fa-user fa-lg fa-fw" aria-hidden="true"></i>
12  </div>
13
14    <table class="order-table" id="tbl" border="1">
15      <tr>
16        <th>Player 1</th>
17        <th>Player 2</th>
18        <th>Player 3</th>
19        <th>Player 4</th>
20      </tr>
21    </table>
22  </section>
23  </body>
24</html>
25
26
27
28
29<!--jQueryのCDN読み込み-->
30<script
31
32  src="https://code.jquery.com/jquery-3.6.0.slim.min.js"
33  integrity="sha256-u7e5khyithlIdTpu22PHhENmPcRdFiHRjhAuHcs05RI="
34  crossorigin="anonymous">
35
36</script>
37
38
39
40<!--クライアント側javascript-->
41
42<script>
43
44  window.onload = function(){
45
46    google.script.run.withSuccessHandler(function(dt){　//dtにvalgetの取得値を渡す
47      for(let i =0;i<dt.length;i++){   　//dtの配列の数だけ繰り返し
48        let $tr = $('<tr></tr>');        //jqueryオブジェクトでtr作成
49        $tr.append($('<td></td>').text(dt[i][0])); //trにtdを追加していく
50        $tr.append($('<td></td>').text(dt[i][1])); 
51        $tr.append($('<td></td>').text(dt[i][2]));
52        $tr.append($('<td></td>').text(dt[i][3])); 
53
54        $('#tbl').append($tr);　　　//出来上がったtrをtableに行追加       
55      }     
56    }).valsget();
57  }
58
59</script>
60
61
62
63
64<!--検索javascript-->
65<script>
66
67(function(document) {
68	'use strict';
69
70	var LightTableFilter = (function(Arr) {
71
72		var _input;
73
74		function _onInputEvent(e) {
75			_input = e.target;
76			var tables = document.getElementsByClassName(_input.getAttribute('data-table'));
77			Arr.forEach.call(tables, function(table) {
78				Arr.forEach.call(table.tBodies, function(tbody) {
79					Arr.forEach.call(tbody.rows, _filter);
80				});
81			});
82		}
83
84		function _filter(row) {
85			var text = row.textContent.toLowerCase(), val = _input.value.toLowerCase();
86			row.style.display = text.indexOf(val) === -1 ? 'none' : 'table-row';
87		}
88
89		return {
90			init: function() {
91				var inputs = document.getElementsByClassName('light-table-filter');
92				Arr.forEach.call(inputs, function(input) {
93					input.oninput = _onInputEvent;
94				});
95			}
96		};
97	})(Array.prototype);
98
99	document.addEventListener('readystatechange', function() {
100		if (document.readyState === 'complete') {
101			LightTableFilter.init();
102		}
103	});
104
105})(document);
106
107
108
109</script>
110
111

制作中のソースコード③

css
1<style>
2
3table {
4    border-collapse:  collapse; /* セルの線を重ねる */
5}
6 
7tr:nth-child(odd) {
8    background-color:  #ffffff;    /* 背景色指定 */
9}
10 
11th,td {
12    padding: 5px 5px;          /* 余白指定 */
13}
14

試したこと

ここまではひたすらググって、意味を確認しながらコピペで様々なコードの組み合わせで行いました。
（記述されているコードをすべてを理解できているわけではありません。）
やりたいことをググって試してみましたが、これ以上欲しい情報にたどり着けず、現状進めることができなくなってしまいました。
最初はSQLを使おうとしましたが、難しかったためスプレッドシートを採用しました。

補足情報（FW/ツールのバージョンなど）

データベースは、4カラムに1試合ごとに4人のPlayer名を入力しています。
各試合のデータが入力されるごとに、レコードも増えるというものです。

例えば、以下ようにデータがスプレッドシートにあるとした場合、

ーーーーーー
A　B　C　D
E　F　G　H
I　C　A　J
ーーーーーー

”A”と検索したら以下のような結果を出力したいということです。

ーーーーーー
C　←色掛け
C　←色掛け
B
D
I
J
ーーーーーー

”C”と検索した場合はこのようなイメージです。

ーーーーーー
A　←色掛け
A　←色掛け
B
D
I
J
ーーーーーー

”B”と検索した場合はこのようなイメージです。

ーーーーーー
A
C
D
ーーーーーー

退会済みユーザー

2022/11/06 04:40 編集

開示されている情報では、表示する形式、特に「色付けしなければならない条件」が不明確です。下記のような形式・条件であるという理解でよろしいでしょうか。 ① １回あたりの対戦メンバーは、必ず「４人」であり、それ未満でもその人数を超えることも、絶対にない。（1回あたりの対戦メンバーが２人や3人になったりする場合は絶対にない） ②「データベース」として用いているスプレッドシートには、１行目から、対戦した4名の名前が1行あたりA～D列の4列に記録されており、対戦ごとに行を変えている。1行目からデータの末尾まで、各行4名の対戦記録以外のデータはなく、また途中に空白行もない。 ③ 表示する形式は、「データベース」として用いているスプレッドシートに記録されているすべての対戦者のうち、検索条件に指定したプレイヤー以外の全員の名前を、重複ありで、名前昇順で、１列に並べるものとする。（この点、質問文に記載の「制作中のソースコード②」のHTMLは「4列」を表示する形式になっています。つまり、質問文記載の文章と、ソースコードに食い違いがあるということです） ④ ③のうち、検索条件に指定したプレイヤーと2回以上対戦したことがあるプレイヤーの背景セルに、色を付ける。（検索条件に指定したプレイヤーと2回以上対戦したことがあるプレイヤーが2人以上存在している場合であっても、全員同じ背景色でよい）仮に上記の表示形式・条件が正しいと仮定した場合に、たとえば「B」で検索したら、下記のような結果表示になるはずです。 A A C C D E F G H I J ※4人1組での対戦履歴上、Bと2回以上対戦した人は存在しないため、どこも色掛けしない ---------- もし違う点があれば、上記の形式で具体的に書くか、または・データベースとして使用しているスプレッドシートや、イメージする表示結果をキャプチャして質問欄に貼り付ける・作成中のスプレッドシートの公開リンクを貼り付けるなどして、前提情報を何も持っていない人に対しても、内容が正確に把握できるように示してください。

nightl

2022/11/06 07:04 編集

qnoirさん閲覧、コメントありがとうございます。素人故に説明不足があり申し訳ありません。 ① １回あたりの対戦メンバーは、必ず「４人」であり、それ未満でもその人数を超えることも、絶対にない。（1回あたりの対戦メンバーが２人や3人になったりする場合は絶対にない） →　はい、1試合につき必ず4人のデータが入力されます。 ②「データベース」として用いているスプレッドシートには、１行目から、対戦した4名の名前が1行あたりA～D列の4列に記録されており、対戦ごとに行を変えている。1行目からデータの末尾まで、各行4名の対戦記録以外のデータはなく、また途中に空白行もない。 →　はい、その通りです。ちなみにこのスプレッドシートのデータはGoogleフォームなどから誰でも入力できるように考えています。 ③ 表示する形式は、「データベース」として用いているスプレッドシートに記録されているすべての対戦者のうち、検索条件に指定したプレイヤー以外の全員の名前を、重複ありで、名前昇順で、１列に並べるものとする。（この点、質問文に記載の「制作中のソースコード②」のHTMLは「4列」を表示する形式になっています。つまり、質問文記載の文章と、ソースコードに食い違いがあるということです） →　説明がわかりにくくて申し訳ございません。確かにHTMLは4列としていますので、「実現したいこと」の欄に、"別のテーブルや結果表示欄に出力"と記載しました。検索（フィルタ）に応じて記載済みの4列のテーブルを可変させるのはは難しいのかなと思い、ソートしたデータを別のテーブルとして既存のテーブルの右側あたりに表示できればと考えていました。 ④ ③のうち、検索条件に指定したプレイヤーと2回以上対戦したことがあるプレイヤーの背景セルに、色を付ける。（検索条件に指定したプレイヤーと2回以上対戦したことがあるプレイヤーが2人以上存在している場合であっても、全員同じ背景色でよい） →　その通りです。重複するプレイヤー（2回以上対戦したプレイヤー）が2人以上いる場合は、プレイヤーごとに色を変えられると望ましいですが、複雑な処理になるのかと思い、実装後に詰められればと思っていました。 ※よく見たら投稿した検索結果の出力イメージがおかしかったです。（間違っていたので本文も修正します）正しくは、以下のデータに対してーーーーーー A　B　C　D E　F　G　H I　C　A　J ーーーーーー Aと検索した場合、以下のようにしたいです。（E,F,G,HはAと対戦していないため、検索をかけると抽出されないようになっているかと思います）ーーーーーー B C　←色掛け C　←色掛け D I J ーーーーーー上記の抽出ソートされたデータをさらに以下のように、願わくば重複するデータを優先して上にソートできると良いと考えています。ーーーーーー C　←色掛け C　←色掛け B D I J ーーーーーー Bと検索した場合は、以下のような出力となります。ーーーーーー A C D ーーーーーーご一読いただけますと幸いです。

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

回答1件

ベストアンサー

変更が最小限になるようにしている関係で、元と同様つぎはぎ状態ですが、一応下記のようになります。
※「テキストボックスのサイズやデザインをカスタマイズ」についてはすみませんが回答できていません。
検索結果が複数ある場合にも対応できるように、input に入力された文字を名前の一部に含むプレイヤー名を見出しとして表示し、その下に対戦相手を表示するようにしています。

gs
1// 3番目の重複している valsget を削除し、引用符のミスを修正しただけです。
2function valsget() {
3  let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
4　let vals = ss.getDataRange().getValues();
5  Logger.log(vals); 
6  return vals;
7}
8
9function doGet(){
10  let hotpt = HtmlService.createTemplateFromFile('index').evaluate();
11  hotpt.setTitle('Sample');  // 左引用符がなかったのを修正
12  return hotpt;
13}
14

html
1<!DOCTYPE html>
2<html>
3
4<head>
5  <base target="_top">
6  <?!= HtmlService.createHtmlOutputFromFile('css').getContent(); ?>
7</head>
8
9<body>
10  <section class="container">
11    <div class="cp_iptxt">
12      <input type="search" size="25" class="light-table-filter" data-table="order-table" placeholder="ここにプレイヤー名を入力"  />
13      <i class="fa fa-user fa-lg fa-fw" aria-hidden="true"></i>
14    </div>
15
16    <table class="order-table" id="tbl_all" border="1">
17      <tr>
18        <th>Player 1</th>
19        <th>Player 2</th>
20        <th>Player 3</th>
21        <th>Player 4</th>
22      </tr>
23    </table>
24
25    <table class="order-table" id="tbl_filtered" border="1" style="display:'none';">
26      <tr>
27      </tr>
28    </table>
29  </section>
30
31  <!--jQueryのCDN読み込み-->
32  <script src="https://code.jquery.com/jquery-3.6.0.slim.min.js"
33    integrity="sha256-u7e5khyithlIdTpu22PHhENmPcRdFiHRjhAuHcs05RI=" crossorigin="anonymous">
34
35  </script>
36
37  <!--クライアント側javascript-->
38  <script>
39    // 全対戦データを格納するグローバル変数
40    let allData = [];
41    // 全対戦者名を格納するグローバル変数
42    let allMembers = [];
43    // 全対戦データを格納する。
44    window.onload = function(){
45      google.script.run.withSuccessHandler(function(dt){　//dtにvalgetの取得値を渡す
46        allData = dt;
47        allMembers = [...new Set(allData.flat())];
48        getAllMatches();
49      }).valsget();
50    }
51
52    /*全対戦テーブルを構築する*/
53    function getAllMatches() {
54      for(let i =0;i<allData.length;i++){   　// allDataの配列の数だけ繰り返し
55        let $tr = $('<tr></tr>');        // jqueryオブジェクトでtr作成
56        $tr.append($('<td></td>').text(allData[i][0])); // trにallDataのアイテムを追加していく
57        $tr.append($('<td></td>').text(allData[i][1])); 
58        $tr.append($('<td></td>').text(allData[i][2]));
59        $tr.append($('<td></td>').text(allData[i][3])); 
60
61        $('#tbl_all').append($tr);　　　//出来上がったtrをtableに行追加       
62      }     
63    }
64
65  </script>
66
67  <!--検索javascript-->
68  <script>
69  (function(document) {
70    const LightTableFilter = (function(Arr) {
71
72      //input に文字入力されたときに発火
73      function _onInputEvent(e) {
74        const _input = e.target;
75        const tableAll = document.getElementById('tbl_all');
76        const tableFiltered = document.getElementById('tbl_filtered');
77
78        // inputが空白なら対戦表を表示、検索結果非表示
79        // 空白でないなら対戦表非表示、検索結果表示
80        if (_input.value !== '') {
81          tableAll.style.display = 'none';
82          tableFiltered.style.display = '';
83          buildFilteredTables(_input.value );
84        } else {
85          tableAll.style.display = '';
86          tableFiltered.style.display = 'none';
87        }
88      }
89
90      /*
91        検索結果のテーブルを構築
92      */
93      function buildFilteredTables(inputValue) {
94        const tbl = $('#tbl_filtered');
95        tbl.empty();
96        const members = allMembers.filter(e => String(e).toLowerCase().includes(inputValue.toLowerCase()));
97        
98        for (const member of members) {
99          const matches = getSortedMatches(member, allData);
100          tbl.append($('<tr></tr>').append($('<td></td>').text(member).attr('class', 'style-td-membername')));
101          for (const match of matches) {
102            if(match[1] >= 2) {
103              tbl.append($('<tr></tr>').append($('<td></td>').text(match[0]).attr('class', 'style-td-backfill')));
104            } else {
105              tbl.append($('<tr></tr>').append($('<td></td>').text(match[0])));
106            }
107          }
108        }
109      }
110
111      /*
112        指定したプレイヤーの対戦相手を対戦回数降順に抽出。
113      */
114      function getSortedMatches(target, allMatches) {
115        const matchRecords = []; // 検索対象を除くすべてのプレイヤーを記録する配列。
116        const counts = {}; // ターゲットとの対戦回数を格納するオブジェクト。(キー：対戦相手の名前、値：対戦回数)
117
118        allMatches.forEach(oneMatch => {
119          if (oneMatch.includes(target)) {
120            // 対戦メンバーから、検索対象を除外する。
121            const players = oneMatch.filter(player => player !== target);
122            // プレイヤーごとに対戦回数を加算する。
123            for (const player of players) {
124              if (counts.hasOwnProperty(player)) counts[player] += 1;
125              else counts[player] = 1;
126              // プレイヤーを追加
127              matchRecords.push(player);
128            }
129          }
130        })
131
132        return matchRecords.map(e => [e, counts[e]]).sort((a, b) => {
133          if (a[1] > b[1]) return -1;
134          if (a[1] < b[1]) return 1;
135          if (a[0] > b[0]) return 1;
136          if (a[0] < b[0]) return -1;
137          return 0;
138        });
139
140      }
141
142      return {
143        // inputにoninputイベントを登録（初回のみ）
144        init: function() {
145          const inputs = document.getElementsByClassName('light-table-filter');
146          Arr.forEach.call(inputs, function(input) {
147            input.oninput = _onInputEvent;
148          });
149        }
150      };
151    })(Array.prototype);
152    /* End of  LightTableFilter*/
153
154
155    document.addEventListener('readystatechange', function() {
156      if (document.readyState === 'complete') {
157        LightTableFilter.init();
158      }
159    });
160  })(document);
161  </script>
162</body>
163
164</html>

css
1<style>
2
3table {
4    border-collapse:  collapse; /* セルの線を重ねる */
5}
6 
7 
8th,td {
9    padding: 5px 5px;          /* 余白指定 */
10}
11
12
13.style-td-membername {
14  padding: 10px 20px;
15  font-weight: bold;
16}
17
18.style-td-backfill {
19  background-color: #ffff00; /* 背景色指定 */
20}
21</style>

投稿2022/11/06 15:10

編集2022/11/07 11:40

退会済みユーザー

総合スコア0

nightl

2022/11/06 16:31

本当にありがとうございます。思っていた通りの動作で感無量です。細かく記載していただいたコメントを参考に、何度もコードを読み返し、理解を深めていきたいと思います。これで最低限やりたいことが実現できているので、デザインなども含めてここから少しずつ改変していければと思います。当方でも勉強し、できる限りの努力はしていく所存ですが、力及ばずでまた機会がございましたら、お力添えのほどよろしくお願い致します。

退会済みユーザー

2022/11/07 11:42

コメントどうもです。がんばってください。

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